Filtering by a value in a text field

[matpj]

Hi people,
i'm using Crystal Reports 10 to query 2 data sources, one being a project control datasource that contains information about change activities.

Each entry in the table (several entries per project) has an associated status, such as 'Testing Complete', 'Scheduled', 'Coding Complete' etc.
I need to filter out all results for a project on the earliest status.

For example if I have 4 rows for a project (4 different change activities), with statuses of 'Scheduled', 'Testing Complete' and 2 'Coding Complete', I want to only display the row with 'Scheduled' as this represents the 'earliest' status.

Does anyone have any suggestions about how I can achieve this?
thanks in advance,



Matt
London (UK)

 

[Madawc]

What I'd do in group by project. Have a running total for each possible status. In the group footer, use a formula field to test the running totals and work out which of them applies.

Of course if the entry has a date, you could sort the group by date, descending. The group footer could then have the first entry.

Or you could derive a formual field for each detail line and sort by this formula field, giving it a value that sorts in the order you want.

Madawc Williams (East Anglia, UK)

 

[lbass]

Is "Scheduled" always the earliest status? If every group has a status of "Scheduled" as the earliest status, then you would just use this in your record selection formula:

{table.status} = "Scheduled"

If this isn't the case, you need to explain how your records would be identified as earliest. Is there a date field? Is there a numerical field that is always in chronological sequence?

-LB

 

[matpj]

hi,
'Scheduled' is not the earliest as there are several statuses.
I just listed a few to make the thread easier to understand.
Sometimes a project will have several CA's (change activities) logged against this - of which some may be at Coding Complete, and some may be Testing Complete.
I've grouped by project and then by status, so I only have one row per status now.
I just need to know how to code it so that it supresses all but the earliest status on that particular project.
Ideas on a postcard.......

Matt
London (UK)

 

[Madawc]

Convert the status to a code that will sort in the right order. E.g.

if your.status = "Scheduled" then "a) Scheduled"
else 
if your.status = "Coding Complete" then "d) Complete"

etc.

Once you have the right sequence, you can group and sort by the sequence within that group. The desired value can then be shown in the header or footer, with details suppressed.

Madawc Williams (East Anglia, UK)

 

[matpj]

I have just tried, this, but the formula field is not displayed in the sort option.


Matt
London (UK)

 

[Madawc]

Then have a group within a group. It definitely will work for groups, because I've done similar things myself.

Madawc Williams (East Anglia, UK)

 

[lbass]

I repeat: "... you need to explain how your records would be identified as earliest. Is there a date field? Is there a numerical field that is always in chronological sequence?"

-LB

 

[matpj]

hi folks,

I created a new formula field and this time it worked (was visible as a sort option).

not sure why it wasn't before!

lbass, I identified the records as earliest by using Madawc's method.
thank you both.

Matt
London (UK)