Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 15, 2020 03:36 PM
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.
Jul 15, 2020 05:24 PM
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!
Jul 25, 2020 10:01 AM
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.
Jul 25, 2020 12:16 PM
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:
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:
Jul 25, 2020 01:21 PM
Prediction: the Scripting Block will forever be free.
Jul 27, 2020 02:47 PM
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.
Jul 27, 2020 03:59 PM
Hello @Dalton,
I tried to follow your logic for my Base:
I’m going to try it.
Time to pull an all-nighter. “Plays Eye of the Tiger”
Jul 27, 2020 04:30 PM
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.
Jul 28, 2020 06:41 AM
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.