Pulling records from multiple tables into another table

[BryceD]

I have a base used to track conservation information about media in different formats. Each format has its own table. The tables are: Film, Video, and Computer Media.

Across the tables, each record is an individual item. The fields for each table differ, as the conservation data in those fields is different depending on the media. (For example, Computer Media like a floppy disk does not have a film gauge of 35mm, 16mm, etc.)

I want to create a fourth table that pulls in select records from all three tables. I want to pull in only records that have "reformatting candidate" checkbox field selected.

I know this could possibly work by having all the records in a single table, but the amount of fields would grow quite large and would require multiple different views with various fields left empty and hidden. It is also more user friendly for us to have each table be delineated by media type.

So, is there a way to pull select records from multiple tables into another table? Thank you!

[Kenneth_Raghuna]

You can't truly pull the records from multiple tables into another table. The best database design would include having all records in the same table and creating different views as you've outlined. I understand your reasons for not wanting to go this route though.

There's a couple workarounds you can do that involve either linking records from the 3 parent tables into table 4, or running automations to create duplicate records. Both can become cumbersome depending on how you intend to use table 4.

If all you need to pull from the parent tables is the record names, and the rest of table 4's data is new info, then you can achieve a decent workaround with 4 fields (3 linked fields-- one to each parent table, then 1 formula field that displays the name of whichever linked field is not empty). If you need to pull more data from any of the parent tables though, it will start getting cumbersome fairly quickly.

If you go the automation route, you can create duplicate records that can quickly pull in information from the parent tables. It will require one automation per parent table (3 total). Your team will need to remember though that edits to this information in table 4 will not be reflected in the parent tables, unless you write more automations to handle that. Even if you do that, syncs will always be instantaneous and this can cause problems.

[TheTimeSavingCo]

If you're on a Business / Enterprise plan, you could also try syncing your tables into a single table in another base, and then syncing that back over maybe?