SHOW Blank records 1

[JulieWard]

I want to create a report in Crystal that pulls blank or expired insurance information from our database. The insurance information is held in another table from the "main" provider information. The problem is, when I pull the insurance table into the report, if the insurance information is blank, it will not pull that providers record into my report. How can I get Crystal to pull ALL records into my report? (WITHOUT using a subreport if possible)

What I've had to do in the meantime is pull ALL records, do a subreport to pull insurance information into the report, then filter the records I want. It gets quite messy and the report takes hours to export because of the subreport and the number of records.

I am using Crystal Reports 8.5, and Vistar.

 

[lbass]

You need to use a left join FROM the provider table TO the insurance table. Then if you want to pull expired or null records, use a record selection formula like:

isnull({insurance.expiredate}) or
{insurance.expiredate} < currentdate

-LB

 

[JulieWard]

I've done that, and it works if only one field in the table is blank. If that particular records entire malpractice table is blank, he will not show up in my report.

 

[lbass]

Are you referring to the insurance table as the malpractice table? What is the field you are using to link it to the provider table? Let's say it is the customerID field. Be sure you have a left join from provider to malpractice, and that there are NO OTHER record selection criteria using the malpractice table. Then try:

isnull({malpractice.customerID}) or
{malpractice.expiredate} < currentdate

-LB

 

[JulieWard]

Yes, sorry, the malpractice is the insurance table. I'm using the "record number" field to link the tables, and I'm using a "left join".

I had a record selection to remove all the archived records and I removed that and my blank records show up in my report. However, I still need be able to show providers on the list who have ONLY archived records (they would normally show up as blank). I tried to supress the "archived" records a couple of different ways, and I always ended up supressing the "blank" records I wanted to keep. Any suggestions?

(Thanks for your help, by the way!)

 

[JulieWard]

For Example:

Dr. A has 2 insurance's in his record. One is archived, one is active.

Dr. B has 1 insurance in his record, and it is archived.

If I supress archived records, I'll supress Dr. B's record completely. If I am able to mark the archived records then Dr. B's record would show as archived and likely not show as "blank" (needing updating).

Does this make any sense?

 

[lbass]

I'm assuming that all providers are represented in the provider table whether they have only archived records or not, right?

How do you identify archived records? It seems to me that you would have an archived malpractive table separate from the malpractice table, and that you would need to bring this into the report. Can you explain?

-LB

 

[JulieWard]

We have a field in both tables (the provider table AND the insurance table) that indicates Active or Archived.

 

[JulieWard]

Sorry, I forgot: The archived tables are not separate.

 

[lbass]

If a provider has only archived records in the malpractice table, would that provider then be shown as archived in the provider table? I'm assuming there is only one record per provider in the provider table.

What is the purpose of this report? I think it might help to know.

-LB

 

[JulieWard]

The purpose of the report is to show all the providers in our database who have blank or expired insurance so that we can obtain updated insurance.

The provider can have only archived records in the insurance table and still be active in the provider table. There is only one record per provider in the provider table, but if there multiple records in the insurance table, it makes the provider show up multiple times in my report.

 

[lbass]

So wouldn't you want to identify the most recent record in the malpractice table by date and determine whether that was active or archived? Or is it possible that a record with an earlier date is active while the most recent record is archived? What I'm trying to determine is whether you have to detect ANY active record or only whether the most recent record is active.

-LB

 

[JulieWard]

That seems like it might work. . . if I can identify the most recent record and supress the others.

 

[lbass]

Does the SQL expression expert appear in your field explorer? It would speed up your report if you could use this.

-LB