Create Dynamic Countdown in SharePoint List items

[cbristol]

I have a SharePoint list that submits Orders. I need a flow to update the Order Item every 24 hours to update the "Countdown to Due Date". What I hope to do is Due Date column - Today = amount of days until Due Date.

This is what I built but the flow is not updating the item, even though it runs without issue.

Variable for Countdown Days is:

div(sub(ticks(formatDateTime(utcNow(),'MM-dd-yyyy')),ticks(outputs('Get_item')?['body/Date_x0020_Due'])),864000000000)

The Countdown to Due Date is single line of text.

I've been working on this for a week and can't figure it out! Please help.

[ScottShearer]

@cbristol 

I believe that your Flow is a lot more complicated than it needs to be.  

First, use a filter query so that items that have shipped are not returned.

Then, just loop through the items returned and update each one using the expression that you have created.  The expression will need to be slightly updated because you will be moving it to new loop.

Here is a skeleton of what you flow should look like:

Let me know if you need more help.

One more thing - be sure to go to the settings of the Get Items action and turn on pagination and set the threshold up to 5000.  Otherwise, Get items will only return 100 a maximum of items.

[OliverR-82 · Accepted solution]

Hi @cbristol ,

I think you've made your flow unnecessarily complex. Am I right in assuming that you only want to update the countdown field for items for which the 'order status' field is not equal to shipped?

In that case, your flow could look like this:

I have different fields, but essentially you would only need 1 'get items' action, and then an 'apply to each' containing 1 'update item' action. In the 'Update item' action you would update the countdown field directly with the expression that you mentioned in your post (see the circled part). There's no need to use a variable, just insert it directly via the expressions pane. You will need to update your expression, though. I think it should be:

div(sub(ticks(formatDateTime(utcNow(),'MM-dd-yyyy')),ticks(item()?['Date_x0020_Due'])),864000000000)

Notice that it says item() to take the value from the Date Due field of the current item in the apply to each action, that's what you want.

To only update the items for which the order status is not equal to shipped, you need to specify an Odata filter in the Get items actions, like follows:

The filter query would look like ColumnName ne 'Shipped' where ColumnName is the internal name of your SharePoint column containing the order status.

Let me know if that gets you any further.

[cbristol]

It doesn't let me select the Date Due field without a variable or second "Get Item"

[OliverR-82]

Yes, sometimes Power Automate does not display all available content, especially in apply-to-each actions. In those cases, you usually have to type them out yourself in the Expressions pane. Within an apply-to-each action, you can reference fields using the item() function. For example, to reference the Date Due field you would insert the following expression:

item()?['Date_x0020_Due']

 Once you've saved your flow, closed and reopened it, most times Power Automate will then display the actual name of the field you're referencing.