Skip to Content
0
Apr 29, 2013 at 07:45 PM

Two UDV functions in a single UDV

58 Views

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?