Need a script for conditional Sum in Form calc

[RAJI52CE]

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 No	Product	Document Type	Original Amount
10	A	Customer Invoice	100
20	B	Payment	20
30	C	Credit Memo	50
40	D	Clearings	-10
		Total	150

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

Can someone help me on this.

Thanks.

[Mayank_Tiwari]

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

[RAJI52CE]

Hi Mayank,

Thanks for the update.

The table is already repeatable. All I need is a script to calculate the totals based on the document type.

I would rather say it as conditional Sum.

Thanks.

[RAJI52CE]

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]

@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:

[RAJI52CE]

@Mayank_Tiwari ,

That's a typo, but still, it doesn't work.

I have tried your script, as stated above, but that is not working on my form.

Please have a look at my form.

https://drive.google.com/file/d/1D_eJKfWe_elQ9N_lS0WQTF78qzCTPKjS/view?usp=sharing  

Let me know if am missing any.

[Mayank_Tiwari]

can you also share your input data xml which you are using in your form?

When I looked into your script, I found many syntax errors like  these:

if(rows.item(i).coltxtTransaction.rawvalue == "Customer Invoice"){

total = total + Number(rows.item(i).coldecAmount.rawvalue);

it should be rawValue, not rawvalue. 

[RAJI52CE]

@Mayank_Tiwari,

Yes, it is automatically changing to rawvalue even when I give rawValue.

I don't know why this is happening.

Here is the link for the XML file,

https://drive.google.com/file/d/1wdckbXpFK7LEXtxl4SgAABqfYtOO4MC_/view?usp=sharing 

[Mayank_Tiwari]

I can change it to rawValue in your form, without any issues.

Also, your total amount is showing up in cell6 of the footer row, then why you are running the script on cell5?

Besides, you have already set the value of cell6 through data binding as shown below. That's why it is not working on your form.

$.TradeReceivablesPayablesAccountStatement.StartEndBalancePerCurrency[*].StartTransactionCurrencyAmount

You should first test the script independently on a sample form without any other scripts, then keep on adding them one by one and check which one is the culprit. 

[RAJI52CE]

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.

[Mayank_Tiwari · Accepted solution]

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

[RAJI52CE]

Hi Mayank,

Thanks, appreciate your efforts.

It is working fine for me as well.

May I know what correction you did in the form.

Thanks.

[Mayank_Tiwari]

I did some changes in the script. You can compare your form's script with mine to see the differences. Thanks!