Automation updating between two unlinked tables

[GHR]

Hi,

I am running network managing research participants for my organization. They have two options for signing up, which are in separate tables. One is the 'Full Sign Up', which is in my main participant table, where I am linking other information not related to the form. The other is a 'Sign Up Later' form, used when recruiting partipants who don't have time for the full form (fx in person at a conference). They sign up with their name and email and I have created an automation to send out a link with full form. 

I'd like to create an automation that sends out a reminder email those who have completed the 'Sign Up Later' form, but not completed the 'Full Sign Up' form, to do so. This means I need an automation that removes those who have completed the 'Full Sign Up' form, from the 'Sign Up Later' table, but I am unsure how to do that, since the tables are not linked. 

Any help is appreciated 🙂

[ibayub · Accepted solution]

One quick option (although may be a bit long depending on how many records you have in Full Sign Up and Sign Up Later) is to set an automation for each 'Sign Up Later' where you have a find records with the condition of its email (= an email in Full Sign Up), and if there are 0 results you send the email, if >0 then delete the record using a script below

let table = base.getTable("Table Name");
let inputConfig = input.config();
let recordId = inputConfig['recordId']
await table.deleteRecordAsync(recordId);

To not do it 1 by 1 you could also have a scheduled automation in Make that could loop through both.  

Another cleaner longer term option may be to remove the table of Sign Up Later and have one table of Participants, with a status field (e.g. Waiting on Details, Reminder Sent, Fully Signed Up, etc.) which might make it easier for you to manage over time and you can more easily track the status / set up different automations from there (either by conditions or views)

For different types of forms you can also check out fillout which is much more powerful than airtable native forms

[GHR]

Thank you for the reply! I think you are right in having all participants in one table, instead of having a separate table for Sign Up Later. I'll try out the automations around the status field. Fillout will be the solution for updating information in what will be an existing record. 

Thank you so much for the script, I'll try it out for the sake of learning 🙂

[ibayub]

my pleasure and feel free to reach out if you need help with anything else 🙂