SAME RECORD QUESTION

[Kim296]

I work for a jail and I am trying to create Superior Court List and State Court List from our Record Management System, but I can't quite figure out how to word my formula to make it pull correctly.

For example: Each arrest has a unique system id (armain.armainid) that can contain multiple charges for an individual that are either Felony "F" or Misdemenor "M" (archg.fel_mis).

The tricky part is that I have to leave the entire arrest record together for an individual, so that the person is on one or the other list regardless of charge status. This is what I'm having trouble with; I can seperate the "M" and "F" to one list or the other, but it seperates the arrest itself and may list one person on two seperate list when the person can only be on one list or the other.

This is how my formula could work, but I need help wording the formula so that it will work correctly.

If the unique arrest (armain.armainid) has only "M" charges then "State Court" else
If the unique arrest (armain.armainid) has only "F" charges then "Superior Court" else
If the unique arrest (armain.armainid) has both "M" & "F" charges then "Superior Court"

Does anyone know how I would formulate this to work?

 

[hilfy]

Do you have other tables in your report? If I assume that armain has a single record per arrest and you have a separate table containing the charges, which I'll call "Charges" for this example, here's what I might do:

1. Add the armain table to the report.
2. Add the Charges table to the report twice - when you add a second copy of a table to a report, Crystal will throw a warning saying that the table is already in the report and asking whether you want to "alias" the table. It will then add the table with "_1" on the end of the table name.
3. Link from armain to BOTH Charges and Charges_1. Right-click on each of the links, select "Link Options" and make the link a Left Outer Join.
4. In the Select Expert, add the following:
(
IsNull({Charges.armainid}) or {Charges.ChargeType} = 'M')
)
and
(
IsNull({Charges_1.armainid}) or {Charges_1.ChargeType} = 'F')
)
5. Create a formula that looks something like this:

If IsNull({Charges_1.armainid}) then 'State Court' else 'Superior Court'

6. Group on this formula.
7. Group on the person who was charged and put the person information in the group header.
8. Create a second person group header section.
9. In the new group header section, place a subreport that will pull ALL of the charge information for the person - link to the subreport on {armain.armainid}.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Kim296]

I've added the charges report twice (archg) and I'm trying to follow your directions, but I don't understand step 4. Where in the Select Expert do I add this? When I open up the select expert, I must choose where to place this under "common". ???

 

[hilfy]

If you don't have any selection criteria yet, pick a field, any field to get you to where you need to be.

Click on "Show Formula" and then on "Formula Editor". Manually enter the filter. Be sure to use the parentheses - it won't work right without them!

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com