How can I link the value of multiple items together onto one item through automation?

In my case, I am looking to take the orders from my order board, each with a “product type” status and an “amount of product used” number, and connect them to my inventory board, where each item in that board represents a different product type. Currently, I can manually link each “order” item (in the order board) to the associated “product type” item (in the inventory board) using the connect boards column. Then I can use the mirror column on the “amount of product used” to sum all of those order’s values, thus giving the total amount used of that particular product. And because, that mirror column is a cell (unlike the summary) I can use formula columns to find out how much product if have left, how much profit gain, etc. But I would love to be able to do this using automation do to potential mistakes and repetition required to do this manually.

Now if there is another way to do this, I would be happy to give it a try, groups would work, if the summary could be used in a formula or used in another item, but that isn’t the case.
The end goal is to have the amount of product left that is found by adding up the amount of product used for a specific product.

1 Like