1 Reply Latest reply on Mar 21, 2017 9:47 AM by philmodjunk

    IF Calculation Help

    MikeMartish

      I have attached a small database that I need some guidance on.

       

      What I am trying to do is set fields Item Width and Height based on a related record. The get rules button should get the result of the records for ITW and ITH and return the rule text.

       

      This has got to be something stupid I am missing.

       

      Mike

        • 1. Re: IF Calculation Help
          philmodjunk

          This is a very common "Newbie" mistake.

           

          When you write a calculation that executes from the context of your parent record (Jobs) and it follows this form:

           

          If ( Rules::Rule Code = "ITW" ; Do this if true ; do this if false )

           

          The calculation only references the first related record. It thus evaluates as:

           

          If ( "XYZ" = "ITW" ; Do this if true ; do this if false )

           

          The calculation never even "sees" the other related records.

           

          To do what you want will require either a different relationship or a completely different approach.

           

          a) You could create two new Ocurrences of Jobs and link them by Job number AND by Rule Code. You'd set up two calculation fields with text results with the Strings "ITW" and "ITH" respectively as the additional match fields in Jobs.

           

          b) you could write a script that pulls up the found set of related Rules records and then constrains the found set to just the one record, copies the value into a variable and then returns to Jobs to set the field with that value.

           

          c) ExecuteSQL could return this value from Rules using both the Job Number and Rule Code in a simple WHERE clause.