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 BuitView original