InfoPath Dev

Hello, I have a scenario where I will have two data connections to two different databases. One extracts employee file number (Extract A), and the other connection extracts the employee file number as well as their name (Extract B). I need to display the employee file number from Extract A as well the employee name from Extract B. I need to link the results of the two queries by the employee number so I can show the employee number and name in a text box (or repeating table). Is this possible in Infopath? Thanks for any assistance....Lou

Yes - it isn't really a join, but you could have a calculated value that looks up the Employee Name from data connection B by employee number. When you add a calculated value, you could select Employee Name from B, and click the filter button, and select the row where the employee id is equal to the one in data connection A. Feel free to attach a copy of your form under the Options tab (with the data connections added) in a reply if you need help.

Thanks. I will try this out, but before I do that and before I attach a copy of the form, I was wondering how the lookup of employee name in data connection B actually works with the employee number from data connection A. Is the employee number from data connection A actually passed to the SQL of data connection B and used in the WHERE clause? I ask because when I execute the SQL for data connection B it runs for over 7 minutes and brings back hundreds of thousands of records. Ideally, I would like to be able to pass the employee number(s) from data connection A into the SQL for data connection B so that the query runs faster and only brings back the data for what was passed. I cannot join the two tables in SQL server because they are in separate database instances, which is why I am asking the question. Thanks! Lou

I see - so the problem you will have here is that out of the box InfoPath is not going to support passing in form values to your SQL Where clause. You have to return all the data, unless you write code or use a third party tool like a web service or qRules in your form. Is there any chance this data could also be in a SharePoint list, via BDC or something? Then at least you could leverage list query fields in your secondary data connections.

Thank you. I'll have to do some research. You mention I can write code. What would the code be written in? If there are posts on this subject feel free to direct me to them. Thanks!...Lou