Sum the values from specific rows in a repeating table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JR - 08 Aug 2005 23:09 GMT

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?

Reply to this Message

JerryTh [MSFT] - 09 Aug 2005 00:01 GMT

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.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Sample code subject to http://www.microsoft.com/info/cpyright.htm
Do not send email to this alias. It is for newsgroup only

> Hi all;
>
[quoted text clipped - 20 lines]
>
> 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?

Reply to this Message

JR - 09 Aug 2005 15:22 GMT

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.

> One approach would be to use 1 large table for all of your data and
> then use
[quoted text clipped - 14 lines]
> from table1 and row 1 from table2, etc, but the Master / Detail would
> be much simpler.

Reply to this Message

JerryTh [MSFT] - 12 Aug 2005 00:17 GMT

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?

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Sample code subject to http://www.microsoft.com/info/cpyright.htm
Do not send email to this alias. It is for newsgroup only

> 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.
>
[quoted text clipped - 20 lines]
> > from table1 and row 1 from table2, etc, but the Master / Detail would
> > be much simpler.

Reply to this Message

JR - 12 Aug 2005 17:35 GMT

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

/my:Custom/my:ProjectCosting/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/my:ProjectCosting/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)

Reply to this Message

JR - 15 Aug 2005 15:15 GMT

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

> Sorry it took so long to get back to you, I had to get some help
> myself!
[quoted text clipped - 10 lines]
> This will add the 1st row of each table and the 2nd row, etc. Will
> that work for you?

Reply to this Message