Skip to Content
Author's profile photo Mike Taylor

How to Pull UDF Dropdown Descriptions into Queries / Crystal Reports SAP Business One

When you make a dropdown list in SAP the data is stored as a code in the system. When trying to do an SQL query or Crystal Report you will find that only the code is output so the user reading the query / report doesn’t see the description.

You could do a case statement like this:

SELECT
CASE
WHEN T0.[U_UDF] = ‘A’ THEN ‘Alligator’
WHEN T0.[U_UDF] = ‘B’ THEN ‘Bumblebee’
WHEN T0.[U_UDF] = ‘C’ THEN ‘Cat’
ELSE NULL END

But this isn’t very pretty and frankly is annoying if you have more than three or four options. Same with Crystal Reports; you would have to manually write a formula and have this evaluate while the report is generating which is annoying.

There is a simple solution for this which is table UFD1. This is not listed in the query manager so you would want to play around with it in SQL Management Studio to see the structure:

UFD1.TableID – NVARCHAR(20) – The table name where the UDF is located (in my example I will use a UDF called UDF on the OCRD or BP Master Data table)
UFD1.FieldID – INT(6) – Represents a code correctional to the specific field on the form. Not necessarily ordered 1, 2, 3, 4 since not every field has predefined options. So you need to look at the table to find the specific field you want (in my example it turns out to be 37)
UFD1.IndexID – INT(6) – The order of the predefined value in the UDF combobox.
UFD1.FldValue – NVARCHAR(254) – The code value of the predefined option for the UDF.
UFD1.Descr – NVARCHAR(254) – The description of the value of the predefined option for the UDF. This is what we want!

So the easiest way to find the field you are looking for is to go to SQL Management Studio and run the following query:

SELECT * FROM UFD1

This will give you the list of all the predefined fields. Then you have to filter for the table you are using. In SAP Business One click View >> System Information to turn on the system information viewer then hold your mouse over your new UDF and look at the bottom left of the screen. You should see the appropriate table. In my example it’s on the Business Partner Master Data which is OCRD. But it can be on any form.

Then you want to run:

SELECT * FROM UFD1 WHERE TableID = ‘OCRD’
(replace OCRD with your table name)

Then you will see just a list of UDFs on the specific form you are working with and it should be easy to identify the FieldID of the correct set of results.

Then you run:

SELECT * FROM UFD1 WHERE TableID = ‘OCRD’ AND FieldID = 37
(replace 37 with your actual FieldID)

The results will be just the information need and your selected descriptions should be visible.

Now you want to output this into a query you just have to do a LEFT OUTER JOIN to your main table by the FldValue. Something like:

SELECT
T0.[CardCode]
,T0.[CardName]
,T1.[Descr] ‘My UDF’
FROM
OCRD T0
LEFT OUTER JOIN UFD1 T1 ON T0.[U_UDF] = T1.[FldValue] AND TableID = ‘OCRD’ AND FieldID = 37

Finally you want to restrict the joined table to only come from the OCRD table and the specified field. Make sure you change U_UDF to your UDF field name, the TableID to the table you are working with and the FieldID to the specific field you are working with.

The results will be output but the description of the UDF will be the third column instead of simply the UDF code. Don’t forget to “LEFT OUTER JOIN” the UFD1 table or you will get limited results.

To use in Crystal Reports it would be slightly more tricky but you could do either a Crystal Reports command or an SQL view and join it onto the field in the Database Expert. Comment below if you want me to do a video screencast on how to do this!

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      A video screencast would be great.

      Author's profile photo Mike Taylor
      Mike Taylor
      Blog Post Author

      Hey Kaye, I'll try to do this one as query video #2 on my YouTube channel.  Subscribe to http://youtube.battleshipcobra.com/ where I post my SAP videos.  Thanks for the idea!

      You could also do a UDT, UDO combination which is much easier to manage and to work into a query.  I'll add this to the video!

      Author's profile photo Mike Taylor
      Mike Taylor
      Blog Post Author

      Hey Kaye,

      I did a video on this topic here: https://www.youtube.com/watch?v=6eCTMW_WBCM

      Thanks!

      Mike

      Author's profile photo Muhammad Noman Sajid
      Muhammad Noman Sajid

      Thanks! that helped a lot.

      Regards!

      Muhammad Noman Sajid

       

      Author's profile photo Ildi Wileman
      Ildi Wileman

      Hi Mike,
      I realise this is an old post but I haven't found anything similarly in-depth on this topic, so big thank you for writing it in the first place!

      I have a list of FieldIDs but some of them are missing. When I run the query with no restriction (SELECT * FROM UFD1 WHERE TableID = 'OITM') I get these only so you see how for example 8 and 11 are missing? Needless to say, those must be the ones I need. I know they are on the Item's UDF sidebar but I can't see them here. Any ideas where to look?

      TableID FieldID IndexID FldValue Descr FldDate
      OITM 6 0 1 Web Only NULL
      OITM 6 1 2 Shop Only NULL
      OITM 6 2 3 Both Web and Shop NULL
      OITM 7 0 1 Current NULL
      OITM 7 1 2 Discontinued NULL
      OITM 7 2 3 Clearance NULL
      OITM 7 3 4 Cellar NULL
      OITM 7 4 5 Pending NULL
      OITM 7 5 6 Promotion NULL
      OITM 9 0 1 Racks NULL
      OITM 9 1 2 Floor NULL
      OITM 9 2 3 Shelves NULL
      OITM 9 3 4 Unknown NULL
      OITM 10 0 Y Yes NULL
      OITM 10 1 N No NULL
      OITM 17 0 Y Yes NULL
      OITM 17 1 N No NULL

      Many thanks, Ildi

      Author's profile photo Mike Taylor
      Mike Taylor
      Blog Post Author

      Hi Ildi!  Thanks for the feedback.  Did you check your UDF setup to see if it's maybe linked to a UDT / UDO?  You can see this in the definition of the UDF, might not be from "Valid Values".

      If the values are from a UDT / UDO then you can directly link them via [Code] (usually) and then output the [Name] (usually).

      Don't forget to check out my YouTube channel (http://youtube.battleshipcobra.com) with lots of other stuff.

      Good luck!

      Author's profile photo Ildi Wileman
      Ildi Wileman

      Yes, you're right  - I do have linked tables!

      Can you please explain what you mean by linking them via [Code] and [Name]?

      Long time follower of your YouTube channel - half my SAP B1 knowledge comes from there!

      Author's profile photo Ildi Wileman
      Ildi Wileman

      Ah, did you mean to join the table - that would make sense!

      I'll give that a go!

      thanks! 🙂

      Author's profile photo Mike Taylor
      Mike Taylor
      Blog Post Author

      Hi Ildi, thanks for subscribing 🙂

      To do the links it's just like any table link, the value in your UDF will = the [Code] in the UDT.

      So it would be something like example:

       

      SELECT
          T0.[CardCode]
         ,T0.[CardName]
         ,T1.[Name] AS 'UDF Name'
      
      FROM
          OCRD T0
          LEFT JOIN [YOUR_UDT] T1 ON T0.[YOUR_UDF] = T1.[Code]

      Your UDT will always default to having columns [Code] and [Name].  You can see the values in the Query Generator or by doing the SELECT * FROM [YOUR_UDT].

      The UDT will normally be joined on [Code] when done this way.  Then the [Name] is just the value corresponding to the UDT code value in the UDT.

      Let me know if this helps,

      Mike

      Author's profile photo Ildi Wileman
      Ildi Wileman

      Yes, it works! After using lookups for years to identify the 100s of values in this UDF in our reporting my entire team has a great amount of gratitude to you! 😉

       

      Thanks!

      Ildi

      Author's profile photo Mike Taylor
      Mike Taylor
      Blog Post Author

      Awesome!  Keep in touch on LinkedIn (http://linkedin.battleshipcobra.com/) if you have any more quick questions.