Calling Child Records to the Beginning of a Report

[butkus]

I am working with a relational database that has One master table and four supporting tables that track info of Victims of crime (the relation key is a unique number assigned to the victim from the master table)

The supporting tables capture involved persons (defendant / next of kin / guardian / witness / etc...) (this is one table)

Another table (events) keeps sequential records of the case as it goes through the criminal justice system

Yet another table (narrative) is used by our counselors to make notes as the case progresses.

My problem:
I'm trying to develope a report which will produce a summary of these things for the counselors use. Most of the info comes from the master table (and that works just fine). The challenge comes from trying to extract info from the supporting tables. Example: in the persons table I would like to pull (to the beginning of the report) the info for the next of kin (for a deceased victim) or guardian (for a child victim). These data elements may have been captured at place 2 or place 200 in this table. While I have been able to write a formula to call this info out, Crystal places a blank line in details for every child record before the "hit" that does not meet the criteria of my formula. This problem is compounded by the fact that I had to place all of the summary info in the report header and the notes (from the narrative table) in details (so the notes would stay together). I tried to place this formula in details A and the notes in details B and suppress details A when the criteria was not met, but the "hit" shows up on page 6 of a 9 page and there after.

I hope I've made some sense with all this, and further, I hope someone can shead some light on this.

Thanks
James
Network Admin

 

[dgillz]

So is your problem basicall all the white space in your report in details A makes it unreadable?

If so format details A to "suppress blank section".

If this is not your problem please elaborate. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[butkus]

I've tried the supress if blank. does not work.

An axample of the output:
PAGE 1
Report Header)

-Case information
-Defendant information
-Victim Information
-(this is were I want next of kin or guardian to appear - but the formula never returns a value)

Page header - is the currentlocation of my nok / guardian info formula)
Details A
-1st narrative entry and any other sequential entries that fit on the page (from detail section)

PAGE 2 - 5
more narrative entries (as they sequentially appear in the supporting tables)

PAGE 6 NOK / Guardian info appears
More narrative entries

PAGE 7 - 9 identical to page 6 (with NOK / guardian info in page header)

The report is 9 pages long because it contains 36 individual memo (narrative entries)

In this particular data record the Guardian is the second entry in the PERSONS table (why the name first appears on page 6 is a mystery to me. I can supress the duplicates that appear on page 7 thru 9. But I want to know how to call this data to the beginning of the report. What would happen if this name appeared at place 50 in the PERSONS table?

 

[basil3legs]

Am I right in thinking the supporting table which includes the next of kin includes fields for the case/vicim ID, the person involved and there relationship.

e.g.

VictimID Person Relation
123 Fred Bloggs Witness
123 Joe Smith Next of Kin
123 A.N.Other Defendant
456 John Doe Guardian
.
.
.

If so, and there is one, and only one entry for Guardian or Next of Kin for a victim, you could add another copy of the table to the report with an alias and use Select Expert to filter out all the other entries. Then you could just add the needed field to the report in any level below or including the vistim group without affecting the position of other information. This may cause problems if there is no entry for next of kin for a victim but this can be worked round. If the name was just left blank but the line appeared there should be no problem.

The alternative to this would be to use a subreport to return the Guardian/Next of Kin but this would be extremely slow and inefficient if the tables are large.

 

[butkus]

basil3legs,

Thanks for the reply. Your assumption is correct, the persons table auto fills the victim# from the master table and has additional fields that capture the persons name / there involvment to the case / relationship to the victim / and a whole host of other data (like contact information). Not every record will have next of kin or guardian involved persons however (most victims of crime are 1. alive and 2. adult).

I was thinking of coming up with a formula that would run a Loop through the child records and assign a string variable the contents of the "hit" (but have not yet come up with a good exit for the loop if nothing meeting the condition is found). Your alias idea sounds good - but I'm not familiar with its use. Can you elaborate?

I thought about a sub-report, but I need to distribute this to the counselors through Crystal Enterprise and subreports under that environment have a nasty habit of asking the user to re-authenticate to the data source and re-input the parameter. I can see the users complaining now.

Thanks again,

James
Network Admin

 

[kenhamady]

I think the problem stems from the fact that you are pulling from tables that have multiple one-to-many relationships to the core table - which causes redundancy.

What is the database platform for this data (ie Oracle, SQL Server, Dbase, B-Trieve). Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[butkus]

Ken,

IBM's DB2. And yes, there is a lot of redundancy.

 

[kenhamady]

Unless you can do some pretty fancy SQL, I think you are going to need subreports. Crystal formulas can't loop through data. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com