InfoPath Dev

I'm kind of newbie on this InfoPath stuff. I have a secondary data connection (Departments) on a form based on a SQL data source. I have a button that run a query on the database based on the employee_num. I have a table (Personal_Info) with all the employees data on it. The Department field on this table are integers. I have another table (Department) on which the name of the department is related to an integer.

What I want to do is to run a query based on the employee_num and have an expression box that display the department of that current employee using the relation between the 2 tables Personal_Info and Department. This is the Xpath that I use on the expression box

 xdXDocument:GetDOM("Departments")/dfs:myFields/dfs:dataFields/d:Departments/@DepartmentName[../@DepartmentId = ../d:PersonalInfo/@Department[. = current()]]

The problem is that the expression box came out empty.

Can someone help me with this?

current() is always so much fun to debug!

One thing that looks suspicious to me (not knowing your whole set of DOMs, etc):

[../@DepartmentId = ../d:PersonalInfo/@Department

You are comparing the @DepartmentId on the same row as d:PersonlInfo

Not that that's necessarily wrong.... but it stands out as a possible issue.

I'm trying to compare the DepartmentId (from Departments) with the Department field (from PersonalInfo). That is because I run the query and want to show the department of the employee that resulted from the search.That also why I used the current() function. 

Hi

I think what you are trying to do is link the Employee ID from PersonalInfo to the Department ID then use the department to link to the DepartmentID to tell the expression box what department the employee is in.  If it were me so I could debug accurately I would have field on the form called DEP (in test, hidden in production) that displayed the @Department field on the form from PersonalInfo.

Then set the default value of the expression box = DepartmentName where DepartmentID (Departments) is equal to DEP (On Form), no need for the current function unless this is all part of a repeating table.  I think the issue you were having was where you were trying to do a three step link in only two steps.

Going from Employee ID to Department to DepartmentID to DepartmentName

By adding in the extra field you break it down into two two-step processes which InfoPath should have no problem with.

I hope this helps

Hi

This is a subsequent post to the one at the bottom of the form but now I ahve worked it through your statement doesn't reference the fields on the form in any way which is why it came up blank.  See my answer at the bottom of the posts.

Cheers

Hi thanks for your comments:

After some trials with your idea I finally found that I was referencing the PersonalInfo / Department field from the Secondary Source. Now that I tried the Main it worked!

This is what I have on my Expression Box now:

xdXDocument:GetDOM("Departments")/dfs:myFields/dfs:dataFields/d:Departments/@DepartmentName[../@DepartmentId = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:PersonalInfo/@Department]

Thanks a lot!

Galford, glad you got it working!!

Adam--thanks so much for interjecting and helping out here! Great job!