Skip to Content
author's profile photo Former Member
0
Former Member

Show UDT description in query

Posted on Sep 16, 2008 at 11:52 AM 814 Views

I have seen a couple of other post like this but none that quite answer my question. We populate a UDF (County) from a set of valid values which create a drop down list. The UDF correctly has the description and not the code. However a simple query (below) returns the value number and not the description

SELECT T1.CardName , T0.U_city , T0.U_County

FROM OCPR T0 INNER JOIN OCRD T1 ON T1.CardCode = T0.CardCode

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2008 at 02:25 PM
    2

    Hi Ralph

    Try the following:

    SELECT T1.CardName , T0.U_city , T0.U_County, T2.Descr

    FROM OCPR T0 INNER JOIN OCRD T1 ON T1.CardCode = T0.CardCode, UFD1 T2, CUFD T3

    WHERE T0.U_City = T2.FldValue and T2.TableID = 'OCPR' and T2.FieldID = 2 and T3.TableID = 'OCPR' and T3.FieldID = 2

    Replace FieldID 2 with the ID of the T0.U_city field. Look in CUFD to get the internal ID for the field.

    The only problem is you have 2 UDF's, so you will have to find a way of bringing both Description Fields back, possibly using a UNION clause or something along those lines. Test with just one first and see if it returns the correct values.

    Kind regards

    Peter Juby

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2008 at 12:17 PM
    0

    Hi Ralph

    SAP Business One automatically displays the name and not code for the valid values. There are 2 tables you can look at. Firstly CUFD and secondly UFD1. The code and name for valid values is stored in UFD1.

    Kind regards

    Peter Juby

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Sep 16, 2008 at 02:05 PM

      Perhaps I've missed something. If I try to look at CUFD or UFD1 in report generator it does not recognise the table but brings up a blank 'choose table' box. I can see the tables in SQL manager but not from within B1.

  • author's profile photo Keith Taylor
    Keith Taylor
    Posted on Sep 16, 2008 at 02:37 PM
    0

    This is an example of how to display the UDF field description instead of the UDF field Code. In my example the udf field is in the OITM file

    How to display the name instead of the codes for user defined fields:

    SELECT T0.ItemCode, (select T1.[Descr] from UFD1 T1 join dbo.[CUFD] T2 on T2.[TableId] = T1.[TableId] and T2.[FieldId] = T1.[FieldID] and T2.[TableId] = 'OITM' and T2.[AliasId] = 'YourUdfFieldName' where T1.[FldValue] = T0.[U_YourUdfFieldName]) FROM [dbo].[OITM] T0

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 16, 2008 at 02:44 PM
    0

    Hi Keith

    Ralph is looking for the description of the valid values and not the UDF itself. It seems quite involved though to achieve this as the UFD1 only links to CUFD and all the fields are using internal references (numeric). The sample I sent him works for one UDF and not 2. Any ideas?

    Kind regards

    Peter Juby

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Sep 17, 2008 at 03:32 PM

      I'm just about there now. This is the full query:

      SELECT T1.CardCode AS 'BP Code', T1.CardName AS 'BP Name', T0.Name AS 'Contact Person Name', T0.U_MS1 AS 'Mailshot 1', T0.U_Add1 AS 'Address1', T0.U_Add2 AS 'Address 2', T0.U_add3 AS 'Address 3', T0.U_city AS 'City', T2.Descr AS 'County', T0.U_Postcode AS 'Postcode'

      FROM OCPR T0 INNER JOIN OCRD T1 ON T1.CardCode = T0.CardCode INNER JOIN UFD1 T2 ON T0.U_County = T2.IndexID, CUFD T3

      WHERE (T2.TableID = 'OCPR' and T2.FieldID = 10 and T3.TableID = 'OCPR' and T3.FieldID = 1) AND T0.U_MS1 = N'FDM Target'

      Joining the U_County with the IndexID of UFD1 did the trick. I'm actually missing a few results compared to the original. I've just got to find what's missing from these BP's.

      Thanks for all your help

    Comment on This Answer
  • author's profile photo Mike Taylor
    Mike Taylor
    Posted on Dec 10, 2014 at 06:04 AM
    0

    I just wrote a blog article on how to solve this exact problem: http://goo.gl/xuZZtV

    Just join UFD1 and you can pull the description that way,

    Mike

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.