Hardest Problem I've Ever Encountered

[GolfCartsUSA]

Okay guys, got a doozie...

Basically, in the screenshot I have a table, which is the main table that 99% of things sprout off from. We have customers, who through a webflow form are able to select accessories to add on to their purchase. That gets relayed to the table in the form of a checkbox being selected/unselected. 

Here's the problem -- I need to calculate accessories inventory, for each individual column AND be able to automatically update the rolling amount in another table, which uses the accessory name as the primary record. 

I've tried EVERYTHING I can think of from linked records, to roll up fields, to automations, and formulas. If anyone can calculate how this can be done I'll literally send you a $100.

[Dan_Montoya]

Are we to assume that each check mark represents a quantity of 1?  

[GolfCartsUSA]

Yes

[Dan_Montoya]

I put together a POC for you here. This is just one way to approach it.  

There is an inventory table that keeps track of what has been ordered via rollups.

Then there is an inventory items table that has you actual inventory levels.

An automation updates the on order field when ever it changes in the inventory table.

[GolfCartsUSA]

Can you create a loom on this so I can replicate it? This looks like it has potential

[Joseph_Roza]

@Dan_Montoya's solution looks pretty solid.

The Customers table is data from your webflow form. Each record appears to automatically be connected to the Inventory record of the Inventory table (likely done with an automation or within the form itself with an uneditable field). The Inventory table rolls up each record from the Customers table which has its associated field checkbox field as checked. For example, the Side Steps on Order field appears to be a rollup/count field of the Customers linked record field that counts the records with a filter of if the Side Steps field of those records is checked. That inventory record is also connected with all the records of the Inventory Items table and rolls up/counts each individual item in the On Order field. The In Stock field appears to be a number field that you manually fill, and the Items Available field appears to simply subtract On Order value from In Stock value.

Granted, this is simply an explanation from looking at the base. Dan can probably explain his idea better, but I can't see anything I'd personally change here. I would suggest copying the base yourself and verifying if my explanation fits. I didn't copy it myself as I already have ~a million bases in my workspace.

I don't mean to hijack your thread, @Dan_Montoya. @GolfCartsUSA asked me to hop in, so I'm just trying to be helpful.

Feel free to ask any questions if any of this doesn't make sense.

[pressGO_design]

With the same caveat as @Joseph_Roza (I was asked to weigh in), I would tweak @Dan_Montoya‘s solution by having a 1:1 junction table with linked customers and linked inventory items.

[GolfCartsUSA]

Would you be able to share a base/loom with this setup so I could replicate it? Not sure what a junction table is.

[pressGO_design]

Here's a link to the base. Short video attached.