I hate OData filters! (filtering by date and employee number)

[crispybits1977]

Hi all

I can never seem to get my head around OData filters - every time I have to use one it never works until I spend ages troubleshooting...

Anyway, rant aside I'm trying to get items from a sharepoint list (that may or may not exist) and then depending on whether they exist I'll either update or create new item.

The OData filter is as follows:

'EmployeeNumber' eq @{outputs('EmpNumber')} and @{formatDateTime(outputs('Start2'),'yyyy-MM-dd')} eq @{outputs('Start2')}

When I try to run it with test data that does exist in the list I get the following error:

The expression "'EmployeeNumber' eq 770125 and 2024-02-14 eq 2024-02-14" is not valid. Creating query failed.

EmployeeNumber is a number column in the Sharepoint list and the output it references is pulled from an email subject line:

int(substring(item()?['subject'],sub(length(item()?['subject']),6),6))

The dates seem to match format...

What on earth am I doing wrong? 😫

[crispybits1977]

Just realised the second part is checking against itslef. here is the code as I should have posted it (see what I mean about OData and my brain just don't mix...)

'EmployeeNumber' eq @{outputs('EmpNumber')} and 'Date Absence Started' eq @{outputs('Start2')}

Where 'Date Absence Started' is a date column in the Sharepoint list

[crispybits1977]

OK so quick update - I separated the filters - when I filter on just employeenumber it works fine, it's the date filter causing me issues (it's always the ****ing date filter...)

I've even taken it right back to basic and hardcoded the date like this:

'Date Absence Started' eq '2024-02-14'

This should return a result, but it returns an error.

The Sharepoint list column is set up correctly (and is already in use in a power app and has a whole load of data in it so removing and recreating it isn't particularly viable...)

Why can't OData just work? 😭

[crispybits1977]

Not a solution, but given the uses for this list I've got it working by just taking all the lines with a given employee number and then doing a filter in a condition in the next step instead. It's not pretty but at least it works now...

[abm]

Hi   

'Date Absence Started' eq '2024-02-14'

Here you having spaces between the column names. So there are few things you need to understand here. If you have spaces in your column names then you need to pass the actual schema name. To find the actual schema name click List settings > Click column date field Date Absence Started. Check the browser url at the end it should say Field=xxxxxx

Take the value after the equal sign. This is your schema name which you need to use for the filters.

'EmployeeNumber' eq @{outputs('EmpNumber')} and @{formatDateTime(outputs('Start2'),'yyyy-MM-dd')} eq @{outputs('Start2')}

The above filter is ok except you missed the single quotes around the date comparison. For numeric values you won't need quotes but for all the other ones you need a single quotes around it.

Hope it make sense. If you need any further help in this please let me know.

Thanks

[creativeopinion]

 You might find this YT Tutorial helpful: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

You can refer to this section of the tutorial where I cover how to troubleshoot a Filter Query. There are certain col...

Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.

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

@crispybits1977

[crispybits1977]

Thanks both - I've already built loads of bits around it now and it's working so I'll leave this one alone, but I've bookmarked your links @creativeopinion for the next time I inevitably come undone with the OData filters (it won't take long...)