Advanced Data Selection 1

[Zbrojovka]

Hello all. I'm using Crystal 2008 and have a new report which requires expertise.

I have a record set which includes a chain of custody for a piece of property. Here's the scenario: A piece of property is in holding and checked out to an examiner. When checked out, a chain of custody event is created. The chain of custody table allows me to query the who, what, where, when, and why the item left it's original location. So I have built a report which displays all events that were checked out to the Medical Examiner's Office. No date range at this point I just want to see everything that is out.

Here's the complicated part. A similar event is created for chain of custody if the property is checked back in. I need to be able to exclude any pieces of property that were check back in. So...show me all records that were checked out to the Medical Examiner, but never returned.

This is complicated to me because each piece of property is tracked via a control number in chain of custody. One control number can have several events associated with it and they might be for all kinds of different things.

Like I said above, I've set my filters for records that equal Medical Examiner, and event = 'O' for out:

{pecust.ccinout} = "O" and
{pecust.releasedto} in ["ME OFFICE", "OFFICE, MEDICAL EXAMINER"]

How should I go about reviewing records within the same control group and excluding them if they were received back?

I have attached a link to the form used to enter Chain of Custody events for clarification.

 

[IanWaterman]

YOu can insert a command based on pecust table.

something like
Select * from Pecust where {pecust.ccinout} <> "O"
or
Select * from Pecust where {pecust.ccinout} = "I"

If "I" means In

Link on the item ID to original Pecust table using a left outer join.

In your select statatement add

and isnull(command.pecustID)

Ian

 

[Zbrojovka]

Thanks Ian.

Would there be a way to evaluate the records if I grouped by the control #? Read all records in the group and if the group does not contain {pecust.ccinout} = "I" then display?

 

[IanWaterman]

But there will not be any {pecust.ccinout} = "I" because you have filtered them out.

You can alias the pecust table in again and do what you suggest but this will duplicate data making summaries very difficult.

Ian

 

[Zbrojovka]

Ahh, I forgot to say, "If I dropped the filter" and went with the different method. Would an alias still be the solution or can I take a more direct approach by excluding records with an "I" in the group?

 

[IanWaterman]

YOu can but it makes everything a bit more difficult.

Using the command as i suggest will be much easier, as it will bring back the dataset you require.

Ian

 

[lbass]

Is this issue resolved? I was wondering whether an item might have multiple "in" and "out" records. If so, you would need to make use of a date field related to the ins and outs or an ID field that is a sequential numeric field. You could then use group selection to return the most recent record (maximum of date or number per item group) and only when that maximum record is "O".

-LB

 

[Zbrojovka]

Good Morning lbass and thank you for your added input. There should be a date field yes, so your idea is exceptional. I believe that would give me exactly what I needed without too much hocus pocus. I'll give it a shot!

 

[Zbrojovka]

Well LB...I gave it a shot. I've concocted all sorts of different formulai trying to get this done and it's not working. Here's the jist of what I've been trying to do. Tell me what I'm doing wrong:

Created Two formulas:
@CCDate-maximum ({pecust.cctime})
@CCStatus-if {pecust.ccinout} = "O" then 1

I attempted to create a Group Selection as:
({CCDate}) and
sum({CCStatus},{pemain.ctrlnbr}) > 0

Not much experience with Group formulas. I'm sure this is simple.

 

[lbass]

Your group selection formula should be:

{pecust.cctime} = maximum({pecust.cctime},{pemain.ctrlnbr}) and
{pecust.ccinout} = "O"

This would give you the most recent record per group and display it only if it was an "Out" record. Is that what you wanted to do?

-LB

 

[Zbrojovka]

Yes, that is what I was aiming for. Thank you again! The formula works great, but it has uncovered some data inconsistencies, which I guess is also a blessing.