Turning a list of linked records into separate records

Hi community!

For a job-sharing matching platform I’m aiming to automate the matching process.

There are three parameters that define the matching: 1) Location 2) Specialty 3) Seniority

I have managed to automatically link a person (A) upon registration to all other matching persons (B, C, D) . Those matching persons (B, C, D) are all shown in column named “matches” in the row of the newly registered person (A)

Rather than having all matches in one column (A+B+C+D) I want to have for each match for rhe newly registered person (A) a separate entry in a new table (A+B ; A+C ; A+D).
Do you know how to do this?

Many thanks
Jakob

hi Jakob,
to achieve this you would need to create an additional table that takes care of each match, we could maybe call it “Matches” and contains a field with the two people you matched and then other fields containing maybe other details about the match depending on your use case (reason for the match, matching time, etc.). To achieve this you have two automations

  • (MEDIUM) create an Airtable automation with Airtable native triggers which, whenever it picks up a match, creates a new record in the other “Matches” table you created
  • (BEST) create a script that does the same thing in an Airtable automation. This gives you much more flexibility in terms of adding more complex logic to each match. The choice between this and the other option, however, totally depends on your specific needs and how complex and multi-functional each match needs to be.

Cool Additions: You could add a google calendar connection so that each match directly creates a google calendar event between the two people who matched, you could create an automatic email system to the two people who matched telling them about it

Hope this helps!

Alessio
Website: alessiomonino.com
Email: alessio.monino@gmail.com

Hi Alessio,

Many thanks for your quick reply! I quickly tested your MEDIUM version as I’m not familiar with scripts.

The problem I face with your MEDIUM suggestion:
Steps:

  1. A new person (A) registers (this works)
  2. with an already set up automation matching partners get linked (A,B,C,D). (this works)
  3. Linking A,B,C,D happens instantly so if I create an automation that creates a new record in a “Matches” table it is not creating separate records for each match (A+B, A+C, A+D) but combines them again (A+B,C,D) (here I struggle).
    Any suggestion how to over come this?

Many thanks for your great idea with the google invite!

Thanks
Jakob

Unfortunately, this is only possible to do with Airtable’s automations using scripts.

The other alternative is to use an external automation tool like my personal favorite Integromat, which doesn’t require any scripting.

hi @Jakob_Kisker,
why do the matching partners get linked instantly? Do you have an automation that does this instant matching (maybe a find records automation followed by an update one)? In this case, yes, you would need to set it up in Integromat or create an Airtable script that does the job for you. I think the Integromat solution is easier to set up but the script, if well done, is more flexible and can accomodate future, more complex logic.

Happy that you liked the idea of the google invite. I recently created a similar system for a client who was setting up a matching system and I think that, together with automatic emails, really adds value. I also wrote an article here covering how to set up the google calendar automation (it is for a different use case than yours but still I think it gives a good idea of its potential): https://alessiomonino.com/ultimate-airtable-base/

Hope this helps!

Alessio
Website: alessiomonino.com
Email: alessio.monino@gmail.com

Hi,

Please describe what do you mean by linking A to B,C,D at first step.
Do you have separate table for newly registered and link it to ‘match candidates’ in other table? You can’t update more than one record per one automation step (without code), but you can use linking to ‘mark’ other matches for second automation.
It’s quite simple to do in 2 steps that way.

Other scenario - when you link A,B,C,D in a single table by ‘self-linking’, which doesn’t create reverse-links. Your goal can be accomplished as well, but you still need additional automation and use inter-table linking.

table1: Name1 | Link1 | lookup from t.2(name2)
table2: Name2 | his match | Link2

1st auto: triggered by A, (step2) find B,C,D in 1st table (step3) put their record IDs to field “link2” of t.2, and Name of A in field Name2
2nd auto: triggerred in table 1 when field Link1 is not empty (step2) create new record in table2, use data from triggered record Name1=>Name2, lookup=> his match (step 3) clear field Link1

thus, (step3) of 1st auto will cause update of 3 records in t.1, triggering three different runs of 2nd auto creating three new records.

i would also suggest to be careful, if you accidentally put something in Link2 in second automation, you can call ‘chain reaction’ that (if you have significant number of records) will hang your tables|automations for some time and quickly deplete your monthly limits (if any) :slight_smile: