Take the SUM of Column Data in one Table and Present it as a Line Item for a Specific Row in another Table

[Vulf_Kirman]

The Set-Up:

• I have a Table called “Building Master List”

  • This Table has 2 views in which a Stone and Tile Takeoff was completed:

    • 

• I have another Table called “Stone and Tile General Info” which contains a summary of the information contained in the Building Master List Views:

  • 

The Task

• As an example, I would like to SUM the data in the “CT001” Column from the “Building Master List” Table and have it show up under the “Quantity” Column for the “CT001” Row in the “Stone and Tile General Info” Table:

  *

Please advise if this is possible to accomplish without manual typing as mentioned above.

I’m assuming this may be accomplished using Rollup.

Please advise.

[Bill_French]

@Vulf_Kirman,

I don’t have the skills to recommend a strategy that uses a native roll-up, but it seems it should be possible. You can certainly achieve this with a script block, but should ideally see if it can be a native approach first.

BTW - excellent requirements analysis and guideposts!

[Justin_Barrett]

I’ll second @Bill.French’s kudos for a very clear explanation. The one thing I don’t see in your explanation, though, is whether or not you have a link between [Building Master List] and [Stone and Tile General Info]. I don’t see one in your screenshots. Without a link tying records together, you can’t do a rollup.

I’m curious about your base design. The [Building Master List] table has a single field that only tracks the quantity for CT001. Looking at your other table, you’ve also got CT002, CT002A, CT003, etc. Do you have unique quantity fields for each of these items in [Building Master List]?

I also noticed that both tables are displaying views for what appears to be a specific property/project (“167 Chrystie”). Does that mean that for a different project/property, you have different records in [Stone and Tile General Info] for those same items? Meaning that there are numerous records for CT001, each one tied to a different project/property?

Something tells me that your base design needs an overhaul, but without seeing the full setup, it’s difficult to offer more specific suggestions. If you’re pretty deeply invested in the current setup, then my gut says that the scripting suggestion that @Bill.French offered is the best way to go. It could (and would need to) be designed to let you choose a specific view in each table, and it would build summaries from there to put into the {Quantity} field.

[Vulf_Kirman]

Hello Justin,

Thank you @Bill.French and @Justin_Barrett for the feedback!

• I’d like to clarify a few things. For the other tables such as Plumbing Fixtures and Appliances, I did link those tables to the Building Master List and was able to successfully roll-up the data. See below:
  

• The reason I was able to do that was because the appliances, from the perspective of a general takeoff, only required to be counted by unit. Additionally, due to the low level of variation per living space (ex: at most 2 of the same appliances per living space, as is demonstrated by the 2 column “Appliance” Placeholders.)

• I was able to quickly reference the 2 placeholders in the Roll-Up field.

• The tile and stone, on the other hand, had to be computed by surface area. This introduces, in my mind, a problem. Take CT001, for example:

• 

  • If we dive into the details, we’ll notice that CT001 is actually a combination of (4x) unique tiles in which the only common attributes are the ceramic material, thickness, and grout.
    • Based on this, assuming I took the “Appliance” Roll-Up approach, I would need to reconfigure my “Stone and Tile General Info” Table to include every variation of each type of tile. In short, there are a lot of variations and because there are a lot of variations, the Master List would need to have A LOT of columns or placeholders for each variation.

In conclusion, I am a very lazy person, and I was looking for the smartest path of the least resistance.

The scripting option sounds enticing; however, as I understand it, the Scripting block is only available until September 2020 for Free and Plus Users, so, as it stands, the Scripting option does not seem like a long term solution.

Thanks again for providing your perspective on the matter; I really do appreciate it. :slightly_smiling_face:

[Bill_French]

Vulf_Kirman:

the Scripting block is only available until September 2020 for Free and Plus Users

Prediction: the Scripting Block will forever be free.

[Dalton]

Hi,

Dalton from Airtable here! :wave:

I think a rollup field is going to be the best solution to solve this problem. I might be missing something so let me know if I’m taking a step in the wrong direction.

First, we’ll want to use a linked record field to link the first table, which has a lot of numbers we want to add up, to the table where we want to add them up. (screenshot below of an example instance)

The two images above highlight that I created different people’s run times (any quantity you’d like counted) and they are now being summed in a single record in my individual Run table (CT001).

I then created a rollup field and chose the linked table and field I wanted to pull from and the aggregation formula to use. (screenshot below) You can see that the sum from my Run Times table is now the same as the Total Time rollup field in my Run table.

I hope this highlights how I think a rollup field will solve you have to enter the quantity manually. Please don’t hesitate to let me know if you have any follow-up questions or if this doesn’t quite hit the mark.

[Vulf_Kirman]

Hello @Dalton,

I tried to follow your logic for my Base:

• Created a column called “CT001 Link” to connect “Building Master List” with “Stone and Tile General Info”
  • 
• In “Stone and Tile General Info” created a rollup field for the “Quantity” column and under “Aggregation” chose to take the SUM of all of the values in the “CT001 Quantity” Column:
  • 
• So now for the result:
  • 
    • Everything checks out; however, CT002 and the other types of Tiles are left without being aggregated.
      • Correct me if I’m wrong, but in order to roll-up multiple fields, I would need to use the Rollup field type for each Tile and finally create a Column that Sums the Sum of each Rollup using the SUM function in a “Formula” field type. Am I over-thinking this?

I’m going to try it.

Time to pull an all-nighter. “Plays Eye of the Tiger”

[Justin_Barrett]

Vulf_Kirman:

Correct me if I’m wrong, but in order to roll-up multiple fields, I would need to use the Rollup field type for each Tile and finally create a Column that Sums the Sum of each Rollup using the SUM function in a “Formula” field type. Am I over-thinking this?

Not really. In the end, it’s going to be a massive collection of links and rollups, but something in the back of my head says that some wires are going to cross somewhere and make this not work as cleanly as it sounds on the surface. Remember, you’ll need unique link and rollup fields for each material type, because they’re each pulling from a unique quantity field per type. It’s doable, but part of me feels that a script would be easier to work with because you wouldn’t have so many extra fields to mess with, plus you’ll have to make those links every time.

[Bill_French]

Justin_Barrett:

… something in the back of my head says that some wires are going to cross somewhere and make this not work as cleanly as it sounds on the surface.

Technical speak for - don’t cross the streams - it would be bad. :winking_face:

This is fair warning that you are probably going to end up with something that no one - possibly not even you - could explain in six months. Ergo, unsustainable, brittle, difficult to support, and likely to create more problems than it solves.

Complexities like this are probably best handled in script; no one – not even Airtable – cannot easily deny there are some practical limitations of a codeless architecture. But they also cannot easily deny you the ability to overcome these limits. One must ask…

To what benefit is it to prevent a simple data set - fully limited by the free tier to begin with - from being useful?

There is no advantage to charging for the right to script your way out of a cul-de-sac. The simple but powerful script block feature is the gateway for free tier and paid users to realize why Airtable’s value proposition is something worth growing with.

That’s why I believe my prediction is a good bet.