InfoPath Dev

I'm not entirely sure what I should be searching for, which is probably why this is becoming more and more frustrating! 

What I want to do is have my form look at a SharePoint list and populate customer information fields (address, city, etc.) based on two pieces of criteria.  Since the list is so huge I want the user to narrow the customer information results based on Project ID and then select from a Site Number, which is a unique number to the customer info.  Then I want that unique site number to be the one that populates the customer information fields. 

The form will be used to send a project description email to a customer and all of the information is already on SharePoint list.  Instead of just manually typing out the email or copy/pasting from SharePoint I figured why not just have a user select the project and site number from a list and then only have to enter in the email address of the customer and off it goes.  Seems pointless to have to rewrite things we already have on SharePoint.  Plus we don't want to be sending all fields on the SharePoint list anyways.

What's happening is two things.  I can only get the Project ID to appear in a dropdown box correctly, but the Site Number drop down box doesn't seem to be populating based on my filtered list.  I'm not even entirely sure what it's populating off of!  I think it's using all projects and not the specific one.  And if that is the case how do I filter/sort that list based on what is selected in my Project ID drop down box? 

Any sort of guidance here would be greatly appreciated!!  Even if anyone can tell me what I should be searching for.

Hi Drake,

Best practices:

• Filter your ShP list so you only pull down only the Project IDs that you want (pulling down everything could result in a delay when you open the form)
• Connect your Project ID dropdown to the secondary data source that is querying your ShP list and getting those filtered values
• Filter your Site Number dropdown to display only those that match your Project ID (use filtering inside the form (both should use current() and preceding-sibling to select the correct items to filter on) - search this forum for posts on those. Greg has a good blog on "selecting the right XPath axes"
• Setup a second query that queries your customer list based on Project ID and Site Number - you'll use those to filter down the list.
• Add a rule to your second dropdown (or to a button) to query those values
• Display the queried values and add a button to allow copying the item you want
• Button will have to use current() to select the correct item in the secondary data source
• Button will set values in main data source based on the item clicked (essentially copying the item over)
• To copy multiple entries in one full swoop, you can use qRules' Insert command with a count

The filtering is easy to do with qRules (free trial download available here). You can also search the web for FilterOWSSVR and write code to do it yourself, but qRules is fast and cheap.

Good luck!

• The Project ID field populates just fine and I have my project ID list filtered by the field "Status is equal to "To Scheduling".  Yay.
  

Whoo hoo!  I got the Site# to filter based on the Project ID!  For reference, they're called "Cascading Dropdowns".

Thanks to Patrick's information I searched for "infopath current()" and found these two articles:

http://blogs.msdn.com/b/infopath/archive/2004/09/13/228881.aspx
http://blogs.msdn.com/b/infopath/archive/2004/03/24/95529.aspx

However, the form runs much, much slower than it did before.

What I did was added a filter to the Site# selection drop down where the Project ID is equal to itself in the Main Data Source.  So the Project ID drop down is pulling data on its own and I just told my Site# drop down to look at the Main source for the Project ID since that field already did the work.  In other words "Dynamic option is equal to static option" or "Secondary Data source is equal to Static Selection in Main" as a filter.

Now for pulling the addressing I'm assuming I'd need to do something similar and say "Where [Main data source] Site # is equal to..."  It'd be nice if I could do that on an external connection (Where ProjectID= and Site#=) so I wouldn't need to apply it to all fields.

Edit: I suppose I could simply use an Optional Section for the last part.