I'm having trouble with an if..then in a custom calculated form.

[HeatherTe]

Background: We have a pull down field to indicate the status of results. (In progress, posted, updated, N/A). The default is in progress. We also had a date field that would show if posted or updated was selected and the user would enter the date. We really wanted the date to autopopulate the date to 'today' so that we can know when new things are posted or updated and didn't want users to backdate. So we created a new field to do a calculation. Since we had the previous field that had some dates, we had to include that too so that we kept correct dates for things already posted. Below is the calculation we came up with:

IF(ISBLANK(Results Posted Date),IF(Results="Posted"||(Results)="Updated",IF(ISBLANK(Results Date),$$NOW,Results Date),""),Results Posted Date)

Results posted date is the new calculated field and resuts date is the previous field where they had put it in themselves.

This works for some combinations but if you go from Posted to Updated, it doesn't update to the current date. we really want it to change to the current date when status goes to 'updated' from 'posted'. Any suggestions on how we need to alter our syntax?

Thanks

Heather

[TealeMcCleaf]

Hi Heather -

What I think I hear you describing is...

IF the Results Date field is blank and the Results = Posted or Updated then the Results Posted Date should equal Today.

IF the Results Date field is populated and the Results = Posted or Updated then the Results Posted Date should equal the Results Date field.

If that is correct, this calculation works.

IF(ISBLANK(Results Date),IF(Results="Posted"||Results="Updated",$$NOW),Results Date)

I'm not sure where the Results Posted Date fits into that calculation because when I think about it, if you want it to update when it changes from Posted to Updated, you can't indicate to only do that if the Results Posted Date field is blank.

Crossing my fingers that my logic is correct.

Hope this helps,

Teale

[HeatherTe]

Hi Teale,

Thanks for the response. It isn't working quite right. The one thing you mentioned:

IF the Results Date field is populated and the Results = Posted or Updated then the Results Posted Date should equal the Results Date field, is not what we are looking for. If the results date is populated and 'updated' then we want it to equal NOW.

For example, a project has the status of 'posted' with the date 3/31/2021. Someone updates the results so they change the status from 'posted' to 'updated'. We want this to have today's date, not the 3/31/2021 date that is currently showing. Does that make sense?

Thanks

Heather

[TealeMcCleaf · Accepted solution]

Hi Heather,

Maybe just add $$NOW again on the tail end of the IF statement instead of Results Date as the THEN portion. When I test that the date remains today.

Let me know if that works for you.

Best -

Teale

[HeatherTe]

I think that works! Thanks so much. I was totally overcomplicating the formula.

[HeatherTe]

So that did work but here is my new issue. I actually have 4 separate calculated fields similar to this one. That was for results, we also have one for questionnaire, tables, and exploration doc. They are all separate fields but with similar calculation. For example,

IF(ISBLANK(Tables Posted Date),IF(Tables="Posted"||(Tables)="Updated",$$NOW),$$NOW)

While the calc works, it actually updates for all the fields. If I change 'results' to "updated", all 4 fields change to today's date. I think this is because when I hit save, all of those recalculate and even if they already said posted or updated, it is switching them to today's date. But we only want to save today's date if the status has changed to one of those.

[TealeMcCleaf]

Hi Heather,

This sounds like a bug of some kind. I've created the additional fields and when I change Tables to Posted or Updated my other calculated fields don't change and I wouldn't expect them to change even with a recalc unless one of the fields were actually changed.

I would ask your support rep to look at that to determine why all of the calculated fields are updating when changes weren't made to the fields to perform the calc.

Sorry I can't be of more help.

Teale

[HeatherTe]

Yes, at first I thought it was a bug. It seems weird that everything would update. Thanks!