Flow to check field on SharePoint Item, then move the Item to another list

[ubikwitous]

Hello out there!

I'm trying to create a flow to 
1. Run Weekly.

2. Check all the items in the list for Status.Value = Completed and if they are older than 180 days. (expression = @less(formatDateTime(triggerBody()?['Completed_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(addDays(utcnow(), -180), 'yyyy-MM-dd'))

3. Get the ID of that item and copy all the fields to another SharePoint list item

4. Delete the original item in the original list.

This has the effect of moving Completed tasks I am tracking to an archive list.

I have tried this in the below Flow, but I'm getting a failure at Apply to each and I'm not sure why. It looks like something in the expression, but I'm using it in another Flow with no issues. Here's the error. Thanks!

ActionFailed. An action failed. No dependent actions succeeded.

InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '2505': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

[AlanPs1]

Hi @ubikwitous , thank you for your post.

Try this as your condition:

less(formatDateTime(triggerBody()?['Completed_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(addDays(utcnow(), -180, 'yyyy-MM-dd'), 'yyyy-MM-dd'))

If you have found my post helpful, please mark thumbs up.

If this post has solved your problem, please click "Accept as Solution".

Any other questions, just ask.

Thanks, Alan

[ubikwitous]

Hi AlanPS1!

I'm still getting the same error unfortunately. I think I must be doing something wrong in the flow of the Flow. I don't see a way to enter the formula in, so I actually have:

formatDateTime(triggerBody()?['Completed_x0020_Date'], 'yyyy-MM-dd' 

is less than

formatDateTime(addDays(utcnow(), -180, 'yyyy-MM-dd'), 'yyyy-MM-dd')

InvalidTemplate. Unable to process template language expressions for action 'Condition_2' at line '1' and column '2506': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

[AlanPs1]

Put that expression on the left of your condition and make equal to true(expression)

Check here for info on this as I tested it my end. Pretty sure I didn’t miss anything

https://www.alanps1.io/power-platform/flow/flow-so-where-has-edit-in-advanced-mode-gone-on-condition...

[ubikwitous]

Dang! I had a couple of 'ohhhhhhhh' moments, but it still isn't working. Same error. You can see I pasted the formula on the left, is equal to, then true as an expression.

Where Status.Value does equal Completed, it is returning false and then Condition 2 states:

ActionBranchingConditionNotSatisfied. The execution of template action 'Condition_2' skipped: the branching condition for this action is not satisfied.

Where Status.Value does not equal Completed, it is the same error as before. I've even tried moving Condition 2 up to Condition 1 with an And, but it still returns Null. 

This screenshot is before I moved it up.

[Sunay · Accepted solution]

Do you have items in your SharePoint list where the Completed Date is not set?

This error message -

Unable to process template language expressions for action 'Condition' at line '1' and column '2505': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'

states that - The first parameter passed to the "formatDateTime" function was null.

I'd recommend filtering out items from the SharePoint list that have Completed Date set to null using OData fitlers.

Additionally, if you are not comfortable with Date Time conversions using expressions - you can use the GUI based date time operations. Convert Date time also allows you to pick custom formatting.

[ubikwitous]

You guys both got me there - thanks for the suggestions!