Need a script for conditional Sum in Form calc | Community
Skip to main content
R
RAJI52CE
October 22, 2021
Solved

Need a script for conditional Sum in Form calc

  • October 22, 2021
  • 1 reply
  • 3200 views

I have a requirement as below.

The table has one header and two rows, one row is for repeating the Items in the transactions, and the other one is to display the total.

There is a field called Document Type, and when the value of this field is "Customer Invoice" and  "credit Memo," then the total should be summed up from the field Original Amount.

The total should calculate in the next row.

I have tried multiple scripts; unfortunately, nothing works.

 

Path for Document Type

  FormTradeReceivablesPayablesAccountStatementNotification.bdyPage1.frmTableBlock.tblOpenItemTable.rowOpenItems.coltxtTransaction

Original Amount

FormTradeReceivablesPayablesAccountStatementNotification.bdyPage1.frmTableBlock.tblOpenItemTable.rowOpenItems.coldecAmount

Total

FormTradeReceivablesPayablesAccountStatementNotification.bdyPage1.frmTableBlock.tblOpenItemTable.rowFooterRow.colCell5

 

Please refer to the below table for a better understanding.

Item NoProductDocument TypeOriginal Amount
10ACustomer Invoice100
20BPayment20
30CCredit Memo50
40DClearings-10
  Total150

 

I tries with form calc, but that doesn't works.

Can someone help me on this.

Thanks.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Mayank_Tiwari

Hi Mayank,

 

Yes, there are two totals in the table one for Original Amount and the other one for Remaining Amount.

I need to remove the direst binding as I need to do the same calculation for the other one as well.

 

Thanks.


I have fixed your form. Now it's calculating the Total Amount as per script.

 

 

 

Please find the fixed Form here: https://drive.google.com/file/d/1YoAItaL0DPhHE4emty-5PYOmzyK-OvK4/view?usp=sharing

1 reply

Mayank_Tiwari
Adobe Employee
Mayank_TiwariAdobe Employee
Adobe Employee
October 22, 2021

Please use instanceManager[0] to create the repeatable rows in the table, so that it will create an array object of the repeatable rows, and you can set/get the values from the column by iterating through the array object using a conditional loop.

 

[0]: https://helpx.adobe.com/pdf/aem-forms/6-3/scripting-reference.pdf

R
RAJI52CEAuthor
October 22, 2021

Hi Mayank,

 

I have used the below script, could you please let me know if I am making anything wrong here,

var oRows = xfa.resolveNode("tblOpenItemTable.rowFooterRow[*]");
iTotal = 0;
for(var i=0; i<oRows.length; i++)
{

if(tblOpenItemTable.rowOpenItems(i).colID_18448.rawValue == "Customer Invoice"){

iTotal =+ tblOpenItemTable.rowOpenItems(i).coldecAmount.rawValue;
}
}
this.rawValue = iTotal;

 

Thanks.

 

Mayank_Tiwari
Adobe Employee
Mayank_TiwariAdobe Employee
Adobe Employee
October 22, 2021

@raji52ce ,

var oRows = xfa.resolveNode("tblOpenItemTable.rowFooterRow[*]"); // This is wrong

var oRows = xfa.resolveNode("tblOpenItemTable.rowOpenItems[*]"); // correct

 

check this out. This is working for me.

My Form: https://drive.google.com/file/d/1GI2K5wZtjlht1Gksu9L_9bPhnjF2Xkze/view?usp=sharing

 

I have added the following JS on "Click" event of the "Calculate Total" button.

var total = 0;
var rows = this.resolveNodes("Table1.Row1[*]");
 
for (var i = 0; i < rows.length; i++) {


    if  (rows.item(i).Cell2.rawValue == "a" || rows.item(i).Cell2.rawValue == "b") {

       total = total + Number(rows.item(i).Cell3.rawValue);
    }
    }
    
    form1.Page1.Table1.FooterRow.TotalAmt.rawValue=""+total;


Output:

 

 

Terms & ConditionsAccessibility statement

Loading footer...