Merging data from one table to another

[CharlieMullin]

We’re having to maintain data records in two tables, rather than one, because forms only create new records, not updates.

Here’s the scenario:

New Assets are created in the Assets table:

ASSETS

Asset Name	Asset Location	Module Name
Apple	apple.com	Truck
Banana	banana.com	Truck; Plane
Grape	grape.com	Boat

The Module Name column is Linked to the Modules table:

MODULES

Module Name	Module Location	Module Type
Truck	apple.com	Course
Boat	banana.com	Course
Plane	grape.com	Video

There’s no problem creating the Assets records, either through a form, or directly in the tables, by those of us with those permissions.

(Note that the Banana asset is associated with two Modules: Truck and Plane.)

When we want to update an Asset record, we can only do so by working directly in the table.

Some team members can only update records through a form. When they do so, a new record is created, but we do not want to add more records to the Assets table since we expect each Asset record to be unique. We would prefer not to see two Apple records, for example. So, we use a form pointed at the Assets Update table, and it might look like this:

ASSETS UPDATE

Asset Name	Module Name
Apple	Plane
Apple	Boat
Banana	Boat
Grape	Plane
Grape	Truck

So, in order to see all the Modules that an Asset is associated with, we have to look at two tables.

What are some ways to merge Module Name updates made in the Assets Update table back to the Assets Module names in the Asset table, so it would look like this:

ASSETS

Asset Name	Asset Location	Module Name
Apple	apple.com	Truck; Plane: Boat
Banana	banana.com	Truck; Plane; Boat
Grape	grape.com	Boat: Plane; Truck

Maybe there’s an automation we could execute.

Or maybe there’s a better way to handle this from the beginning.

Thanks for any ideas!

[ScottWorld]

Yes, lots of people just setup an automation to do this. You would have one table that is simply your "form collection" table, but then the automation would do the heavy lifting of putting everything into the right places.

However, if you're open to using external tools to do this, here are 2 great options:

1. Move your form to Fillout, which is currently the best form tool on the market for Airtable. Fillout natively handles everything on your behalf within one form. Everything you want to do is natively handled by Fillout, and it's extremely inexpensive too. It's so inexpensive that they even have a very generous free plan... most of my clients are using Fillout for 100% free.

2. Still keep your forms within Airtable, but instead of using Airtable's limited automations, you can use the advanced automations of Make. Make has much more advanced automations than Airtable, it has much better conditional support & looping support than Airtable, and it can handle merges/updates significantly easier than Airtable because it offers its own upsert function for Airtable. There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with the links to a few other Make training resources.

p.s. If you'd like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Sho · Accepted solution]

I think you could ARRAYJOIN the ASSET UPDATE table from the ASSET table with a ROLLUP.

Rollup Field - Overview | Airtable Support

[TheTimeSavingCo]

+1 for what Sho recommended, you should be able to get the data to display the way you want pretty easily with rollups

For future reference, you can also check out Airtable's "How to update records via a form" guide

[CharlieMullin]

Thanks for the info, Scott. The Fillout app looks intriguing and I'll try that out after I exhaust some native Airtable capabilities. (Rollups may work for me after all.)

[CharlieMullin]

Hi SHo:

Yes, using a Rollup gets me very close. I already use an automation to move some data around and am able to see, in the main Assets table, the original Module value, when the record was first created, and also the additional Module values added via a form into the Updates table. But, I wish I could get these all into one column, rather than two, and ensure that each value can be a link to the Modules table.

Consider this display from the Assets table::

Asset Name and Module values were added in the original record. The Module linked field contains one value. 

Through my update form, tied to the Asset Updates table, I added two more Module values:

You can see in the Assets table pix at the top that both newly added Module values Adding Captions and Adding Photos appear in the Module in Updates column.

This is good because all three Module values are now part of a single record in the Assets table, even though they appear in two columns.

How can I make those two newly added Module values links back to the Modules table record, like the value shown in the Module column?

How can I merge all three values Module values into one column, where all three are links?

Any ideas are most welcome.