InfoPath Dev

I,ve been trying to get this Xpath right for 15 days now. I just want to add up monthly Expenses for the current year from a database for the department selected drop down box. Is this Xpath good enough or do I need to add rules as well. Because I've tried all kinds of Xpath and it won't work.

sum(xdXDocument:GetDOM("TOSSReceive")/dfs:myFields/dfs:dataFields/d:TOSS[@employeeDepartment = /../dfs:dataFields/d:TOSS/@employeeDepartment]/@monthlyExpenses)

This works for one Department

sum(xdXDocument:GetDOM("TOSSReceive")/dfs:myFields/dfs:dataFields/d:TOSS[@employeeDepartment = "5201 - Management IS-Hatfield"]/@monthlyExpenses)



here is my Shema

- <dfs:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:q="http://schemas.microsoft.com/office/infopath/2003/ado/queryFields" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-10-03T15:05:30" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003">
- <dfs:queryFields>
<q:TOSS ReportID="" expenseReportDate="" expenseStartDate="" expenseEndDate="" monthlyExpenses="" monthlyBudgeted="" monthlyBudgetVariance="" expenseExplain="" CapitalReportDate="" CapitalStartDate="" CapitalEndDate="" monthlyCapitalExpenses="" monthlyCapitalBudgeted="" monthlyCapitalVariance="" CapitalExplain="" employeeDepartment="" />
</dfs:queryFields>
- <dfs:dataFields>
<d:TOSS ReportID="" expenseReportDate="" expenseStartDate="" expenseEndDate="" monthlyExpenses="" monthlyBudgeted="" monthlyBudgetVariance="" expenseExplain="" CapitalReportDate="" CapitalStartDate="" CapitalEndDate="" monthlyCapitalExpenses="" monthlyCapitalBudgeted="" monthlyCapitalVariance="" CapitalExplain="" employeeDepartment="" />
</dfs:dataFields>
- <my:YTDTotals>
<my:ytdExpenses />
<my:ytdBudgeted />
<my:ytdVariance />
<my:ytdCapitalExpenses />
<my:ytdCapitalBudgeted />
<my:ytdCapitalVariance />
</my:YTDTotals>
- <my:YTDTotals>
<my:ytdExpenses />
<my:ytdBudgeted />
<my:ytdVariance />
<my:ytdCapitalExpenses />
<my:ytdCapitalBudgeted />
<my:ytdCapitalVariance />
</my:YTDTotals>
- <xd:SchemaInfo LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution">
- <xd:Namespaces>
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
</xd:Namespaces>
</xd:SchemaInfo>
- <xd:DataConnection Name="TOSSReceive">
- <dfs:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-10-03T15:05:30" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003">
- <dfs:dataFields>
<d:TOSS ReportID="" expenseReportDate="" expenseStartDate="" expenseEndDate="" monthlyExpenses="" monthlyBudgeted="" monthlyBudgetVariance="" expenseExplain="" CapitalReportDate="" CapitalStartDate="" CapitalEndDate="" monthlyCapitalExpenses="" monthlyCapitalBudgeted="" monthlyCapitalVariance="" CapitalExplain="" employeeDepartment="" />
</dfs:dataFields>
- <xd:SchemaInfo LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution">
- <xd:Namespaces>
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
<xd:Namespace LocalName="myFields" NamespaceURI="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" />
</xd:Namespaces>
</xd:SchemaInfo>
</dfs:myFields>
</xd:DataConnection>
</dfs:myFields>

You should be able to sum all the entries for one department with XPath, try something more like this:

sum(xdXDocument:GetDOM("TOSSReceive")/dfs:myFields/dfs:dataFields/d:TOSS[@employeeDepartment = xdXDocument:GetDOM()/my:Root/my:DepartmentDropDown]/@monthlyExpenses)

You need to compare the @employeeDepartment attribute to the value of the drop-down, but neither of your given XPaths do this.

You should be able to do all of this from the designer and the only bit you'll have to type in manually is the sum() function call. So in the Expression box, follow these steps:

1. Type "sum("
2. Click "Insert field or group"
3. Find the Group that field that you are trying to sum, highlight it
4. Click Filter Data in the lower left corner of the dialog box
5. Insert the condition so that "employeeDepartment" IS EQUAL TO
6. In the last drop-down box select "Select Field or Group" - browse to whatever node that the drop-down is bound to, select, click OK twice.
7. Click OK until you are back on the expression box formula field.
8. type ")" to close the sum function
9. click OK