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.
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".