Merging data from multiple forms by primary field

[Michael_Stein]

We are currently looking to collect a wide array of data on individual subjects at several data collection stations. Because of this, we have taken a single table, and have built several distinct forms off of it in order to split the data collection between different computers. For instance, on a single football player, one station may record height and weight, while another station records position and running speed.
However, when we try to do this, despite having the same primary field, we have multiple records for each sample, looking something like this:

Individual…Height…Weight…Position…Speed (sec)
John_______6’_____200
Peter______6’2_____210
Andrew____6’4_____220
John_____________________QB________4.75
Peter_____________________WR_______4.69
Andrew___________________RB________4.20

Despite the fact that the sample in the primary field the same individual, using two forms to fill in data is causing a repeat of the individual’s entry. Is there a way for us to have the table fill in so that it looks something more like this:

Individual…Height…Weight…Position…Speed (sec)
John_______6’______200_____QB_____4.75
Peter______6’2______210____WR_____4.69
Andrew____6’4______220_____RB_____4.20

We are really looking for a way to get separate forms on separate computers to fill in fields on the same data entries on a single central table.

[openside]

Hi @Michael_Stein - you’ll likely need to use something like Zapier/Integromat to perform an update on the existing record.

Also check out On2Air: Forms for a robust approach to advanced form functionality (by also using Jotform).

[JonathanBowen]

Hi @Michael_Stein - one option would be to have the data entered as you suggest, i.e. multiple rows for the same person, then, if you have the pro version, use the dedupe block to match and resolve any dupes:

Result:

The only slight downside of this process is that you need to do each pair (or more) of dupes one by one - it doesn’t look like you can set a method or a macro and have AT run through all matched dupes. If you’ve got a lot of records this might be an issue.

JB

[Justin_Barrett]

I worked out a way to pull this off directly within Airtable, though it does require a tiny amount of prep.

Make a [Players] table, delete all but the primary field, and enter the names of all players for whom you’ll be entering data.

This is the “tiny amount of prep” mentioned: player names will have to be entered onto this table before any other data entry can occur. However, if one of your staff has access to this base on the day that data entry is happening, someone can just yell across the room/field, “Hey, add an entry for Bob Hoskins, would you?” :winking_face:

Before doing any more on that table, make another new table. I called mine [Data Entry], and deleted all records before messing with fields. I made the primary field an autonumber field. The next field I named {Player}, and made it a link to the [Players] table. The next four fields are {Height}, {Weight}, {Position}, and {Speed (sec)}, designed as you wish for the data you want to collect. Here’s my setup:

Back in the [Players] table, you’ll have a link field coming from [Data Entry]. I renamed mine to {Stats}.

Next I added four Lookup fields to the [Players] table. They all target the {Stats} field, pulling data from, and echoing the names of, the various stat fields from [Data Entry]:

In terms of forms in the [Data Entry] table, you could go with a single form view that has all fields visible, or make two forms, one to be used by each data entry station, and only containing the fields relevant to the respective stations. The only difference compared to your current setup is that those doing data entry will select the player name from a list instead of entering it manually.

Once data entry is done with our three sample players, the [Data Entry] table will look similar to your first example above:

However, on the Players table, the lookups keep the details for each player lined up properly.

[Justin_Barrett]

Okay, I couldn’t resist playing further. Added some fields, tweaked some things, and now you can use the same forms to update a given player’s stats.

Let’s say that John loses a little weight and picks up the pace of his speed test. Fill out the relevant form(s), but only enter his new weight and speed. After that, the [Data Entry] table might look like this, with a second record for John at the bottom of the table:

With the original setup outlined in the previous post, you’d see the weight on the [Players] table listed as “200, 195” and the speed listed as “4.75, 4.50”. With my tweaks, only the newest data appears:

With people often asking about how to use forms to update records, I’ll probably write up the details for these tweaks in a new post in the Show and Tell category. Obviously there are restrictions, the biggest one being that you have to set up one table with the primary field values entered in advance. However, this system could be very useful in situations where that’s an option.

EDIT: While fine-tuning the details for my show-and-tell post, I found that it is possible to add names on the fly…with a tiny catch, of course. With more than one data entry station operating at the same time, it may be tricky coordinating between them so that new additions at one station are picked up by the other, but not horribly so. I’ll add a new comment with a link to that S&T post once it’s done.

[Justin_Barrett]

Here’s the post that outlines the full system:

Editing an existing record via an Airtable form…sorta-kinda… Show and tell

Summary People sometimes ask whether it’s possible to use an Airtable form to edit an existing record. Technically the answer is “no” because Airtable forms always create new records. However, a base can be set up so that records created by a form end up affecting records in another “mirror" table. As with my first show-and-tell submission, this was inspired by a solution that I created in another thread. Long story short, the key to that solution was using two tables: one for collecting form …

Now, about that “tiny catch” I mentioned. Say you have people entering data at Station A and Station B. A is covering height and weight, B is doing position and speed. Whoever is the first to create (or, technically, assign) a record for a new player will need to inform the other station once they’re done, so that the other station can refresh their form. Without refreshing the form, the newly-created/assigned record won’t show up in the “Player” list. It’ll still appear as “Unnamed record”. It’s entirely possible that picking it would still make the correct assignment, but it’s a gamble that I haven’t tested.

In short, if you use the full system I outlined in that other thread—meaning you’re adding names as you go, rather than pre-adding them as I outlined in my initial solution above—make sure that the two (or however many) stations doing data entry stay in constant communication. If Station A assigns the record first, Station B must refresh their form before updating that record, and vice versa.

[Moe]

This is a tool if you would like to do it the other way around and have one form that splits data in multiple tables.