When a new record is created, copy data from previous record?

[flyingbren]

I'm sure there is a way to do this, I just can't think of the way the write the "logic" for it in automations... Hopefully someone can help me understand 🙂 

My rows have a date as the primary field. I have a long text field columns called "Notes for next training" and "Notes from last training". When I create a new row (new date), I'd like the text from "next training" of the most recent date (in the past) to be copy/pasted/imported into the "last training" column in the new entry.

I did it manually for this screenshot, just to illustrate what I would like to happen automatically. 

[Mike_AutomaticN]

Hey @flyingbren!

I’m on my phone rn, so I can’t play around much. However I believe the following should work:

1. Trigger: When a record matches conditions (When Date field is not empty)

2. Action: Find records (search records on the corresponding table; where record id is not that of the record which triggered the automation, and date is before date of the record which triggered the automation)

3. Action: Sort. Sort the list of records found by Date, latest to eseliest; I believe you can choose to use only 1 record (first on list for the next steps)

4. Update record triggered. 

There are certain tips and tricks that will probably be needed along the way (eg. Create a new field with Datetimeformat “x”, etc) so you might be better off just having a script for it. 

Hopefully I’ll be on a computer soon and can test things out a bit. In the meantime, I hope this somehow helps!

Mike, Consultant @ Automatic Nation

[kuovonne]

---

2. Action: Find records (search records on the corresponding table; where record id is not that of the record which triggered the automation, and date is before date of the record which triggered the automation)

3. Action: Sort. Sort the list of records found by Date, latest to eseliest; I believe you can choose to use only 1 record (first on list for the next steps)

4. Update record triggered. 

---

My understanding is that even after the list is sorted, you cannot access the values of only the first record in your update record action. While you can limit the number of records returned from the find action, that is currently based on unsorted records, so limiting the records in the find actions will not help.

In situations like this, I usually use a checkbox field to indicate which record is the last record and have the automation manage the checkbox field.

Trigger: when record is created.

Find action to find the record with the {Is Last Record} checkbox selected.

Update record action to copy values from the found record to the triggering record and also to set the {Is Last Record} checkbox for the triggering record.

A second update record action to clear the {Is Last Record} checkbox for the found record.

You will need to preset the {Is Last Record} the first time, but the automation will maintain the checkbox field after that.

As long as there is enough time for the automation to run between the creation of new records, this will work okay. If two new records are created without enough time between them, there will be problems. If someone accidentally sets the {Is Last Record} for more than one record or clears it for all records, there will also be problems, so you might want to set the permissions so that it can only be updated by automations.

[DisraeliGears01]

This isn't quite an automation, but you could pretty easily make this an internal linked record with lookup. So when you add the next training on say 2/1/25, you'd then indicate that the last one was on 1/15/25 and when you did that it'd fill in the appropriate notes as a lookup field. Just make a linked record field that points to the same table, and then use a lookup on your Notes for next training field.

The advantage of this over a fully automated solution like Mike's is that it doesn't need to be perfectly in order, so you can have different types of meetings in the same table. It would work similarly I think to what Kuovonne proposes, but without the automation (you have a click a checkbox in her example, in this you have to select the linked record). As always with Airtable, all kinds of approaches!

EDIT: Ooh, I think another approach if you have different meeting types is to bounce the information between two tables and use the Rollup MAX() function to return the most recent notes only.