Skip to main content

Hi everyone,

I'm hitting a major roadblock trying to consolidate data for our care planning system. The main goal is simple: we need to merge the results of 25+ different assessment outcomes (Core Prompts) into a single Linked Record field on our final tracking table. Basically, we need the system to reliably weld 25 lists of IDs into one final master list.
 

-Our Automation Setup 

  1. Structure: We have 25+ independent Automations, each dedicated to one Core Prompt (e.g., "Pain," "Swallowing").

  2. Trigger: All 25+ Automations are set to trigger at the same time when a new assessment record is created.

  3. Action Flow: Each Automation executes a quick sequence:

    △Action 1 (Find): Finds all related Care Problem records based on a Formula field value (the core prompt name).
    △Action 2 (Write): Executes an Update Record to append the found list of Problem IDs to the target record.

-Observed Problems & Failure Modes

The concurrent writing of data by the 25+ Automations is causing severe data integrity failure:

  • Initial Validation Success:
    When testing the Find Records action for any single prompt in isolation, it successfully returns all associated records without errors. The individual logic is correct

  • System Corruption upon Merging:

    • Data Loss: When all outputs are funneled into the final single Update Record action, the total count of linked records is often less than the expected total (e.g., 11 items become 10). Airtable is incorrectly de-duplicating or truncating the list when multiple automations write concurrently.

    • Format Corruption: The final linked record field frequently displays scrambled Chinese text or Airtable Record IDs . This happens because the system fails to perform a clean append and the data structure breaks down.


We are completely stuck at this point and would greatly appreciate any expertise you can share to help us solve this critical database issue.

Hmm, unfortunately I think you’re going to need a script for this.  I was thinking that if you swapped the automation around to update the found items instead of the triggering record it would work, but that doesn’t work either because of how it’s all running in parallel

Ideally we would have all the ‘Find Record’ steps and then funnel the results into a Run a Script action that would then compile it all for a final ‘Update Record’ action to use, but because you have 25+ automations we can’t do that either

And so the options I can think of are either:

  1. Replace all 25+ automations with a single automation that has a ‘Run a Script’ action that does the finding of the records, and then output the result for an ‘Update Record’ step
  2. To avoid too much scripting, create two automations:
    1. Automation 1 will consist of the first 20 Find Record steps, followed by a ‘Run a Script’ step that will compile the results and do a ping to Automation 2 which will trigger on webhook
    2. Automation 2 will receive the previous results and do the final 5+ Find Record steps, and we’ll use a ‘Run a Script’ action to compile and output the results for an ‘Update Record’ step

The benefit of option 2 is that you’ll be able to update all those Find Record steps yourself without a script

Hopefully someone else has a better idea!

Terms & ConditionsAccessibility statement