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!

[Adam_TheTimeSav · 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!

[Adam_TheTimeSav]

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