Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Need a script for conditional Sum in Form calc

RAJI52CE
Level 2
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
Mayank_Tiwari
Correct answer by
Employee
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
Mayank_Tiwari
Employee
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

RAJI52CE
Level 2
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.

RAJI52CE
Level 2
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.

 

Mayank_Tiwari
Employee
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

 

RAJI52CE
Level 2
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.

 

Mayank_Tiwari
Employee
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. 😉

RAJI52CE
Level 2
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 

Mayank_Tiwari
Employee
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. 

 

RAJI52CE
Level 2
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.

Mayank_Tiwari
Correct answer by
Employee
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

RAJI52CE
Level 2
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.

 

 

 

 

Mayank_Tiwari
Employee
Employee

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