Need to find agency number

[Creakinator]

I'm not sure if this is the right forum or not as I'm not sure if a formula or a sql expression will do this for me.

SQL databse through an ODBC connection
Crystal Reports XI

I have a table name 'cases' that has a field 'T_AGENCY_REF'. In this table I have both 'parent' cases and 'sub' cases.
The parent case contains all the demographic information including the T_AGENCY_REF number. A parent case number would be ##-##### (ex: 11-12345)
The subcase contains the statistical data I am trying to compile into a report. A sub case number would be ##-#####-A-## (ex: 11-12345-L-01).

cases.Name cases.T_agency_ref cases.Parent_case
Parentcase filled in NULL
subcase sometimes completed/sometimes null parent_case #

My report shows all the statisical data from a subcase, but I need to show the T_agency_ref field from the parent case.

I hope this makes sense. Let me know if I need to make anything clearer

Thanks.

Christy

 

[lbass]

But what are you displaying in the report? What is the group structure?

You could use a formula like this:

left(cases.subcase,8)

Please show sample data with several rows of results. At first I thought the T_agency_ref number held the case number you were looking for, but in rereading your post, I'm not sure.

-LB

 

[Creakinator]

Here's some data from cases table
name T_agency_ref Parent_case stat1 stat2 stat3
11-12345 2345RT null null null null
11-12345-L-01 Null 11-12345 1 3 4

I want to display the t_agency_ref for the parent case but showing the statical data from the subcase Here is what I want to show:

Name T_agency_ref stat1 stat2 stat3
11-12345-L-01 2345RT 1 3 4

Another problem is that sometimes T_agency_ref for the subcase is completed and sometimes not.

I'm not sure if this will help: The parent_case field 'links' the subcase to the parent case. If that field has data then the record is a subcase. If the parent_case field is NULL then the record is a parent case.

Clearer?

Thanks for looking at it.

Christy

 

[lbass]

You didn't explain your grouping, but assuming you are grouping on a formula like:

//{@name8}:
left({cases.name},8)

...then you could create another formula like this:

//{@Tagencyref}:
if isnull({cases.parent_case}) then
{cases.T_agency_ref}

Then you could use a formula like this placed in the detail section:

maximum({@Tagencyref},{@name8})

Then conditionally suppress the details with:

isnull({cases.parent_case})

Suppress the group header and footer, too.

-LB

 

[Creakinator]

I am grouping by user_name which is from another table x_aproval, but when I removed that grouping and did what you indicated, the formula field (fmAgencyNumPC) I put on the report is coming out empty.

Here are the formulas I used (they are named differently):

//fmParentcase (I grouped by this one after removing the user_name grouping)
Left({CASES.NAME},8)

//fmAgencyNum
if isnull({cases.parent_case}) then
{CASES.T_POLICE_REF}

//fmAgencyNumPC
maximum({@fmAgencyNum},{@fmParentCase})

It didn't seem to matter if I suppressed the details or not.

Thanks.

 

[lbass]

Maybe the field is blank, not null. Try:

if isnull({cases.parent_case}) or
trim({cases.parent_case})= "" then
{CASES.T_POLICE_REF}

I only suggested the suppression to get the display you were trying to achieve, but if you want to suppress then if the new
formula works, you should use the same syntax for the suppression formula.

Also I didn't intend for you to remove any groups particularly--I was just guessing that you were grouping on something like {@fmParentCase}.

-LB

 

[Creakinator]

Thanks. I had to make one change in the formula you gave me above.

I put a not in front of the isnull and it is working for most of the records. There are a few that are not showing the agency case number at all and I can't figure out why.

I put the not in the formula as the subcase records have the parent_case completed, so if the parent_ case is completed, ie (not(isnull)), then get the agency number from the parent case. If that makes any sense at all.

I understand about the grouping. I removed it to make sure that having the grouping wasn't causing the formulas to work.

Thanks so much for your help.

Christy

 

[IanWaterman]

I think you will make life easierif you alias the table in again.

Add the table again Crystal will ask if you are sure say yes and it will be added with alias 'T_AGENCY_REF_1'

Join thtis table by linking 'T_AGENCY_REF.name to 'T_AGENCY_REF_1.Parent

In select expert
not(Isnull(T_AGENCY_REF.t_agency_ref))

In report
Group by T_AGENCY_REF.t_agency_ref

This will now assign and agent name to all your child records.

If some of your child records can have an Agent ref then to ensure you only have parent records from T_AGENCY_REF change select expert to

not(Isnull(T_AGENCY_REF.t_agency_ref))
and Isnull(T_AGENCY_REF.parent_case)

Ian

 

[lbass]

No, my original formulas should have worked as is, based on your sample data, but you must use the maximum formula to get the desired result.

Try Ian's suggestion--which is another approach I had also been thinking about.

-LB

 

[Creakinator]

I tried Ian's approach and the report shows no records. Not sure why. The other issue with this approach I have to call these reports from another program that overwrites any select expert settings I've set in the Crystal Report and I'm not sure if I can put that formula in my other program.

LB - If I leave out the not, then the T_AGENCY_REF doesn't show. If I put the Not in, then the T_AGENCY_REF shows for most of the subcases but not all.

I'm trying the LB's idea on another field (Incident_type) in the same report, but it is not working at all on this second field. I have to have the T_AGENCY_REF at this record too. The T_AGENCY_REF is showing but not this other field.

Maybe it would be easier to have the program I am using to write the agency ref # and the other demographic information to the subcase record too. I can write a subroutine in this program to make sure these demographic fields are the same using the parent case record as the correct data and if not, then update the subcase fields. It would slow the appearance of the report slightly but it would be easier to maintain and make reports as I have many reports that need this demographic information. Or am I taking the chicken way out?

I can't change the way the parent cases or subcase records are stored in the database. Maybe it would have been nice to have parent case records in one table and subcase in another table - not sure if this would have helped in making reports such as these.

Thanks all of the help.

Christy

 

[IanWaterman]

Check report options, if you have show Nulls ans default then

not(Isnull(T_AGENCY_REF.t_agency_ref))

Will result in no records returned.

Ian