SOLVED

Populate a date column automatically

Occasional Contributor

How do I populate a date column automatically when the status field is changed to "Implemented" with a Sharepoint List?

 

I am new to using PowerAutomate, and I am trying to have the project close date automatically populate to "Today's Date" when the status field has moved to Implemented. It doesn't seem to work, and the field is not updating after testing. The following error message is coming back after testing. 

 

Message: ActionDependencyFailed. The execution of template action 'Update_item' is skipped: dependant action 'Condition' completed with status 'Failed' and code 'BadRequest'.

 

 

What am I missing?

Powerauto3.pngPowerauto2.pngPowerauto1.png

 
 
10 Replies
best response confirmed by Sharepoint_Chris (Occasional Contributor)
Solution

@Sharepoint_Chris this is not difficult and it compares the current version of the item with the previous one, so you MUST make sure that in your list settings -> versioning settings that Item Version History is set to on. I normally set the number of versions to 10.

 

In this example I prepared for a similar forum post a while back we are checking whether the Status column has changed and, if so, whether the status is now Completed. If it is then add today's date in the Completed column.

0-SPList-Versioning.png

 

The trigger in the flow is "when an item or a file is modified":

 

1-Flow.png

 

The first action is "get changes for an item or a file (properties only)". For the ID field select ID from the when an item or a file is modified section of the dynamic content box. 

 

For the Since field you need to add the following expression to get the previous version:

sub(int(triggerOutputs()?['body/{VersionNumber}']),1)

 

2-Flow.png

 

Next, add a condition:

 

  • in the left box select Has Column Changed: Status
  • in the middle box select is equal to
  • in the right box type true

 

3a-Flow.png

 

Add a new row to the condition and if your SharePoint status column is a choice column then select Status value, if it's not a choice column then just select Status, and again select is equal to and then type Completed in the right hand box:

 

3b-Flow.png

 

Finally, you can leave the red if no channel empty, but in the green if yes channel add an Update item action and in the Completed field add an expresion of utcNow()

 

4-Flow.png

 

So the Status has changed to completed and the flow runs and adds today's date into the Completed field.:

 

5-SPList-StatusChanged.png

 

6-SPList-Completed.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott

 

 
Hello Rob,

Thanks for the quick response. I am still unable to get it to work, not sure what I am doing wrong. I have followed all your instructions, but when I select "get changes from an Item or a file (properties only) it gets sub -sorted under an action that says "Apply to Each." I am not sure if that impacts this automation. Also, after selecting the condition - I enter all the necessary fields. Many required fields require information within our list, so I select the corresponding "dynamic content" that matches that field. I have included screenshots below. Powerauto4.pngPowerauto5.pngPowerauto6.png

@Sharepoint_Chris Why have you got an apply to each after the trigger? The get changes action must follow immediately after the trigger as in my earlier screenshots, there shouldn't be an apply to each there.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott, I am not adding the "Apply to Each" trigger. PowerAutomate is doing that automatically after adding Get Changes for an Item or file. Is there a way to stop that from adding Apply to each? 

@RobElliott 

Once I complete the site address, list name, and ID field it sub-groups the triggers and adds the "Apply to Each" trigger.

 

 

This is what shows initially and it changes as soon as I complete the ID field. Powerauto7.png

Thank you, Rob. With some tweaking and minor adjustments, I could get everything to work.
Thanks for the help!

@Sharepoint_Chris  @RobElliott 

 

I have created a flow using the examples provided in this post to enter the date into a column called Completion Date when a user marks the sharepoint item (Status column) completed, and send an email to my user to complete a survey. 

 

My list is configured for versioning (15) but the flow does not execute as it does not recognize the Status Column being changed to Completed.  I have tried both equals to and contains for the Status condition but nothing is working.  I used Terminate in the No condition for testing purposes. Any ideas what is missing?  Thanks. 

 

JTJ98_0-1650543026938.png

JTJ98_1-1650543193257.png

JTJ98_2-1650543350784.png

 

 

@JTJ98 if your Status column is a choice column you need to select Status Value from the dynamic content box is equal to Completed. It looks to me that you have just selected Status which might be the reason it doesn't work.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

3b-Flow.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Thank you @RobElliott!   I did try the flow with Status Value Condition value set to Completed but it still shows the flow condition is not trigger.  In some tests while Power Automate showed the flow not triggering, the list was populated with the Completed date.  Not sure why it would not be working.