Editing Values of a Dynamic Parameter

[TheBig1980s]

Hello:

I use Crystal to extract data from Dynamics GP--an accounting application, in layman's terms.

Apparently, there is only one table in GP's Manufacturing series that shows a manufacturing order’s status: “2” = open and “8” = closed for the status field. (“MO” in my e-mail here means “manufacturing order”.)

What’s unfortunate about this table is that it will show records of when the MOs were open and show records for that same MO now that it is closed. So, even if an MO is closed right now, there will be records in the table that show 2 and 8. Stupid, but what are you gonna do?

The problem is that I need to get crystal to not bring in any MOs that have ever had “8” in this table. GP considers an MO as open if it only has records in this table of “2” and only “2”—no other numbers.

I have tried going into select expert and saying “Status = 2”. But, this brings in closed manufacturing orders, as well as open ones. That’s because, at one time, the manufacturing orders that are closed were once open and therefore in this table have “2s” and “8s” in them.

So, I told select expert to not show any records in the report where status = 0, 1, 3, 4, 5, 6, 7, or 8. I would have thought that would have brought just orders that = 2. But, once again, I got the same result as the first thing that I tried.

Now, this report runs based on a dynamic parameter. This parameter is the MO number.

What I need to be able to do is to tell Crystal to not pull in for this parameter field any MO #s for closed MOs.

Can this be done easily? If so, how?

The Big 1980s

 

[lbass]

Use a command like this for your parameter picklist:

select table.MO
from table
where not exists
(
select A.MO
from table A
where A.MO = table.MO and
A.status = '8'
)

The punctuation you add depends upon your datasource.

-LB

 

[TheBig1980s]

But, how do I place this in Crystal "form" so that the dynamic paramter values are restricted by this?

 

[lbass]

You create this in database->database expert->add command (above the table list) and then when you set up the dynamic parameter, you select this as your source for the MO picklist.

Not sure what you mean by the Crystal "form".

-LB