There are situations in which we have a regular table containing for example all services and a linking table, linking services to for example objects.
When I want to add a service to a record in a third table that already contains an object then I would like to filter on these objects and only be able to select services that are linked to that object.
Now this is very easy by making this linking table the lookup table for the service. Sometimes we also use views as a lookup, where the view enables us to filter on multiple complex conditions for services to select from.
Now when a service suddenly doesn't comply to these conditions and would drop out of the view then the display of the service would be empty. Afterall the service isn't present in the lookup table anymore. However I would very much like this service to still be displayed correctly.
I would like to name one lookup (the view) to select a service from and one lookup (the regular table containing all services) to use as the source for the display-value. In this case the service can disappear from the view, but still be visible in my application, possibly with a tooltip or color signalling that there is something that needs attention.
Have a lookup for display and a lookup for input.
Windows GUI
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
By using (locked) prefilters on the view instead of filtering in the view definition, you can probably already achieve this. The user interfaces only apply the lookup prefilters when retrieving values for selection and input, not when translating readonly or unmodified fields.
Does this solve your problem?
Thanks for your reply.
There is this other thread in which I examined the state of prefilters when changing the prefilters on the lookup and then close and reopen the lookup again:
https://community.thinkwisesoftware.com/development-13/look-up-pre-filters-stay-active-562
Maybe the prefilter state works differently on locked prefilters, I didn't test that, and if so then I could definitely achieve this using your solution. In fact I have already used views with an extra column indicating whether the record is selectable or not. So I will definitely try this.
Still it means that the view needs to contain a lot of excess records, while the shortlist that I am really interested in might be pretty small. I would think that the overall performance of a lookup might improve if the display and the selection of a value are properly divided.
Kind regards,
Mark
Most RDBMSs, including SQL Server, will actually try to merge the (pre)filter conditions with the join and filter predicates in the view. This is called predicate pushing.
As long as the view is not too complex, it does not matter whether you put the conditions in the view definition or in prefilters. The optimizer will ensure that the query is executed as efficiently as possible.
When creating the situation that Jasper explained, make sure to test the queries (view combined with prefilter) for performance. You'd might even want to add an extra index for this situation. If you do, performance (based on 'pushing the predicate') shouldn't really be a problem.