InfoPath Dev

I have an SQL server 2003 database with two tables:

Customer(ID, name)
Location(CustomerID, address, phone number)

I have inserted a drop down menu that auto populates with IDs from the database (I get the values from a secondary data source, which is the database itself). What I want to do, is to be able to select an ID from the drop down menu, and have all the fields autopopuate. So if I choose id number "50" from the drop down menu, it will stick the name of the customer into one textbox, then it looks for a location that matches that customer id and sticks the address and phone number into two other textboxes.

Is there any way to do that?

This can be done pretty easily with a database type form.

Create a new form and select database in the wizard.

Select the customer table and add the location table as a child.

This will build a form which can query the records as you desire.

Play with the default form so you understand the process.

Than add your secondary DC of the IDs and then use your dropdown to have a rule so that the ID chosen sets the query field and executes the query.

The problem with these types of forms are:

They must be built from the start as a DB type.

You can only query and submit the one table and its children

All children are created when a parent is inserted

no large file types

no browser form compatibility

A better solution is to use web services, which improves performance, gives you all of the flexibilty and removes the limitations I listed.  Check out www.qdabra.com for more info.

Hi there!  I have created a form using secondary data sources that pulls information into drop downs and auto-populates other fields.  When I set it up so users can access the form from a browser or SharePoint the information from the data source doesn't come through.  Is this simply not compatible, as above, or is there a Qdabra product that will help with this, or something?

 Thanking you in advance!
 

Rebecca
There are few quick thing i want to sumarize:
1) Just check your data connections once again and look that everything, i.e., the server name, table and field u selected for the form are appropriate.
2) Just goto properties of each controls of  yours and check that:
        i) you have selected "Lookup values from external data source"
        ii)select appropriate data source
        iii)Finally Entries, Display name & Values; if all these field are properly binded you will find that data is properly binded in drop-down lists.

Hi again.

I'm a little confused on your first point, on what I should be checking?

And on the second point, I didn't give enough information in my first question, sorry!  The drop downs and auto-fill works fine when the form is used in InfoPath, it's only when we try and set it up so users can fill the form in from a browser that we have issues.  The fields and drop downs are just empty, there's no errors or anything.

This is very confusing for me!  I have been through pages and pages in these forums, and have been going through numerous help files and tutorials trying to find out where I've gone wrong.  Any help is appreciated and thanks for taking the time to help me.  :)

Rebecca 

At what security level (trust) you are publishing your form in sharepoint enviroment?

Hey Rebecca.

If you are moving to a web based form your data connections need to be come UDC, Universal Data Conenctions. Do some research on these.

Under data connections there is a button to convert the existing ones, you just need a place to publish them in SharePoint.

Hi Clay
What i have learnt is that you can have two options to get your browser based form publish into sharepoint enviroment, first method is told by you here by making UDC and add those in connection libray. The second, to make them publish with full trust.

That is true but Full trust is a last resort and adds more trouble than it is worth except in rare coded solutions.