InfoPath Dev

Filtering with the current() function from a SQL database into a repeating table. Please help


I'm trying to Use a drop down box to select a department, then I want a repeating table below to select just that department monthly expenses and just sum it up for that department which was select from the drop down box above. The expenses that are being summed up are coming from a SQL database. I've tried this with rules, but I think I'm a little off. If someone can assist me, I will greatly appreciate it. If anyone knows anyway else I can do this, let me know.

Here is my Schema

.myFields
....queryFields
....dataFields
.......d:TOSS
...........employeeDepartment (drop down box with 10 departments)
.......my:ytdTotals (repeating table that sums up the monthlyexpense)
.............ytdExpenses
.............ytdBudgeted
.............ytdVariance


I found that I may have to use current(), but I can't get it to work exactly right. And don't know where to insert the Xpath. Here are the 2 Xpath's that I got so far to put in Xpath.


/myFields/datafields/d:TOSS/employeeDepartment[../@monthlyExpenses = current()/SelectedemployeeDepartment]

/myFields/datafields/d:TOSS[@monthlyExpenses = current()/SelectedemployeeDepartment]/employeeDepartment

First question: Will monthlyExpeses actually equal SelectedemployeeDepartment, or should you be comparing employeeDepartment against SelectedemployeeDepartment? I'm guessin the latter.

Sounds like you need something more like:

/myFields/datafields/d:TOSS[employeeDepartment = current()/SelectedemployeeDepartment]/@monthlyExpenses

Of course, I probably have some things wrong in that XPath because of what little I've seen of your schema. I'm not even sure where SelectedemployeeDeparment is coming from --if that is not even part of the repeating table then you shouldn't need to be using current() for this. Probably something more like:


/myFields/datafields/d:TOSS[employeeDepartment = /XPath_to/SelectedemployeeDepartment]/@monthlyExpenses

Greg,



I know this is a little entry level, but where do I place this Xpath at? In the Drop down list for the the department, in the repeating table filter? Thanks

Greg the part of the part of the Xpath below doesn't point to a Valid location. Any ideas.


/XPath_to/SelectedemployeeDepartment

Sorry... "XPath_to" is meant to be replaced by the xpath leading up to the SelectedemployeeDepartment node, not to be typed in as is. This type of fill-in-the-blank response is common, and once you know about it, it's easy to spot it and do the right thing.

The xpath should probably go in whatever node you are using to sum the values. Most likely as a default value formula.

Greg,

Where do I put the sum(monthlyExpenses) at to sum the monthlyExpenses. If I put that Xpath in there.

Okay, obviously I'm confused. . . . :o)

The XPath needs to be placed in whatever field you are expecting to show the results of that XPath. (Sorry so vague).

Okay Greg,
I almost have it. Just a little help. This is the Xpath I have now..

sum(/../../dfs:dataFields/d:TOSS/employeeDepartment[../employeeDepartment = ../SelectedemployeeDepartment]/@monthlyExpenses)


it's just displays $0.00 as I change department with the drop down list box...it should add up all the monthly monthly expenses for that department.

Greg here is my schema. Thanks. Any help will be greatly appreciated.

- <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>

Modified:

sum(../../dfs:dataFields/d:TOSS[@employeeDepartment = ../SelectedemployeeDepartment]/@monthlyExpenses)

Note that the beginning slash forces the XPath to start at the root node, therefore /../.. means to go two levels above the root node, which is invalid.

Also note that employeeDepartment is an attribute, not an element.

Finally, I don't find SelectedemployeeDepartment anywhere in the schema you defined above, and must therefore assume that it is present in a separate data connection. Therefore you will need to either use current() to get back to it (if you started your query from in the context of the data connection that contains SelectedemployeeDepartment, or give an absolute XPath (which you should be able to consruct from within InfoPath and will likely include a call to get the other DOM).

I modified my Xpath to look like this....I don't know why I put Selectedemployee in there. It still doesn't sum up for every specific department. Shows $0.00 in the ytdExpenses text box.



sum(../../dfs:dataFields/d:TOSS[@employeeDepartment = ../../dfs:dataFields/d:TOSS/@monthlyExpenses]/@monthlyExpenses)

Greg this is the Xpath I put in the ytdExp0enses text Box

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

Its coming from my secondary data source, because that is where I want to add up the totals depending on department.

The XPath is still not right... your filter on d:TOSS is comparing the first instance of @employeeDepartment (i.e. the field value on the first row of the table) to itself.

The beginning of the filter (or the parts in the square brackets '[ ]' is an absolute path to the first row of d:TOSS. The second part of the filter steps back up the tree, and then comes back down, once again selecting the first row of d:TOSS.

Following is just pseudo-code for an XPath, so you'll need to find the right fields to fill in with, but it should point you in the right direction.

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