Use automations to update the last record of a different table when new items are added

[Edd_Tillen]

I’ve got a table where I add records, and I have another table where I’m trying to make an annual summary of all the records I’ve added to the first table. So I should end up with one record for each year on the second table, with multiple records listed in a linked field. This will happen for several tables across the space.

Currently I’m achieving this by having a filter view, to the last 12 months, and then linking the linked field to that view. Then I can just manually select every record that shows up when I press the plus button.

However, I’d like to automate that process. My first thought is that I could use an ‘update record’ automation to somehow update the last record in the Summary Table every time I add a new record to the first table. Then perhaps I could use another automation to create a new row (last record) in the Summary Table each year (I’m not sure if that’s possible).

Or perhaps it’s possible to pick the record to update based on the corresponding date fields ie use a formula field to work out the year of the item in the first table, and then update the record with the corresponding year.

But I don’t really know what I’m doing when it comes to implementing it. Is any of this even possible?

[TheTimeSavingCo]

Yeap it’s possible

Edd_Tillen:

Or perhaps it’s possible to pick the record to update based on the corresponding date fields ie use a formula field to work out the year of the item in the first table, and then update the record with the corresponding year.

This is how I usually tackle this, yeah. The formula field would be a DATE_FORMAT() that would output just the year from one of the corresponding date fields

You could then have an automation that triggers whenever a record in your first table has a value in said formula field AND is not linked to a record in the Summary Table, and its action would be to find the correct record in the Summary Table via a Find Records action, then you’d do an Update Record action to link the original record to it via the found record ID

[Edd_Tillen]

Thanks Adam. Nice to know I was sort of on the right track. Find records seems so obvious now, I hadn’t really had a reason to play with that before, but I can see how it could be very useful.

The first table, by the way, is a list of non-conformance (NC) issues, the summary is a monitoring table.

However, I’m stuck. This is what I have so far:

Trigger: When record is updated: View = Grid View, Fields = Year (fx)
(I’m not sure how to do AND not linked to a record in Summary Table)

Action 1: Find records: In Summary table where Year = 2022.
I can see I can click on the cog to change this to a dynamic number based on a field from a previous step, but my year field from the previous table is greyed out. If I choose display as grid or list I have more options, but still the Year field doesn’t appear in these options. Is it because it’s a formula?
For now I’ve just put a static number of 2022 in so I can move on and test it.

Action 2: Update Record: Record ID = List of Record ID from previous step. I can only do a list or grid here. I’ve never really seen this before, but I guess it’s because Find record could potentially find several?

Fields = List of NC Raised List of ID.
I’m not sure if I’m choosing the right options here, I can either choose list, and NC raised, which results in ‘List of Records’, or Make a new list of field values > NC raised > make a new list of ID, which is what I’ve done.

I’ve ran a test, and I’ve got to be honest, I was pleasantly surprised to see it works. It’s the section in bold I’m having real difficulty with. It’s not much good unless I can automatically find the record that matches the year of the non-conformance report.

If I can crack this, it will be amazing. There are loads of tables I have to link in a similar way to this monitoring log.

[TheTimeSavingCo]

Hmm, may I have some screenshots of the relevant bits of the automations and the tables please?

[Edd_Tillen]

Sure thing. I assumed sharing screen shots would be difficult on here. I was wrong, it’s really easy! I hope you can make sense of these.

[TheTimeSavingCo]

If anyone else ever hits a similar issue, we resolved it by changing the Year of Review field from a Number type field to a Single line text type field!

[Edd_Tillen]

And I was updating the wrong table. I needed to be updating the NC Table based on the field where the two tables were linked. That then populated the linked field in the Summary table.