SOLVED

Create timestamp in Sharepoint

%3CLINGO-SUB%20id%3D%22lingo-sub-2166447%22%20slang%3D%22en-US%22%3ECreate%20timestamp%20in%20Sharepoint%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166447%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20SharePoint%20Online%20List.%26nbsp%3B%20One%20of%20the%20columns%20in%20the%20list%20is%20a%20Yes%2FNo%20titled%20%22Completed%22%20and%20another%20text%20column%20titled%20%22Time%20Completed%22.%26nbsp%3B%20When%20an%20item%20is%20changed%20from%20No%20to%20Yes%20I%20would%20like%20a%20flow%20to%20run%20and%20place%20a%20timestamp%20in%20%22Time%20Completed%22%20column.%20I've%20tried%20finding%20the%20correct%20steps%20in%20Power%20Automate%20but%20cannot%20get%20it%20to%20work.%26nbsp%3B%20Is%20this%20a%20possibility%20with%20PA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2166447%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2169139%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20timestamp%20in%20Sharepoint%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2169139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F80138%22%20target%3D%22_blank%22%3E%40Rob%20Nunley%3C%2FA%3E%26nbsp%3B%20Yes%20this%20is%20possible.%20First%20make%20sure%20that%20versioning%20is%20turned%20on%20in%20List%20Settings%20as%20the%20flow%20compares%20the%20current%20version%20with%20the%20previous%20one%20to%20see%20if%20the%20Completed%20column%20has%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20trigger%20is%20a%20SharePoint%20%3CEM%3EWhen%20an%20item%20or%20a%20file%20is%20modified%3C%2FEM%3E%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-Flow.png%22%20style%3D%22width%3A%20553px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258391iBD0B314EA8EC749D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221-Flow.png%22%20alt%3D%221-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20have%20selected%20your%20site%20and%20list%20you%20need%20to%20add%20trigger%20conditions%20so%20that%20you%20don't%20end%20up%20in%20an%20infinite%20loop.%20So%20click%20the%203%20dots%20at%20the%20top%20right%20of%20the%20action%20and%20select%20Settings.%20At%20the%20bottom%20of%20the%20panel%20that%20appears%20you%20add%20the%20following%202%20trigger%20conditions%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%40equals(triggerBody()%3F%5B'Completed'%5D%2Ctrue)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%40empty(triggerBody()%3F%5B'TimeCompleted'%5D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20this%20does%20is%20to%20trigger%20the%20flow%20only%20if%20the%20Completed%20column%20has%20a%20Yes%20in%20it%20%3CU%3Eand%3C%2FU%3E%20if%20the%20TimeCompleted%20column%20is%20empty%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221a-Settings-TriggerCondition.png%22%20style%3D%22width%3A%20577px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258387i7D37FBF5F3B7BDB0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221a-Settings-TriggerCondition.png%22%20alt%3D%221a-Settings-TriggerCondition.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20we%20want%20to%20get%20the%20changes%20that%20have%20happened%2C%20so%20add%20a%20%3CEM%3EGet%20Changes%20for%20an%20item%20or%20a%20file%20(properties%20only)%3C%2FEM%3E%20action.%20In%20the%20Since%20field%20select%20Trigger%20Window%20Start%20Token%20and%20in%20the%20Until%20field%20select%20Trigger%20Window%20End%20Token%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221b-Flow.png%22%20style%3D%22width%3A%20553px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258393i31D034DDE7854E0B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221b-Flow.png%22%20alt%3D%221b-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20I%20want%20the%20flow%20to%20add%20the%20data%20into%20the%20TimeCompleted%20column%20in%20a%20specific%20format%2C%20so%20I've%20added%20a%20Compose%20control%20and%20in%20the%20inputs%20added%20the%20following%20expression%3A%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSTRONG%3EformatDateTime(utcNow()%2C'dd%2FMM%2Fyyyy%26nbsp%3BHH%3Amm')%3C%2FSTRONG%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E(the%20capital%20Hs%20are%20to%20use%20the%2024-hour%20time).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-Flow.png%22%20style%3D%22width%3A%20555px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258386iD4ACC84DFBE82DF3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222-Flow.png%22%20alt%3D%222-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20we%20want%20to%20check%20if%20specifically%20the%20Completed%20column%20has%20changed.%20Add%20a%20%3CEM%3ECondition%3C%2FEM%3E%20and%20select%20%3CSTRONG%3EHas%20Column%20Changed%3A%20Completed%3C%2FSTRONG%3E%20from%20the%20dynamic%20content%2C%20in%20the%20middle%20field%20select%20%3CSTRONG%3Eis%20equal%20to%3C%2FSTRONG%3E%20and%20in%20the%20right%20field%20type%20%3CSTRONG%3Etrue%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20this%20is%20a%20Condition%20you'll%20have%20the%20normal%20green%20If%20yes%20channel%20and%20the%20Red%20If%20no%20channel.%20You%20don't%20want%20anything%20to%20happen%20in%20the%20red%20channel%20so%20leave%20it%20empty.%20But%20in%20the%20green%20channel%20add%20a%20SharePoint%20%3CEM%3EUpdate%20item%3C%2FEM%3E%20action.%20Select%20the%20site%20and%20list%20from%20the%20dropdowns%20and%20the%20ID%20and%20Title%20from%20the%20dynamic%20content%20box.%20In%20the%20TimeCompleted%20field%20select%20the%20outputs%20from%20the%20Compose%20control.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%223-Flow.png%22%20style%3D%22width%3A%20944px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258395i44EBC578E4E9E866%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223-Flow.png%22%20alt%3D%223-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20a%20user%20changes%20the%20Completed%20column%20in%20the%20SharePoint%20list%20to%20yes%20the%20flow%26nbsp%3B%20will%20run%20and%20add%20today's%20date%20and%20time%20into%20the%20TimeCompleted%20column.%20It%20might%20take%20a%20minute%20or%202%20to%20do%20it%20but%20you%20can%20see%20it%20happening%20in%20the%20attached%20video.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2170256%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20timestamp%20in%20Sharepoint%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2170256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%26nbsp%3BThank%20you%20sooo%20much!%26nbsp%3B%20Thank%20you%20for%20explaining%20the%20reasoning%20and%20not%20just%20answering%20the%20question.%26nbsp%3B%20Thanks%20again!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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! 

7 Replies
best response confirmed by Rob Nunley (Contributor)
Solution

@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:

1-Flow.png

 

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:

1a-Settings-TriggerCondition.png

 

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:

1b-Flow.png

 

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).

 

2-Flow.png

 

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.

 

3-Flow.png

 

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 Thank you sooo much!  Thank you for explaining the reasoning and not just answering the question.  Thanks again!!

@RobElliott

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 

 

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