I have a membership database.
The Members DB has all the primary members, and an ID for all of them. It also includes family members with the same ID.
When I select a member, I would like to have another widget show all the other records with the same ID (Basically - A dynamic filter depending on what is selected). When I have two table widgets, and link them it moves the cursor - but does not filter. It works fine as long as another table is used in the second widget.
The behavior you describe actually does work but only for Reference columns. You could make it work by making another table “Primary Member IDs” or “Primary Members” with an ID column, and change Members.ID to be a Reference to the ID table. Then you should be able to link your second widget by the Members.ID column
Another way you could make it work without introducing new tables is if each member had a Reference to their PrimaryMember (with primary members having that field blank maybe), and then selectBy all members that refer to the selected one. However, that would only work when clicking on the primary, not when clicking on a non-primary family member
Thanks. I thought about both these options, but not sure they would work for me.
I have a few tables - but the relevant ones are Membership, and Payments.
A membership applies to a family, so we have at least the primary member and their spouse listed.
Ideally the family members would have their own records so we can record their contact info, and some licensing info.
When payments come in they may come from the primary member, or a family member. Having this in two tables means we can’t easily search for them as it may not be clear if its the primary or not, and can’t link both tables to the payments table/widget.
If I use two linked tables, it auto-populates the ID when I add a new record (Which is exactly what I want), but as indicated above - I can’t link both records to the widget showing the billing records. I was hoping to re-create that behaviour, but using one membership table rather then two.
This also needs to be easy for our membership director, and Treasurer (Who are not technical), so I’m trying to keep the interface simple, and don’t want to introduce extra steps where they have to link records - or they will be orphaned.
Looks like I may need to collapse all the family records into a single member, and forego the “many to one” relationship I was hoping for.
Upon further reflection - just needed an intermediary widget. I could then link (and possibly collapse) a second widget from the first, then add a 3rd widget (Which contains the same table as widget #1), and link it to the second.
Not sure if this applies, but spoke to GetGrist about the same (or a similar issue), and I agree with the reply:
There are several kinds of linking. I think you mean when you link a table widget showing table T to another widget also showing table T: In this case […] the linked widget shows all rows and only highlights the linked record. At the time this was decided, it seemed that a single-row table is not very useful, and it was decided Grist should only indicate the linked row.
This remains the subject of some debate. …
Let me share also a workaround for how you can get a single row to show. Create a new column R in your table T with the formula “$id”. Change this column’s type to Reference to the same table (T). This makes R a reference to the row itself. You can now link tables using “T → R” in the “SELECT BY” dropdown, in effect filtering for all rows by this newly-created reference. This would cause the linked table to show only the one selected row.
I like his workaround, and additionally, I suspected his reasoning as to why my linked widget was returning all records instead of only the one linked record. I agree with his logic and the original logic, so long as there is a simple workaround. And, (in my case), there was.