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. 

[WarrenBelz]

Hi @CCJones ,

Try the below

If(
   Self.Selected.Value = "Completed" && 
   Value(ThisItem.DateCompleted) = 0, 
   Set(
      answeredDate, 
      Now()
   );
   Reset(YourDatePickerName)
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[CCJones]

Thanks, I think this might work. It is changing the date in the Date Completed field. I need to wait til tomorrow to see if it changes the date to the current one or if the value remains the same. However, it does not seem to be saving the value to the SharePoint list that is the source. All the other fields save just fine. Very confusing. 

[WarrenBelz]

Hi @CCJones ,

Assuming you are using SubmitForm, then the Update of the Data Card simply needs to be 

YourDatePickerName.SelectedDate

and it will update the SharePoint list.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[CCJones]

There's only one form because it's just a custom SharePoint list form. The Update field for the data card for Date Completed contains:    DataCardValue11.SelectedDate

DataCardValue11 is the date picker name. 

So far it looks like everything is working right up to the day after the question is set to completed. Then the Date Completed field updates (in the form) when it is opened in display or edit modes. It also displays the date that is set in the form one day off in the SharePoint list view, but that's another issue. Do you know how I can prevent the date from changing again once it's been set (unless it's changed manually by an Admin)?

[WarrenBelz]

Hi @CCJones ,

This code you posted should achieve that result as it should not trigger if there is already a value in DateCompleted.

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

Is there something else triggering the change ?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[CCJones]

Hi @WarrenBelz ,

I'm not sure if something else could have been doing it or not. I decided I'd better wipe the entire thing and start fresh. I put just the code you show above in the OnChange for the Status choice selector and the variable "answeredDate" in the default for the DateCompleted card. It doesn't work, so it must have been something else that was setting the correct value. 

If you were building something similar from the ground up, what direction would you go? 

[CCJones · Accepted solution]

Hi @WarrenBelz ,

I found another question that helped me get the date updating here:

Possible to update Date Field when Drop Down Value... - Power Platform Community (microsoft.com)

I added it to the default for the Date Answered card and made a few modifications to it to have it only trigger when Status = Completed and the Date Completed value is empty.

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

Will check that tomorrow. I ran into another issue with it though. The date value that appears in the form is not the same as the date that appears in the SharePoint list view (List view displays the previous day).  I've seen a couple of questions about this issue as well, but the solutions are pretty confusing. 

[WarrenBelz]

Hi @CCJones ,

In answer to your two questions - the first one is fairly broad, but there is not a lot wrong with the concept of what you are doing - you just need to get your values and flow of changes correct. The Date issue is generally a difference between the time zone on the (I assume SharePoint) data source and the user's PC.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @CCJones ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps