Updating every row in a SharePoint number column based on date column

[bblande]

Hi,

I have two columns in a SPO list:

The "Date Cert Expires" column is a number column that I'd like to update daily as today's date gets closer to the expiration date in the left column. How would I go about doing this with a flow?

[creativeopinion]

@bblande You don't need a Power Automate flow for this. In your case, you can use a calculated SP column. 

=IF([YOURDATECOLUMN] < TODAY(), 0, DATEDIF(TODAY(), [YOURDATECOLUMN], "d"))

Select the number data type.

You might be interested in this video: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[wskinnermctc]

@creativeopinion does a calculated column in a SharePoint list update automatically without the item being modified?

I thought a calculated column only updated when the item was created or modified. So the value would be correct when the item is created or modified, but if nothing is changed, the calculation won't update.

I have not tested this, I'm just remembering having an issue with a calculated column before, and I believe that was the problem of not actively updating.

Is there a difference of functionality between SharePoint Online List and a regular SharePoint List?

[bblande]

@creativeopinion I forgot to mention I already tried a calculated column, and as @wskinnermctc mentioned, it does not update automatically each day.

[wskinnermctc]

@bblande You can look at a post I made here that shows how to use the function ticks() to get time between two datetimes.

https://powerusers.microsoft.com/t5/General-Power-Automate/Using-the-dateDifference-expression-or-ti...

Days Between Datetimes Using Ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)

Edit - I had values backwards - Corrected 

You are going to use utcNow() as the Start date time. The End date time will be your expiration date.

So if you use Get Items it will probably be something like:

div(sub(ticks(items('Apply_to_each')?['ExpirationDate']),ticks(utcNow())),864000000000)

[bblande]

Thanks, I'm getting the following error:

The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/DaysUntilCertExpires' is required to be of type 'Number/double'. The runtime value '"div(sub(ticks(item()?['Expiration Date']), ticks(utcNow())) div ticks('1 day'))"' to be converted doesn't have the expected format 'Number/double'.

My expression looks like this:

Next I reference the output of the compose action:

[wskinnermctc]

The sharepoint column is a number type. The value in power automate is being read as a text string. You need to convert it to an integer.

wrap it with int() integer/number function so that it will not be text.

int(div(sub(ticks(items('Apply_to_each')?['ExpirationDate']),ticks(utcNow())),864000000000))

Or instead of putting it into a Compose, you could put it into a variable which is a integer type.

[bblande]

Still getting an error on the Update Item action:

The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/DaysUntilCertExpires' is required to be of type 'Number/double'. The runtime value '"int(div(sub(ticks(items('Apply_to_each')?['ExpirationDate']),ticks(utcNow())),864000000000))\n"' to be converted doesn't have the expected format 'Number/double'.

[wskinnermctc]

Ohhhhhh facepalm - I should have looked closer at your previous photo.

You are putting the expression into the wrong place.

You can't just type it into the compose box. If you do, it will just treat it like it is a text.

You have to put it in as an expression.

See how it put it into the Expression fx box and clicked OK.

This put it over into the compose field as an expression.

[bblande]

😂Facepalm, indeed! I knew this yet somehow just pasted it in there. I put the original div expression you mentioned earlier in the thread:

div(sub(ticks(items('Apply_to_each')?['ExpirationDate']),ticks(utcNow())),864000000000)

 Now I'm getting this error:
Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'ticks' expects its parameter to be a string that represents a timestamp. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#ticks for usage details.'.