Yeap it’s possible
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
Yeap it’s possible
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
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.
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.
Hmm, may I have some screenshots of the relevant bits of the automations and the tables please?
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.