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