Making a listbox's entries dependent on another listbox's selectio

[InfoHound]

David_W asked a question on 4/5 that is similar to my question. I read
Wong-a-Ton's reply, and in concept one would hope that solution would work.
In practice however, there's a challenge when you are working on a form that
is connected to a database. One obstacle is that controls are, by default,
named after a field in the database, not a name that I assign to the control.
So, for example, I have a drop down listbox called "OL_TOOL_ID", which
corresponds to a field name in my database. I'd like to have a second drop
down listbox (called ROLE_ID by default) display only the roles that
correspond to the tool selected in the first listbox. My table structure is
comprised of a Tool table, an associative table (contains Tool_ID and
Role_ID), and a Role table. I tried using a filter that specifies "show all
roles that are associated with a specific Tool_ID" To do this, I created a
filter that looks at the tool_id associated with the role_id and I compare
that to the tool_id associated with the first listbox. But as David_W points
out, all roles are listed in the second box, irregardless of the Tool that is
chosen in the first listbox. Remember, this is situation specific to
designing a form that is connected to a database and it seems that different
Infopath functionality is available than when you work with a stand-alone
form. Thank you for any advice.

 

[S.Y.M. Wong-A-Ton]

The solution I outlined works perfectly for drop-down list boxes connected to
secondary data sources bound to database tables. If I understand you
correctly, you have bound your Main data source to a database? This is a
slightly different situation, but you can apply the same principles. If your
drop-down list boxes are in the query section of your form, you need to add
the same tables that you currently have in your Main data source back into
InfoPath as secondary data sources, bind the drop-down list boxes to these
secondary data sources, and then filter the second drop-down based on the
value that the corresponding queryField (NOT dataField) has in the first
drop-down.

Names of fields are irrelevant in this scenario. The contents of fields
determine whether data compatability is an issue, and thus whether the
filtering will work.

 

[InfoHound]

Thanks for your response. I tried creating the secondary data connection, but
I only have datafields available, no query fields. When I create a filter on
my second listbox, there are only datafields available. My form is only to
be used as a vehicle for users to submit data (I use a few secondary data
connections to populate the dropdowns). When I created the form I selected
"design a form based on a datasource" ...it was the only way I could create a
form that submits data to a database. I have a Master-Detail set of
repeating sections where I have these two listboxes, but they're in the same
repeating section. Maybe it will help to know those details. Thanks for any
additional advice.

 

[InfoHound]

I found a solution to my question...

The scenario: Two drop down listboxes on a repeating section on a form that
will submit data to a database. The desired outcome: Populate the second box
based on a user selection in the first listbox.

The solution was to create a secondary data source that included three
related tables (I'll call them Parent, Associative, and Child). The
associative table resolves a many to many relationship between the Parent and
the Child. For a datasource, Listbox1 (the Parent data) uses the Parent's
group at the highest level of the secondary data source. Listbox2 uses the
Child's group at the lowest level of the secondary datasource hierarchy.

Listbox2 also uses a filter, and this filter correlates the Parent's ID from
the Associative table with the Parent's ID from the DataFields (not the
queryfields) in the Main datasource. I was unsuccessful until I referenced
the DataFields in the Main datasource.

There is a helpful microsoft article at:
http://office.microsoft.com/en-us/assistance/HA011177131033.aspx called
"Create a cascading list box"

Hope this helps somebody else.