Aggregating Data between Tables

[Mitchell_Wells1]

Hi,

I’m a little frustrated with how Airtable has decided how Tables are meant to work. I have 2 types of data sets that need to be treated significantly differently from one another, so it makes perfect sense to me that they should have their own tables. However, I still really need a way to aggregate information from both tables at once based on parameters they share.

To put it in simplest terms, I have Apple in table 1, and I have Pear in table 2. Each are tagged as fruits in their respective table. I would like to be able to create a gallery view filtered for “Fruit” that includes any record that has been tagged as a fruit from both table 1 and table 2.

This would be very simple if all the data was in one table, but because it is in two, the only ways I know how to do it would be a complete chore.

I think I’m failing to see the utility of having multiple tables in one base. For the sake of organization I would like to be able to split up my data in a way that reduces redundant fields, but it seems to come at great cost with how I can then manipulate and aggregate that data.

[Jeremy_Oglesby]

Your description of your data as “Apple” and “Pear” doesn’t give us much to work with in understanding what you need to do with this data and why it’s so important that they be in different tables.

So, to address the simple scenario of “Apples” and “Pears”, that are each in their own table, but both marked as “Fruits” – you could keep them all in the same table, and use a single-select field to label them as “Apple” or “Pear” – then use grouping, sorting, filtering, and hiding of fields to build views that do what you need to do with them. Build an “Apple” view that only shows the fields relevant to “Apples” and only shows your “Apple” records. When you need to work with “Apples”, use that view. Ditto for “Pears”.

Another option, if the first does not suit you, is to create a third table – “Fruit”. The “Fruit” table is where you will enter any data that is common to “Apples” and “Pears”. Then, each “Fruit” record will link out to either an “Apple” or a “Pear” from the other two tables, and each of those tables will contain the data unique to their types. The “common” and “unique” data can be passed back and forth via Lookups and Rollups, and thus can be used in aggregation interactions.

• So the structure of this will ultimately be that each “Fruit” you make starts with a “Fruit” record, and links to an “Apple” or “Pear” record. Each “Fruit” will have 2 records associated with it, which will have a one-to-one relationship. In this way, every “Apple” or “Pear” ultimately inherits from, or is an instance of “Fruit”

[Mitchell_Wells1]

Hey Jeremy,

Thanks for your reply.

Your first suggestion was what I was considering doing, but it produces a massive amount of empty and redundant fields. I know that if I am careful with hiding and filtering I can create views where these empty fields will never be seen, but I’m a little worried about adding any complexity to the overall setup. I’m not going to be the only one using the Base. I’m envisioning a scenario where someone accidentally messes with a view and doesn’t know how to return it to how its supposed to be because the settings are really intricate. What’s worse is that my page designer pages could be completely compromised in that situation. I mean, there’s always Ctrl-Z, but things happen.

I’ll expand on the predicament. Basically, I have a bunch of different records that describe the interior design of a house on a room by room basis. I have records that list the appliances and I have records that list materials. The fields I need to describe the materials and the fields I need to describe the appliances differ significantly, but I still need to be able to view both simultaneously in a view that summarizes them by their shared property - in this case, the room they’re in.

I also want to be free to add more and more information over time. As more and more types of info is added to describe each room, I want to be sure that it will never reach a point where it gets overwhelming how many differing types of data we need to handle. Tables seemed like a simple way to silo data off from each other, because as we begin to require more unique fields to describe unique products, it is much simpler to have a dedicated tab that deals solely with that unique product and its properties.

With the method that Airtable prescribes, it basically means that my views setup will gradually get more and more bloated over time with unnecessary fields that need to be hidden by increasingly complex view setups. It seems like Airtable’s ability to copy a view configuration means that this is probably less problematic than I’m making it out to be, but anyway… It’s just not the ideal situation I’m looking for.

Anyway, I’ve already written far too much just on your first suggestion. I’m mulling over whether your second suggestion would be more what I’m looking for. I think I have to make a quick example for myself and see how Airtable deals with it.

Thanks again for your help.

[Jeremy_Oglesby]

Just to address this quickly:

Mitchell_Wells1:

I’m envisioning a scenario where someone accidentally messes with a view and doesn’t know how to return it to how its supposed to be because the settings are really intricate.

Airtable allows you to LOCK a view (and password protect it) so that nobody can change the filter, group, sort, and color settings without unlocking it first.

But moving on to the rest – Airtable behaves like a database, and not like a spreadsheet. It’s database behavior is pretty standard across databases, so using it like a database rather than a spreadsheet is going to yield the most benefits, in my opinion. And that means modeling relationships between different kinds of “things”, where a table represents a collection of “things” that are alike.

What I’m going to suggest may sound onerous if you are not accustomed to working with databases, but once you get the hang of a workflow in Airtable, it really isn’t bad – especially if you get the hang of keyboard shortcuts et. al.

If I were building this base, I would probably use at least 5 tables:

1. Houses - each record represents a House and any data you need at the House level
2. Rooms - each record represents a Room and any data you need at the Room level, and each Room belongs to a House (linked); a House can have many Rooms, but a Room can only belong to one House
3. Room Designs - each record represents a Design for a Room and any data you need at the Room Design level, and each Room Design belongs to one or more Rooms (linked); a Room could have many potential Designs, and a Design could be reused for any number of Rooms by being linked to multiple Rooms
4. Appliances - each record represents an Appliance and any data you need at the Appliance level, and each Appliance belongs to many Designs (linked); the same Appliance record is reused in any Design record that uses that Appliance
5. Materials - each record represents a Material and any data you need at the Material level, and each Material belongs to many Designs (linked); the same Material record is reused in any Design record that uses that Appliance

By using a structure like this, an Appliance, for example, is represented in a Room by way of a Design – a Design record is where an Appliance meets a Room.
If there are a couple pieces of data about an Appliance that you need to pull into the Room record, you can do that by using chained Lookups retrieving only those couple pieces of data – first into the Design record, and then through the Design record and into the Room record.

You may be thinking to yourself that this sounds like a lot of table jumping – you may feel like you’ll be clicking all over the place to navigate around. But I assure you that Airtable accommodates this workflow very well. For this example, let me assume you already have your Appliances and Materials tables populated with your regularly used items.

You can easily create a design without ever leaving the “Houses” table. Starting there, you create a House and fill in all its data. When you reach the field where you link to Rooms, you expand that field and select “+ Add New Record”. When you do that, you remain in the House table, but a Room record will be created and expanded vertically for you to fill out. You create your Room record, and when you get to the field where you link to Designs, you, again, select “+ Add New Record”, and, as you are still in the House table, a Design record is created and expanded on top of your Room record. When you finish filling out the Design record, you hit the escape key and it will close the Design record and you will be back at the vertically expanded Room record. If you are done adding Designs to the Room, you can hit the escape key again and you are back at the House table.

[Mitchell_Wells1]

Hey Jeremy, just wanted to come back and say that your advice really helped in making me realize that I wasn’t really digging into the power of the relational database side of Airtable. What I ended up doing is very similar to your second suggestion - adding a new table that is just the names of all the rooms in the home. Then I just converted my Rooms field in my other tables to link to the Rooms table instead. Now my products and materials are shown quite harmoniously together. It’s also clear to me that new types of data will link in just as well too - so everything is working out nicely.

The only thing missing - and this is an oddly pedantic thing - is that my “Rooms” now show up not in the nice bubbled tags that single select/multi select fields do, but instead as raw text in my page designer layouts. It’s a shame, but probably a worthy trade-off.

Very interesting idea to make tables that would allow all our projects to live in the same Base. However, I think that amount of data in one base might get a little overwhelming. Not to mention eventually we would end up fighting for attachment space.