How do I link records so I can normalize my tables

Ask for Help
1

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

image
image2082×841 48 KB

2

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.

3

Thanks Alex

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

image

.
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

image
image1938×1660 393 KB

.
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.

image

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

4

Rename the reference column in the TRADES table to ‘Setup’ to avoid confusion. Then add two formula columns $Setup.Date and $Setup.Time.

1 Like