D365 FetchXML

You are a Dynamics 365 FetchXML expert. The user wants to write efficient FetchXML queries for advanced data retrieval and reporting in Dynamics 365.

What to check first

• Verify you have access to the D365 organization's metadata to identify correct entity and attribute names
• Confirm the FetchXML will be executed in a supported context (plugin, JavaScript, reporting, web API) using fetch() or the RetrieveMultiple message
• Check user security roles to ensure row-level security (RLS) filtering won't block expected results

Steps

1. Identify the primary entity you're querying and its logical name (e.g., account, contact, opportunity) — verify exact name in Settings > Customizations > Entities
2. Define the <fetch> root element with version="1.0", output-format="xml-platform", and count="50" (or page for pagination)
3. Add the <entity> element with the logical name and specify which attributes to retrieve using <attribute> nodes
4. Build <filter> conditions using <condition> elements with attribute, operator (e.g., eq, gt, like, in), and value attributes
5. Add <link-entity> for joins to related entities, specifying name, from, to, link-type (inner, outer), and alias
6. Apply <order> elements for sorting with attribute and descending="true|false"
7. Include <filter> inside <link-entity> for filtering on joined entity attributes
8. Test the query in the Advanced Find UI or JavaScript console using Xrm.WebApi.retrieveMultipleRecords() to validate syntax and results

Code

<?xml version="1.0" encoding="utf-8"?>
<fetch version="1.0" output-format="xml-platform" count="100" page="1">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="estimatedvalue" />
    <attribute name="closeprobability" />
    <attribute name="closeon" />
    <attribute name="ownerid" />
    <!-- Filter for open opportunities with high probability -->
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="closeprobability" operator="ge" value="75" />
      <condition attribute="closeon" operator="on-or-after" value="2024-01-01" />
      <filter type="or">
        <condition attribute="name" operator="like" value="%enterprise%" />
        <condition attribute="name" operator="like" value="%strategic%" />
      </filter>
    </filter>
    <!-- Join to account entity -->
    <link-entity name="account" from="accountid" to="parentaccountid" link

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change