InfoPath Dev

The backend for this form is SQL 2008R2, the form is designed in InfoPath 2010 and will be used with the InfoPath form filler.

In my SQL2008R2 table I have store information (Store number, Store Name, City, State, Zip) and in my second table I have more information about the store (Store Number, Opening Date, Owner, Manager). Store Number is the primary key on both tables. This form that I need help with is for data entry into this second table.

Currently my main data connection is to the second table. I have a receive data connection setup for the first table. I can create a Dropdown List query field and setup the options to come from the receive data connection to give me a dropdown list of store numbers. I can use that dropdown list and run a query to populate the form.

 What I need to do is have other dropdown lists, like say Store Name, that I can use to populate that form.

I'm kind of lost on this one after trying a lot of things (adding tables to my main data connection had very strange results that I wasn't expecting at all). Hopefully I've explained this well enough that someone might have some ideas on how I can accomplish this. 

Are you able to fill your first dropdown list ? You mean you need to fill droplist from first table and based on selection in droplist you need to fill second table ? In this requirement, I advise you go for code solution by using VSTA and ado.net. If you aware of C#, Ado.net and Xpath you can get this done accurately as you need. DC in your requirement will never be a good option.

 I was afraid the answer would be in coding. That is outside my abilities. I was able to find a bit of a hack -- I duplicated the status query field a few times to cover each field that I need to use. On each dropdown I changed the external lookup to display what I wanted but kept the value as the store number. Then I used a rule to cause each change in the dropdown to re-query and thus update the form. Ideally some of these dropboxes would just be read-only text boxes but I can live with what I have.

SeanPT:

 What I need to do is have other dropdown lists, like say Store Name, that I can use to populate that form

This sounds like cascading drop-down lists easily created in Infopath OOTB (out-of-the-box, without coding).

See, for example,
Browser Based InfoPath 2010 - Creating Cascading Dropdown Fields with no Code 
https://www.nothingbutsharepoint.com/sites/eusp/Pages/Browser-Based-InfoPath-2010---Creating-Cascading-Dropdown-Fields-with-no-Code.aspx

Oops, "Inser/Edit Link" doesn't work for me

 

Server-side filtering of DB data is a common issue that comes up in InfoPath form development and we have developed some approaches to allow people to do this without writing their own code.

 qRules provides a command called ExecuteSqlQuery that allows you to modify the SQL query for a secondary data source and perform filtering by doing that.  This command requires a good deal of caution, as it can be an SQL injection risk if used improperly.

 A safer, but somewhat more involved approach is to pull the data through a web service, and this is precisely what the QueryDB service in DBXL provides.

 Those look like some interesting tools and I will definitely be checking those out but this isn't quite what I'm looking for.

 Basically I'm looking to do a JOIN on two tables and be able to submit to and query from those two tables in one single form.

You should be able to add another table when you create your form off your SQL DB - Select the main table first, then you can add the other table and relationship - as long as all the columns are allowable for submit (IP has some restrictions there) it shouldn't be a problem. You'd mentioned:

I'm kind of lost on this one after trying a lot of things (adding tables to my main data connection had very strange results that I wasn't expecting at all).

What were the strange results? From your original post, it sounds like you build the form off the child table and not the parent?