Power Pages, FetchXML and Liquid: Group results by Lookup fields

When we run FetchXML queries on Power Pages Liquid code we get the results as a single table data structure. But sometimes it would be useful if we could group the results by some of the fields on the data set before rendering the items on the page, so we can use that for some calculations or to render data in a structure that is not a table structure or flat data structure display, like a hierarchy view for instance.

We can do it easily by using the group_by Liquid filter and the correct syntax.

Example scenario

For example, for the following data set, where we have a table named submissions and each submission is assigned to an Account:

If we want to render the following page, with the Account names as h2 heading elements, and below it an unordered list (ul) tag with the submissions listed as list item (li) items for each account:

Given that we have the following FetchXml query on the page or WebTemplate:

{% fetchxml fetch_submissions %}
<fetch returntotalrecordcount="true" count="5000">
  <entity name="pnp_submission">
    <attribute name="createdon" />
    <attribute name="modifiedon" />
    <attribute name="pnp_account" />
    <attribute name="pnp_name" />      
  </entity>
</fetch>
{% endfetchxml %}

We can use the code below that uses the group_by filter to group the results and later iterates through grouped items and render this page:

{% assign groups = fetch_submissions.results.entities | group_by: "pnp_account.id"%}
{% for group in groups  %}
   {% assign thisAccountId = group.key %}
   <h2>{{group.items.first.pnp_account.name}}</h2>
   <ul>
   {% for submission in group.items %}   
      <li>
      {{submission.pnp_name}} ({{submission.createdon | date: "dd/MM/yyyy"}})
      </li>
   {% endfor %}  
   </ul>
{% endfor %}

Note that:

• First, we iterate through all the groups, and get the first item’s name values to render the header (group.items.first.pnp_account.name). This is useful in case you want to display related values which are not the key (field used to group) for the grouped items. As we are sure that in this case, all the items in the group have the same account name, we can simply get the value from the first item.
• Then for each of the ‘groups’, we iterate through its items and render accordingly.
• Note that for grouping lookup fields it will be slightly different from the documentation, since we need to use the “fieldname.id” or “fieldname.name” syntax.

Conclusion

By using the group_by liquid filter and the “fieldname.id” or “fieldname.name” syntax we are able to easily group results from FetchXML queries in custom Power Pages Web Templates or Pages developed with Liquid.

Note: This technique takes into account grouping the data obtained in the current result set. So for example, if your fetchXml query has more than 5000 items, it default loads only the first 5000 items, and obviously the grouping will work only on top of that.

To load the full data set with more than 5000 items and do the grouping properly, more logic/techniques would be needed. This post’s intention is to explain how we could leverage the group_by liquid filter to manipulate the current results.

References

Group By – Microsoft Learn

The post Power Pages, FetchXML and Liquid: Group results by Lookup fields appeared first on michelcarlo.

Original Post https://michelcarlo.com/2024/08/10/power-pages-fetchxml-and-liquid-group-results-by-lookup-fields/