odata query for filter on basis of value in a lookup field(not by guid)

(0) Share

Report

Posted on by Community Member

How to write odata query for filtering records on basis of lookup Title value (eg retrieve all records from employee which contains employee band lookup value equals "MANAGER BAND" without using guid of "

MANAGER BAND" record )?

6825.community.png

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Rajat Awasthi 675 on at

Like (0)

Report

Hi Purnima ,

You can retrieve both i.e name and Id of a lookupfield.

also , to write your odata query you can download and install Odata Query Builder and generate your desired code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for reply ...I use odata query builder but i am unable to bulild query for filter on the basis of lookup title

Was this reply helpful? Yes No
Rajat Awasthi 675 on at

Like (0)

Report

Can you paste a screenshot here.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I attached the screenshot

Was this reply helpful? Yes No
Rajat Awasthi 675 on at

Like (0)

Report

Hi Purnima ,

Sorry but I don't think lookup will allow you to get record based on name.

I suggest you to do this by retrieving the GUID for the Lookup and then retrieving the all the employees record.

Was this reply helpful? Yes No
Suggested answer

Moh Helper 250 on at

Like (0)

Report

Hi,

Unfortunately for your case it's impossible via odata.

You can make this example of odata query using with a lookup :

/ContactSet?$select=FullName&$filter=ParentCustomerId/Name eq 'XYZ'

But the limitation is that you can only query on a single Entity Type...

Kr,

Moh

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Options as I see it:

I'm assuming

a. You have an entity named 'Title'

b. You are using this entity like a category so there are not many records in it eg. MANAGER BAND

1.

Just create a subgrid within your 'Title' enties form with properties:

-Data Source-

Records: Only Related Records

Entity: Employees

Deault View: Active Employees

Now when you click on the 'MANAGER BAND' record, a list of all the employees willl be displayed in this subgrid (employees which have MANAGER BAND as title lookup)

2. Redesign your database schema

3. Just use the {GUID}. There is only one GUID you need to get which you can get by filtering using advanced find - the GUID of your MANAGER BAND Title record will be displayed in the URL when you find and open it.

Then run your ODATA query using that ID.

Was this reply helpful? Yes No