Adding Total Columns to an Entity List

Hey Hetz, we want totals for those columns on our list page...

I was recently working with a client that needed to have the total amounts displayed on a Power Pages web page for certain columns within an entity list that is displayed on that page. There may be a better way to go about this, but here is the solution that I came up with and it works. I hope this post can help others out as well if they find themselves in a similar situation.

The background information

This client has a very interesting Dataverse and Power Pages setup which is entirely dependent on virtual entities. Their entity lists are actually populated by SQL views from an on-premises SQL server. So, I was not able to add calculated columns for the totals nor is the list filtered prior to loading in the portal. The entity list simply contains the results of the SQL view, and the portal list is set to restrict the list based on the currently logged in user (portal contact). I am not going to go into all of the design details for this particular client in this post, but it required me to come up with a solution for totaling columns within the client-side HTML/JavaScript/Liquid code. See also the Power Pages documentation on their usage of Liquid tags: https://learn.microsoft.com/en-us/power-pages/configure/liquid/liquid-overview

The problem

This client maintains a list of training and certification courses which their membership has completed within a given reporting period. When one of their members logs into the membership portal, one of the lists they are able to view is their personal record of training and certifications completed for their personal reporting period. This list includes various data about the courses (name, description, locations, type, etc.) Four of the columns contain credit hour data and work together to accumulate the total credits for an individual for a given reporting period. The algorithm for displaying the raw and adjusted totals needed to be implemented within the client-side power Page code for the reasons given above. After trying several JavaScript based approaches, including the standard approach of adding custom JavaScript to the list, I found that since this client is using the new Portal Studio, these conventional approaches do not work. The default studio page template includes the list within a Liquid tag and allows for the modern list control to be used as follows:

{% include 'entity_list' key: 'Your List Name' isModern:'true' %}

It should also be noted that portal studio does NOT add the list to the portal web page in the List lookup metadata for the web page in the site admin application.

Power Pages Web Page meta data

The solution

I was able to implement a solution in this client's particular situation which utilized Liquid code on the content page which hosts the entity list. Here is that solution:

First, add the entity list to the page's meta data.

Redacted list on web page setting

Next, add liquid tags after the include tag to reference the entity list (and the view of that entity which the page is using) from the page's settings:

{% entitylist id:page.adx_entitylist.id %}
   {% entityview my_view = logical_name:'Your entity's logical name', name:'Your view's display name' %}
      <!--  Variable declaration, loop, and visualization code will go here -->
   {% endentityview %}
{% endentitylist %}

Now that I had the entity list and the view within the liquid code (outside of the default studio page template's implementation of it) I could begin to work with the filtered values in an effort to create the totals and implement the client's logic for adjusting those totals based on their custom logic for maximum values within a reporting period. The first step was to create the holding variables for the total amounts and the maximum values for a given period:

{% entitylist id:page.adx_entitylist.id %}
   {% entityview my_view = logical_name:'Your entity's logical name', name:'Your view's display name' %}
      {% assign totalC = 0 %}
      {% assign totalE = 0 %}
      {% assign totalW = 0 %}
      {% assign totalSS = 0 %}
      {% assign maxSS = 15 %} <!-- Max SS credit in a reporting period is 15 --> 
      {% assign maxW = 6 %}  <!-- Max W credit in a reporting period is 6 --> 
      <!--  Loop, and visualization code will go here -->
   {% endentityview %}
{% endentitylist %}

Let's move onto looping through the list and accumulating the total amounts. An additional wrinkle in the SQL virtual entity where this data is retrieved from is the numerical values can be null, so a null check needs to be performed before adding a value to the total. Adding null to a numerical value in Liquid results in null otherwise:

{% entitylist id:page.adx_entitylist.id %}
   {% entityview my_view = logical_name:'Your entity's logical name', name:'Your view's display name' %}
      {% assign totalC = 0 %}
      {% assign totalE = 0 %}
      {% assign totalW = 0 %}
      {% assign totalSS = 0 %}
      {% assign maxSS = 15 %} <!-- Max SS credit in a reporting period is 15 --> 
      {% assign maxW = 6 %}  <!-- Max W credit in a reporting period is 6 -->
      {% for rec in my_view.records %}
            {% assign totalC = totalC | plus: rec.c %} <!-- liquid implementation of addition -->
            {% if rec.e %}
              {% assign totalE = totalE | plus: rec.es %}
            {% endif %}
            {% if rec.w %}
              {% assign totalW = totalW | plus: rec.w %}
            {% endif %}
            {% if rec.ss %}
              {% assign totalSS = totalSS | plus: rec.ss %}
            {% endif %}
          {% endfor %}
      <!--  Visualization code will go here -->
   {% endentityview %}
{% endentitylist %}

Using dev tools in Edge, I was able to determine the CSS that was being used for the entity list, so I re-use that CSS to visualize the totals and align them underneath the proper columns. Screenshots appear at the end of this post to aid in your understanding of the overall layout of the list and the column totals.

{% entitylist id:page.adx_entitylist.id %}
   {% entityview my_view = logical_name:'Your entity's logical name', name:'Your view's display name' %}
      {% assign totalC = 0 %}
      {% assign totalE = 0 %}
      {% assign totalW = 0 %}
      {% assign totalSS = 0 %}
      {% assign maxSS = 15 %} <!-- Max SS credit in a reporting period is 15 --> 
      {% assign maxW = 6 %}  <!-- Max W credit in a reporting period is 6 -->
      {% for rec in my_view.records %}
            {% assign totalC = totalC | plus: rec.c %} <!-- liquid implementation of addition -->
            {% if rec.e %}
              {% assign totalE = totalE | plus: rec.es %}
            {% endif %}
            {% if rec.w %}
              {% assign totalW = totalW | plus: rec.w %}
            {% endif %}
            {% if rec.ss %}
              {% assign totalSS = totalSS | plus: rec.ss %}
            {% endif %}
      {% endfor %}
      <!-- Format table with CSS from the portal page with totals and text -->
      <div class="ms-DetailsRow-fields fields-270" role="presentation" style="font-weight: bold">
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 150px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 111px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 120px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px; text-align: right">TOTALS: &nbsp;</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalC }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalE }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalW }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalSS }</div>
      </div>
   {% endentityview %}
{% endentitylist %}

After the raw totals row, I needed to determine the adjusted totals, using the maximum variables declared earlier:

{% entitylist id:page.adx_entitylist.id %}
   {% entityview my_view = logical_name:'Your entity's logical name', name:'Your view's display name' %}
      {% assign totalC = 0 %}
      {% assign totalE = 0 %}
      {% assign totalW = 0 %}
      {% assign totalSS = 0 %}
      {% assign maxSS = 15 %} <!-- Max SS credit in a reporting period is 15 --> 
      {% assign maxW = 6 %}  <!-- Max W credit in a reporting period is 6 -->
      {% for rec in my_view.records %}
            {% assign totalC = totalC | plus: rec.c %} <!-- liquid implementation of addition -->
            {% if rec.e %}
              {% assign totalE = totalE | plus: rec.es %}
            {% endif %}
            {% if rec.w %}
              {% assign totalW = totalW | plus: rec.w %}
            {% endif %}
            {% if rec.ss %}
              {% assign totalSS = totalSS | plus: rec.ss %}
            {% endif %}
      {% endfor %}

      <!-- Format table with CSS from the portal page with totals and text -->
      <div class="ms-DetailsRow-fields fields-270" role="presentation" style="font-weight: bold">
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 150px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 111px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 120px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px; text-align: right">TOTALS: &nbsp;</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalC }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalE }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalW }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalSS }</div>
      </div>

      <!-- Subtract max c for ss and w for the reporting period -->
      {% if totalSSS > maxSSS %}
         {% assign sSAdjust = totalSS | minus: maxSS %}  <!-- Liquid implementation of subtraction -->
         {% assign totalC = totalC | minus: sSAdjust %}
      {% endif %}
      {% if totalW > maxW %}
         {% assign wAdjust = totalW | minus: maxW %}
         {% assign totalC = totalC | minus: wAdjust %}
      {% endif %}

      <!-- Visualize the adjusted totals -->
   {% endentityview %}
{% endentitylist %}

Finally, the adjusted totals are visualized on the page, just as the raw totals were (this is the final code):

{% entitylist id:page.adx_entitylist.id %}
   {% entityview my_view = logical_name:'Your entity's logical name', name:'Your view's display name' %}
      {% assign totalC = 0 %}
      {% assign totalE = 0 %}
      {% assign totalW = 0 %}
      {% assign totalSS = 0 %}
      {% assign maxSS = 15 %} <!-- Max SS credit in a reporting period is 15 --> 
      {% assign maxW = 6 %}  <!-- Max W credit in a reporting period is 6 -->
      {% for rec in my_view.records %}
            {% assign totalC = totalC | plus: rec.c %} <!-- liquid implementation of addition -->
            {% if rec.e %}
              {% assign totalE = totalE | plus: rec.es %}
            {% endif %}
            {% if rec.w %}
              {% assign totalW = totalW | plus: rec.w %}
            {% endif %}
            {% if rec.ss %}
              {% assign totalSS = totalSS | plus: rec.ss %}
            {% endif %}
      {% endfor %}

      <!-- Format table with CSS from the portal page with totals and text -->
      <div class="ms-DetailsRow-fields fields-270" role="presentation" style="font-weight: bold">
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 150px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 111px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 120px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px; text-align: right">TOTALS: &nbsp;</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalC }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalE }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalW }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalSS }</div>
      </div>

      <!-- Subtract max c for ss and w for the reporting period -->
      {% if totalSSS > maxSSS %}
         {% assign sSAdjust = totalSS | minus: maxSS %}  <!-- Liquid implementation of subtraction -->
         {% assign totalC = totalC | minus: sSAdjust %}
      {% endif %}
      {% if totalW > maxW %}
         {% assign wAdjust = totalW | minus: maxW %}
         {% assign totalC = totalC | minus: wAdjust %}
      {% endif %}

      <!-- Visualize the adjusted totals -->
      <div class="ms-DetailsRow-fields fields-270" role="presentation" style="font-weight: bold">
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 150px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 111px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 120px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px;"></div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 320px; text-align: right">ADJUSTED TOTALS: &nbsp;</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalC }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalE }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalW }}</div>
         <div role="gridcell" class="ms-DetailsRow-cell isMultiline-272 ms-DetailsRow-cell cell-267 cellUnpadded-265" style="width: 115px;">{{ totalSS }}*</div>
      </div>
   {% endentityview %}
{% endentitylist %}

And here is a screenshot of the portal page list with the totals and adjusted totals displayed:

Redacted List

I hope this post helps someone else in this situation and thanks again for reading!

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.

Hey Hetz, we want totals for those columns on our list page...

The background information

The problem

The solution

Leave a comment