Rolling up items based on column data

[josh_vurnovas]

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.

Thanks in advance!

[TheTimeSavingCo]

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

[josh_vurnovas]

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.

[TheTimeSavingCo]

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

[josh_vurnovas · Accepted solution]

So I ended up doing something similar. I when you link a table's records it creates a column in the linked table that puts a linked record for each one that is linked. So here's what I did. I already had table 2 linked to table 3. So I made 4 rollup columns in table 2, each with their own special filters from columns in table 3 (things like materials ordered, materials received, the packet's location, etc.). Then in table 4 I linked table 2's records and the 4 rollup columns and parsed them into their appropriate views based on the rollup columns filters. The only hangup is that when a material is created--usually job specific--I need that material/record to be created in table 4 so I set up a form  as the only way to input materials in table two that runs an automation to link that record to table 4.

Thanks for the replies!