Power Pages: How to Merge Two Dataverse Tables in a Single View

Unlike using UNION in SQL, Power Pages cannot perform a union-like statement with views and/or Liquid since we don’t have any out-of-the-box options. With FetchXML, we have the capability to address this limitation.

Requirement: Merge the ‘Orders’ and ‘Sales’ tables into a single view.

Step 1: Retrieve data from both tables via FetchXML, ensuring they share the same column headers.

{% fetchxml orderFetch %}
  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="mag_order">
      <attribute name="mag_name" />
      <attribute name="mag_price" />
      <filter type="and">
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </entity>
  </fetch>
{% endfetchxml %}

{% fetchxml salesFetch %}
  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="mag_slaes">
      <attribute name="mag_name" />
      <attribute name="mag_price" />
      <filter type="and">
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </entity>
  </fetch>
{% endfetchxml %}

Note that to merge two tables into one view, both tables need to have the same column headers.

Step 2: Create your own view and insert rows from different tables. I simply use a basic HTML table for this purpose.

let html =
            `<div class="editable-grid">
        <table id="purchaseTable" class="table">
            <thead>
                <tr>
                    <th class="th-headertitle">Name</th>
                    <th class="th-headertitle">Price</th>
                </tr>
            </thead>
            <tbody>
            {% for order in orderFetch.results.entities %} 
                <tr id="purchaseTableId" >                    
                    <td class="td-mag" data-label="Name"><label>{{order.mag_name}}</label></td> 
                    <td class="td-mag" data-label="Price"><label>{{order.mag_price}</label></td> 
                </tr>
            {% endfor %} 

            {% for sales in salesFetch.results.entities %} 
                <tr id="purchaseTableId" >                    
                    <td class="td-mag" data-label="Name"><label>{{sales.mag_name}}</label></td> 
                    <td class="td-mag" data-label="Price"><label>{{sales.mag_price}</label></td> 
                </tr>
            {% endfor %} 
            </tbody>
        </table>
    </div>`;

Step 3: Insert the new view into your form/page.

To insert this table into the view, there are several ways to achieve it. I simply want to add it above my custom field ‘mag_customerid’.

$("#mag_customerid").parent().parent().prepend(html);

Hope this workaround helps :-)