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"
Solved
Filtered lookup
- Warrior
Best answer by Anne Buit
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:
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.
View originalThis 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:
code:
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.
This topic has been closed for comments
+5Hi 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:
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.
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:
code:
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.