8 Replies Latest reply on Feb 11, 2013 9:20 AM by eshoshin

    Using a range in an If Statement

    rwvanleer

      Hello all.

       

      I am stuck on a scripting issue I could use some assistance with. I have a couple of fields which require user input and a calculation is returned in a third field. This all works fine however, the next step is what is throwing me off. Based on the range which the calculated result falls in I want to have another field populated with data. For example, if the calculation total is less than 3500 I want the last field to show 3500. If the number falls between 3501 and 4000 I would like it to show as 3750. There are total of 14 ranges each in increments of 500 each resulting in a different end number.

       

      Getting the script to fill in the amount for totals less than 3500 was simple, but I am having problems getting it to work beyound that. Any ideas would be appreciated.

       

       

      Thanks

      Russ

        • 1. Re: Using a range in an If Statement
          eshoshin

          I would use Case instead of If.

           

           

          Case (

               FieldName < 3500 ; 3500 ;

               FieldName > 3500 and FieldName < 4000 ; 3750 ;

               FieldName > amount and FieldName < amount ; AmountYouWantDisplayed ;

          )

           

           

          Keep repeating until you're done with all 14 ranges.

           

           

           

          Edit: spelling (doing this via mobile... sorry)

           

          Message was edited by: eshoshin

          • 2. Re: Using a range in an If Statement
            darrenburgess

            Using set field script step:

             

            table::thirdField =

             

            Let (

            [

            ValueLow = table::lowValue ;

            ValueHigh = table::highValue

            ] ;

             

            Case (

             

            ValueLow > 100 and ValueHigh <= 200 ; 150 ;

            ValueLow > 200 and ValueHigh <= 300 ; 250 ;

            ValueLow > 300 and ValueHigh <= 400 ; 382 ;

            234234                                                              // this is the defaullt result if all of the above fail

             

            )  // endCase

             

            ) // end let

             

            That should work if there are no syntax errors ;-)

             

            Darren Burgess

            http://www.mightydata.com

            1 of 1 people found this helpful
            • 3. Re: Using a range in an If Statement
              rwvanleer

              Thanks for the response E. I have altered my script to reflect your suggestion. However, I am still having a problem. As long as the perameters fall within the limits of the first case, the result completes correctly, Unfortunately, once the perameters fall into any of the other levels of the cases the result is the final default.

               

              Any other suggestions?

              • 4. Re: Using a range in an If Statement
                rwvanleer

                Thanks for the feed back Darren.

                 

                I am just a little unclear on one thing. In your example, is the word "table" referring to the field I am referencing? I have not been able to get your example to work, but feel it is because I am doing something wrong. I will continue to see if I can figure it out, but would appreciate any other sights you may have.

                 

                Russ

                • 5. Re: Using a range in an If Statement
                  rwvanleer

                  I think I found my error. It seems to be working but I need to do some more testing.

                   

                  thanks

                  Russ

                  • 6. Re: Using a range in an If Statement
                    darrenburgess

                    Russ,

                     

                    Yes, I meant that you should substitute in the correct field names and whatnot to make the calc work in your solution.

                     

                    I elected to use LET() because it allows you to specify a variable to use in the calculation, improving its readability.

                     

                    This calc basically says this:"Set the Low and High value entered by the users to variables.  Then check if the values are in a specified range and return the corresponding result for each range."

                     

                    Good luck.

                     

                    Darren Burgess

                    http://www.mightydata.com

                    • 7. Re: Using a range in an If Statement
                      BruceHerbach

                      Russ,

                       

                      table is the name of the table that the data is in.  When you create the Case statement,  on the upper left of the calculation window is a list of fields. Click on the field you want.  It will put it in the calculation.  So for example if the table is mytable and the field is lowvalue it will look like mytable::lowvalue.  In short it is tablename followed by :: and then field name.

                       

                      Bruce

                      • 8. Re: Using a range in an If Statement
                        eshoshin

                        Except there isn't a field mytable::lowvalue or mytable::highvalue.  The case is based on a calculated field, so he'll have to specifiy in the code exactly what ranges are needed.  At least, that's how I understood it from the orginal post.  The two user input fields are used in the calculation, but it was not specified that the two fields are the low and high ranges.