ROLL UP VARIOUS METHODS OF CALCULATING

Jmanriquerios · ‎07-09-2024

In Dataverse we have rollup fields (consolidated) that according to the official documentation has as definition "Since consolidated columns persist in the database, they can be used to filter or sort just like normal columns. Any type of process or add-in uses the most recently calculated value in the column. System jobs calculate consolidated column values asynchronously. Administrators set when a job runs or pause it. By default, each column is updated every hour."

Formula, calculated, and rollup columns using code - Power Apps | Microsoft Learn

Now, when we talk about consolidated fields, there is something that the user does not like and that is the refresh since it does not occur immediately, "When a consolidated column is created or updated, a job of Consolidated Fields calculated in bulk is scheduled to run in 12 hours. The 12-hour delay is intended to perform this resource-intensive operation for a time that has less impact on users."

This factor means that consolidated fields can go from being attractive from easy to implement to not liking the business user, and perhaps a Citizen Developer without programming knowledge.

So what can we do to resolve this 12-hour warning, waiting for the functionality of using Power FX as a replacement for cumulative columns to be released? Implement calculated column capabilities inside formula columns | Microsoft Learn

The first thing we must know is that there may be different approaches and everything is associated a little with the experience and use of the development and low code tools that we may have, but also with the user experience. I share 4 scenarios to calculate it: 1) Low Code plugin, 2) Rollup + Power Automate, 3) Javascript 4) Rollup + JS

The scenario is that we have a table called offer and then offer products, in the offer we have created the price field that should accumulate the values of the offer product amount field

Plugin Low Code

The first thing we do is create a Currency field, called Price (it's not a cumulative field), it's a normal currency field.

Let's start with the official documentation: Get preview features and tools early with Dataverse accelerator - Power Apps | Microsoft Learn, in our case we use the automated plugins

This approach can be more oriented to a Citizen Developer who handles Power FX since he will be using an application to build the plugin, he just has to be clear about the concept of Post and Pre Operation as well as Create and Update.

The first thing we need to know is that I can't create a single plugin to create and update, I have to duplicate the plugin (in the case of the update we must be careful not to fall into a loop when it's against the same table, a little bit about this point). Example of Dataverse low-code plugins - Power Apps | Microsoft Learn).

Now, what we're going to do is we're going to build our Power FX formula:

With({OfferID:LookUp('Product, Deals','Product, Deal'=NewRecord.' Product Offer')},Patch(Offers,OfferID.Offer,{Price:Sum(Filter('Product Offers',Offer.Offer=OfferID.Offer.Offer),Amount)}))"

Use With to keep the same scope and get the GUID of the offer so that I can update the field that I create for amount.

I use the same code for Update and Create

At the end we will have a video for everyone to draw their conclusions

2. RollUp + Power Automate

We create a field called Price 2, this field if we use the cumulative field to create it

Then we create a Power Automate using the Dataverse trigger, using creation, update, and delete, and in the update we use the amount field from the offer product table

We added an action of the http connector "Invoke an HTTP request", in this we build the url through the ODATA to force the update

https://organización.crm4.dynamics.com/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'entityname(**GUIDHERE**)'}&@FieldName='fieldrollup'

Then simply every time the flow is activated, the field is forced to update

This approach can be built by a citizen developer

3. Javascript

In this scenario we have built a Price3 field type Currency and we simply use Javascript by making a call to Dataverse's WEB API to accumulate the values, this approach is more for a pro developer however a Citizen might

Let's look at the JS

function onLoad(context) {
  var formContext = context.getFormContext();
  var fields = ["sl_precio4rollupjs"];
  var entityid = formContext.data.entity.getId().replace("}", "").replace("{", "");

var subgridControl = formContext.getControl('Subgrid_new_1');
    
        if (subgridControl) {
            Add a listener for the subgrid's OnChange event
            subgridControl.addOnLoad(SumGrid);
}

function SumGrid(executionContext) {
        var formContext = executionContext.getFormContext();
        var recordId = formContext.data.On the other hand, the TgetId();
        Optional: remove keys and convert to lowercase
        recordId = recordId.replace("{", "").replace("}", "").toLowerCase();
        
        Replace 'TableBname' with the logical name of your table B
        Replaces 'relatedfield' with the name of the relationship field in table B pointing to table A
        Replace 'numericfield' with the name of the numeric©field you want to add
        const fetchXml = '<fetch aggregate="true">
<entity name="sl_productooferta">
<attribute name="sl_monto" alias="Monto" aggregate="sum" />
<filter type="and">
                                    <condition attribute="sl_oferta" operator="eq" value="${recordId}" />
</filter>
</entity>
                          </fetch>`;
    
        Make the Dataverse Web API call
       Xrm.WebApi.retrieveMultipleRecords("sl_productooferta", `?fetchXml=${encodeURIComponent(fetchXml)}`).then(
            function (result) {
                if (result.entities.length > 0) {
                    Shows the sum of the numeric©field
                    formContext.getAttribute('sl_precio3js').setValue(result.entities[0].Monto);
                    formContext.data.save();
                    formContext.data.refresh(save); 
                    
    
    
                } else {
                    console.log("No related records found.");
}
},
            function (error) {
                console.log("Error: ", error.message);
}
);
        
}}

In this case we must construct a fecth and use the accumulation to use this query in the JS; additionally we must add a function to the subgrid.

4. Rollup + JS

https://www.linkedin.com/posts/jonathan-manrique-rios_dataverse-microsoft-powerapps-activity-7216166...

We create the cumulative field called Price 4, and now in the form we load the JS into the onload of the form

Step 1: Detect the add event in the subgrid
function onSubgridLoad(executionContext) {
    var formContext = executionContext.getFormContext();
    var subgridControl = formContext.getControl("nombre_del_subgrid"); Replace with the real name of the subgrid
    if (subgridControl) {
        subgridControl.addOnLoad(forceRollupFieldUpdate.bind(null, formContext));
    }
}

Step 2: Force Rollup Field Refresh Function
function forceRollupFieldUpdate(formContext) {
    var fields = ["nombre_del_campo_rollup"]; Replace with the actual name of the rollup field
    var entityid = formContext.data.entity.getId().replace("}", "").replace("{", "");
    var entityName = formContext.data.entity.getEntityName();
    var clientURL = formContext.context.getClientUrl();

    Step 3: Build the URL of the request
    var requestUrl = `${clientURL}/api/data/v9.1/CalculateRollupField(Target=@target,FieldName='${fields}')?@target={'@odata.id':'${entityName}(${entityid})'}`;

    Step 4: Create and send the HTTP POST request
    var req = new XMLHttpRequest();
    req.open("POST", requestUrl, true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.onreadystatechange = function() {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                console.log("Rollup field successfully updated.");
            } else {
                console.log("Error updating rollup field: ", this.statusText);
            }
        }
    };
    req.send();
}

Now let's see the following video where we will see the loading time of the 4 scenarios and from there we can draw conclusions, but something I can say while the accumulated with Power FX is not available the best option could be Power Automate, because although I like the Plugin more it is still in Preview.

https://youtu.be/TUka4ufH28E

https://www.linkedin.com/posts/jonathan-manrique-rios_dataverse-microsoft-powerapps-activity-7216166...