How to filter on lookup column in FetchXML

[jwnz]

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?

[eugenevanstaden]

@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>

[jwnz]

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?

[Fubar]

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

[jwnz]

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

[OliverRodrigues]

@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