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

[BattleshipCobra]

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!

[Former Member]

A video screencast would be great.

[BattleshipCobra]

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!

[BattleshipCobra]

Hey Kaye,

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

Thanks!

Mike

[former_member488878]

Thanks! that helped a lot.

Regards!

Muhammad Noman Sajid

 

[IldiW]

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

[BattleshipCobra]

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!

[IldiW]

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!

[IldiW]

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

I'll give that a go!

thanks! 🙂

[BattleshipCobra]

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

[IldiW]

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

[BattleshipCobra]

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