filter on expand in web API query?

(0) Share

Report

Posted on by ishraqiyun77

154

So I have an API GET that looks like the following:

https://company.crm.dynamics.com/api/data/v9.0/accounts?$select=accountid,accountnumber,name&$expand=account_productbuyer($select=productbuyerid,status)&$filter=status code eq 1

I'd like to only return records in the expand from the account_productbuyer where the status equals a certain value. I've tried adding filter into the expand, but it is the incorrect syntax.

Is this possible to do via the web API? I'm trying to avoid iterating through the records returned in the expand to check for a certain status value.

Categories:

Customization / Solutions / SDK and API

All responses (3)

Answers (1)

Saad Kabarousse 734 on at

Like (0)

Report

RE: filter on expand in web API query?

Hello,

I am facing the same problem i'm trying to add a filter on a field on an expanded entity , i tried your example abouve but i'm getting the following error:

xxxxxxxx/.../accounts eq 1;$select=contactid)

Only $select clause can be provided while doing a $expand on entitysets
ishraqiyun77 154 on at

Like (0)

Report

RE: filter on expand in web API query?

That did the trick. Thanks for the help!
Verified answer

LeoAlt 16,331 Moderator on at

Like (1)

Report

RE: filter on expand in web API query?

Hi partner,

You could try this url:

company.crm.dynamics.com/.../accounts$filter=statuscode eq 1&;$select=productbuyerid,statuscode)

You need to put the condition "statuscode" in the expand.For examle, I tried to search for the contacts from account entity with statuscode = 1,and here are my url and response.

https://xxxxx.crm.dynamics.com/api/data/v9.0/accounts?$select=accountid,accountnumber,name&$expand=primarycontactid($filter=statuscode%20eq%201;$select=contactid)

You could also refer to this doc for more detals about how to use expand.

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/retrieve-entity-using-web-api#options-to-apply-to-expanded-entities

Hope it helps.

Best Regards,

Leo