InfoPath Dev

I'm a newbie to this site and infopath development alltogether.  I have been working on developing a customer equipment site audit form.  Currently I have a query that is run on customer account that populates mailing address, site contact names and equipment.  This information is coming from an SQL database where I set up the Customer info as the main data source and under that I have the customer contacts and customer equipment tables set up.  The customer equipment data is populated in a repeating table and returns EQ_Name, EQ_Type, EQ_SerialNumber, EQ_Warranty and EQ_Installed. 

What I want to set up is a repeating section where I have 15 check items that has to be completed for each piece of equipment.  The first field I have set up is the Serial Number field which is a drop downlist.  Since this data was already populated by my query under the list box entries I selected the option lookup values in the forms data source.  Which I can see and it works just fine.  The two things I can't figure out is how to set up the filter so the EQ_Name is autmatically prepopulated based on my serial number selection.  The second thing I can't figure out is when I have more than 1 piece of equipment at a site, say 4, how can i get the second repeating table Serial Number drop down to only show the serial numbers that are remaining.

 I've spent a lot of time researching this and attempting many solutions but I haven't gotten it to work yet.  It sounds like I have to utilize a secondary data source but my question would be why if I already have this data in the data source.

Hi JefJar and welcome to the wonderful world of InfoPath!

Thank you for the well-worded and articulate questions. Always a pleasure to read questions that make sense. Sounds like you have set up a significant camp at the base of InfoPath mountain. Here are some quick replies with thoughts.

1. There are many problems with out-of-box SQL forms. For example, you can't use this on the internet with SharePoint browser based forms due to impersonation limitations with double hops (user->browser->SQL). If you are using the rich client, this isn't an issue (since it's just (user->SQL). Another issue is that you can't do parameterized querying of data on load. The SQL query string doesn't take fields from your form. To filter your EQ_Name based on your EQ_SerialNumber you just need to set a filter. In your data source, double click on the field where you want to store EQ_SerialNumber and click the Fx to set the default value. Insert the field that you want to set and you should have the option to filter at that time. 
   
   Or, perhaps you are instead asking about how to query SQL based on the EQ_SerialNumber and not pull down all of the rowsets? To query only the SQL rowset corresponding to the serial number would require passing that serial number to the SQL query. You can't do this with the out-of-box approach. We created our Database Accelerator Web Service (DBXL) way back in 2004 to address this limitation and many others, so one option is to download the free trial of that and redo your form to be an XML schema - it will allow for much greater flexibility in the type of data you can store and how you query and submit to your database. But, assuming you don't want to install DBXL, the only option is to have it download all of the rows and then just filter on the one that matches the serial number. Note: this is only an issue for new forms since filled out forms should open with the correct EQ_Name, etc. (no need to query the complete DB for those).
    
2. To only show the serial numbers that are remaining just add a bunch of conditions to your filter to show only the ones that haven't appeared before. The xpath expression can be a little daunting but it should be doable.

Hope this helps a wee bit.