Check Date/Time column if date has passed move file to a folder ERRORS!

[laz8592]

Hi, I have a sharepoint library with a 'end of contract date' date/time choice column; the next column is another choice column called 'contract status' with two values either active or expired.

I basically have an 'active' folder and I want to do a flow that checks if the 'end of contract' column has passed (i.e. less than today's date' , if it is change the 'contract status' column field from 'active' to 'expired' and then move the file to another folder called 'expired'. 

See screenshots of my flow and the errors. 

Thanks. 

[Anil_g]

@laz8592 

Looks like your update activity failed...how are you getting the ID of item you want to update?

ideally you should be looping on the sharepoint list items which have status active and date has passed ...and then find the corresponding file and move it...

currently looks like you are getting file but you dont know which item it belongs to in sharepoint...

Also if you expand further you would see exact error on the yes side of your condition

cheers

[laz8592]

See the error, I add the condition and then put type the 'end of contract date' column' is less than todays date, and it opens the 'apply to each' section with the value already in there, see screenshots. It fails from there as well as the 'yes' condition as it finds other folders in there and stops it seems, I want it to check the whole 'active' folder and check even other folders in there for files expirying. Where do I do the change  'ideally you should be looping on the sharepoint list items which have status active and date has passed' ? Do I have to add another trigger before get file properties for any sharepoint items created of modified? 

[laz8592]

Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'The template language function 'less' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

[Anil_g]

@laz8592 

As per error end of contract date is null for few rows I believe and it failing over there

Also you flow should be like this I beleive

1. Trigger to run daily once

2. Get list items with filter on Status Active and End of contract less than today

3. Loop on retreived items

4. Inside loop update item to status as passed and one more activity to move the file from active to passed

cheers

[laz8592]

Thanks Anil, as I'm new to Powerautomate please can you send me a screenshot on how the flow should look graphically, thank you 

[Anil_g]

@laz8592 

this is how it looks

similarly even in move file you can use the currentitem just like how I got ID you can get other items

hope this helps

cheers

[laz8592]

Hi @Anil_g 

Thanks for your help, It gives be an error on the expression, see image below. 

I have changed the triggers as it is a document library not a list. Does my timezone that I have added in the recurrence affect the flow at all?

thanks. 

[Anil_g]

@laz8592 

you need to use compose to get the date...dont use the expression directly it cannot parse it(Please check screenshot of flow I have given)

Also the timezone your environment is what the recurrence time takes into consideration

cheers

[laz8592]

@Anil_g 

Sorry I'm not getting you, when I type the command expression below it accepts it but when I test the flow it gives me that error. 

concat('ContractStatus eq "Active" and "EndofContractDate" It''', outputs('Compose'),'''')

Does it have to be below:

concat('ContractStatus eq "Active" and EndOfContractDate It''', outputs('formatDateTime(utcNow(),'yyyy-MM-dd')'), '''')

Even that it does not accept it. 😞 

[Anil_g]

@laz8592 

Did you happen to use a compose action or no?

cheers

[laz8592]

@Anil_g yes in the previous step and in the expression 

[Anil_g]

@laz8592 

Small correction ..please remove the concat...

try this expression...retain compose the same way

ContractStatus eq 'Active' and EndofContractDate lt 'outputs('Compose')'

Make sure you use the exact same as above

and in compose use 

formatDateTime(utcNow(), 'yyyy-MM-dd')

Note: Remove concat and also inlcude single quotes around the time in filter

cheers

[laz8592]

@Anil_g nope still the expression in Filter Query is invalid without concat and adding time frame in compose 

ContractStatus eq 'Active' and EndofContractDate lt 'outputs(' formatDateTime(utcNow(), 'yyyy-MM-dd')')'

[Anil_g]

@laz8592 

In compose you have to do the format and use only the output of compose in the query

please dont include format

FYI ..this is hoe it should look

cheers

[laz8592]

Column 'EndOfContractDate' does not exist. It may have been deleted by another user.
clientRequestId: bd6d88c3-9aca-471d-939f-d9dc3e34b5a1
serviceRequestId: bd6d88c3-9aca-471d-939f-d9dc3e34b5a1

[Anil_g]

@laz8592 

I believe the format is fixed now...as per error the column name is wrong can you check the column name properly by going to list properties

cheers

[laz8592 · Accepted solution]

sorted it out myself