Convert FetchXML Data to JSON in Power Pages for Dynamic Experiences

Introduction

Power Pages provides robust capabilities for displaying Dataverse data through FetchXML and Liquid templates. However, many advanced custom development scenarios require this data to be converted into a more universally consumable format: JSON. Whether you're building interactive dashboards, integrating with external APIs, or rendering dynamic charts, transforming FetchXML results into JSON is a crucial step.

By converting Dataverse records into JSON, developers can unlock the full potential of client-side scripting, build highly responsive user interfaces, and create truly dynamic portal experiences that enhance user engagement and streamline business processes.

In this article, we'll delve into a practical, step-by-step guide on how to retrieve Dataverse records using FetchXML and then efficiently convert them into JSON format directly within your Power Pages environment.

Understanding FetchXML

FetchXML is a proprietary query language specifically designed for Microsoft Dataverse. It provides a powerful and flexible way for developers to interact with Dataverse, enabling them to:

Here's a basic example of a FetchXML query to retrieve account records:

<fetch>
  <entity name="account">
    <attribute name="name"/>
    <attribute name="telephone1"/>
  </entity>
</fetch>

This query instructs Dataverse to retrieve all 'account' records, specifically including their 'name' and 'telephone1' attributes.

Why JSON is Essential for Modern Web Applications

JSON (JavaScript Object Notation) has become the de facto standard for data interchange in modern web applications due to its lightweight nature and human-readable format. Converting your FetchXML data to JSON offers several compelling benefits:

The Power Pages Data Conversion Workflow

The process of converting FetchXML data to JSON within Power Pages typically follows this workflow:

  1. FetchXML Query: Define the Dataverse data you need.
  2. Retrieve Dataverse Records: Use Liquid's {% fetchxml %} tag to execute the query.
  3. Liquid Template Processing: Iterate through the retrieved records using Liquid loops.
  4. Convert to JSON: Structure each record into a JSON object string within the Liquid loop.
  5. JavaScript Processing: Embed the generated JSON into a JavaScript variable.
  6. Display Data: Use JavaScript to render, filter, or integrate the data into your Power Pages UI.

This structured approach enables rich, client-side experiences that significantly enhance the interactivity and performance of your Power Pages portals.

Step-by-Step Guide: FetchXML to JSON

Step 1: Crafting Your FetchXML Query

First, define the FetchXML query that will retrieve the specific Dataverse records you need. Ensure you select all the attributes that you intend to use in your JSON output.

<fetch>
  <entity name="account">
    <attribute name="accountid"/>
    <attribute name="name"/>
    <attribute name="telephone1"/>
    <attribute name="emailaddress1"/>
  </entity>
</fetch>

This example retrieves the account ID, name, primary phone number, and email address for all account records. You can add more complex filters or joins as needed to refine your dataset.

Step 2: Executing FetchXML with Liquid

Next, embed your FetchXML query within a Liquid {% fetchxml %} tag. This tag executes the query against Dataverse and makes the results available as a Liquid object within your template.

{% fetchxml accounts %}
  <fetch>
    <entity name="account">
      <attribute name="accountid"/>
      <attribute name="name"/>
      <attribute name="telephone1"/>
      <attribute name="emailaddress1"/>
    </entity>
  </fetch>
{% endfetchxml %}

In this example, the results of the FetchXML query will be stored in a Liquid object named accounts. This object contains properties like accounts.results.entities, which is an array of the retrieved records.

Step 3: Transforming Records into JSON

Now, iterate through the accounts.results.entities collection using a Liquid {% for %} loop. Inside the loop, construct a JSON object string for each record, mapping Dataverse attributes to JSON properties. Crucially, use {% unless forloop.last %},{% endunless %} to correctly separate JSON objects with commas, ensuring valid JSON syntax.

[
  {% for record in accounts.results.entities %}
    {
      "id": "{{ record.accountid | escape }}",
      "name": "{{ record.name | escape }}",
      "phone": "{{ record.telephone1 | escape }}",
      "email": "{{ record.emailaddress1 | escape }}"
    }
    {% unless forloop.last %},{% endunless %}
  {% endfor %}
]

Notice the use of the | escape filter. This is vital for ensuring that any special characters within your Dataverse attribute values (like quotes or newlines) are properly escaped, preventing JSON parsing errors. The output will be a valid JSON array of objects, like this:

[
  {
    "id": "123e4567-e89b-12d3-a456-426614174000",
    "name": "Contoso Ltd.",
    "phone": "+1-555-123-4567",
    "email": "info@contoso.com"
  },
  {
    "id": "87654321-abcd-efgh-ijkl-1234567890ab",
    "name": "Fabrikam Inc.",
    "phone": "+1-555-987-6543",
    "email": "sales@fabrikam.com"
  }
]

This structured JSON data is now ready for client-side consumption.

Step 4: Consuming JSON with JavaScript

The final step is to embed the JSON output generated by Liquid into a JavaScript variable within a <script> block on your Power Pages page. This makes the data immediately accessible to your client-side scripts.

<script>
  var accountData = [
    {% for record in accounts.results.entities %}
      {
        "id": "{{ record.accountid | escape }}",
        "name": "{{ record.name | escape }}",
        "phone": "{{ record.telephone1 | escape }}",
        "email": "{{ record.emailaddress1 | escape }}"
      }
      {% unless forloop.last %},{% endunless %}
    {% endfor %}
  ];

  // Example: Log the data to the console
  console.log(accountData);

  // Example: Iterate and display names
  accountData.forEach(function(account) {
    console.log("Account Name: " + account.name);
    // You can now manipulate this data, render it to the DOM, etc.
  });
</script>

With the JSON data stored in a JavaScript variable, you can now leverage the full power of JavaScript to process, filter, sort, and display the records dynamically on your Power Pages site.

Real-World Applications and Use Cases

Dynamic Customer Dashboards

Imagine a customer portal where users can see their related accounts, contacts, and cases in a single, interactive dashboard. By converting FetchXML data to JSON, you can build dashboards that:

The workflow here involves fetching relevant customer data via FetchXML, converting it to JSON using Liquid, passing it to JavaScript, and then using a client-side library to render the dynamic dashboard components.

Integrating with Charts and Visualizations

JSON is the preferred data format for most modern charting libraries. Once your Dataverse data is in JSON, you can easily integrate it with:

Seamless API Integrations

The JSON output from Power Pages can be consumed by various external systems and APIs, significantly simplifying integrations:

Common Use Cases

Best Practices for Robust Implementations

To ensure your FetchXML to JSON conversion is efficient, secure, and scalable, consider these best practices:

Key Benefits of This Approach

Adopting this FetchXML to JSON conversion technique in Power Pages brings a multitude of advantages:

Conclusion

Converting FetchXML data into JSON format within Power Pages is a powerful and indispensable technique for building modern, interactive portal experiences. By skillfully combining FetchXML for data retrieval, Liquid templates for structured conversion, and JavaScript for client-side consumption, developers can transform raw Dataverse records into highly structured JSON objects that are easy to consume across various applications and integrations.

This approach not only significantly improves front-end performance and user experience but also provides unparalleled flexibility for advanced Power Pages development scenarios. Embrace this technique to unlock the full potential of your Power Pages portals and deliver truly dynamic, data-driven solutions.