Filtering OData Feeds

Last time we talked about getting OData Feeds into Excel, this time let’s talk about how to filter that OData Feeds.

When you connect and pull data using OData, it pulls ALL the records from the feed that you have selected. If you are dealing with large data, it is expensive to pull large data every single time you do the pull. Historical data such as ledgers can take from minutes to hours to pull it. It can be frustrating when you need to wait for a very long time, especially when you are planning to use only a small subset of the data.

There is also another case where you need to limit the data for security reason.

Regardless of the reasons, there is an advantage to be able to pull only the data you need. This can be achieved by using Filter Expression on the OData Feed.

To add a filter to OData, add $filter= at the end of the URL.

Let’s take an example of Vendor Ledger Entries OData. Below is the result if you pull the data as it is.

Let’s try pull only data for Vendor No. = 20000.

Original:
https://api.businesscentral.dynamics.com/v2.0/e6x7218c-xxxx-xxxx-xxxx-c9a01375b7f6/Sandbox/ODataV4/Company('CRONUS%20NZ')/VendorLedgerEntries

With Filter:
https://api.businesscentral.dynamics.com/v2.0/e6x7218c-xxxx-xxxx-xxxx-c9a01375b7f6/Sandbox/ODataV4/Company('CRONUS%20NZ')/VendorLedgerEntries?$filter=Vendor_No eq '20000'

We can see that it only pulls records with Vendor No. 20000.

Let’s try putting more filter, such as any records with due date up to 31 January 2019.

Original:
https://api.businesscentral.dynamics.com/v2.0/e6x7218c-xxxx-xxxx-xxxx-c9a01375b7f6/Sandbox/ODataV4/Company('CRONUS%20NZ')/VendorLedgerEntries

With Filter:
https://api.businesscentral.dynamics.com/v2.0/e6x7218c-xxxx-xxxx-xxxx-c9a01375b7f6/Sandbox/ODataV4/Company('CRONUS%20NZ')/VendorLedgerEntries?$filter=Vendor_No eq '20000' and Posting_Date le 2019-01-31

If you want to limit the fields that you pull, you can use $select. Use & to combine $select and $filter.

Let’s try getting only Vendor No, Document No, and Posting Date fields. Less data is always good.

Original:
https://api.businesscentral.dynamics.com/v2.0/e6x7218c-xxxx-xxxx-xxxx-c9a01375b7f6/Sandbox/ODataV4/Company('CRONUS%20NZ')/VendorLedgerEntries

With Filter and Select:
https://api.businesscentral.dynamics.com/v2.0/e6x7218c-xxxx-xxxx-xxxx-c9a01375b7f6/Sandbox/ODataV4/Company('CRONUS%20NZ')/VendorLedgerEntries?$select=Vendor_No,Document_No,Posting_Date&$filter=Vendor_No eq '20000' and Posting_Date le 2019-01-31

If we try to do $select in Excel, it will return errors for each of the fields that you do not specify in the $select statement. You need to make sure to remove the Columns using Power Query Editor.

However, if you try to use the normal web services, you can see that it only pulls the specified fields.

Below are some of the list of filtering expressions that you can use.

Definition	Example and explanation	Equivalent C/AL expression
Select a range of values	filter=Entry_No gt 610 and Entry_No lt 615 Query on GLEntry service. Returns entry numbers 611 through 614.	..
And	filter=Country_Region_Code eq 'ES' and Payment_Terms_Code eq '14 DAYS' Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.	&
Or	filter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US' Query on Customer service. Returns customers in Spain and the United States. Alert: You can use OR operators to apply different filters on the same field. However, you cannot use OR operators to apply filters on two different fields.	|
Less than	filter=Entry_No lt 610 Query on GLEntry service. Returns entry numbers that are less than 610.	<
Greater than	filter= Entry_No gt 610 Query on GLEntry service. Returns entry numbers 611 and higher.	>
Greater than or equal to	filter=Entry_No ge 610 Query on GLEntry service. Returns entry numbers 610 and higher.	>=
Less than or equal to	filter=Entry_No le 610 Query on GLEntry service. Returns entry numbers up to and including 610.	<=
Different from (not equal)	filter=VAT_Bus_Posting_Group ne 'EXPORT' Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.	<>
endswith	filter=endswith(VAT_Bus_Posting_Group,'RT') Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.	*
startswith	filter=startswith(Name, 'S') Query on Customer service. Returns all customers names beginning with “S”.
substringof	filter=substringof(Name, ‘urn’) Query on Customer service. Returns customer records for customers with names containing the string “urn”.
indexof	filter=indexof(Location_Code, ‘BLUE’) eq 0 Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.
replace	filter=replace(City, 'Miami', 'Tampa') eq 'CODERED'
substring	filter=substring(Location_Code, 5) eq 'RED' Query on Customer service. Returns true for customers with the string RED in their location code starting as position 5.
tolower	filter=tolower(Location_Code) eq 'code red'
toupper	filter=toupper(FText) eq '2ND ROW'
trim	filter=trim(FCode) eq 'CODE RED'
concat	filter=concat(concat(FText, ', '), FCode) eq '2nd row, CODE RED'
round	filter=round(FDecimal) eq 1
floor	filter=floor(FDecimal) eq 0
ceiling	filter=ceiling(FDecimal) eq 1

You can refer Microsoft Docs to learn more about filtering.