Implementing filter reference columns

Grist Feedback
1

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!

2

I’m struggling to understand anything here.

doesn’t really display as most folks probably wish it might. Instead…

How would this be fixed if

This would require no changes in UI and or the actions needed to create a selector. It’s an under-the-hood implementation decision.

What exactly are you referring to that behaves strangely now, and what do you imagine happening 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.

How would using a view instead of a table make reference columns better? Does this idea only apply in the context of summary tables, or for all reference columns? How would using views to implement summary tables help, considering that reference columns cannot currently refer to summary tables?

Python is very slow, and using SQLite more is generally desirable, but it’s tricky. For example, summary tables are useless without formulas, and formulas use Python.