Sum the values from specific rows in a repeating table

[JR]

Hi all;

We are trying to customize the Status Report template that comes with Infopath to replace an existing report that we created some time ago that was built using Excel.

The Excel form contains three cost tables; capital cost, operating cost and total cost. Each table has a column for expense type (eg. hardware, software, consultant fees, etc.) and a cost value. The users can add any number of rows that they wish, however; they have been instructed that the capital cost and operating cost tables both contain a row for each expense type used. If the expense was only a capital cost (for example), they would still create the row in the operating cost table and just report $0. Then, the total cost table totals up all the costs and reports a single number.

However, in addition to converting this to an Infopath form; they would like the totals table to break down the totals of each type before returning a grand total.

eg.

Capital Costs
hardware $100
software $50

Operating Costs
hardware $75
software $0

Total Costs
hardware $175
software $50
grand total $225


I'm have no problems coming up with the grand total, but I haven't been able to get the cost break downs, because the rows in each table are dynamic. How can I determine the value in each row, in each table; combine them; and then dynamically create a cost breakdown row in the totals table?

 

[JerryTh [MSFT]]

One approach would be to use 1 large table for all of your data and then use
Master/Detail tables to break it into more useable chunks.
1 Detail table (Columns 1 - 10) would be the Capital costs, another Detail
table (columns 11 - 20) would be Operating costs and then the totals per row
would be quite easy.

This would simplify the rule of "every row in this table must have a
corresponding row in the other tables".

There is a limit of 63 columns per table. So that would be the limit for
your Master table.
Would that work?

It will be possible to do the math where the Totals table adds row 1 from
table1 and row 1 from table2, etc, but the Master / Detail would be much
simpler.

 

[JR]

It would certainly work, but "those in charge" want to be able to quickly scan all of the totals without having to click on individual records. Also, all of the totals will be rolling up into another reporting method.

I'm sure it's a lot harder than your idea, but I need to go the route of adding each row from each table. If you could point me in the right direction with that, it would be greatly appreciated.

Thanks.

 

[JerryTh [MSFT]]

Sorry it took so long to get back to you, I had to get some help myself!

You will need to use a formula like this where field1 is in table 1 and
field4 is in table2. Put this in the Totals table in the field that
represents field1+field4.

/my:myFields/my:group1/my:group2[count(current()/../preceding-sibling::*) +
1]/my:field1 +
/my:myFields/my:group3/my:group4[count(current()/../preceding-sibling::*) +
1]/my:field4

This will add the 1st row of each table and the 2nd row, etc.
Will that work for you?

 

[JR]

I'm hoping it will - but I'm running into an error in the formula now. I've changed it to this:

/my:Custom/myrojectCosting/my:OperationalCosting/my:CostItems/my:CostItem[count(current()/../preceding-sibling::*) + 1]/my:ApprovedBudget + ....


to match my control structure, but am getting the following error message:

"/my:Custom/myrojectCosting/my:OperationalCosting/my:CostItems/my:CostItem/my:ApprovedBudget" does not point to a valid location path of a field or group.


This is my data source control structure

my:Custom (group)
-ProjectCosting (group)
--OperationalCosting (group)
---CostItems (group)
----CostItem (repeating group)
-----ApprovedBudget (field)

 

[JR]

Thanks again for all your help. Not sure why it wasn't working before, but it is now. Weird.