9 Replies Latest reply on Oct 16, 2014 11:35 AM by tdennis

# Help with some scripting

Hi all - Can someone please help me translate the below into a calculation?

If Field A= "1" then multiply Field C by 75. The answer must be reflected in Field D.

If Field A= "2" then multiply Field C by 65. The answer must be reflected in Field D.

If Field A= "3" then multiply Field C by 55. The answer must be reflected in Field D.

If Field A= "4" then multiply Field C by 45. The answer must be reflected in Field D.

• ###### 1. Re: Help with some scripting

Table::Field C

* Choose ( Table::Field A - 1 ; 75 ; 65 ; 55 ; 45 )

or

Table::Field C

* ( 85 - 10 * Table::Field A )

• ###### 2. Re: Help with some scripting

Hi there.

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.

Good luck!

-- Tim

• ###### 3. Re: Help with some scripting

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.

i.e.

Case (

Field A = 1; Field C * 75;

Field A = 2 ; Field C * 65;

Field A = 3; Field C * 55;

Field A = 4; field C * 45 )

1 of 1 people found this helpful
• ###### 4. Re: Help with some scripting

Got it! Thanks!

• ###### 5. Re: Help with some scripting

Hi Tim,

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?

Thanks,

T

• ###### 6. Re: Help with some scripting

I think it depends...

What might those other values look like? And what would their relationship be to the multiplier?

-- Tim

• ###### 7. Re: Help with some scripting

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?

• ###### 8. Re: Help with some scripting

tdennis wrote:

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

• ###### 9. Re: Help with some scripting

Thanks a million!