Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Automation updating between two unlinked tables

Solved
Jump to Solution
620 4
cancel
Showing results for 
Search instead for 
Did you mean: 
GHR
5 - Automation Enthusiast
5 - Automation Enthusiast

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 🙂

1 Solution

Accepted Solutions
ibayub
6 - Interface Innovator
6 - Interface Innovator

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

See Solution in Thread

4 Replies 4
Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

I suggest you create a link between the two tables.

Then, add the linked field into the Full Sign Up Form. Amend the form link to hide the linked record field.***

Create a new formula field in the "Sign Up Later" table that crafts a unique form link that both hides the linked field and prefills it with the record ID of each record. This is the URL you should now use in your automation that sends out links to "Sign Up Later" people. Now when they submit a full sign up, it will contain a link to their record in the "Sign Up Later" table (and vice versa).

Now, you can create an automation that periodically checks the "Sign Up Later" table for records that don't have a linked record in the "Full Sign Up" table (Find Records: Link to Full Sign Up IS EMPTY), and sends out reminder emails with their unique Full Sign Up form link.

***Note: You will need to make sure to use the Full Sign Up form url with the parameters to hide the linked record field when doing Full Sign Ups, or the submitters will be able to see the records in the linked table, which you probably don't want. If you don't want to have to worry about the url parameters being forgotten and exposing data, you can create two nearly identical forms, one with the linked field and one without. You can then use the base url of the form that does not contain the linked field without any parameters for Full Sign Ups, and exclusively use the form with the linked field when sending out emails to the "Sign Up Later" people.

Here is Airtable's support article on prefilling and hiding form fields.

ibayub
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
6 - Interface Innovator
6 - Interface Innovator

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