Two UDV functions in a single UDV

[jeff_putnam]

Hello experts,

I have a UDV for a UDF that uses a simple query to provide a list of options for the field.  In addition to that I would like to automatically select a particular option based on the value of another UDF (if any).

My simple query is this:

     SELECT distinct SuperCategory from Categories order by supercategory

This provides a list of Categories for the user to choose from, ie.

     1.Health Care

     2.Education

     3.Retail

     4.Government

     etc.

This works fine to provide a list of options for the U_SuperCategory field.  Now I want '2.Education' to be automatically selected when another UDF (U_I_College) is filled in but still have the list available in U_SuperCategory if U_I_College is not filled in.

It's like I require two UDV's for the same field which you can't have, but I was hoping for a clever query that would do both.  I've tried things like the following:

     select case when $[ORDR.u_i_college] is not null then '2.Education' else (select distinct SuperCategory from Categories order by supercategory) END

It's hard to test UDV queries outside of a form but a modified version of this in the Query Generator generated something like "Query returns more than one value".

Any suggestions?

[kothandaraman_nagarajan]

Hi,

If you got the answer, please close the thread by marking helpful/correct answer.

Thanks & Regards,

Nagarajan

[jeff_putnam]

Thanks for all the answers, but I already knew my query doesn't work and why.  My question was, is there a way to do what I want?  That is, provide a list for the user to select from in a UDF, but sometimes automatically set it to one of the list values based on another UDF.

[kothandaraman_nagarajan]

Hi Jeff,

Not possible to list of value and automatic value for same UDF. You have either search in existing user defined field nor search existing user defined filed by saved query.

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

Case can only be used for single value result. Therefore, your goal may not be achievable through this grammar. From logic point of view, this is not a normal selection unless you can designate all different values with unique identifier for each of them.

Thanks,

Gordon

[KennedyT21]

Hi Jeff Putnam

select case when $[ORDR.u_i_college] is not null then '2.Education'

else (select distinct SuperCategory from Categories order by supercategory) END

The Subquery retrieves more than one value so better dont try with the Auto refresh in Fms..

Hope helpful

Regards

Kennedy

[kothandaraman_nagarajan]

Hi Jeff,

message meaning, the output for the above FMS containe two values.

Please check with FMS.