How to append an updated linked field to a string

[ajatyourservice]

Hey all,

I am stumped and looking for some help. Here's the situation; I have linked record called subgroups (a string with an integer i.e. Pacific 6) that will be occasionally updated when the person is moved to a new subgroup. I want to keep a running record of each group a person has ever been in, so if they are in subgroup X, then moved to subgroup Y, then moved to subgroup Z, the record keeping cell would now contain "subgroup X, subgroup Y, subgroup Z" but I cannot figure out how to append the new information each time a record is updated instead of replacing or concatenating with just one other record. My automations keep failing but I am really green with automations so I'm not sure if it's even being built correctly.

So far, I've tried the following:

• Using a formula with a change trigger in the form of a I/O single select and reference cell with the original group (called orig) - IF( Select ="Changed", Subgroup & ", " & Orig)​
  • This just leads to a record with the original and current subgroups, not the legacy record.
• An automation that runs something like this - when a record is updated (subgroup - should this be the original field and not the linked?), find record (subgroup), update record (legacy group)
  • Same issue, replaces the record instead of appending and creating a longer string.

Any advice or suggestions on how to achieve an appended list of subgroups as they are updated?

Thanks in advance to anyone who takes the time to read this and help out!

[Elyes80]

Hi @ajatyourservice,

I recommend you first to add a "last modified date" field specific to "subgroups".

Then add a formula field to concatenate the subgroup and last modified date.

Finally add a long text field which we will call "history".

Now you can set up your automation :

as trigger you can use when subgroup record is updated.

as action, update record, for the record id select the same airtable record ID and for the field select the history field. Finally, select the history field and the concatenate formula field as the update.

hope it makes sense and helps you.

[TheTimeSavingCo · Accepted solution]

I've set something up here for you to check out:

And the automation looks like this:


You should be able to view the automation set up fully after duplicating the base too

 

[ajatyourservice]

This is awesome, thanks so much Adam!