knowledgecenter-breadcrum

Knowledge Center

28 Feb, 2023

Filter in expand query of OData (D365 CE)

Posted on 28 Feb, 2023 by Admin, Posted in Dynamics 365 Web Api Dataverse

Filter in expand query of OData (D365 CE) Blogs

Introduction: In this Blog, we will learn how to use filter in expand of OData.

Example:
Let’s say we have an account with multiple contacts, and we want to filter out only the contacts who have their contact method listed as ‘Email’.

How to write API Request:

a. You can get Service Root URL from Advance Settings >> Customizations >> Developer Resources.

b. You have to put  Entity Set Name after Service Root URL and it can be get from XRM Tool Box. (The tool name is MetaData Browser).


c. You have to put Guid of the entity that you have given in Point b. (GUID is a unique ID for all the records in CRM).


d. $select is used to select the attribute which you want to show in output.

e. $expand is used to expand the lookup field. You can get the relationship of the of account and contact from XRM Tool Box. (The tool name is MetaData Browser).


Solution:
Write an OData query for a particular account from which we will select attributes like name, telephone, and Parent account. We will do expand (contact related to account) to get some data want from the contact like firstname, lastname, email address, mobilephone, contact method.

API Request:
https://dev-onepiece07.api.crm7.dynamics.com/api/data/v9.2/accounts(c07762b7-96b2-ed11-83ff 002248627a02)?$select=name,telephone1,_parentaccountid_value&$expand=contact_customer_accounts($select=firstname,lastname,emailaddress1,mobilephone,statecode,preferredcontactmethodcode)
Output:

2. We will set filter condition by filtering contact method = Email in expand to filter all the contact who has Email in contact method.


API Request:
https://dev-onepiece07.api.crm7.dynamics.com/api/data/v9.2/accounts(c07762b7-96b2-ed11-83ff-002248627a02)?$select=name,telephone1,_parentaccountid_value&$expand=contact_customer_accounts($select=firstname,lastname,emailaddress1,mobilephone,statecode,preferredcontactmethodcode;$filter=preferredcontactmethodcode eq 2)
Output:

We have successfully applied a filter to display only the contacts that have an email listed as their contact method.

Comment

This is a Required Field

Loading

Recent Updates

Ad hoc support Thumbnail image
Blogs
12 Mar, 2025

What is Ad hoc Support and Why Was It Created?

What is Ad hoc Support and Why Was It Created? Businesses today are constantly navigating obstacles that need swift and…

READ MORE
Data Migration thumbnail
Blogs
11 Mar, 2025

Data Migration: On-Premises to Cloud CRM Systems - A Step-by-Step Guide

Data Migration: On-Premises to Cloud CRM Systems - A Step-by-Step Guide Many businesses are moving their CRM systems from on-premises…

READ MORE
D365 Customer Service - Thumbnail
Blogs
03 Mar, 2025

What Is Dynamics 365 Customer Service?

What Is Dynamics 365 Customer Service? Dynamics 365 Customer Service is a powerful solution by Microsoft designed to enhance customer…

READ MORE