There is nice suggestion to use 2 levels of tables that reference each other to enable a reference column in a third table to be restricted to values based on the choices in the 2 filter tables. However, it is difficult to setup and doesn’t really display as most folks probably wish it might. Instead…
You could implement this feature pretty easily if the reference column source was a view (in the formal SQL sense) on the table rather than the table itself. This would require that when a grid in a widget was created as a summary of a table you create the view. Today you show this as mytable [mycolumn] so the name is already distinct.
This can be implemented in SQLite as a view using the CREATE VIEW statement. Then, the reference would only show rows that were in the view as filtered, rather than showing all the rows in the source table. While a View doesn’t persist the result set–executing the query each time the view is accessed–the query strategy is compiled and persisted so that executing the query is marginally faster. In the grand scheme of things break-even or not really noticeably faster. Indeed, it might be faster as the feature would then exercise SQLite code, which is very fast–especially compared to Python in your middle tier.
This would require no changes in UI and or the actions needed to create a selector. It’s an under-the-hood implementation decision. Just a thought to make this actually work sensibly. It might even improve all summary tables to use a View–unless this is what you do already!