Sub grid totals

[Acropolis9064]

Hi,

I have two custom entities in a model driven app, purchase orders and purchase order line items, they follow a similar patterns to quotes and quote line items relationship.

On the main purchase order form there is a subgrid in which the products for that PO that entered. What I want to happen is when a new item is added or removed from the subgrid (basically when it's refreshed) to calculate the total of the columns oms_totalexvat and oms_tax and set the fields in the po table oms_totaltax and oms_totallineitems.

If the subgrid had events handler this would be easier to achieve, but I'm struggling to know where to start with this.

It must be possible as order, quotes, invoices all do it, can anyone shed any light with this?

[MudassarSZ365]

Hi @Acropolis9064 ,

JavaScript Web Resource: Create a new JavaScript web resource that will contain your functions to calculate totals.

Register Event Handlers: In your JavaScript, write a function to attach an event handler to the subgrid’s OnLoad event. This event can be triggered when the subgrid is refreshed.

Calculate Totals: In the event handler, use the subgrid's data to calculate the totals for oms_totalexvat and oms_tax.

Update Parent Form: Once the totals are calculated, use the Xrm.Page API (for older versions) or formContext (for the Unified Interface) to set the values of oms_totaltax and oms_totallineitems on the main form.

Attach to Form: In the main form's properties, add your JavaScript web resource and set the function to be called on the form's OnLoad event and when the subgrid is loaded.

Example JavaScript snippet for step 2 and 3:

function onSubgridLoad(executionContext) {
  var formContext = executionContext.getFormContext();
  var subgridControl = formContext.getControl("your_subgrid_name");
  if (subgridControl) {
    var allRows = subgridControl.getGrid().getRows();
    var totalExVat = 0;
    var totalTax = 0;

    allRows.forEach(function(row) {
      var rowData = row.getData().getEntity().getAttributes().getAll();
      totalExVat += rowData.getByName("oms_totalexvat").getValue();
      totalTax += rowData.getByName("oms_tax").getValue();
    });

    formContext.getAttribute("oms_totaltax").setValue(totalTax);
    formContext.getAttribute("oms_totallineitems").setValue(totalExVat);
  }
}

Publish: After setting up your JavaScript functions and attaching them to the form, publish all customizations.

If this works for you, please mark it as the "Accepted Solution."

[Acropolis9064]

Hi,

thanks for the reply. 

what I’ve been struggling with is that there are no event handlers for a sub grid on which to attach the script to. 

however, that said I don’t it actually needs to trigger from the sub grid. Whenever anything is added to the sub grid or edited on the sub grid, then the main form is reloaded afterwards, so something that triggers on the on load event of the main form will suffice. 

the snipet you posted would appear to do that with an additional check to make sure there are rows of data first should do it hopefully?