Forum Discussion

Rob Nunley's avatar
Rob Nunley
Brass Contributor
Feb 25, 2021
Solved

Create timestamp in Sharepoint

I have a SharePoint Online List.  One of the columns in the list is a Yes/No titled "Completed" and another text column titled "Time Completed".  When an item is changed from No to Yes I would like a flow to run and place a timestamp in "Time Completed" column. I've tried finding the correct steps in Power Automate but cannot get it to work.  Is this a possibility with PA?

 

Thanks! 

  • Rob Nunley  Yes this is possible. First make sure that versioning is turned on in List Settings as the flow compares the current version with the previous one to see if the Completed column has changed.

     

    1. The trigger is a SharePoint When an item or a file is modified:

     

    Once you have selected your site and list you need to add trigger conditions so that you don't end up in an infinite loop. So click the 3 dots at the top right of the action and select Settings. At the bottom of the panel that appears you add the following 2 trigger conditions:

    @equals(triggerBody()?['Completed'],true)
    @empty(triggerBody()?['TimeCompleted'])

     

    What this does is to trigger the flow only if the Completed column has a Yes in it and if the TimeCompleted column is empty:

     

    Next, we want to get the changes that have happened, so add a Get Changes for an item or a file (properties only) action. In the Since field select Trigger Window Start Token and in the Until field select Trigger Window End Token:

     

    Next, I want the flow to add the data into the TimeCompleted column in a specific format, so I've added a Compose control and in the inputs added the following expression: 

    formatDateTime(utcNow(),'dd/MM/yyyy HH:mm')

    (the capital Hs are to use the 24-hour time).

     

     

    Next we want to check if specifically the Completed column has changed. Add a Condition and select Has Column Changed: Completed from the dynamic content, in the middle field select is equal to and in the right field type true

     

    As this is a Condition you'll have the normal green If yes channel and the Red If no channel. You don't want anything to happen in the red channel so leave it empty. But in the green channel add a SharePoint Update item action. Select the site and list from the dropdowns and the ID and Title from the dynamic content box. In the TimeCompleted field select the outputs from the Compose control.

     

     

    When a user changes the Completed column in the SharePoint list to yes the flow  will run and add today's date and time into the TimeCompleted column. It might take a minute or 2 to do it but you can see it happening in the attached video.

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

     

  • RobElliott's avatar
    RobElliott
    Silver Contributor

    Rob Nunley  Yes this is possible. First make sure that versioning is turned on in List Settings as the flow compares the current version with the previous one to see if the Completed column has changed.

     

    1. The trigger is a SharePoint When an item or a file is modified:

     

    Once you have selected your site and list you need to add trigger conditions so that you don't end up in an infinite loop. So click the 3 dots at the top right of the action and select Settings. At the bottom of the panel that appears you add the following 2 trigger conditions:

    @equals(triggerBody()?['Completed'],true)
    @empty(triggerBody()?['TimeCompleted'])

     

    What this does is to trigger the flow only if the Completed column has a Yes in it and if the TimeCompleted column is empty:

     

    Next, we want to get the changes that have happened, so add a Get Changes for an item or a file (properties only) action. In the Since field select Trigger Window Start Token and in the Until field select Trigger Window End Token:

     

    Next, I want the flow to add the data into the TimeCompleted column in a specific format, so I've added a Compose control and in the inputs added the following expression: 

    formatDateTime(utcNow(),'dd/MM/yyyy HH:mm')

    (the capital Hs are to use the 24-hour time).

     

     

    Next we want to check if specifically the Completed column has changed. Add a Condition and select Has Column Changed: Completed from the dynamic content, in the middle field select is equal to and in the right field type true

     

    As this is a Condition you'll have the normal green If yes channel and the Red If no channel. You don't want anything to happen in the red channel so leave it empty. But in the green channel add a SharePoint Update item action. Select the site and list from the dropdowns and the ID and Title from the dynamic content box. In the TimeCompleted field select the outputs from the Compose control.

     

     

    When a user changes the Completed column in the SharePoint list to yes the flow  will run and add today's date and time into the TimeCompleted column. It might take a minute or 2 to do it but you can see it happening in the attached video.

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

     

    • AB21805's avatar
      AB21805
      Bronze Contributor
      Hi ! Thanks for this, if I had a column called status and wanted the trigger to happen when I change the status to "Completed"

      What would the following formula look like?

      @equals(triggerBody()?['Completed'],true)

      Would it be:
      @equals(triggerBody()?['Status'],Completed)

    • vheer's avatar
      vheer
      Copper Contributor
      Hi! Im following the instructions, but for some reason the right column doesnt show as a "Has Column Changed" option. Any idea what might cause this?
      • RobElliott's avatar
        RobElliott
        Silver Contributor

        vheer sorry I don't understand the problem: the left field of the Condition must have the Has Column Changed: yourcolumn selected from the dynamic content box. In the right field you type true.

         

        Rob
        Los Gallardos
        Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

    • robrobrobrob's avatar
      robrobrobrob
      Copper Contributor
      Wow ... how can i set the trigger condition to trigger when the column Completed has the value " closed " instead of true or false ?

Resources