Hi! I’m trying to create a dataset that will track changes-over-time for scoring attributed to different individual entities, and having a second table/chart lookup the most recent values for all of the entities. I had been doing this in Google Docs by way of a custom formula that would allow me to use a different table for each entity and pull out the table name to use as a field as well as the last row that was entered.
In Grist I’m trying to organize the data in a more logical way, by where it comes / who enters it instead of just a ‘weekly’ summary entered for each entity.
Am I going about this the right/wrong way? Was having a table for each entity with rows entered chronologically easier to work with in Grist rather than having tables organized by “data source” with entries entered whenever?
Thanks.
1 Like
Hello @Jason_Martinez!
You are right that in Grist it’s better to keep all records in a single table, and distinguish them by source. There are a few benefits you can get this way.
-
When entering data, you can set up formulas to automatically fill in who created the row, and the time it was creating, using Authorship and Timestamp columns.
-
If different records are tied to different entities, you can set up a Reference column tying the record to the entity, and create a Linked view so that selecting the entity you’ll see all the records for that entity.
-
You can set up a view of most recent values by creating a formula column “IsMostRecent” that has the value True
only for the most recent value (for a particular entity), and create a view filtered to only show records where IsMostRecent is True
. I’d guess this is the trickiest part (may be best to do using summary tables and/or the lookupOne() or lookupRecords() functions). If you can share a sample document (you can make up one without sensitive data and link-share it), I’d be happy to help with this formula part.