Forum Discussion
Create timestamp in Sharepoint
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
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:
(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
- 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) - 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?
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)
- Wow ... how can i set the trigger condition to trigger when the column Completed has the value " closed " instead of true or false ?
RobElliott this is an excellent explanation and I easily got it working on a SharePoint list I have. Unfortunately, I have the majority of items organised into 4 Folders within the list and I cannot get this working to trigger on any changes to items within the folders. Do you have any advice for this?
Thanks in advance,
Greg
Hi Rob,
thank you for these instructions. I tried to use this for an inventory list and it is working with one issue:
The timestamp that is entered in my "date_used" field is always the 1st of July.
Can you help me with this?
Kind regards,
Michael
MichaelMr It sounds like you've either got the regional settings set incorrectly on your site or the date format in your flow is the wrong way round. I'm in Spain working for a British company and today, the 7th of January, is saved in dd/MM/yyyy format correctly as 07/01/2022. But in the USA someone would read this as 1st July. So check the format in your flow's expressions are in the format you need them to be.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Is there a trigger that is the opposite of @empty(triggerBody()?['TimeCompleted'])?
ie only run the step if TimeCompleted is NOT empty.
Thanks
- @not(empty(triggerBody()?['TimeCompleted']))
- @RobElliot
Thanks again Rob!
RobElliott Thank you sooo much! Thank you for explaining the reasoning and not just answering the question. Thanks again!!
