Dynamic Cascading Parameters ...

[TomSalvato]

Hi - I've been looking around for a good step by step procedure that does what I need, with no luck. Let me try and state what I 'need' to do ...

I have a table (TABLE.Device) with the following two fields that I wish to set to parameters ... (FIELD.division and FIELD.Municipality). I want the second parameter to cascade from the first and the record selection on the report to be filtered by both parameters (division & municipality). I can do all this with no problem, here's the catch ... those two fields are populated with codes. I need to access two other tables to get proper descriptions for the fields. Each of those two Description Tables are linked in the report to the parent table (Device) by the appropriate fields.

I need a way to display these descriptions (from the two other 'description' tables) rather than the straight codes from the parent table (Device).

How the heck do I accomplish this?

Thanx in advance for any assistance ... still kind of new to Crystal (running 2008).

-TS

 

[briangriffin]

It sounds like you've got everything working fine - if you don't already have the description tables linked, then do that first.

In the edit parameter dialog, to the right of your value field column you'll see a column titled "Description". If the linking is done properly, you can select your description field from the dropdown.

Now if you refresh the report you'll be prompted with both the code and the description - for instance "100 - USA". You don't want that, so in the edit parameter options you'll see one titled "Prompt with Description Only". Set that to true and you should be good to go.

 

[TomSalvato]

Firstly, thanks for your quick response, Brian.

Ok, I have done that. I'm using the two fields from the main (Device) table as the values (in the parameter option), and I found the two Description fields from the two other tables on the 'description drop down menu' that you mentioned. Set the 'prompt with description only' option to true ...

What I get now is two empty boxes (that you can enter data into) ... no dropdown options at all. It's as if I'm missing the 'fill all values' option or something. What am I missing?

 

[briangriffin]

If I'm understanding correctly, the parameter is working fine when you don't use the description - the user selects the Region code, then gets a filtered list of Municipality codes. Your selection criteria is table.municipality = {?Municipality} (and you have not created a parameter for Region).

If you then add the description and it stops working, I can only imagine that there is a bad link to the description table. Even then I would the region values to still populate.

Dynamic parameters can be tricky, or impossible, to edit. The first thing I would try is to delete the parameter entirely and recreate it, including the description fields.

 

[TomSalvato]

Actually, my selection criteria is table.division = {?division} and table.municipality = {?municipality}

* I 'created new parameter' on both fields in order to filter on both.

This works fine when I don't add the descriptions (I'm working with just the one main table here), but when I do, I completely lose the drop down options for the user and all I have are those two empty boxes. It's as if Crystal doesn't like the idea of linking those two description tables to my main table and is choking when trying to fill those dropdown menus now.

I can't imagine my links being bad. They're just 'left outer joins' from the value fields in the main table to the appropriate fields in each of the description tables.

And you're right, you can't really edit these parameters after trying them out. You have to constantly hit 'new' and start over.

 

[lbass]

In your description tables, you would need to have a linking field (ID?) as well as the descriptions. What happens if you reference the ID field and description field only from the lookup tables?

-LB

 

[TomSalvato]

Hi LB - Yes, my linking is pretty straightforward ...

From the main table (Device), I have a field (Division) and I link that field to a field (Value) in the other table (V_Division), which includes the description field that I'm trying to use.

Same goes with my municipality field in the main table, linked to a V_Municipality table.

Both links are 'left outter joined' ... I'm just not seeing why the dropdown menus aren't populated when I try to use these description fields (from the two other tables). I would think that if they weren't linked properly, they wouldn't even show up in the Description pulldown option.

 

[lbass]

I am suggesting that you use V_Division.Value and V_Division.Description, and V_Municipality.Value and V_Municipality.Description for you dropdowns.

-LB

 

[TomSalvato]

Interesting, when I just do the one parameter (Division) using the V_Division.value with V_Division.description (WITHOUT adding a second parameter) it gives me a populated dropdown menu ... looks great except that there are too many values. I assume this is because it's not looking at the main table now.

When I add the second parameter (Municipality), I lose the dropdown menus again. I just have the two empty boxes.

 

[lbass]

Are there any fields in the Division table and Municipality tables that would allow you to link them together, e.g., a Municipality ID field in the Division table?

-LB

 

[TomSalvato]

Hi LB - Nope, those two tables can not link to each other. They have to work from a main table. They're just description files.

 

[lbass]

Is there possibly a fourth table that links the Division to Municipality? How do you know which Municipalities fall within which Division? I think there has to be a built-in explicit relationship between the two in your tables for you to use dynamic parameters.

You could potentially create a command where you establish the relationship, but you would have to spell out that Division A corresponds to Municipalities B, C, and D, etc.

-LB