Simple How-to: totaling costs per order for online retail store?

[KirstenR]

Hello everyone,

   I am a complete novice to coding anything - I still need to reference for spreadsheet formulas, for example. I am also a fairly light user of Airtable. That being said, I would love to figure out if there's a way to auto-total merchandise costs for a complete order coming from our online store.

Essentially, we have two tables that pertain to orders - one is for an entire order which has basic things like subtotal, total, tax, etc that are pulled from our store via Airpower. We would like to create a Merchandise Cost field per order as well, auto-calculated from another table... but I'm not sure how to get that done. The second view (where the cost information lives) is a Order Line Items view that has the information for each item as a field - what order number, the number of that item, the cost, the sale price, etc. 

Is there a way to do a conditional sum by order number from the Line Items table (cost, quantity, order number) to the Order table so we can see the total item cost..? We'd be perfectly happy to add a third table to do the extra lifting as well, just no idea how to go about it. Super simple mock-up attached because describing this at the end of the day seems somewhat more difficult than it should be.

[TheTimeSavingCo]

Is there a link between the two tables that links the Order record to its Order Line Items records?  If so, you'd just need to add a rollup field with the formula `SUM(values)` I believe

If you could share some screenshots of your setup that'd be great

[KirstenR]

Hi!

No, there is no link, both pull directly from Shopify via Airtable and I'm not sure how that works.

The big issue I see is that Order # is not the same thing in both fields even though it originates from the same field. I don't know how the shopify backend handles orders as a whole vs items in each order, honestly.

On the orders table, the order # shows only once and gives a summary of the entire order - I have fields like the subtotal, tax, shipping, etc linked into this table but there's no way to get total costs from this section directly - those are linked to line items and don't seem to be summarized in the Spotify order itself anywhere directly.

In the Order line items table, the information is being pulled per item and the order # field has one record for each item. The other fields give a summary for that item: quantity ordered, quantity in-stock, price, cost... 

I would love to know if it's possible to create a field on the order page where I have a automatic total of the costs of those individual line items with the same order number. "Sumif" sort of argument. The summing can happen on either table.  

[TheTimeSavingCo]

Thanks for the screenshots!  You've got a link between the two tables that links the Order record to its Order Line Items records actually, so all you need to do is:

1. In the "Orders" table, add a new field
2. Select "Rollup"
3. For "Linked record field to use for rollup", select the "Order Line Items" field
4. For "field you want to roll up", select the field in the "Order Line Items" table that you want to sum up
5. For the formula, enter `SUM(values)`

This will do what you need I believe