I'm currently working on a specific type of expense form, where multiple expenses can be added for up to 8 people.
For each of the expense amounts, the person completing the form should first of all select the type of expense from a dropdown. We have 6 different expense type values
I'm trying to create a financial summary page, which first of all sums all of the expenses per person. (So person 1 total, person 2 total etc)
What I would also like to do is calculate each of the totals on a "per expense type" basis - so for example, add up all expense amounts where "meals" was selected as the expense type, and so on
I've tried to do this a few different ways but can't get it to work.
My first thought was to set a rule for each expense type on each of the expense type and expense amounts, where it would add the expense amount to the total - so for example, if meals is selected as expense type, then that expense amount should be added to the "meals total value" amount. However, this isn't dynamic - if they accidentally select the wrong type, then the value would be incorrectly added to the wrong total, and then not removed when the expense type is corrected. Also, I didn't like this one as it would have meant creating lots and lots of rules!
So that idea is scrapped.
I've now tried using the default value for the expense type total - below is an example of the formula I used for a different expense type as a test:
rep1Expense1Amount[rep1ExpenseType1 = "Baggage Allowance"] + rep1Expense2Amount[rep1ExpenseType2 = "Baggage Allowance"] + rep1Expense3Amount[rep1ExpenseType3 = "Baggage Allowance"] + rep1Expense4Amount[rep1ExpenseType4 = "Baggage Allowance"] + rep1Expense5Amount[rep1ExpenseType5 = "Baggage Allowance"]
However, when I test this, the value is showing as not a number.
I set the default value of all the expense amounts to zero, thinking this may help, but it hasn't made a difference
Does anyone have any suggestions for how I can get this to work, and for it to dynamically reflect the correct value?