Filtered lookup

Userlevel 3
Badge +3
Sometimes we want to make a lookup in which you can only pick items that are not already used. Up till now I have been able to come up with three solutions, the first being a view in which the used items are left out, the second being a view in which these items get an 'in-use' flag where a prefilter takes them out of the lookup.

Option 1 is not great since it empties the lookup for all saved records

Option 2 works much better, but as soon as the user edits the record, the lookup responds by going empty (the selected item is no longer available for selection)

Option 3 create a view with a cross join between the source records and the target records, filtered on items that are in use. This allows perfect use with existing records, from them you can pick all available lookup items and keep seeing the selected lookup item. Problem is though that if you create a record the identity is null, now I can make my view include a null value for the key, but then the prefilter checks for "null = null" which is always false. This idea would work great if the prefilter could also check for "null is null".

So my question is, is there a way around this, or is there a way to make prefilters check for "s.key = t.key or s.key is null and t.key is null"

Best answer by Anne Buit 3 May 2019, 15:51

View original

2 replies

Userlevel 7
Badge +5
Hi Pim,

This is quite the challenge that we plan to adress in the future by having table-valued functions as datasources. This would allow for conditional logic to evaluate the incoming filters within the function, for instance a null-check.

Until that time - The last solution is indeed the only solution that seems very close to achieving your goals. Instead of using the identity as filter for the look-up, you can add an expression field:

isnull(t1.identity_column, 0)

Instead of including a record with a null value in the view, you'd include a record with a 0 value. Make sure a the real identity value can never become 0!

Not very elegant, but it does get the job done.
Userlevel 3
Badge +3
Hey Anne,

Thanks a lot. Elegant or not, this takes my solution to a working state.
Good to hear new ideas keep coming that will improve the software.