Adding multiple rows in a many-to-many table after an input

[Greg_Lhotellier]

Hello everyone!

I am trying to build an ATS (Applicant Tracking System) using AirTable. I basically have a Candidates table, a Companies table and a Candidate_Company table that links a candidate to a company including other infos such as the recruitment step of the candidate in the company.

Whenever a new candidate is created, I’d like to automatically add rows in the Candidate_Company with the newly created candidate id and the id of all the companies. Same when I add a new company so it automatically add rows in the Candidate_Company with the newly created company id and the id of all the candidates.

From what I’ve understand so far, Automations alone won’t be able to do that, it looks like I’ll have to combine Automations and Scripts, am I right?

Do you have any article or tutorial that could give me some hints on how to proceed?

Thank you! :slightly_smiling_face:

[Greg_Lhotellier · Accepted solution]

I did manage to do it with Automation + script.

Here is the script (sorry for the french table names and missing indentation) :

let inputConfig = input.config();
let posteId = inputConfig[“posteId”];

let postesTable = base.getTable(“Postes”);
let candidatsTable = base.getTable(“Candidats”);
let candidatsPostesTable = base.getTable(“Candidats_Postes”);

let result = await postesTable.selectRecordsAsync();
let postes = result.records;
let poste = postes[0];
for (let posteTemp of postes) {
if (posteTemp.id == posteId) {
poste = posteTemp;
}
}

result = await candidatsTable.selectRecordsAsync();
let candidats = result.records
let candidatsPostes = candidats.map(candidat => ({
fields: {
‘Poste’: [poste],
‘Candidat’: [candidat]
}
}));

while (candidatsPostes.length > 0) {
await candidatsPostesTable.createRecordsAsync(candidatsPostes.slice(0, 50));
candidatsPostes = candidatsPostes.slice(50);
}

[Julien_Mottet]

Thanks for the script. Your candidates have no choice but to apply to all companies :).

[Greg_Lhotellier]

You’re welcome!

In fact, they have :blush:

The point is for us, on the recruitment side, to never forget about a potential match between a candidate and a company :winking_face:

Once we have examined all criteria, only a few candidate - company tuples are left and then we ask the candidates.