Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Multi-Select List Box question(s)

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.

Cheers.

Ron...
0
RonCourteau
Asked:
RonCourteau
1 Solution
 
Clay FoxDirector of Business IntegrationCommented:
First advice would be to get rid of the multi-select check box.  These sound great but I have still never seen one implemented successfully. For the very issues you bring forth, they have a lot of gaps.

You should display your main data in a repeating table. And then add a button or standard dropdown control in the repeating list to signify what you want.  Then you submit the data back.

This is much more controlled and straightforward. You can approximate to the user most of what the multi-select list box does.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now