Show UDT description in query

[Former Member]

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

[Former Member · Accepted solution]

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

[BattleshipCobra]

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

[Former Member]

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

[keith_taylor2]

My query does return the description of the values - I use this all the time

[Former Member]

Keith I tried yours but got all blank results:

SELECT T0.CardCode, (select T1.Descr from UFD1 T1 join dbo.CUFD T2 on T2.TableId = T1.TableId and T2.FieldId = T1.FieldID and T2.TableId = 'OCPR' and T2.AliasId = 'U_County' where T1.FldValue = T0.U_County)

FROM dbo.OCPR T0

We have created UDF's against contacts rather than their companies (better for mailings etc). We have Address1, Address2, City, Post code etc. These are all just alphanumeric fields and these display fine in all queries. We were just trying to be smarter with counties as people tend to write 'Oxfordshire' 'Oxon' and of course mis-spell things. Our thinking was to create pre defined values so our future searches would be accurate.

Peter, as you can see we only need to pull one name from the 2 tables you mentioned. Using your solution:

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 T2.TableID = 'OCPR' and T2.FieldID = 10 and T3.TableID = 'OCPR' and T3.FieldID = 10

I am about there but it returns multiple results for each BP against each and every county rather than just the one which was selected.

[Former Member]

You can try this modified query:

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

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

WHERE T2.TableID = 'OCPR' and T2.FieldID = 10 and T3.TableID = 'OCPR' and T3.FieldID = 10

ORDER BY T1.CardName

Thanks,

Gordon

[Former Member]

With the query I had it returned 413,317! with the distinct command added it returns 221,160. I haven't looked in detail where the difference is but it is still returning each BP against every county. I'm guessing this is because I don't have a join for CUFD or UFD1

[Former Member]

If you don't want each BP show every time, why not take it out. The result will be much less here:

SELECT DISTINCT T0.U_city, T0.U_County, T2.Descr 
FROM  CUFD T3 inner join UFD1 T2 ON T2.TableID = 
T3.TableID AND T2.FieldID = T3.FieldID
WHERE T2.TableID = 'OCPR'  AND T2.FieldID = 10

Thanks,

Gordon

[Former Member]

Sorry, I think you've missed the point. I am trying to create a BP list which has contact name and address from a range of UDF's we have against each contact. These are all manually entered apart from the County which users enter from a drop down list. I had taken out most of the other required fields so that the query looked easier on the eye for this forum. There is an additional 'WHERE' statement which selects certain BP's interested in one of our product lines.

The solutions so far at least show the county description but unfortunately repeat each BP for each and every county.

Any thoughts on how I can join in the UFD tables to prevent this?

[Former Member]

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

[keith_taylor2]

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

[Former Member]

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

[Former Member]

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.