Calculating a price from several items.

AppSheet AppSheet Q&A
1

Hi all, My first app,

My tables

  1. List of raw materials (Items).
ItemID (this UID) Title Price
IID1 Bolt 1
IID2 Nut 1.20
IID3 Washer .5
IID4 Locking Nut 1.50
  1. List of products made from the raw materials (Products).
ProductID (this UID) Title Price
PID1 Standard Fixing This is what I want calculated
PID2 Locking Fixing This is what I want calculated
  1. List of raw materials associated with each product. (Product_Items)
ProductItemID ProductID (ref to products table) ItemID (ref to Items Table) Quantity
PIID1 PID1 IID1 1
PIID2 PID1 IID2 1
PIID3 PID1 IID3 2
PIID4 PID2 IID1 1
PIID5 PID2 IID3 2
PIID6 PID2 IID4 1

I need a virtual column in Products that gives me a total price by returning the Item price (in items)* Quantity of each of the raw materials used (in the product_items table)

All the REFs: are in place, the product_items correctly links the items from Items to Products. I now just need the formula that will change as raw materials cost change.

I have to go, my 2 yr olds waking but I hope it makes sense, I will proof read tomorrow.

Thanks for your help on this.

2

Hi,

The most explanatory way is to add in Product_Items table some VC (short for virtual column)
[Material_Price] = [ItemID].[Price] DOT BETWEEN ].[
[Item_Price] = [Material_Price]*[Quantity]

Then in Products table VC
[Price] = SUM([Related_Product_Itemss][Item_Price]) NO . BETWEEN ][

VC are to be avoided because they have a great impact over performance but for your first app and “to get there quick”, they’ll do.

I hope it helps!

1 Like