Unanswered

How to filter on lookup column in FetchXML

(0) Share

Report

Posted on by jwnz

111

There are two tables, Customer and Order, in Order table there is a lookup column pointing to the Customer table.

If already knowing the customer id (assume it's 3), and want to search all orders belong to this customer, in SQL query we could do something like below without using Customer table:

SELECT order_date FROM Order WHERE customer_id = 3

What's the equivalent fetchxml to the above SQL query? Do I have to use the <link-entity></link-entity> to use Customer to achieve the same filtering result?

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

Sort by

eugenevanstaden 441 on at

Like (1)

Report
Copy link

Link copied!
@jwnz

Like this below as the Customer field is on the Order table.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="salesorder"> <attribute name="name" /> <attribute name="customerid" /> <attribute name="statuscode" /> <attribute name="totalamount" /> <attribute name="salesorderid" /> <order attribute="name" descending="false" /> <filter type="and"> <condition attribute="customerid" operator="eq" uitype="contact" value="{CD32B0AC-DC7F-EC11-8D21-00224810BB6F}" /> </filter> </entity> </fetch>

Was this reply helpful? Yes No
jwnz 111 on at

Like (0)

Report
Copy link

Link copied!
Hi @eugenevanstaden thanks.
In Power Pages, is there a place that I could export this xml?
<condition attribute="customerid" operator="eq" uitype="contact" value="{CD32B0AC-DC7F-EC11-8D21-00224810BB6F}" />
Is there any documentation about fetchxml, would like to learn the meaning of each element, like the above line of code, what could be in attribute, uitype, value?

Was this reply helpful? Yes No
Fubar 8,491 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

In a Model Driven App you can use Advanced Find to create a query and then download FetchXML. For more advanced queries people would generally use the FetchXML Builder plugin that is in XrmToolBox.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/use-fetchxml-construct-query

Was this reply helpful? Yes No
jwnz 111 on at

Like (0)

Report
Copy link

Link copied!

@Fubar I assume Power Pages is not a Model Driven App, so wonder if Advanced Find is still available?

Was this reply helpful? Yes No
oliver.rodrigues 9,471 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@jwnz Power Pages is not a model-driven app, however it has a model-driven app behind it, which is the Portal Management App

From there you can export data, I believe Advanced Find is coming disabled by default now as this is being replaced by a more modern search engine: Modern advanced find with enhanced view management in model-driven apps | Microsoft Power Apps

Was this reply helpful? Yes No