Skip to main content

Syncing Two Tables of Form Responses Tables into a Third, Combined Table

  • June 17, 2021
  • 3 replies
  • 57 views
K

Hello!

I have one AirTable database with three tables.

The first two (Form Responses 1 and Form Responses 2) are synced with JotForm, and they show the responses from two separate forms I have created with JotForm. All of my clients fill out both forms. The forms have different questions, but both start by asking for the name and email.

I would like for the third table to combine my clients’ responses in both forms (currently separated in tables 1 and 2) into one row. This would likely be through filtering by email. Is this possible? Let me know if screenshots would be helpful or if I can clarify in any way. Thank you!

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

I would recommend linking records together as opposed to syncing into one table in this case. If you were to sync into one table, assuming each person filled out both forms, you’d have two rows per person and likely will have every record missing one half of the fields since the forms ask different questions.

A third table in the same base with the email field as the primary column would allow you to pretty easily link all records in Table 1 to appropriate ones in Table 3, and all records in Table 2 also to Table 3. You’d just convert your two existing Email fields into Link to another record-types connecting to Table 3. If the field conversion messes with your integrations, then you could have a separate Link to another record-type field and copy in the value of the email (or use an Automation to do it for you)

That way you’d have one row per email (person) and you could use Lookup/Rollup fields to bring in the answers to all their questions from either table.

    K
    • Kelsey_Nichols
    • Author
    • New Participant
    • June 23, 2021

    I would recommend linking records together as opposed to syncing into one table in this case. If you were to sync into one table, assuming each person filled out both forms, you’d have two rows per person and likely will have every record missing one half of the fields since the forms ask different questions.

    A third table in the same base with the email field as the primary column would allow you to pretty easily link all records in Table 1 to appropriate ones in Table 3, and all records in Table 2 also to Table 3. You’d just convert your two existing Email fields into Link to another record-types connecting to Table 3. If the field conversion messes with your integrations, then you could have a separate Link to another record-type field and copy in the value of the email (or use an Automation to do it for you)

    That way you’d have one row per email (person) and you could use Lookup/Rollup fields to bring in the answers to all their questions from either table.


    Hi! Thank you so much for the advice.

    I set up the third table. In the second and third columns, I copied and pasted the same list of emails (taken from the first sheet, form responses 1) and loved how easy it was to link all of the records back to the first and second sheets (form responses 1 and form responses 2)!

    The only issue is that some people have filled out the first form and not yet the second form (they are still in the process of filling out the second form), which means that AirTable automatically created a blank record in the second form with just their email. I had to go back in and delete all of those blank records that were created in the second sheet (form responses 2).

    I’m not sure how to automize this process so that I don’t have to manually copy and paste emails into the third sheet. Can I do this as a basic automation? I also would like for AirTable to automatically add link the record to the second form only once the record has already been created in the second form.

    Let me know if screenshots would be helpful or if I can clarify anything. Thank you SO much for your help!

    Kamille_Parks11
    Forum|alt.badge.img+27

    Hi! Thank you so much for the advice.

    I set up the third table. In the second and third columns, I copied and pasted the same list of emails (taken from the first sheet, form responses 1) and loved how easy it was to link all of the records back to the first and second sheets (form responses 1 and form responses 2)!

    The only issue is that some people have filled out the first form and not yet the second form (they are still in the process of filling out the second form), which means that AirTable automatically created a blank record in the second form with just their email. I had to go back in and delete all of those blank records that were created in the second sheet (form responses 2).

    I’m not sure how to automize this process so that I don’t have to manually copy and paste emails into the third sheet. Can I do this as a basic automation? I also would like for AirTable to automatically add link the record to the second form only once the record has already been created in the second form.

    Let me know if screenshots would be helpful or if I can clarify anything. Thank you SO much for your help!


    A very simple Automation could do it. You just need an Update record automation step that copies the field value from email into the linked record field.

    Terms & ConditionsAccessibility statement