Capture Date When the Status Gets Changes

[AkshayManke]

Hi,

I have a list which is having 15 Status values. I am trying to capture the respective date when the status changes, I have created 15 date columns for that (which we will use later to generate Power BI Report and see that how much time it took to change the status from Status A to Status B). Hence i created two more columns than the original 'Status' column to check the 'Current Status' and the 'Previous Status'. I think, I may be using a long route/way to achieve what i want. Can i get some help please that, what could be the best way to capture the dates when the status changes? With that, we also need to ensure that when the status has not changed the date should not be added for the respective status. Because it may happen that the record will get updated with other data and status remain same. Hence we just want the date to be captured when the status is not same as previous.

Thanks in advance!

Warm Regards,

Akshay

[Gochix]

Hi @AkshayManke ,

If you would like to update a record if specific conditions are met then why not to use a UpdateIf() Function?

_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

[AkshayManke]

Hi @Gochix, Thanks for suggesting the solution. I will test the same and will get back with the result in next couple of hours.

Regards,

Akshay

[AhmedSalih]

Hello, @AkshayManke , DataTime system columns values can't be manipulated. You can create custom datatime columns to achieve your requirement. What data source you are using?

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[AkshayManke]

Hi @AhmedSalih, Thanks for the reply. I have created separate columns for date capturing. The data source is SharePoint List. 

Regards.

Akshay 

[AhmedSalih]

@AkshayManke, in this case, as @Gochix suggested, using the UpdateIf function will be suitable for your use case. You will just run the updateif function wherever you edit the record from the PowerApps e.g. Edit form OnSuccess property or after the Patch function is that if what your using. 

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[AkshayManke]

Hello @AhmedSalih, I have used SubmitForm to append the data of the edit form. I checked the syntax of Updateif as suggested by @Gochix, however i cant see much references for that related my case. Can you please tell in more detail that where and what code I need to add. Your help will be really very great for me. 

Kind Regards,

Akshay

[AhmedSalih]

@AkshayManke, The Forms control have a property called OnSuccess. Whatever formula you put there will only be executed when the Forms data is submitted successfully the Data source.

In your case, you are using the Edit Form to submit Edits in the prospective record. On that form OnSuccess property, use the UpdateIF function with the Lookup function:

But before that, on the Button you use to cal the SubmitForm function, you need to retrieve the current status from the Data source and have it in a variable so you can compare it to the value after the Form is submitted. Because you are using the Edit form mode, that means the Item property of the Form is the record you need to retrieve to get the current Status. 

Use something like: Set(varCurretnStatus, Lookup(DataSource, ID=Gallery1.Selected.ID,Status))

NOTE: I am assuming that Gallery1 is where you are viewing the record and selecting which one to edit in your edit form)

Then, in the OnSuccess property of the Edit Form, you will need to verify if the Status has changed and here you will use the EditForm.LastSubmitted function:

If( EditForm.LastSubmitted.Status <> varCurretnStatus,

UpdateIf(YourDataSource, Lookup(YourDataSource, ID=EditForm.LastSubmitted.ID)

&& varStatusChanged ,

{ Date:Now()})

)

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[AkshayManke]

Hello @AhmedSalih,

Many thanks for helping on this. I tried both the steps as suggested by you. However getting some error in the 2nd step when we configure OnSuccess Property. Below the snaps.

On.Select- Below the code is there.

OnSuccess - Below code i am trying to configure and i have three points.

1. It is giving error in when comparing the string at the first line. 

2. In the second line it is showing error which i am unable to understand. Can you please suggest any workaround for that. 

3. The variable varStatusChanged is adding value to of the current time. so that we can add the current time packaged in the variable to any of the 15 date columns. is this understanding correct?

Warm Regards,

Akshay

[AhmedSalih · Accepted solution]

@AkshayManke, Let's start with the UpdateIF. If the VarRecord1 is a global variable and it is the same edited record, you can use it in your UpdateIf function as following:

If( EditForm_3.'Current Consolidated Status' <> varCurretnStatus,

UpdateIf(YourDataSource, VarRecord1,{ Date:Now()})

Now for the first line error, is the 'Current Consolidated Status' column is a choice column?

If my reply helped you, please give a 👍, & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com