Powerautomate filters on a calculated column

(2) Share

Report

Posted on by NC-25081327-0

Hello, I have a sharepoint list that contain my stock with entry date and I have a calculated column (Duration) between today and the entry date (EntryDate) .

I would like to receive a daily mail when the Duration column equals to 100 for each stocks.

I know that I can't filter directly a calculated column, but when i try to use filter command, I can't find the correct formula to filter it correctly. I saw multiple subject around this topic, but none permit to unblock my situation.

Who could help me ?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Suggested answer

rezarizvii 352 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, hope you are doing well.

The easiest way around this is, don’t use the calculated column at all in your flow. Instead, just recreate the logic.

As per your request, you wish to get an email notification for items that were added 100 days ago (whose entry date is 100 days before today), right?

In that case, you can create a recurring flow to run every day, filter your list in the flow itself, and then send a notification for items that hold true.

1. Create a recurring flow to run every day

2. Add a Get items (SharePoint) action to fetch all the stocks that were added 100 days ago. The expression that I used is: formatDateTime(addDays(utcNow(), -100), 'yyyy-MM-dd')

3. Add an Apply to each action with its items set as the output of the Get items action.

4. Send your email reminder inside this Apply to each action.

If this reply helped you in any way, please give it a Like 💜 and in case it resolved your issue, please mark it as the Verified Answer ✅.

Was this reply helpful? Yes No
Haque 3,653 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @NC-25081327-0,

What do you mean by "but none permit to unblock my situation"? Could you please clarify a bit?

Please note that, you cannot directly filter on a calculated column in SharePoint using Power Automate's "Get items" action because SharePoint does not support filtering on calculated columns via OData queries. Can you please explore on this?

In a recurrent flow, you do the following stuff:

1. Use "Get items" action without filtering on Duration.

2. In the flow, filter the items where the difference between today and EntryDate equals 100 days.

sub(int(formatDateTime(utcNow(),'yyyyMMdd')), int(formatDateTime(items('Apply_to_each')?['EntryDate'],'yyyyMMdd'))) = 100

3. Collect the filtered items and send a daily email listing those stocks whose duration equals 100

Was this reply helpful? Yes No
Suggested answer

11manish 3,333 on at

Like
a
(0)

Report
Copy link

Link copied!
Recalculate Duration inside the Flow instead of using the calculated column

Best fix:

Either calculate in Flow

OR filter using EntryDate = Today - 100

Was this reply helpful? Yes No
NC-25081327-0 12 on at

Like
a
(0)

Report
Copy link

Link copied!

In fact, I'm quite sur that all errors reported to my requests could due to date format. All your answer indicated earlier have a different date format : yyyyMMdd or yyyy-MM-dd, so maybe a simple question : where can I found the date format that I have to use ?

Action« Gets item » failed: Expression « 2026-01-31T00:00:00.0000000 » isn't valid. Creating query failed.

Was this reply helpful? Yes No
rezarizvii 352 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @NC-25081327-0,

You can find the various date-time formats that Microsoft supports here: Standard date and time format strings - .NET | Microsoft Learn

Note: While there are tons of options there that are acceptable and the formateDateTime function supports, the standard and go-to format is usually the ISO-8601 format which is "yyyy-MM-ddThh:mm:ssZ"

Was this reply helpful? Yes No
NC-25081327-0 12 on at

Like
a
(0)

Report
Copy link

Link copied!

OK, but how can I determine the good format to set into my powerautomate function to have the good result ?

None of the answers provided (by you or hake) doesn't worked, due to this kind of error.

Was this reply helpful? Yes No
Haque 3,653 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @NC-25081327-0 Can you please post the screenshot of the error - with action card.

Was this reply helpful? Yes No
NC-25081327-0 12 on at

Like
a
(0)

Report
Copy link

Link copied!

Here is the error code :

Sorry, error message is in French, I don't know why ...

Here is the action card - (Obtenir les éléments = get items)

Was this reply helpful? Yes No