Power Pages - Dynamic execution of long FetchXML without $batch

Introduction

Whether we like it or not, based on our recent hands-on experience, it is observed that, in Power Pages (Earlier Power Apps Portal), batch operation ($batch) is not supported yet.

Here is the MS Learn article link, which talks about batch operations, but it doesn’t apply to Power Pages yet.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/execute-batch-operations-using-web-api

Batch operation is the way to run long FetchXML using web api. But as it is not supported in Power Pages, an alternate approach would be to reduce the length of long FetchXML.

Here are some ideas/tips for keeping FetchXML minimal,

• Use <all-attributes /> instead of explicitly adding each attribute separately.
  • Note. This might cause performance issues if there are a lot of columns in the table.
• Try to avoid using ‘alias’ everywhere.
• If possible, keep one ‘link-entity’ element per one linked table.  
• Try to minimize the usage of the ‘order’ element. Sorting can be handled on the client side once data is fetched.

Client Side (Jquery) Code Example

In the below example, we are getting the current user id from the server and getting some key value from the browser’s local storage. Based on these input parameters, we are building dynamic FetchXML.

In FetchXML, you will find 2 elements for link-entity for “table2_logicalname”. It is because 2-level sorting works like this in FetchXML. It means first sorting will be done with the “table2_field10_logicalname” field and then with the “table2_field8_logicalname” field.

<script type="text/javascript">

$(document).ready ( function () {
  
	// Get current user id
	var currentUserId = "{{user.id}}";

	// Get some key's value from localStorage
	if (typeof (Storage) !== "undefined") {
	  someValueFromLocalStorage = window.localStorage.getItem('SomeKeyInLocalStorage');
	}

	var FetchXMLQuery = `<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">      
	<entity name="table1_logicalname">
	  <all-attributes />
	  <link-entity name="table2_logicalname" from="table2_idfield_logicalname" to="table1_field1_logicalname" link-type="inner" alias="LE1">
		<all-attributes />
		<filter>
		  <condition attribute="table2_field5_logicalname" operator="eq" value="` + currentUserId + `"></condition>
		</filter>
		<order attribute="table2_field10_logicalname" />
	  </link-entity>
	  <link-entity name="table2_logicalname" from="table2_idfield_logicalname" to="table1_field1_logicalname">
		<order attribute="table2_field8_logicalname" />
	  </link-entity>
	  <link-entity name="table3_logicalname" from="table3_idfield_logicalname" to="table1_field2_logicalname" link-type="inner" alias="LE2">
			<all-attributes />
			<filter type="and">
				<condition attribute="table3_field4_logicalname" operator="eq" value="` + someValueFromLocalStorage + `"></condition>
			</filter>
		</link-entity>
	  </entity>
	</fetch>`;
	

  $.ajax({
    type: "GET",
    contentType: "application/json; charset=utf-8",
    datatype: "json",		
    url: "/_api/table1_setname?FetchXML=" + encodeURIComponent(FetchXMLQuery),
    beforeSend: function(XMLHttpRequest) {
        XMLHttpRequest.setRequestHeader("Accept", "application/json");
        XMLHttpRequest.setRequestHeader("OData-MaxVersion", "4.0");
        XMLHttpRequest.setRequestHeader("OData-Version", "4.0");
        XMLHttpRequest.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    },
    async: false,
    success: function(data, textStatus, xhr) {        
        if(data.value.length > 0) {          
          // perform some operations on success
        }
    },
    error: function(xhr, textStatus, errorThrown) {
        console.log(errorThrown);
    }
  });
  
});
</script>

Happy Learning, Anywhere!