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

# Building a multiple If statement...

### 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...

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...

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...

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...

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.