I am attempting to set up a base to manage volunteer records. Volunteers complete several forms in the registration process. I would like their submissions from the multiple forms to be automatically merged into a single record, determined by a common email address entered on the different forms. I have the base set up with the different forms set up, I just can’t figure out how to auto-merge entries. Is this possible? Any suggestions? (Though I understand a single form would make this process very easy, a single form unfortunately will not work for our needs.)
Hi @Maggie_Ratnayake - this can be done, the trick is NOT to use Airtable forms. Here’s how I would set up the base:
- Each form submits to own table
- There is a link field which links the data in each table
So in a simple 2-form example, I want the data to end up like this:
Because Form 2 has the email address of the submitter, it can be a link field to Form 1. Back in Form 1, you can lookup the Form 2 answers and so merge your form data into a single table:
Here’s the problem with Airtable forms though - Form 1 is fine, but Form 2 isn’t suitable for this process because, as a linked field, email is “select from an existing list”. This is a problem because you have to expose the list of all emails in table Form 1, which you probably don’t want to do.
If you use a 3rd party form solution like Typeform, it doesn’t “know” about the field linking so your Typeform Form 2 can just have an email input field. When the Typeform submissions are sent to Airtable (I’ve used Zapier as the glue between Typeform and Airtable) the linking is automatically done by AT.
This solution relies on Form 1 being completed first to create the “master” record for each person (although any subsequent forms could be completed in any order).
It is a bit more complicated than a purely AT solution, but Typeform -> Zapier -> AT is a well-trodden path, so will work nicely. I’ve implemented similar “step process” solutions for clients before with no issues. Subject to certain limits, this could be implemented on the free plans of all 3 products too. You could also look at JotForm too - haven’t implemented Jotform in this way, but I’m sure it would work similarly.
Hope this helps!
In the case we are using the same form instead of having a form per tab, is there a way to reconcile after.
I think the Link and Lookup records can help with this issue.
For example, let’s assume that in the first form you want to get a name and phone namber,
and in the next form you want to get an additional details like an email, for the same person.
So, the first form can look like this:
and the table:
Now, in the second form you can use a lookup to get the data you already have:
and then create another form:
To be able to view the whole data in one table you can use a lookup, like here:
(you can see the ‘live’ table here)