Data Connections

Re: Filtering with current from an SQL database

Matt Faus — Tue, 21 Nov 2006 17:57:45 GMT

Michael,

I really think 12 posts in a row is a little unnecessary. E-mail me your form and I will reply with the XPath you need.

Re: Filtering with current from an SQL database

Michael71 — Tue, 21 Nov 2006 15:42:13 GMT

Greg,

This what I want, but the Xpath still won't work. This works for one department.

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

Re: Filtering with current from an SQL database

Michael71 — Tue, 21 Nov 2006 15:21:03 GMT

Greg,

Is it something else I should be doing with rules, because the Xpath seems to be right. When I put it in and click verify it's not coming up with any errors. When I click the drop down department box, it doesn't change amounts. Could you give me more insight please. If anyone else can help, I would appreciate it.

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

Re: Filtering with current from an SQL database

Michael71 — Mon, 20 Nov 2006 19:52:02 GMT

Greg,

this is what I got so far, but it neither works

sum(xdXDocument:GetDOM("TOSSReceive")/dfs:myFields/dfs:dataFields/d:TOSS[@employeeDepartment = /descendant-or-self::node()/../../selectedemployeeDepartment]/@monthlyExpenses)

or

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

Re: Filtering with current from an SQL database

Michael71 — Mon, 20 Nov 2006 18:57:48 GMT

Greg,

I bought the Xpath 2.0 book, will that help. Because I still can't figure this out. I got a presentation tommorow on this. Can you please help.

Re: Filtering with current from an SQL database

Michael71 — Mon, 20 Nov 2006 18:46:22 GMT

Greg do you mean ReverseAxis or something

Re: Filtering with current from an SQL database

Greg Collins — Mon, 20 Nov 2006 18:05:22 GMT

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)

Re: Filtering with current from an SQL database

Michael71 — Mon, 20 Nov 2006 15:51:55 GMT

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

Re: Filtering with current from an SQL database

Michael71 — Mon, 20 Nov 2006 15:50:49 GMT

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)

Re: Filtering with current from an SQL database

Michael71 — Fri, 17 Nov 2006 19:23:55 GMT

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)

Re: Filtering with current from an SQL database

Greg Collins — Fri, 17 Nov 2006 17:33:18 GMT

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

Re: Filtering with current from an SQL database

Michael71 — Thu, 16 Nov 2006 18:47:55 GMT

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>

Re: Filtering with current from an SQL database

Michael71 — Thu, 16 Nov 2006 18:23:23 GMT

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.

Re: Filtering with current from an SQL database

Greg Collins — Thu, 16 Nov 2006 18:17:33 GMT

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

Re: Filtering with current from an SQL database

Michael71 — Thu, 16 Nov 2006 17:58:54 GMT

Greg,

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