InfoPath Dev

I have 2 tables that need to be linked.

First table that needs to be linked is a Postcodes Table, with all the postcodes in the country.

The second table has a list of email addresses from all the local district offices.

The link between these 2 tables is a field called cost center.

What I am trying to do is pull out an email address of a district office after entering a Postcode into a text box then clicking on a button, linking the 2 tables using the cost center.

How can I do this?

(Thanks for your help in advance).

Hi Micky and welcome!

How many postcodes do you have in your country? This could be a long query. You probably don't want to pull all of these down into a dropdown since it will be too hard to search the list. My suggestion would be to have them enter a value and then query the second table (Local District Office E-mail addresses) and display that. That means the user will have to know the postcode, but that is probably a safe assumption.

To do this, unfortunately, in InfoPath there is no way to pass a parameter to a SQL query. You will either a) have to write some code, or b) use a Web service. We have a Web service that allows you to filter SQL queries based on values without requiring code.

Send me e-mail, if you'd like to try our Web service.

Regards,

Hi Micky,
I would suggest you write some code to retieve the Email address.
1. Take a DB Data connection from the InfoPath design mode
2. Write an OnAfterChangeEvent for the textbox field.
3. Get reference to the data connection created in step 1.
ADOAdapter2 adoAdapter = (ADOAdapter2)thisXDocument.DataAdapters["Data Connection Name"];
//retrieve value from the textbox and prepare you query here
adoAdapter.Command = "Write your Query here";
adoAdapter.Query();

4. You can get your email address this way
IXMLDOMDocument2 ixmlDOMDocument = (IXMLDOMDocument2)thisXDocument.GetDOM("Data Connection Name");
ixmlDOMDocument.setProperty("SelectionNamespaces", "Give required namespaces here");
ixmlDOMDocument.selectSingleNode("Xpath to the email address");