I’m struggling on how to link records without having a lot of duplicate info
There is a cascade of information that goes from DATES to TIMES to TRADES
For any given DATE, I have multiple TIMES. There are some fields that are associated with the DATE-TIME combo
Then the next level is for every DATE-TIME combo, there are multiple TRADES records with additional fields.
In my example image below, when I click on a DATE it filters for TIME based on the DATE.
Then when i select a TIME, if filter to TRADES associated with that TIME.
The 2 Widgets on the left are summaries of my current TRADES table on the right.
I cant figure out how to start on the left side and work my way right.
I’d like to add a date to a DATES table
Then add TIMES to a TIMES table that is filtered according to the date picked. There will be additional fields in the TIMES table as indicated by the fields in the red box below.
Then I would go to a TRADES table and add information for individuals.
I think in a regular database, this is normalizing. But the only way I could figure it out was by duplicating info in the Trades table, and then summarizing.
my example image is below
Any help is appreciated
Make a new separate non-summary TIMES table, then add a column to the TRADES table and set the column type to a Reference pointing at the TIMES table.
I got very close
I created a SETUPS table (changed table name from TIMES) that has Date, Time and a few other fields
I changed Date and Time fields in the TRADES table to be Reference Fields from the SETUPS table.
I created a summary widget for that table with just dates. When I select a date from the widget, it filters the SETUPS table for just that Date.
Then I added the TRADES table widget linked to the SETUPS table and linked by SETUPS → Time
That gets me the view of the TRADES table that I want.
But when I add a new record to the TRADES table, the Date field doesn’t populate. The Time field auto populates based on the record I have selected in he SETUPS table.
Grist knows the correct date the TRADES table record is linked to, it just isn’t displayed or populated in the field
But when I want to do data analysis on the TRADES table, the Date field is blank so I can’t analyze data by Date.
Is there a way to get Grist to “type” the date in the Date field of the TRADES table? Then I have the actual Date for analysis, as well as the link so that the tables filter the views correctly
Rename the reference column in the TRADES table to ‘Setup’ to avoid confusion. Then add two formula columns