AnsweredAssumed Answered

Calculation using values in specific records in unrelated table

Question asked by lcot17 on Jun 19, 2018
Latest reply on Jun 19, 2018 by philmodjunk

Ugh, another head scratcher for me. I am attempting to run a calculation to estimate the cost of an order depending on which options have been checked.

 

To simplify, let's say I have an 'Orders' table and a 'CostPrices' table.

In the 'CostPrices' table I have the following fields:

  • pk (unique serial number starting from 1)
  • Name
  • Cost Price

 

So the workflow is as follows:

User creates an order >> selects upgrades from a range of checkboxes. The checkbox value set names are the same as the records in the 'CostPrices' table.

 

The calculation field in the 'Order' table would then SUM the 'Cost Price' values together from the selected checkboxes.

 

If I relate the CostPrice to the Order record with a OTM relationship, I can only have one upgrade per order.

 

So, How best to achieve this?

Outcomes