4 Replies Latest reply on Jul 2, 2012 6:30 AM by brian.curran

    Building a multiple If statement...

    brian.curran

      Title

      Building a multiple If statement...

      Post

      Hi,
      I've been working on this for a while but can't get my head around the issue unfortunately.

      In my Customers table I have two Date fields to signify when contracts 'Start' and 'End' and I would like to generate either a "1" or a "0" result for whether the contract is active or not.

      My thinking so far is:

      Active = 1
      Start date in the past AND End date is blank OR End date is in the future

      Inactive = 0
      Start date is blank
      Start date in the future
      Start date in the past AND End date is NOT blank

      Any suggestions on how to build the IF statement for this?

      Thanks
      Brian. 

        • 1. Re: Building a multiple If statement...
          brian.curran

          Slowly working through it all but think I might need an AND or an OR statement somewhere:
          If ( IsEmpty (StartDate); "Inactive"; If (_gDateToday < StartDate; "Inactive"; If (_gDateToday > EndDate; "Inactive"; "Active" )))

          My thinking is:
          if StartDate is empty then the customer is Inactive
          if StartDate is in the future, the customer is Inactive
          if EndDate is in the past, the customer is Inactive 

          • 2. Re: Building a multiple If statement...
            brian.curran

            Persistence pays off, eventually ;)

            This appears to work but I'll have to test it a little more:
            If ( IsEmpty (StartDate); "Empty"; If (_gDateToday < StartDate; "Future"; If (IsEmpty (EndDate); "Current"; If (_gDateToday > EndDate; "Expired"; "Active"))))

            Using different result words inside the ""'s really helped me to work out where the stumbling blocks were, I'll change Empty, Future, Expired and Active to "0" and Current to "1"

            Any comments/suggestions would still be welcomed

            • 3. Re: Building a multiple If statement...
              philmodjunk

              You should research the case function as an alternative to IF when you have multiple logical comparisons (boolean expressions).

              In this case, however, no if or case is required if you want 1 (true) and 0 (false) as the result. Your last take on this returned "active", "Inactive" as text so maybe that represents a change of mind on your part.

              To get 1, 0 results:

              StartDate < Get ( CurrentDate ) And ( IsEmpty ( EndDate ) Or EndDate > Get ( CurrentDate ) )

              This will return 1 or True if the conditions for "active" are met and 0 (False) if they are not. Boolean dataformatting can display the True result as "active" on your layouts. This expression cannot be used in an auto-entered calculation nor in a stored calculation or the result will not update when the date changes. Instead, create a field of type calcualtion, but click the storage options button and specify that the calculation be unstored.

              You can simplify it slightly to be:

              Let ( T = Get ( CurrentDate ) ; StartDate < T and ( IsEmpty(EndDate ) or EndDate > T ) )

              If you want the text results, write it this way:

              If ( Let ( T = Get ( CurrentDate ) ; StartDate < T and ( IsEmpty(EndDate ) or EndDate > T ) ) ; "Active" ; "Inactive" )

              • 4. Re: Building a multiple If statement...
                brian.curran

                Hi Phil,
                I'm just aiming to return either a "1" or a "0" as this field is then used as a 'flag' elsewhere. The Active/Inactive text elements were just there to make it easier for me to read the results.

                Thanks for the tips, I'll have a look at the Case function as well as the formula you posted...

                Thanks
                Brian.