Hello guys, I have "Date" column in SP list. In Format 'dd/MM/yyyy'.
And in Power Automate I need to write if current date - date from SP list > 30 days, than delete this row.
I've found in one video line of code and I tried to use it in my flow:
div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(outputs('Get_items_from_SP_list:_audit_template__CS')?['body/Date'])),864000000000). So in flow it looks like this (I used greater than one just for testing)
But I got this error
I have checked name of columns is right. It is new error. Error before was Unable to process template language expressions in 'formatDateTime. So maybe problem is in data format that I use 'dd/MM/yyyy', can you please help me to rewrite this line of code
Hi @Aleksandra1,
Apologies, I forgot SharePoint Date fields have a different format. I believe it should be yyyy-MM-dd.
Can you try and use this instead for the comparison?
addDays(UtcNow(), 1, 'yyyy-MM-dd')
Now I was trying to use you last expression
Now this expression is always false. when I write 1 as a parametr it should be true, when i write 30 as a parametr it should be false, but it always false, unfortunately
Hi @Aleksandra1,
I was still looking at the null error of your Set variable action.
If you need an expression which supports a loop (without referring to any specific item) you should be able to use this instead for your set variable action:
div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(item()?['Date'])),864000000000)
For the condition you can add a format in the addDays function.
addDays('<timestamp>', <days>, '<format>'?)
In the condition I would use this expression
addDays(UtcNow(), 30, 'MM/dd/yyyy')
Unfortunately, I cannot specify an index, because it shouldn't be applied only on one index, it should be applied on whole array.
So the point is, I need to write if current date - date from SP list > 30 than delete row. Or
I can say if current date-30 > date from SP list than delete, because this expressions should give the same result if I am not wrong.
but it gives me wrong result: In my sp list date is 10/14/2022 in format "MM/dd/yyyy", so the condition should be false, which means dont delete yet, but condition is true, i dont know why.
Hi @Aleksandra1,
I see you are using some other action to retrieve the date data. Seeing the name of the action I assume it is a SharePoint Get Items action, correct?
You either need to specify a specific item with an index or loop through all items of the outputs. Seeing the logic of your flow I expect you are only interested in one item.
For that scenario you could use something like below:
I used the [0] index reference, but you can also use a first function to retrieve the first item of the list of values.
div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(outputs('Get_items_from_SP_list:_audit_template__CS')?['body/value'][0]['Date'])),864000000000)