Expand my Community achievements bar.

SOLVED

Need a script for conditional Sum in Form calc

Avatar

Level 2

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.

1 Accepted Solution

Avatar

Correct answer by
Employee

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

 

Mayank_Tiwari_0-1635148717998.png

 

 

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

View solution in original post

12 Replies

Avatar

Employee

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

Avatar

Level 2

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.

Avatar

Level 2

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.

 

Avatar

Employee

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

 

Mayank_Tiwari_0-1634927696577.png

 

Avatar

Level 2

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

 

Avatar

Employee

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. 

Avatar

Level 2

@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 

Avatar

Employee

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. 

 

Avatar

Level 2

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.

Avatar

Correct answer by
Employee

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

 

Mayank_Tiwari_0-1635148717998.png

 

 

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

Avatar

Level 2

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.

 

 

 

 

Avatar

Employee

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