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

# Trouble with an IF calculation

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

(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

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

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

• ###### 4. Re: Trouble with an IF calculation

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

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

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

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

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

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

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);

.....