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