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:
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:
,T1.[Descr] ‘My UDF’
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!