9 Replies Latest reply on May 8, 2017 10:40 AM by siplus

    Trouble with an IF calculation

    sdurante

      If ( (not IsEmpty ( FullMember::FullStartDate) and(Get (CurrentDate)- FullMember::FullStartDate>365))

          or

        (not IsEmpty ( Associate::AssociateStartDate)and (Get (CurrentDate)-Associate::AssociateStartDate>365))

      or

      (IsEmpty ( FullMember::FullStartDate)

      and IsEmpty ( Associate::AssociateStartDate))

      ; ""; "Y" )

       

      The above is my calculation. If either of the Startdates is more than 365 days old or if both fields are empty, the condition is satisfied and the field is left empty.

      My question is  why would everything after the second "or" be necessary? And it was necessary.

      I'd expect the "not IsEmpty" conditions above those lines to satisfy my requirements--but they didn't.

        • 1. Re: Trouble with an IF calculation
          philmodjunk

          (IsEmpty ( FullMember::FullStartDate)

          and IsEmpty ( Associate::AssociateStartDate))

           

          Should not be necessary. Get ( CurrentDate ) - null [empty field] will yield a number in the thousands.

           

          I suggest that you check each section of your calculation separately against your data to see if you can figure out what's going on.

           

          I'd write the above expression this way anyway as it's easier to understand and work with.

           

          Case ( (not IsEmpty ( FullMember::FullStartDate) and(Get (CurrentDate)- FullMember::FullStartDate>365)) ; "" ;

                  ( not IsEmpty ( Associate::AssociateStartDate)and (Get (CurrentDate)-Associate::AssociateStartDate>365)) ; "" ;

                  "Y" // Else

                   ) // Case

           

          But with this expression in place, you can temporarily put different values than "" as the result for each of the first two Boolean expressions to see which one is failing to evaluate as expected.

          1 of 1 people found this helpful
          • 2. Re: Trouble with an IF calculation
            philmodjunk

            Don't have time to test, but maybe you need some parenthesis added.

             

            ( ( Get (CurrentDate)- FullMember::FullStartDate ) >365)

            • 3. Re: Trouble with an IF calculation
              sdurante

              I actually tried every sensible (and some maybe not so) combination of parentheses.

              • 4. Re: Trouble with an IF calculation
                philmodjunk

                In what context are you using this calculation?

                 

                Script Step?

                Calculation field?

                Text field with an auto-enter calculation?

                 

                It won't work as an auto-enter calculation and a calculation field with this expression would need to be unstored in order to evaluate correctly.

                • 5. Re: Trouble with an IF calculation
                  sdurante

                  Thanks.

                   

                  It's an auto-enter calculation in a text field, unstored.

                  It works as I presented it in the first post above.

                  • 6. Re: Trouble with an IF calculation
                    philmodjunk

                    Auto-entered calculations cannot be unstored and will not update with new values of Get ( CurrentDate ) as time passes.

                     

                    Depending on whether the "do not replace..." check box is or is not selected, changes to the values of the start date fields also might not cause it to update.

                     

                    And even with "do not replace..." cleared, the calculation will fail to re-evaluate each time the data in a related record is updated--either FullMember, Associate or both are references to data from a related table.

                     

                    Thus, this really isn't a calculation that can be set up as an auto-enter calculation on a text field.

                    1 of 1 people found this helpful
                    • 7. Re: Trouble with an IF calculation
                      IT_User

                      Let's take this step by step, assuming I am reading into this correctly

                       

                      Let's say:

                      FullStartDate = Is Empty (or 0)

                      AssociateStartDate = Is Empty (or 0)

                       

                      That would make this:

                      (not IsEmpty ( FullMember::FullStartDate) and(Get (CurrentDate)- FullMember::FullStartDate>365))

                          or

                      (not IsEmpty ( Associate::AssociateStartDate)and (Get (CurrentDate)-Associate::AssociateStartDate>365))

                       

                      Transferring the isEmpty part:

                       

                      (not 1 and(Get (CurrentDate)- FullMember::FullStartDate>365))

                          or

                      (not 1 and (Get (CurrentDate)-Associate::AssociateStartDate>365))

                       

                      That means that:

                       

                      0 and (1 or 0) = 0

                          or

                      0 and (1 or 0) = 0

                       

                      so therefore:

                      0

                       

                      Which returns false, even though both are empty:

                       

                      Hence, we need this:

                      (IsEmpty ( FullMember::FullStartDate)

                      and IsEmpty ( Associate::AssociateStartDate))

                       

                       

                       

                      If I am reading this incorrectly, please let me know. Thanks

                      1 of 1 people found this helpful
                      • 8. Re: Trouble with an IF calculation
                        sdurante

                        My error/ignorance.

                        Auto-entered calculations cannot be unstored and will not update with new values of Get ( CurrentDate ) as time passes.

                        It's an unstored calculation field, not "auto-entered" although it happens "automatically" to my thinking. Sorry.

                        • 9. Re: Trouble with an IF calculation
                          siplus

                          You can make calculations like these more readable by using Let.

                           

                          Like

                           

                          Let ([

                           

                          fsd = FullMember::FullStartDate;

                          fsa = Associate::AssociateStartDate;

                           

                          bothEmpty = IsEmpty(fsd) and isEmpty(fsa);

                           

                          .....