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

    Help with some scripting

    tdennis

      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
          jbante

          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
            TimDietrich

            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
              RobertWard

              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
                tdennis

                Got it! Thanks!

                • 5. Re: Help with some scripting
                  tdennis

                  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
                    TimDietrich

                    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
                      tdennis

                      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
                        erolst

                        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
                          tdennis

                          Thanks a million!