Checkbox Set summation

[Tracy]

Hi Forum,

I have a text field called "Plate1 Options" that is defined as a checkbox set. This field has 14 options that can be selected (single or multiple selections). An output of what I'm looking for is a second field called "Plate1 Option Labor Time" that adds up all the calculated results based on the selection of the checkbox set. I was hoping the second field "Plate1 Option Labor Time" to have all the calculations with in it, based on the selection using the "If" statement (See attached image).

If this is confusing, here is the premise of what I thought would work, but doesn't:

"Plate1 Options" - Text field defined as checkbox set:

Option#1 - Bake

Option#2 - Brake

Option#3 - C'Bore

Option#4 - C'Sink

Option#5 - Debur....etc

 

Each option has a unique calculation associated with it. For example:

if "Bake" is sected, the calculation I want to use is: .1 * Plate1 Qty = Result

if "Brake" is selected, the calculation is: .02 * Plate1 Qty = Result

if "C'Bore" is selected, the calculation is: .008 * Plate1 Hole Qty = Result (I won't show the rest, but you get the idea)

if "Bake" is only selcted, the result of the calculation will display in "Plate1 Option Labor Time" field. If multiple selections are made, then the calculated results of each selection made is summed and displayed in the "Plate1 Option Labor Time" field.

 

I would like to only use the two fields for this. "Plate1 Options" for the selection and "Plate1 Option Labor Time" to display the calculated results based on the selections of "Plate1 Options". I have attached of what I thought the "Plate1 Option Labor Time" calculation should be, but it's not working.

 

I search many forums and found similar issues but not what I'm trying to achieve. Any help would be greatly appriciated.

Tracy

[comment]

Technically, it is possible to construct a monstrous calculation like the one you want. It would look something like:

Sum ( 
If ( not IsEmpty ( FilterValues ( "Bake" ; Plate1 Options ) ) ; 0.1 * Plate1 Qty ) ;
If ( not IsEmpty ( FilterValues ( "Brake" ; Plate1 Options ) ) ; 0.2 * Plate1 Qty ) ;
If ( not IsEmpty ( FilterValues ( "C'Bore" ; Plate1 Options ) ) ; 0.1 * Plate1 Hole Qty ) ;

// add more here

)

However, this is not a good way to go about this. For one thing, you get no overview and cannot check for mistakes. More importantly, if you ever change the labor time of an item, it will modify your existing records.

What you describe is very similar to an invoice having multiple line items ("options" in your example), with each item having its own price ("labor time" here). And the preferred arrangement to handle such situation is having 3 tables related as:

Invoices -< LineItems >- Products

where the Products table stores the current price of each product, and the LineItems table looks it up into its own field.

__
P.S. Are you really still using version 11?

[Tracy]

Thanks for the response and your valable input. Yes, still working on v11. Company won't allow for any upgades...grrrr!