InfoPath Dev

Extreme newbie. I have search for about 4 hours for an answer to this and I am very close but I can't seem to get this to work.

I have 3 SQL tables.
tblAdAllowanceBudget
tblAdAllowanceActualHeader
tblAdallowanceActualDetail

I have my main connection set to tblAdAllowanceActualHeader joined to tblAdAllowanceActualDetail on AdCode

I have a secondary connection to tblAdAllowanceBudget

tblAdAllowanceActualHeader contains Div,Dealer, AdCode
tblAdAllowanceActualDetail contains AdCode,ProdDesc,ProdCode,InvAmt

tblAdAllowanceBudget contains Div,Dealer,ProdCode,Year, Budget

I have the form setup with the info from the Header file entered in a repeating section with the ability to insert turned off (this is the way it was brought in when the connection was set up). I have the Detail file entered in a repeating table. The ProdDesc DDL is filter based off of the Div entered. The ProdCode is then filed in with a Rule based off of the ProdDesc selected. The section also includes InvAmt. I would like to have the budget amt auto fill in based off of the Div, Dealer and Code that has been filled in already. I am at a compete loss as to how to do this.

I set up a rule to show the concat() of Div, Dealer, Year, and Code. and this works, but I can not filter the secondary data source based on this info.

here is the xpath I used for the concat():
concat(../../@Div, "2006", ../../@Dealer, ../@ProdCode)

and here is the code I am trying to use to get the budget to pop up in a message box just to see if it will work.

xdXDocument:GetDOM("AdAllowanceBudget")/dfs:myFields/dfs:dataFields/d:tblAdAllowanceBudget/@CodeAmt[../@Dealer = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:tblADAllowanceActualHeader/@Dealer and ../@Code = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:tblADAllowanceActualHeader/d:tblADAllowanceActualDetail/@ProdCode and ../@Division = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:tblADAllowanceActualHeader/@Div and ../@BudgetYear = "2006"]

I get a blank message box and I am sure there is info matching the filter in the budget table.

I am at my wits end here. I know this should be possible, but can't seem to get there.

Anyone have any clue on how to do this? There has to be someone smart enough to figure this out.

Hello lotusvball! Where are you putting this button to pop up the message box? My guess is that you will probably have to use the current() function to get at the appropriate values that you are trying to perform the lookup with, but I would like to know a little bit more about the schema of the form and what exactly you are trying to do.

I am not using a button but instead have a rule setup after the ProductDescription is selected. This is within a repeating table.

Ok, I think you need to do this:

Replace each instance of "xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:tblADAllowanceActualHeader/d:tblADAllowanceActualDetail" with "current()" and each instance of "xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:tblADAllowanceActualHeader" with "current()/.."

I hope this helps!

Ok, I tried to simplify this and use just the secondary data source and the tblADAllowanceActualHeader table only.

In the repeating section of the Header table I have a dropdown list for Div. I have two rules setup. The first rule to show me the value of Div currently using:

current()/../@Div

This shows me the current value of Div

My second rule is to give me the division bugdet where Division = Div

xdXDocument:GetDOM("vADAllowanceBudget")/dfs:myFields/dfs:dataFields/d:vADAllowanceBudget/@DivBudget[../@Division = current()/../@Div]

This does not work. I get an empty box. vADAllowanceBudget is the secodary data source and I know the Division has a budget number. What am I missing?