Filtering Cascading Dropdowns Filters Too Much Out

Shanty — Sun, 07 Jun 2009 16:36:26 GMT

I am trying to get 3 cascading dropdowns to display unique values filtered on a previous dropdown. Although I can get it to EITHER display a complete list of unique values OR display filtered (but duplicate) values based on a selection in a previous dropdown, when I combine the 2 filters, the values ARE unique and ARE explicit to the filter BUT there are values now missing. The dropdowns are populated from a Secondary Data Source (a SQL query).

I have 3 tables that are used in the SQL query. Below I show the tables and the pertinent fields used in the query from each table with the KEY of the table bolded and the JOIN field italicized (where there is one). Because each of the first three tables listed have many to many relationships (i.e. - within each "Location" can be many "Company" and each "Company" can be located at many "Location"), the 4th table was created to mitigate issues and contains only key and join fields. As you can see, the Join fields of the fourth table are similarly named to that of the KEY fields in the main tables.

TABLES

Location - LocID, Location
Company - CompanyID, CoyName
Department - DeptID, DeptName
LocationCompanyDept - LCDID, Loc_ID, Company_ID, Dept_ID

QUERY

Combines all tables above and pulls all data for the fields listed in the above tables.

FORM DROPDOWNS

Loc - displays the name of the Location. The XPath statement used here effectively displays unique values of the various locations: not(.=../preceding::*/@Loc)
CompanyName - not(.=../preceding-sibling::*/@CompanyName) AND Loc = Location (filters on the Location of the form being the same as the Location in the query). This is the one where, after combining the filters, the values displayed are incomplete.
Dept - this dropdown will filter on both Loc and CompanyName values as it too has a many-to-many relationship with the other two dropdowns. I have not completed this one as I am pretty sure that once I get the CompanyName dropdown figured out, this one will follow suit.

I have tried various methods including:

adjusting the SQL query to place the filter inside of it. Not a very efficient or subsequently useful idea.
modifying the XPath - but my understanding of XPath is limited so this is mostly guess work
spliting the query into multiple ones - but this slows down the form too much with the additional data connections.

Any help I can get on this would be greatly appreciated as this will be the template for several forms used by our company and completion of those is pending this one.

Thanks all,

- Shanty

Controls

Filtering Cascading Dropdowns Filters Too Much Out