Add an option to pre-filter/sort using SQL the foreign key selection popup dataview

[githubuser181226]

In relation to #4879 please, consider adding an option to use a query to pre-filter / pre-sort the foreign key dataview, before it's shown inside that quick selection popup window.

In pretty much 99% of the cases when selecting foreign key using the popup I don't need nor want to have to sift through all the available records in the foreign key table.

Imagine having 100s of products to sell, but half of them are out of stock, and you want to record an order. It then becomes much less of a hassle to just open the 100s of product list, filter it and copy and paste the key values by hand, than to use the quick select popup.

I know SQLite doesn't allow for views to be used as the foreign key reference, but that is NOT what I'm asking here for.

Instead I just want the program to display a subset of the records referenced by the foreign key by subquerying it or by using a view.

Please, tell, if you think it is even possible,
Thank you, again, for all the great work you do.

[pawelsalawa]

I'm not sure how easy this would be to implement, but I will look into it when the right time comes.

[githubuser181226]

I'm thinking of something like this. In a separate UI place, similiar to snippet or function editor, generate a list of all the names of the foreign key constraints with their corresponding table names.
Then for each such entry in that list allow the user to define a SELECT statement. This will be that pre-filtering/pre-sorting query I was talking about, or "filtering query" for short.
Then, when a table is opened make the program run all the filtering queries to then be used as the source of the FK popup quick select lists.

Sounds like a lot work, if I think about it. However, imagine the potiential of having something like that.
For the sake of portability I think these settings should be stored in the database itself.

What do you think?