# Rolling up items based on column data

Topic Labels: Base design Formulas
Solved
1033 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey, all!

I was wondering if there is a way to gain the sum of values based on column data. Here is what I'm looking for:

I have four tables--materials, packets, procurement, & purchase orders. I link the materials to the procurement table to populate all the details of the material. I link the packet to that line item to signify what I'm charging to (it has job info and everything). Then I need shipping and receiving to check the items in so that we can determine that everything from a given packet has been received which is being validated by the purchase order. What we currently have to do is check every line item in as received. This is rather tedious, and all I really want is to determine that everything on the purchase order is being received. Additionally, if I can solve this, I would love a sum of how much of each material I have in stock for a given job, not a given packet as there are multiple packets per job. While some of this may seem redundant, it is due to the number of hands in the cookie jar, so to speak.

Hopefully I worded this coherently so that the Airtable wizards can help solve this conundrum.

1 Solution

Accepted Solutions
5 - Automation Enthusiast

Thanks for the replies!

4 Replies 4
18 - Pluto

Hmm, could you provide some screenshots of your data and some examples of what you'd like to see instead?  Apologies, I don't really follow what you're trying to solve for

5 - Automation Enthusiast

Sorry for the late reply. Here is a spreadsheet example of my thought process. I have packets that track the given item that travels through the shop. Each packet has a job/client. Additionally, I have a material table that include our stock materials and job specific materials. The third table is where I determine how many of what material I need for each packet. Here's what I'm trying to figure out. How can my fourth table sum the quantities of a given material based on the job...I don't want it to simply group them, because then I will have all the same line items and that currently does not benefit shipping and receiving. An added benefit is the ability to know whether or not I can release a packet to the floor based on the job's material inventory, not the packet's material inventory.

I don't mind writing a script, but my initial attempt implementing a hash map java script started to time out due to the amount of records it had to look through.

Ahh, thanks for the details!

If I were you, I'd create a linked field between Table 3 and Table 4.  In Table 3, I would create a formula field that would concatenate the job name, and material name, e.g. `Job 1 - Material 1`.  Then I'd have an automation or something that would paste the value from this formula field into the linked field.

In Table 4, each record will now represent a single material from a single job.  I would then create a rollup field with `SUM(values)` which should do what you're looking for

5 - Automation Enthusiast