Help

Merging data from one table to another

Topic Labels: Automations Formulas
Solved
Jump to Solution
770 5
cancel
Showing results for 
Search instead for 
Did you mean: 
CharlieMullin
6 - Interface Innovator
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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

Rollup Field - Overview | Airtable Support

See Solution in Thread

5 Replies 5

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
11 - Venus
11 - Venus

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

Rollup Field - Overview | Airtable Support

+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

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.)

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::

CharlieMullin_0-1690730409026.png

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:

CharlieMullin_1-1690730684903.png

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.