InfoPath Dev

I'm real new to Infopath and am hacking my way through. I have figured out how to do what I want so far, with the following exception:
I'm creating a bill of materials... Description, quantity x price = Amount1. Amount 1 is calculated in an Expression Box. The bill of materials is designed in a simple Repeating table. As you add rows to the table, the quanty and price is keyed in and the Amounts2,3 4, each, per row, is calculated, but I can't figure out how to add up all the Amounts into another overall total... sort of a subtotal, then I need to calculate taxes and add that to the subtotal, in order to get a Final total. Please help... I'm missing a critical piece in order to calculate the Final total. It appears I can't add, or calculate Expression Boxes, in this case to arrive at a subtotal and then a Final total.

Hi Jerry and welcome to the forum!

Expression boxes are used only for displaying data, there is no way that you can get at the calculated value within an expression box and use it elsewhere. What you want to do is add a new column to your repeating table to calculate each line item total, and then you can get at these values and create a subtotal. Don't worry, you will only be changing the data source and can make it look exactly the same as you have it now.

Try this steps:
1. From the data source task pane right click the repeating group, choose Add, and add a new field named "LineTotal". Press OK.
2. Double-click the expression box that you have already placed, copy the formula.
3. From the data source task pane, double-click the node you added in step 1. Paste the formula into the default value field.
4. Right-click the expression box, select Change Binding, and bind the expression box to the node you created in step 1.
5. Add an expression box or another node to sum all of the line item totals and for the default value use a formula like this:

sum( "/my:myFields/my:Materials/my:Material/my:LineTotal" );

Hope this helps, let us know if you have any questions!

Matt.... thanks so ever much for your assistance. Following your instructions I was able to calculate 'LineTotals' in the repeating table. Now.... if you refer to the diagram below, I need to calculate a Cumulative Total, which consists of all the LineTotals, then input a Freight amount, and add that to the Cumulative Total amt, to arrive at the SubTotal, then calculate a Tax amount and add that to the SubTotal to arrive at the Grand Total. Would you be so kind as to direct me how to do this? I attempted to include the total boxes as part of the repeating table, but that doesn't work because when you select next line item, it repeats the Grand Total box.
(Repeating table)
Bill Of Materials
Qty field * Unit Price field = LineTotal
Line 1 values 2 * $3 = $6
Line 2 values 9 * $10 = $90

Cumulative Total $96
Freight $25
SubTotal $121
Tax Rate: 6.50% Tax Amt $8
Grand Total $129
(End of repeating table)

Hi Jerry,

Try this:

Double-click on the repeating table in the view. Switch to the Display tab, check the box labeled "Include footer" and then click OK. This will give you a good place to place the totals nodes.

The best place to add the totals nodes are inside the top-level group of the repeating table. For instance when you insert a repeating table control InfoPath automatically generates a schema. You should place your nodes in the group1 group, see this diagram for reference:

myfields
--group1
----group2 (repeating)
------field1
------field2
------field3
----CumaltiveTotal
----Freight
----TaxRate

Now, you can use formulas in Cumlative field to summ all lineItemTotals. Then use formulas for all the other fields ( these are all expression boxes):

SubTotal = CumaltiveTotal + Freight
TaxAmt = (CumalativeTotal + Freight) * TaxRate
GrandTotal = (CumaltiveTotal + Freight) + ((CumalativeTotal + Freight) * TaxRate)

You could store the results of these calculations in nodes instead of just displaying them in expression boxes, but it's not necessary. It will depend on your requirements. I hope this helps, and sorrry if I seem a little rushed!

Matt.... you've been a life saver!!!! Thanks so much for your quick responses to my questions. Part of my initial problem was that I didn't have the latest Infopath updates.... AND 'thought' I HAD to use Expression boxes to perform calculations. That thought sure got me going in the wrong direction. I took your suggestions.... had to tweak and hack with them somewhat, but I was able to do what I wanted.
GREAT ASSISTANCE FROM YOU.... THANKS AGAIN.

Dear Matt.

More then a year since you give Jerry your answer and i have the same problem. Somehow your stages does not work for me. when i put the formula at the expression box in the table its works fine. I copy the formula to the "LineTotal" node into the defult value box - up to here everything is OK. when i bind the expression box to "Linetotal" the Xpath says @my:LineTotal and it show the text of the formula and not the value of the formula.

Have not got a clue where i work wrong.

Pleeeease...

Thank you very much

Ran

I wouldn't do it the way Matt told you, i have nothing against what he told you. You don't need to add extra data fields to your datasource. Use eval to evaluate an expression (but you don't pass the expression box which are your line item totals to eval). Tell eval your fields used in the expression come from a certain datasource and it should work.

sum(eval(your repeating datasource group goes here, "tns:MonTime + tns:TueTime + tns:WedTime + tns:ThuTime + tns:FriTime + tns:SatTime + tns:SunTime"))

I wouldn't type the text, use the "insert field or group button" to add the repeating datasource group and to add your fields. MonTime are fields from my datasource these should refer to the fields from your datasource. This formula i have in the footer of my repeating table.

Matt

Hope you can help me.

XPath Formula:

sum(xdMath:Eval(xdXDocument:GetDOM("Full_Download")/dfs:myFields/dfs:dataFields/dfs:Full_Download/@SP-ProcessAreas[. = xdXDocument:get-DOM()/my:myFields/my:field2 and ../@_.Project_Status = xdXDocument:get-DOM()/my:myFields/my:field3 and ../@SP-SubProcess = current()/../my:field9], "@SP-FOL1"))

Field2 is Infopath field Process Area 

Field3 is Infopath field Project Status

Field9 is Infopath field Sub Process

@SP-ProcessAreas is Secondary Data sources (String)

@_.Project_Status is Secondary Data Sources (String)

@SP-SubProcess is Secoundary Data Sources (String)

@SP-FOL1 is Secoundary Data Sources (Decimal)

I am trying do similar to Excel Sumproduct Formual:

Sumproduct((SP-ProcessArea=Field2)*(_.ProjectStatus=Field3)*(SP-SubProcess=Field9)*SP-FOL1)

When I validated above XPath Expersion it display no error in formula. However when I run the form it display an error as Nan.

Your help in this much appreciated.

I got the solution. Replaced "@SP-FOL1" with "../@SP-FOL1"