SumIf in an expanding table

[Flattysmith]

Hi, I'm creating an expense report that has an expanding table. You can download it using the link here.

https://www.dropbox.com/s/r7qnaegkkx7a57h/Expense%20Form.pdf

Everything I have so far works fine. You can add as many entries as you want and it provides a grand total.

I'm stuck with the next step. At the bottom, I need to list all the categories in the "type" drop down, and I need to do the excel equivalent of a SumIf. So if the "type" category for the "Actual Expense" Table is "Lodging", I need a field at the bottom that says Lodging with the subtotal for all entries marked as such. If there were three Lodging entries anywhere on the table for $10 each, I want a field at the bottom that would say  "Lodging: $30".

Since I've taught myself everything I know on all my forms till this point, I'm stuck. Any help would be appreciated!

[_Bruce_Robertson · Accepted solution]

Hi,

A sumif equivalent in FormCalc could look like;

var totalRows = LoanProposal.Table1._Item.count

var groupTotal = 0

var list = ref(LoanProposal.Table1.resolveNodes("Item.[ItemName == ""Lodging""]"))

for i=0 upto list.length - 1 step 1 do

    groupTotal = groupTotal + list.item(i).Total

endfor

Note the totalRows variable is not used but is required so the calculate event knows to fire when a row is added or removed.

This would have to be repeated for each category so you might want to have a calculate event code at a higher level (like the LoanProposal subform) with some JavaScript code like;

// Reset all fields

xfa.host.resetData([Mileage.somExpression, Lodging.somExpression].join(","));

var rows = Table1.resolveNodes("Item[*]");

for (var i = 0; i < Table1._Item.count ; i++)

{

    var row = rows.item(i);

    switch (row.ItemName.rawValue)

  {

        case "Mileage":

      Mileage.rawValue += row.Total.rawValue;

            break;

        case "Lodging":

      Lodging.rawValue += row.Total.rawValue;

            break;

  }

}

I've only done the first two categories but you can see the pattern

Regards

Bruce

[Flattysmith]

Bruce,

Thank you so much! I can't tell you how much I appreciate your help with this. This worked perfectly.

Warmest Regards,

Stephanie