I’m generating a pretty simple report of names and address of contacts related to companies via a specific “role” (this role is important…)
The way the database is set up, the contacts are in one table, the address are in a second table, the companies in a third table, etc – and they are all related with ID keys - all pretty standard stuff.
The “role” is a subset of data within a user defined (look-up) table that’s set up like this:
Look Up Key--------------Category---------------Value-----00001-------------------Role------------------Advisor
-----00002-------------------Role------------------Accountant
-----00003-------------------Classification-----------Full
To use round numbers for an example, let’s say there are 100 records in this table – only 10 are category type “role”. (In reality, there are 1,000s of records and about 200 "roles".)
What I need to do is make a user defined parameter field (like a drop down menu) to select what type of role the query will search for. I want my drop down to dynamically update, because other users have the ability to add new roles or edit the existing roles. When I try to make a dynamic parameter field, it automatically includes EVERYTHING in the “value” column – and not JUST the subset where “category” = “Role”.
Interestingly enough, I’ve figured out how to setup a “select expert” for category=role – and that works just fine when I run a report WITHOUT the parameter field. This pulls back ALL the data associated with ALL the roles.
But I can’t seem to go the last step to make my drop down dynamically update from JUST the subset of values that are roles…
I can put in a drop down selection menu and use static values by typing in "advisor" or "accountant"...
But, because the DB users at the client can change those "roles" I want to have the "roles" in the drop down menu update dynamically (in other words, the choices are what will currently be in the table - if someone adds "consultant" as a role, the drop down menu will automatically update).
But, by default (at least the way I'm trying to do it), Crystal only selects EVERY value in the "value" column to populate the drop down menu. And in fact, because that table contains thousands of records, the drop down menu only displays the first few hundred. So, it is not only displaying my "roles" along with other useless data, it is also not displaying all of the "roles".
Quite simply, it's obvious that this database is poorly designed and new table should be created... but that's not an option for me.
So, is there a way to do this? Or am I screwed?
I appreciate any help people can provide!
IP Logged
