I have done this before but now it won’t work. Maybe my new document’s master data is corrupted somehow because I copied and pasted from another document?
There seems to be some magical extra step needed to get the summary table to act as a filter. I cannot find it anywhere in the documentation when searching for selector.
Here is the code view of the source table and summary table where the selector won’t work:
import grist
from functions import * # global uppercase functions
import datetime, math, re # modules commonly needed in formulas
@grist.UserTable
class Routine_Master_List:
Frequency = grist.Choice()
Item = grist.Text()
Boat = grist.Choice()
class _Summary:
@grist.formulaType(grist.ReferenceList('Routine_Master_List'))
def group(rec, table):
return table.getSummarySourceGroup(rec)
@grist.formulaType(grist.Int())
def count(rec, table):
return len(rec.group)
It’s not clear from the information given what exactly you’re trying to do, what you’ve tried so far, and why it’s not working.
Broadly, here’s what you need:
- A summary table with at least one group by column,
- on the same page as the source table.
- In the settings of the source table (not the summary table) choose ‘Select by’ and pick an option with the summary table.
Never mind: I solved it.
One has to create a new master table and link one of its columns to the column of an existing table. This can’t be done just by creating a new widget containing a table that is a summary table. It’s the presence of the linked column that enables a table to be a selector. This is what results in the below “SELECTOR FOR” another table. Just creating a summary table doesn’t do it. Must have a linked column.
This is not at all clear from any of the documentation. I’d suggest that the documentation on linking should make this much more clear: in fact, a whole topic on selectors or using tables as filters should be added.
Alex, I don’t think what you’re suggesting would work for creating a selector (or filter by table value, which is a more explicit way to “name” it.
No, it’s definitely possible for a source table to select by its summary table for filtering, based purely on the fact that it’s a summary table, without any need for reference columns. Can you share more info about your document?
Nothing special. Just 3 columns: Boat, Item, Frequency. About 8-12 rows per boat. Wanted to filter on boat to show only rows applicable that boat. Could not do it by adding a table as a widget, clicking on the sigma for the source table, select by widget, and picking the “Boat” column. Never worked.
What’s the secret?
It sounds like you’re using the ‘select by’ dropdown in the ‘Add New’ popup, specifically when adding a summary table. Is that right? But if you want to filter the source table, then it’s the source table that must select by the summary table, which is the other way around from what you’re apparently doing.
Here is what I was doing:
Add widget to page → Table → Sigma next to source table → Group by column=Boat
This certainly creates a summary table with correct values in the Count column. But, it won’t do filter/select.
Can you lay out the command/clicks in sequence as I did above.
You are obviously right; you wrote the product.
It’s really confusing. Looks like the sequence is:
- create source table (yes, the non-summary table–or the table to be filtered).
- create a second table with at least one column that will match the “source” table. This can be created as a new table or can be created as a summary table of the source table.
- Now, go to the source table and do a select by, choosing the second table as the select by table. And not using the Sigma. Just select by the table name.
Unfortunately, I just corrupted my document by trying to do this and got a table refering to each other which results in such a mess of errors that the page containing the errors cannot be removed.
The UI for select by is definitely confusing for basically everyone, even me, and we want to improve it. Getting the linking direction wrong is a common problem. The reason you do the configuration on the source table is because it’s the source table that’s going to be filtered. But it would also be nice to set up the same link from within the summary table, by making it a selector for some other widget of choice.
That sounds really bad and also really strange. Can you share more info? Does refreshing the page make a difference?
So, that is fixed. Load a different document. Then, load the problem document.
Now, make a new page. Add the source table (referring to the table from raw data).
Make a summary table that refers to the boat column. It is really just a way to create a table and populate the values in the boat column.
Now, go the “source” table widget on that page. On the right side panel, go to Table/Data. Choose Edit Data Selection. Choose the summary table.
It works. A simple process but conceptually a muddle. And the naming of things is a muddle, too.
I get what you are saying: the selection will apply to the source table so that’s where the data selection change is made.
I’d really suggest adding an explicit section in the documentation for “filter by table” before you go changing the UI.
I think documentation leans towards marketing: “This is magically simple: Watch this–abra cadabra–presto.”
Better to be a little bit more straightforward and state the process (without justification, possibly with apology).
Then, perhaps you should disentangle the create a summary UI from the create a selector filter UI.
I now realize my earlier attempt (which worked) that created a linked column just created a choice column populated with the right values. I could have done it manually and it would also have worked (there are only 3 values).
‘refers to’ sounds like you’re making a reference column. I assume you mean “group by”.
“Edit Data Selection” is usually for changing which table is being used as the underlying data of the widget. I guess you can also change ‘select by’ in there but I’ve never thought of that. I was referring to the ‘SELECT BY’ section that’s already visible at the bottom of the panel, below “Edit Data Selection” and “Advanced Settings”.
Thanks for the feedback, @Lewis_Levin. You are right, this flow (from source table to “summary which acts as a selector for the source table”) is awkward in this case and feels backwards. Our main excuse is that it relies on functionality that addresses many other cases.
I like your idea of disentangling the summarizing UI from something like a “selector widget”, since in this case the “selector” functionality is the primary intent. (More commonly, summary tables are used to analyze data, e.g. in dashboard pages.)
Maybe even simpler would be to generalize the “SELECT BY” dropdown (when adding a widget) to list both the options like “SELECT BY: MyTable” and “SELECTOR FOR: MyTable”. What do you think?
Probably not obvious enough.
More obvious would be Filter by and Select by.
There seems to be some confusion. “Select by” usually already means “Filter by”, not the opposite. The widget being configured when choosing what to select by is the widget that will be filtered. The only times it doesn’t mean that are when setting up cursor linking, i.e. when there’s no filtering at all either way.
I see there is some ambiguity in the English language when in a concise UI like this. Do we mean “Select [this] by” or “Select by [this]”? In our current interpretation “Select By” means “Select {records in this widget} by {pick another widget}”. But one could also read it as “Select by {this widget}: {pick what it selects}”. Same for “Filter By”.