Grabbing the latest record from another table

[Rob_Lee]

Hi everyone,

I’m trying to write a script here to do a thing but I’m not a coder. I can usually reverse engineer examples to get by but right now I’m coming up short. Here’s what I"m looking to do.

I have Table A
Table A has records for projects taco, sandwich, and cereal.
The base has tables corresponding to each project and share the same name.
Tables taco, sandwich, and cereal have records for each project update, most importantly the Notes field.

For each record in Table A, I want to grab the latest record from the table that matches the record name and display the Notes field content in a field in Table A. I figure I’ll put this in an automation that runs when Table A is opened.

I can make various parts of my intent work but I can’t put it all together. Can any of you epic geniuses help out?

[Kamille_Parks]

Ignoring reservations I have about this particular setup (one table per project instead of one table for all projects), you should be able to just do:

base.getTable(variable)

where variable is the field value (as a string) holding the name of the project/table.

[Rob_Lee]

I have a lot of projects with a lot of updates for each and want/need a way to be able to get the latest status on each at a glance.

That’s one of the things I can’t figure out. How do I grab the name for each record to grab the latest record from the associated table?

[Kamille_Parks]

Obligatory word of caution: I imagine this would be much, much easier with two tables and a few extra fields. One table for all projects, one table for all project updates, and extrapolating from the following process:

Get and show latest status by person via form & view

1. Team Members Projects Table: get a Rollup field to show the MAX(values) of the {Date} field
2. Status Messages Project Updates Table: get a Lookup field of that^ Rollup field.
3. Status Messages Project Updates Table: get a Formula field that does IF({Date} = {Lookup field}, 1, 0)
4. Filter your a view in the Project Updates table by where the Formula field = 1

This is the simplest and most foolproof way to do it, you’ll be introducing a very complex set of issues to fool-proof if you insist on having one table per project, requiring scripting and/or automation.

[Rob_Lee]

That’s good advice, my head typically goes to the most complicated setup first. Thank you fro the feedback.

[kuovonne]

I agree with Kamille that a two table system, one for projects and one for updates, makes sense to me. However, if the taco, sandwich, and cereal are all different enough, there may be reasons to keep them in different tables.

Do you have any linked records joining the tables? Do the [taco], [sandwich], and [cereal] tables link to [Table A]? If so, the system of rollups would work. It is fine if all of the records in the [taco] table link to the same taco record in [Table A], all the records in the [sandwich] table link to the same sandwich record in [Table A], and all the records in the [cereal] table link to the same cereal record in [Table A].

If so, you can still use the system of rollups as described in Kamille’s post. No scripting required.

Rob_Lee:

I figure I’ll put this in an automation that runs when Table A is opened.

It currently isn’t possible to trigger an automation by opening a table in the user interface. Automations can be triggered by creating records or changing record values. (Or in some cases by changing the filtering in a view, or by time-based conditions.)

Rob_Lee:

I can make various parts of my intent work but I can’t put it all together.

Which parts do you have and which parts do you need help with?

If you really need a scripting solution for finding the latest record, you need a field that provides a date/time for the record. You can use a manually maintained {date/time} field, a {created time} field, a {last modified time} field, or a {formula} field based on the created time or last modified time. Your script can query for all the records, then identify the last record based on the date/time field value.