Filter query date eq doesn't return values

[KoenSmeulders]

I'm a bit stuck here, I'm trying to get a specific item from a list based on the Location (lookup) and Date fields, this combination is unique and will only return a single item so I can get it's ID to update it.

In the 'Get items' action I'm using a filter query but for some reason using 'eq' for the date doesn't work for me while testing with 'gt' returns the item I'm looking for plus all later dates, while 'lt' returns every item before (not including) the date.

Looking at the return body of the queries that return items, shows me the date format is correct. For instance, I can get items with "Date": "2020-06-11", while a query with Date eq '2020-06-11' will not return any results.

Can anyone help me with this? My backup plan is just using 'gt' and taking the first item (since it's the date I'm looking for) but this feels wrong.

[Mira_Ghaly]

@KoenSmeulders 

Can you check your column type in sharepoint whether it is date time or date only; Make sure it is date only

If this post helps you with your problem, please mark your as Accepted solution.

If you like my response, please give it a Thumbs Up.

MG (Naturally Curious)

[KoenSmeulders]

It is date only. It's also clear in the output body since date & time would return more than just the date.

[Mira_Ghaly]

@KoenSmeulders 

Can you paste a screenshot of your flow ?

[KoenSmeulders]

Here's the bit I'm having trouble with, It's the query in the "Get items" that's troubling me. The 'lt' you see now should be 'eq' but that doesn't return any values.

The return of 'lt' starts off like this (note the date is in the correct format): 

[v-bacao-msft]

Hi @KoenSmeulders ,

The type of Location field does not seem to be the expected type. If the type of Location is calculated column or Choice, it cannot be configured as filters in Filter Query.

You can consider using Filter array or Condition to filter items. If the field type is Choice, then the Dynamic content you need to select should be Location Value.

Hope that makes sense.

Best Regards,

[KoenSmeulders]

I have no issues using the Location field as a filter, it is a lookup column and I'm using the ID to filter.

The problems I'm having are related to the Date field.

[Mira_Ghaly]

@KoenSmeulders 

to compare to utcnow

use the below expression formatdatetime(utcnow(),'yyyy-MM-dd') instead of utcnow()

If this post helps you with your problem, please mark your as Accepted solution.

If you like my response, please give it a Thumbs Up.

MG (Naturally Curious)

[KoenSmeulders]

It's not utcnow() that's giving me issues, it's when I'm trying to use eq for a specific date (to match a registration date and location in another list).

Now the reason I'm looking up previous registrations is because my flow appears to ignore those of a specific user, but only using the query: Datum eq '@{addDays(utcNow('yyyy-MM-dd'), -1, 'yyyy-MM-dd')}' and Location eq 'ID'. Maybe that's why my new query is giving me trouble too, since it's more or less the same thing. I can find those registrations when I only filter on location or date, but when I try to combine both, it seems to ignore a single user for some reason.

[Red_Snow94]

Did you ever find a solution to this? I am having a similar issue filtering dates