Power Automate and SharePoint ODATA Filters

One of the common issues being faced is dealing with ODATA filtration in sharepoint lists so I will be listing here common issues and how to sort them out:

Filtering by Lookup:

Example:

You have a List called Tickets with a Lookup Column to another List called Ticket Types, Column Name TicketTypes to do the filtration, You can do the filtration either by the ID

Filter by Id:

LookupFielName/Id eq 2
TicketTypes/Id eq 2

Filter by Value:

LookupFielName/Value eq 'New Tickets'
TicketTypes/Value eq 'New Tickets'

Filter by Multi Choice:

currently not supported by the Rest ODATA Operation

Filter by Person Column:

If you want to filter by Person Column email , say your person column called Pupil use the below filter

Pupil/Email eq 'curiousmira@email.com'

Filter by Date:

It is simple as using the simple comparison operators: lt/le/eq/gt/ge or using the Year,Month,Day..etc full reference is mentioned in the references section, if you are using any of the simple operators the only trick here is to format the date field in the correct format which is ‘yyyy-MM-dd’

DateColumn eq 'formatdatetime(utcNow(),'yyyy-MM-dd')'

Filter by calculated column:

Currently Not supported but the workaround to do this as below:

Example:

You have a column called due date and you want to sent a reminder 30 days earlier, so in the happy world you added a new Column Calculated called Reminder Date which is 30 days away from Due Date, so in other words if Due Date is in 30 days send the reminder to do this you need to get the date in 30 Days using the Nice Action Get Future Time , and then in your filter query say Due Date eq the future Time.

formatDateTime(body('Get_future_time'),'yyyy-MM-dd')

Don’t forget the single quotes around the formatDateTime

References:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests#odata-query-operators-supported-in-the-sharepoint-rest-service?WT.mc_id=DX-MVP-5004221