After form submission automatically sync a cell between 2 tables if primary fields match

[Bandersnatch]

Hi everyone,

I’m hoping I can explain well where I am stuck. What I am trying to achieve (in short) is that

1. any time a form is submitted
2. sync a certain (calculated) field from form1/table1 to a corresponding field in table2
3. only if the email adress entered in the form and an email adress in table2 match (both primary fields)

In more detail:

I have created a seld grading quiz using this instruction: Self-grading quizzes with Airtable - DEV Community
What I am trying to accomplish is basically that the grade, that is a calculated field in table2, is synced to table2 which is our user database. The form requires the user to enter an email adress. If this email adress matches an email adress of table2 (pimary field), then the field with the score should be synced with a corresponding field associated to this record.

I hope I was using the correct terminology. I have tried using automations but I haven’t figured it out yet. I am very grateful for any help!

[TheTimeSavingCo · Accepted solution]

Hi Bandersnatch, I’ve set up an automation here that does what you’re looking for

The automation triggers when a form is submitted and then looks for a record in Table 2 with the same email.

If a record exists, then it’ll update the record in Table 2 with the score value

While this works, I highly recommend you check this out and use this instead

It’s basically the same, but instead of updating with the score value, it’ll link the two records together and uses a lookup field to get the Score value from Table 1 instead which ensures that the numbers in both tables are always the same

[Bandersnatch]

Thank you very much. Works perfectly with the second method that you proposed.

In order to prevent cheating I would like to only consider the first quiz taken. As it runs now the student could retake the quiz again and the score will be overwritten.
I have read a bit about preventing duplicates in airtable and it seems to be a problem… But is there maybe is some kind of workaround for this specific scenario?

Thank you again!

[TheTimeSavingCo]

Yeap, there is, and I’ve updated the second base with said workaround

The idea is that we use a rollup field in Table 2 on the Created Date field of Table 1 with the formula MIN(values), which identifies the earliest submission date. This would be the field Is earliest

We then pass that value back into Table 1 via a lookup field (This would be the field Earliest submission), and use a formula field to identify the earliest submission record for that email (This would be field Is earliest). We then use that formula field (Is earliest) in our conditional lookups to only display the data from the earliest submission

[airtable782ccc]

Hi @TheTimeSavingCo 

I'm brand new to Airtable and my query seems to be similar to that above and perhaps you can help with a slight adjustment of your solution?  Trying to avoid starting a new thread if not needed.

I have a form I need people to fill out which I pre-fill with an email address.  I do not hide this as in my use case a different person may complete the form.

I am trying to do a lookup, so that if the email entered on the form matches a person in my contacts list then the response should be linked back to that person.  My lookups on the Form data table aren't selectable however as per below.  Any ideas, please?

Regards, Brendan

[TheTimeSavingCo]

Hm sorry, could you provide some screenshots of your tables, forms and the automation in question?  I'm finding it hard to visualise your setup, apologies

[airtable782ccc]

Thanks for the quick reply Adam, much appreciated.  Please don't apologise.

Quick Overview with excerpts attached.  You can access if you like, it's all dummy data for now - https://bit.ly/3mzMkjt

The Base manages process.  There is a list of processes (one table), and each process has an owner who is responsible for reviewing the processes once per year.  There are two tables to manage the owner, one is a table for roles and another a table for people which includes their email address.  

1 month out from the process review due date, I've set up an automation to email the process owner to remind them, and this sets up a task in a new table with a status of pending.  In that email I send a pre-filled form linking the response back to the process.  Once a user completes the form indicating the process has been reviewed, I will have automation to update the task to completed status, set the process review date to today and make the next review date one year from now.

I pre-fill the email address but don't hide it, as it's possible that the process owner is away and delegates to somebody else, quite likely somebody who is not already in the contact list.  For this reason I'd like to pre-fill the email but have it editable.  I have the form linked to the review ID which is hidden, and the process defaults to the one linked to the task.  Ideally this would be visible but not editable but Airtable doesn't seem to support that.  I need the user to see this to confirm they're reviewing the correct item.

Now to the actual point at hand, when this form response is received, if the email address on the response is a match to a person in my contact list, then it should link that response back to that person's record.

I'm a Business Analyst so I have experience setting this sort of stuff up with developers in proprietary software, but first time in Airtable trying to construct myself sorry.  This is my very first attempt at Airtable.

Thanks in advance for sparing any time.

Regards, Brendan    

[TheTimeSavingCo]

Thanks for the details!  So we're trying to link the "Process Owner Responses" record to the appropriate record in "People" based on their email?

If so, try:
1. In "Process Owner Responses", convert that "People" field into a linked field to the "People table
2. Create a new automation that triggers when a form is submitted
3. Give it a "Find Record" action that will look for a record in "People" based on the email

4. Give it an "Update Record" action that will update the triggering record's "People" field with the record ID of the found record from the previous step

And here's a link to the base with it set up for your reference

[airtable782ccc]

Thanks so much Adam, I was quite close but 90% may as well be 0%!  That's all working, can't thank you enough.

I'm hesitant to accept as solution to mess with original post, but happy to do so or create another post as you wish.

Getting this done now has boosted my confidence, now for onto the next challenge 😄

Take care. Regards, Brendan

[TheTimeSavingCo]

Nah it's all good man, I'm happy stuff is working for you

Best of luck with everything!