Set Date field based on Status selection = Completed

[CCJones]

Hi all,

I have a Q&A form where the user submits a question. Default status is "Submitted". The site admins come to the new question and answer it. They change the status to "Completed" and I would like the "Date Completed" field to be populated with the current date. I had it working, but every time the form was opened, the Date Completed changed to the current date. It needs to not change again once the status has been set to Completed. I've tried ensuring that the function only runs when the Date Completed field IsBlank, but no luck. Not sure if I'm pointing to the wrong element or what. And of course, now that I've changed it, I forgot to record what worked partially and have not been able to get it back to even that point. 

Right now, I have the function set to run in the Status field's "On Change", so that when the status is changed, it will check the status value and whether the DateCompleted field is blank. If all conditions are met, it should set the variable "answeredDate" to Now().

Status On Change:

If(Self.Selected.Value = "Completed" & IsBlank(ThisItem.DateCompleted), Set(answeredDate, Now()))

[I've tried IsBlank(DataCardValue11.SelectedDate) as well with no better results]

DateCompleted date picker Default:

answeredDate

DateCompleted On Change (if the admin needs to change the Date Completed value manually, this should set the variable to the new date and then the default gets updated with the new value in the answeredDate variable, right?):

Set(answeredDate, Self.SelectedDate)

I would have thought something like this would be simple. I've been able to do it with other field types, but not with date. 

Again, I'm trying to make Date Completed set to the current date when the status = Completed, and NOT change again the next time the form is opened. 

[CCJones]

Hi @WarrenBelz ,

Thanks for checking back in!  It seems that at least I have the automation of the Date Answered field when the status is set to Completed working. The date does not change again after having been set once. The only continuing issue is the SharePoint List date showing a day behind while the date in the form shows correctly. I've tried changing the local time on the form to UTC, but that didn't help. Looks like there's a somewhat complicated (at least to me, not being a dev) formula to resolve it, but I can't tell where I'm supposed to plug the formulas in. 

Anyway, that's not in scope for this question, so I may submit a new question to try to figure that part out.

Thanks for your help!

[00236123]

Hello @CCJones ,

Were you able to find the solution for your issue. I am having the same issue. I need a Date DataCard to automatically fill out with the Toda() function based on a selected value of a Choice column. 

I can make it work but the date disappears once someone goes back to the form.

😞

[CCJones]

I did, but I wish I could say for certain how. The customer decided they didn't need the form and deleted the site it was on. And of course, I didn't record how I did it. Ugh. It was just a custom SharePoint form rather than a full blown app. I feel like the formula I used worked (shown in a previous response) but had to be modified so that "ThisItem.Status" had to be empty for it to trigger. That prevented later reset of the date to "Today()" when the form was opened again after completion. So basically something like:

If((IsBlank(ThisItem.'Date Answered') || IsEmpty(ThisItem.'Date Answered')) && (And(StatusValue.Selected.Value = "Completed", IsBlank(ThisItem.Status))), Today(), ThisItem.'Date Answered')

Sorry I don't have this somewhere that I can check it right now, but in my head it seems like it ought to work. 

[CCJones]

Forget that last. It wasn't if ThisItem.Status was empty, it was if it was Completed.

[00236123]

I think that worked, 

Will be checking tomorrow to ensure the dates don't change. I appreciate your help.

[CCJones]

I just can't get my head on straight today! It's if ThisItem.Status is NOT Completed!