Array record selection or more suitable way

[MadCatmk2]

hi all

Using Crystal 9.0 and connecting to Access Database.

I'd like to have my report select records based on whether a any of a number of strings appear in a text field in the report.

I have a diagnosis_text field in the report and want to search that for the strings "TACI" or "TIA".

What i have at the moment is an array specified in the recordselection section and a for loop which i'm trying to loop through for each of the array entries, see below :

----------------------------------------------------------

numbervar i;
global stringvar array Freetext := MakeArray("TIA","TACI"

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and
(
for i := 1 to 4 step 1 do
instr({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS}, Freetext) > 0
)

-----------------------------------------------------------

The date_of_referral and Contact_type are the other selection criteria required, these work. I cannot however seem to get the array and for loop working. Could someone point me in the right direction as to what i've done wrong or how i should be going about doing this.

Thanks in advance

 

[bdreed35]

Typically, you would use the LIKE keyword to return the results you are looking for.

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and 
{PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} LIKE ["*TACI*","*TIA*"] //add other values as needed

~Brian

 

[MadCatmk2]

That worked great.

Thanks for your help, its much appreciated.

Cheers

 

[MadCatmk2]

Hi again

as you suggested, i used the "like" operator and this worked fine for the strings within the text field. I added a couple of lines to the record selection so that it would select either those records with the set strings in them or the records where the keyword field is CVA. This doesn't seem to be working. Can anyone see any problems with my record selection formula. I'd like the records to meet either of the last two criteria.



{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"

and
(
(
{PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like ["*TIA*","*Haemmorage*", "*PACI", "*TACI*"]
and not({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*dementia*&quot
)
or
(
{READ_EXTRACT.KEY_WORD} = "CVA"
)
)

Many Thanks

 

[Reebo99]

Try :


{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)} and
left({CONTACTS.CONTACT_TYPE},1) = "D" and
({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*TIA*","*Haemmorage*", "*PACI", "*TACI*" or
{READ_EXTRACT.KEY_WORD} = "CVA&quot


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Reebo99]

Oh, and if you still want to exclude "*dementia*" then :

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)} and
left({CONTACTS.CONTACT_TYPE},1) = "D" and
({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*TIA*","*Haemmorage*", "*PACI", "*TACI*" or
{READ_EXTRACT.KEY_WORD} = "CVA&quot and
not({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*dementia*&quot

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[MadCatmk2]

Thanks for the quick reply. Tried your suggestion and it didn't work as i'd expected. I have tried using the record selection like this:

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and
{READ_EXTRACT.KEY_WORD} = "CVA"

this provides 588 records. When i change the last part to what you suggested or indeed what i had before i get only 48 records. The way i see it i should get at least the same amount if not more records appearing. Have you any ideas as to why this would be happening?

Thanks

 

[MadCatmk2]

One other thing i noticed about your example. Do i need square brackets around the strings after the like operator i.e.

like ["*TIA*","*Haemmorage*", "*PACI", "*TACI*"]

I get errors without them.

thanks

 

[Reebo99]

Whoops, sorry you do need square brackets. :

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)} and
left({CONTACTS.CONTACT_TYPE},1) = "D" and
({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like ["*TIA*","*Haemmorage*", "*PACI", "*TACI*"] or
{READ_EXTRACT.KEY_WORD} = "CVA&quot and
not({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*dementia*&quot

How many records do you get for the following? :

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and
{READ_EXTRACT.KEY_WORD} = "CVA"
and
not({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*dementia*&quot





Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[MadCatmk2]

i get 45 records if i use the last formula posted. i went back to try it with just the CVA test without the diagnosis text field and sure enough i get 588 records. Really am puzzled as to why this is happening.

thanks

 

[Reebo99]

It's doing it correctly as far as I can make out.

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and
{READ_EXTRACT.KEY_WORD} = "CVA"

= 588

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and
{READ_EXTRACT.KEY_WORD} = "CVA"
and
not({PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*dementia*&quot

= 45

My guess would be that if you did :

{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
left({CONTACTS.CONTACT_TYPE},1) = "D"
and
{READ_EXTRACT.KEY_WORD} = "CVA"
and
{PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like "*dementia*"

= 543

If this works then it is correct. What would you expect to see?

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[MadCatmk2]

you would think so wouldn't you. that would be logical. However it comes up with 0. I'm going to have to go back to the drawing board and check my table joins etc. Its not making a lot of sense.

Thanks for the help.

 

[MadCatmk2]

Hi

I've had a good look at all the tables and they seem to be fine. I've simplified the formula to see if its any easier to spot the problem. Now i have the formula set out as:

(
{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
{READ_EXTRACT.KEY_WORD} = "CVA"
)
or
(
{PATIENT_REFERRAL.DATE_OF_REFERRAL} in {@StartDate(1)} to {@EndDate(2)}
and
{PATIENT_REFERRAL_DIAGNOSIS_TEXT.DIAGNOSIS} like ["*TIA*","*Haemorrhage*", "*PACI", "*TACI*", "*Infarct*"]
)

when i run the part of the formula before the OR operator i get 161 records. The part after the OR when run individually gets 20 records.

From what i can see i should get >161 and < 181 referrals returned. The only thing i can see that may be causing a problem is nulls. There are a couple of records from the 20 record set in the 161 record set, this as far as i can see is because they have both CVA as a keyword as well as the string in the description.

If anyone has any other ideas the help would be much appreciated.

Thanks again