Goal: Recreate many-to-many table links on synced records in the destination base based on csv text field of original source record ids
- Have synced multiple related tables to another base (for reasons of access control, division of unrelated data. Overall syncing 6 of 25 tables to another base)
- Several of the links are many to many
- I have added record_ID to the source tables, and created formula fields that arrayjoin the linked records into a csv (comma separated) text field
- I cannot use the primary name field because they are not unique
- I cannot use the update records directly because I do not have the new destination record IDs for that csv list of source record ids
- I cannot use the find records because it's not letting my search by each item. This means that this problem would be trivial if I only had a 1 to many relationship and worked from the "1" side.
Potential Solution areas:
- create "an array" accepted format of items in a formula field
- convert csv text into an array in the automation. This would enable
- using new "repeat for each" array feature to search for that record ID,
- and return the new destination record ID,
- and then use update records for each item to create the links
- if I could somehow get a csv or array of the new destination record IDs
- I could just jump straight to using update records.
Solution areas I am trying to avoid:
- Integromat - although I am have built several automations in it. I want the low latency of airtable automations instantly linking things up.
- Scripts or External Lambdas - trying to keep this simple
Thanks for the help!