Help: Form with Sum of Rows (Conditional statments)

[jlhawkes]

I am hoping for some help from someone more advanced than I am.

I have a form with a table that asks for imput for travel expenses.  Rows include for receipt number (auto-fills with an interger number), Date, Vendor Name, Expense Type (drop down prefilled with a variable), Payment Type (drop down prefilled with a variable), Expense Cost (user imput- data type interger), Currency (drop down prefilled with a variable), Conversion Rate, Expense Cost in USD (calculated total from Expense Cost * Currency).

The table has one row of data called "Item" and there is a button to add a row to the table.

The table auto SUMs the Expense Cost in USD field so there is a total on the table.

On a previous page (summary page), there is another table.  This table summarizes the expense information based on Expense Type and Payment Type.

Currently I have the following formula to conditionally sum the expenses from the table below based on the response for Expense Type and Payment Type.  This example is supposed to sum all expenses that are "51807 - Membership Dues/Fees" and whose payment type does NOT equal "BMC Credit Card".

form1.#subform[1].Table1.Row1.Expense_51087::calculate - (JavaScript, client)
var sum = 0;
var aLines = form1.resolveNode("#subform[3].General_Expense.Item[*]");
for(var i=0;i<aLines.length;i++)
{
   if(aLines.item[i].expense_type.rawValue == "51807 - Membership Dues/Fees"
      && aLines.item[i].payment_type.rawValue != "BMC Credit Card")
       sum += aLines.item[i].total.rawValue;
}

sum;

However, it does not sum the values.  It does not do anything.

Any suggestions?  I will change to formcalc is someone has the solution.  I have a functioning formcalc formula, but it didn't sum for multiple lines, only the first entry.

Here is the draft of the form:

http://www.brynmawr.edu/provost/documents/Travel_Form.pdf

[Former Community Member · Accepted solution]

Here is a modified version of your form. .... with a few suggestions:

1. You shoudl always name your subforms. I changed your untitled subforms to page1, Page2 ..... this makes things easier to follow when error messages are generated.

2. I did the 1st calculation in the table on page 2. By putting it on the Calc event a dependancy is set up between this field and any field named in the calc. So when any one of those fields changes the script is executed.

3. I use a For loop to loop through each row in the table on Page 3 and if the coonditions are met we update the value on Page2.

The syntax of getting at each item in the row is shown in the calc (using an xfa.resolveNode("string") expression is the only way to get at these values).

Hope that helps

BTW - Nice looking form

Paul