I think this is a pretty basic question, but I can’t quite figure out where to look in the documentation to figure it out (it’s been a while but I kinda miss the tutorial structure from March or so).
In any case, I have a big table with about 1,400 rows and 40 columns.
It is documenting pieces of music, and the music is tagged in one column by genre descriptions – waltz, mazurka, polka, etc.— and in a another column by a “sub-group” description (e.g. "Tants! Corpus).
I know how to use the filtering rules to create a view of the table that only shows the “Tants! Corpus” items, but what I’d like to do (I think) is create a sub-table that will show only the items that are part of the “Tants! Corpus”, that will auto-populate new rows if items are added to that corpus from the main table, and to which I can add additional columns that aren’t necessary for the main table.
Is this possible?? I feel like it must be, but I can’t quite figure out how to do the lookups. Can anyone help point me to the right tutorials (if it is possible) or to a work around (if it isn’t)?
What you are referring to is a Parent Child relationship. The Parent is your current table and the Child will be the new table. The type of relationship will also be one-to-one so that only one row can exist in the child table for each “Tants! Corpus” row in the Parent.
You will need to know how relations work to set the right columns and their settings before you move onto how to make make the related rows.
Be sure to note the Relation column options section and turn of Allow Multiple.
Once the columns exist there are three ways you can go about creating rows and relationships in them.
First is manually. Once the relation columns are in place you can create a row in the Child table for each “Tants! Corpus” as needed and just set the relation to link to each other.
Second is Automation. Set it to look for edits of the column where you set “Tants! Corpus”. If it meets the criteria you can have a formula check to see if a row exists in the child table and if not then create it. (see the third option for how the formula works). This one is more complex and if you have not used automation or complex formulas before it could be a steep learning curve. Also depending on how many rows you add or edit and the account tier you are on you could quickly reach your automation limit.
Third is on a button press. I helped someone out previously where they wanted to make a new row in another table and create a relation with it. Hopefully this can help point you in the right direction.
Note that you could technically create the button and use automation to simply push that button for you as and when needed so you can run the action yourself without the delay that Automation brings in but automation will catch it if you forget.
If you would like more specific help creating the button or setting up an automation please create a sample document with a cut down version of your doc (keep table and column names the same for ease of translation to your live system) and share it here. I will be happy to take a look and see what can be done.
Hmmm… I still think filtered views of the main table (multiple copies, each filtered as you please) is still the right approach.
If you want extra columns that only apply to the Tants! Corpus filtered view, you can just make them (and keep them hidden in other table views). All base table rows will technically have these columns, even if they aren’t applicable, but that’s ok if they’re hidden.
Any drawbacks to this approach?
(Dale’s info about lookups is good, but I think adds unnecessary complexity and challenges in data duplication imho)
To be clear, I’m talking about multiple different views of the table, probably on different pages, and not on-the-fly filtering of a single table. This way you can also customize the columns per view of the table as well.