sharepoint list filter not working on a date time field with show no time

(1) Share

Report

Posted on by JD_26

102

Hi I am trying to filter a sharepoint list i have tried adding a compose and adding

utcNow('yyyy-MM-ddTHH:mm:ssZ')

Then the column name FutureDate which is set as date time format , don't show time , so the dates are in a format either ending 00:00 or 23:00 depending on if the user is in Uk or Us.

I also have a FutureNotified which is a Text field which is either blank or Y , if a notified date has been actioned.

when I run the flow , the flow fails on Get Items , I have tried changing the utcNow('yyyy-MM-ddTHH:mm:ssZ')utcNow('yyyy-MM-dd') but it still fails. here is the error message , any help on this would much appreciated , thanks.

{ "status": 400, "message": "The expression \"FutureDate le 2024-04-27T14:25:51Z and FutureNotified eq null\" is not valid. Creating query failed.\r\nclientRequestId: 87c99acb\r\nserviceRequestId: -473bf7c99acb" }

Categories:

Using flows

I have the same question (0)

All responses (7)

Answers (1)

RobElliott 10,323 Super User 2025 Season 2 on at

Like (1)

Report

The expression utcNow('yyyy-MM-ddTHH:mm:ssZ') is not correct, it should be formatDateTime(utcNow(....)

Was this reply helpful? Yes No
JD_26 102 on at

Like (1)

Report

formatDateTime(utcNow(....) is not valid

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (1)

Report

@JD_26 it always helps if you can add screenshots so we can see what you're trying to achieve and what's happening a bit more easily. In my example I am bringing back any items which are less than 34 days ahead and where the FutureNotified column is empty. So that is less than or equal to 1 June and it should return Antigua only.

I've added a Compose action to calculate the date 34 days ahead using the expression
formatDateTime(addDays(utcNow(),34), 'yyyy-MM-dd')

Then in the Filter Query field I've added the expression Arrival le '' and inside the single apostrophes selected the outputs of the compose action and then followed that with and FutureNotified eq null

That's just followed with an apply to each with a compose to get the country title:

And as you see the flow ran successfully and just brought back 1 item, Antigua.

I appreciate that's not exactly what you are doing and you are trying to bring back items earlier than today. So your first Compose action would be formatDateTime(utcNow(), 'yyyy-MM-dd')

Rob
Los Gallardos
Principal Consultant, SharePoint, Forms and Power Platform, WSP Global (and classic 1967 Morris Traveller driver)

Was this reply helpful? Yes No
JD_26 102 on at

Like (1)

Report

Thankyou that has now fixed the get items filter , but something odd is happening when I attempt to post a message to teams ,
I have 3 set up , they are Schedules , Finance and Lessors , when I test it , it is posting the 3 examples , 1 is in finance and the other 2 are lessors , but they are all getting posted in the finance channel , then I get an error in the lessors part of the flow
in the condition for fianance I have an or condition based field_4 = Bob Jones or field_4 = Dave Smith
and then if true post to the T and F Notifications channel which is the finance channel , then after that I have the lessors condition where field_4 = Mary James or field 4 = Helen Jones and if matched post tems message to Lessors Notification , but for some reason they are all getting posted to the T and F notifications , before this condition is looked at , and then a message saying access prohibited. any help on this would be much appreciated thanks.

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (1)

Report

@JD_26 I wouldn't use multiple for each controls. Instead I would simplify it and add a single afor each with a switch control inside it. A switch is a type of condition that looks at just one thing, whether it's Schedule, Finance or Lessors You can have up to 25 cases in each switch: case channel = Schedule, then post to Schedule channel, case channel = Finance then post to Finance channel and so on.

Rob
Los Gallardos
Principal Consultant, SharePoint, Forms and Power Platform, WSP Global (and classic 1967 Morris Traveller driver)

Was this reply helpful? Yes No
JD_26 102 on at

Like (1)

Report

Thankyou for your reply , is there any examples on how to create a switch condition please as I have never used one

Was this reply helpful? Yes No
Verified answer

RobElliott 10,323 Super User 2025 Season 2 on at

Like (1)

Report

@JD_26 expanding on my earlier flow I have added a switch which you'll see below. IN this example I've changed the filter query to bring back those where the date is less than or eqial to 34 days ahead and FutureNotified ne null. So the flow will post to the Teams Schedule Channel if the Officer is Chris Rea or Mark Knopfler, but if it's Johnny Coppin it will post to the Finance Channel.

Add a Switch control:

In the Switch select the column that you want the switch to look at, in this case it's Officer. Then click the + (outside the default) to add the first case:

After you click the + enter the parameter, so in this case it's equals Chris Rea.

Inside the case click the + and add your post message in a chat or a channel action, selecting your Team and channel and then add your message,

Do the same for the other cases:

Then save & run the flow:

2 messages are posted into the Schedule channel:

And 1 message is posted in the Finance channel:

Rob
Los Gallardos
Principal Consultant, SharePoint, Forms and Power Platform, WSP Global (and classic 1967 Morris Traveller driver)

Was this reply helpful? Yes No