Multi-Select List Box question(s)
Posted on 2011-10-24
Hello – This control is driving me crazy, I think I have two problems that are compounding my frustrations, one is properly extracting the data from SQL Server and the other is presenting it on the form. So any help is extremely appreciated.
Here’s the background - I have a form with a Multi-Select List Box on it. The form can be used to either enter new data or update existing data. Data is stored in a SQL Server database.
On the database side, I have two secondary data sources that are used to populate the fields in the form if the user chooses to update the record. The main table has a one-to-many relationship with another table.
When I query the database, I return the main table in one dataset and I return the second table in another dataset – If I do a JOIN in my query, I will end up with several records for each entry that has multiple entries in table2.
I have a third secondary data source which I use to create the Multi-Select List Box. (All the options)
Where’s what I want to do.
When a user decides to update an existing record, I need to populate the Multi-Select List Box with all available values (Third data source) but select (Add check marks) to the items that currently exist in the database. Then if the user adds or removes a check mark, I need to update the database.
1. What’s the best way to get the data out of SQL Server to do what I need to do? Single query with a JOIN or two queries and let InfoPath figure out the one-to-many relationship? I need to be able to retrieve only one data row for the main data table.
2. How can I set the values of the selected items in the list box?
3. How can I know what’s been changed in the list box?
4. Is there a way to update the form without coding it? Coding is no issue, just asking. I tend to escape the ambiguities of InfoPath by coding like I would in the gold old days of creating Windows Forms.
Again, thanks a million for helping out.