cancel
Showing results for 
Search instead for 
Did you mean: 

USER DEFINED FIELD

Former Member
0 Kudos

I have created several User Defined Fields (UDF) in my SO with some having pull down menus that assign a Value (number) and Description (name) that I fill in each SO when created. When I run a Query to get this information, I am only getting the Value but wish to have the Description appear instead. I believe that this has to do with an Inner Join but can not figure it out on how to do. Can somebody help me figure this out? Thank you.

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

null

Edited by: Frank Romano on Apr 30, 2009 9:13 PM

Edited by: Frank Romano on Apr 30, 2009 9:13 PM

Former Member
0 Kudos

null

Edited by: Frank Romano on Apr 30, 2009 3:58 PM

Edited by: Frank Romano on Apr 30, 2009 3:58 PM

former_member204969
Active Contributor
0 Kudos

To display the description values, you can use the tables UDF1 and CUDF, like this code:

SELECT T0.DocNum,
 (Select T1.Descr
 From UFD1 T1
  join dbo.CUFD T2 on T2.TableId = T1.TableId
  and T2.FieldId = T1.FieldID
  and T2.TableId = 'ORDR'
  and T2.AliasId = 'udfname'
 where T1.FldValue = T0.U_udfname)
 FROM dbo.ORDR T0

Former Member
0 Kudos

Istvan, I tried your SQL script and it still does not pick up the description as the value is still appearing in the query output. Any other suggestions?

former_member204969
Active Contributor
0 Kudos

Would you show your SQL here?

Former Member
0 Kudos

Here it is

SELECT T0.DocNum, T0.CardCode, T0.CardName, T0.U_ISGI_pono, T0.U_ISGI_prjd, U_PriceSts, T0.U_ProdctMgr,

(Select T1.Descr

From UFD1 T1

join dbo.CUFD T2 on T2.TableId = T1.TableId

and T2.FieldId = T1.FieldID

and T2.TableId = 'ORDR'

and T2.AliasId = 'U_ProdctMgr'

where T1.FldValue = T0.U_ProdctMgr)

FROM dbo.ORDR T0

former_member204969
Active Contributor
0 Kudos

Try this:

SELECT T0.DocNum, T0.CardCode, T0.CardName, T0.U_ISGI_pono, T0.U_ISGI_prjd, U_PriceSts, T0.U_ProdctMgr,
(Select T1.Descr
From UFD1 T1
join dbo.CUFD T2 on T2.TableId = T1.TableId
and T2.FieldId = T1.FieldID
and T2.TableId = 'ORDR'
and T2.AliasId = 'ProdctMgr'
where T1.FldValue = T0.U_ProdctMgr)
FROM dbo.ORDR T0

(The aliasid is without U_ !)

Former Member
0 Kudos

Istvan, thanks this worked! One more question, what happens if I have more than one UDF field that I want to get the Description and not the Value? I tried copying the SQL script and editing it for the other UDF fields but it did not work. Suggestion?

former_member204969
Active Contributor
0 Kudos

It should work, if you copy and edit carefully the full subquery (the Select inside the brackets) !

Former Member
0 Kudos

It works. Thank you so much.

Former Member
0 Kudos

Hi Frank,

To solve this problem, you should enter the description too under the value column when you're creating a UDF.

for example you should do this:

Value Description

-


ABC ABC

DEF DEF

instead of:

Value Description

-


01 ABC

02 DEF

Try this, and then execute your query again, it should be ok. Hope it helps!

Cheers,

serene

Former Member
0 Kudos

Hi

Please take a look at this thread

Other work around is hardcoded if you have less.

Hope this helps

Bishal

Edited by: Bishal Adhikari on Apr 29, 2009 12:19 PM