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

Need a script for conditional Sum in Form calc

  • October 22, 2021
  • 1 reply
  • 3226 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
Level 2
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.

 

R
RAJI52CEAuthor
Level 2
October 25, 2021

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. 

 


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.

Terms & ConditionsAccessibility statement

Loading footer...