How to find Latest Modified Record in another Table - Can be either Automation or Script

[clabonty]

Hello, let me first give a little backstory on the process I have leading up to where I need help...

I have two tables (Table1 and Table2). There is a button on Table1 that triggers a script. The script copies a few fields from Table1 over to Table2, one of which is an alphanumeric code. While copying this alphanumeric code over to Table2, it also performs a check to see how many times this code already exists in Table2, and populates that number into a field. Table2 contains a complex formula that uses the existing alphanumeric code and the number of times that code has been seen before, to generate a new unique alphanumeric code. All of this works flawlessly, I'm just struggling to figure out how to execute the last step... copying this new unique alphanumeric code back to a field on Table1.

First attempt at a solve, I setup a new automation that watches for new records entering a view on Table2. From there I know I'll be able to reference my new unique alphanumeric code, but I can't figure out how to identify the record on Table1 to paste it into.

I tried setting up a "Last Modified" field on Table1 that monitors a Text field that is updated by the script the button executes. This should act as a flag for either an Automation or Script to find the proper record, but there doesn't seem to be a way to find the latest date/time in a field with Automations... I'm assuming there probably is a way in a script, but I'm not sure of the proper syntax.

Hopefully that's a clear enough description. I feel like I'm almost to the finish line with this crazy process... if anyone has any ideas on how to complete the last step, I really appreciate it!

[TheTimeSavingCo]

What if we tried:
1. Created a new table
2. Linked all the records in Table 2 to a single record in the new table
3. Create a 'Created time' / 'Last modified time' field in Table 2 (whichever is more appropriate)
4. Create a rollup field with a "MAX(values") looking at the field from step 3
  - This will output the latest date/time from step 3
5. Create a lookup field in Table 2 to display the field from step 4
6. Create a formula field that will compare the field from step 3 to the field from step 5

This will give you the latest record that got updated / created in Table 2, which you can then reference with your automations

(I don't think I fully understand what we're trying to do honestly, sorry if I misunderstood!)

[clabonty · Accepted solution]

Appreciate the response! I actually ended up solving this issue in a simpler way using just an Automation and new fields/view in Table1.

1. Added a new "Input" text field in Table1 that is populated by the Button/Script with some of the data that is copied from Table1 to Table2.
2. Added a new "Output" text field in Table2 that will be populated by the Automation after the script has run it's course.
3. Setup a View in Table1 that is filtered to just show records where "Input" is filled and "Output" is empty. This should only ever be the single record where the button has been pressed, before the Automation has completed.
4. Automation runs after a new record enters Table2 (after the Button/Script finishes).
5. This Automation copies the new unique alphanumeric code generated by the script/formulas in Table2, finds a record in the new View in Table1 (there should only ever be one), and pastes the code in the "Output" field.
6. Profit!