* Choose ( Table::Field A - 1 ; 75 ; 65 ; 55 ; 45 )
* ( 85 - 10 * Table::Field A )
You could setup Field D to be a calculation, and then use a formula such as this:
Field C * Case ( Field A = 1; 75; Field A = 2; 65; Field A = 3; 55; Field A = 4; 45; 0 )
If you want to use a script to set Field D, you could use the "Set Field" script step to set the field based on that same formula.
1 of 1 people found this helpful
One method you could use is to use an auto-enter calculation(and uncheck do not replace) for Field D, and use a case statement to test the various options.
Field A = 1; Field C * 75;
Field A = 2 ; Field C * 65;
Field A = 3; Field C * 55;
Field A = 4; field C * 45 )
Got it! Thanks!
Here is a curve ball. What if Field A is an editable drop down list? Is there a way to leave the calculation open to having new numbers as well?
I think it depends...
What might those other values look like? And what would their relationship be to the multiplier?
This is what the script looks like now(with the actual fields being used).
Square Ft * Case ( Pitch = "10mm"; 75; Pitch = "26+"; 60; Pitch="N/A"; 0; Pitch = "22-26mm"; 60; 0 )
In the pitch field, these numbers are predefined in a value list. If someone inputs a new number into the pitch field, is there a way to have it automatically calculated just like the others are?
If someone inputs a new number into the pitch field, is there a way to have it automatically calculated just like the others are?
Those others aren't calculated “automatically” – there is a logic that you set up. You'd have to either amend that calculation to cater for new values – each time you have a new value, or – and that would be sort of automatic – create a lookup table where each pitch entry has an accompanying value.
You need to provide a way to add new pitches to that table and require the specification a a value to go with it.
Then define your value list based on that table (ideally using a primary IDs and the pitch name), and set up a relationship between the pitchID foreign key in your table and the pitch primary ID in the lookup table. Now it does work automatically, and the calculation is simply
Square Ft * PitchLookupTable::value
Thanks a million!