3 Replies Latest reply on Mar 18, 2014 7:29 AM by philmodjunk

    Ifs and Ands - calculation field basics

    Lordylordy

      Title

      Ifs and Ands - calculation field basics

      Post

           Hi all

           I'm failing miserably and an IF and AND calculation field and just can't understand why.

           In staement below, can anyone see what I have done wrong with the AND part? The OR parts all working fine, but when I add the AND it delivers the wrong result, sometimes.

      If(Process Status = "1" or Process Status  = "31" or Process Status = "49"  and (Activation Date Check TEXT="Y");"True";"False")

           The field Activation Date Check is a calculation field in itself, so I created a text field with the same values in it, but still getting the same result.

           Thanks all!!

        • 1. Re: Ifs and Ands - calculation field basics
          philmodjunk

               It should not make any difference, but there is no need to put the number values in quotes.

               Your syntax looks correct. Is Process Status a text or a number field? If it's text, maybe the field has an extra character in it such as a space or return so it looks like it has one of the three number values but doesn't.

          • 2. Re: Ifs and Ands - calculation field basics
            Lordylordy

                 Thanks Phil

                 I thought syntax was correct, so just needed to sense check. Turns out there were multiple records in the relationship for Activation Date Check, so it was displaying random values on my layout.

                 If I wanted to add another and statement, though, would this be correct?

            If(Process Status = "1" or Process Status  = "31" or Process Status = "49"  and (Activation Date Check TEXT="Y") and (Regrade_New=New);"True";"False")

                 Thanks again Phil!

                  

                  

            • 3. Re: Ifs and Ands - calculation field basics
              philmodjunk

                   When a calculation references a field from a related table, it accesses only the data from the first related record unless you are using an aggregate function or referencing a summary field. Data from any other related record will be ignored. You can, however, specify a sort order in the relationship to have some measure of control over which related record is the "first" related record.

                   Basic syntax for OR, AND looks correct, but it looks like NEW should be "NEW" unless that's the name of a field in your table.