InfoPath Dev

Hello;

I'm new to InfoPath and so far I've figured out pretty much everything I need on my own, but now I'm stumped.

I've created a form that allows the user to either add new data or update existing data depenfing on what is entered in the Application Name Drop-Down List box. If the content is new then a blank form is used to enter all new data, but if the user selects an existing application the form will be pre-populated with existing data from the underlying SQL Server 2008 database.

I have several fields that are populated depending on data provided from related tables in my database. For example, I have a location field, the Main Dataset has a reference to the Locations table, but I display the actual location and not the reference of course cuz no one would know where 23 is when it's actually New York.

My problem is that in creating my rules for pre-populating the fields, in the application dropdown, how can I populate the value field based on the location from the locations table but rerefenced to by the SoftwareList table used in the Main Dataset? (Complicated hu?)

BTW - Using InfoPath 2007.

Thanks for any help.

Hi Ron,

Great to see you're diving head first into data-driven design. We do teach a 3-4 hour course on this that covers:

1. Auto-populating forms from default data
2. Creating config files to hold your default data
3. Iteratively copying data from config files into repeating groups
4. Replacing view-only data with calculated values (expression boxes)
5. Switching data population options through input parameters
6. Loading data from one form into another form of a different type

So, if you're interested in that training (comes with lots of good labs and presentations), please send email to our CourseManager -at- Qdabra *dot* com

Disclaimer: the best practice with SQL connections is to use a Web service as your proxy. The Web service handles the filtering and permissions, etc. I'm guessing you don't have one of those so you have to pull all of the data into your form on load. That can be a lot of data which is why a Web service is recommended. For browser forms, you need a Web service since there is an authentication issue going from client to browser and then SQL database (client to SQL database can work across the web if they have VPN, etc.). Bottom line: you should consider a Web service and we've got a great generic one called DBXL that will save you oodles of time and money, but that's neither here nor there, just a disclaimer comment ...

To do a complex population, you can add filters to your set value actions that pull data from a secondary data source based on conditions with comparisons against your main data source. That's pretty common. You can add multiple conditions here. That should be all that you need. But, if you need to add conditions where the comparison is with another secondary data source, you can do this too but sometimes InfoPath will get confused about the current() axis. Since you will set the value to pull from the Locations table, if the SoftwareList table is in a separate secondary data source, you may need to manually edit the xpath to get it to work. Not hard, but tricky. Hopefully, all of your data is in the same secondary data source and your are comparing to values in the main data source - that's the easy scenario.

Best of luck!

Thanks fo rthe help Patrick - I will most definitely heed your disclamer as a valid resource.

I did however figure it out - I was making it a little too complicated and found out that InfoPath has a lot more smarts built into it that I had anticipated. I guess I'm too much of a SQL-weenie :) - If I just use the reference to the secondary dataset (Location) it will actually translate the ID stored in the Main dataset to the actual location name stored in the secondary dataset - Way cool.

Anyway, thanks for taking the time to respond.