Record Selection Formula For loop

[MadCatmk2]

Hi all

Using Crystal 9.0 with Access Database:

I have a question relating to Record Selection Formulas.

I want to check a field in my database to see if it has a certain string in it. I can use the Instr function to do this and it works with a single search. It currently looks like this:

instr({CONTACTS.INTERVENTIONS}, "SUC&quot = 1

What i want to do is to search for multiple strings within the Interventions field. What is the best way to do this. I thought i'd have to create an array of the strings i wasnt to search for and then loop through a for statement for each one. The only problem is that i don't know if this can be done.

Does anyone have any ideas regarding doing this. I'm sure theres probably an easy way of doing it but i just cannot seem to find it.

Thanks in advance

 

[pgtek]

hi
check this

http://support.businessobjects.com/library/kbase/articles/c2006429.asp

or this one

http://support.businessobjects.com/library/kbase/articles/c2011825.asp

cheers



pgtek

 

[MadCatmk2]

Had a quick look at these, i will have a good look next but i do have one other question. From what i can see these refer to the standard formula editor. Is it ok to have a for loop in the RecordSelection Formula editor to determine what records are viewed?

Thanks for the quick response.

 

[pgtek]

hi
Yes

cheers

pgtek

 

[MadCatmk2]

Hi

I've looked at the two links, not sure if there entirely what i was looking for but there were some good bits of information, so thanks firstly for that.

I have put this together and have only one slight problem and was wondering if you could help:

whilereadingrecords;
stringvar array catheterTypes := ["SUC", "URC", "REC"];
numbervar i;

for i:= 1 to UBound(catheterTypes) do
(
instr({CONTACTS.INTERVENTIONS}, catheterTypes) = 1
);

{CONTACTS.DATE_OF_VISIT} in {?StartDate} to {?EndDate}
and {CONTACTS.PRIMARY_REASON_CARE} = "GUF"

I think this is the correct way to go about it but the for loop is still not doing anything. It is taking any intervention as apposed to the three specified. Have you any ideas.

Sorry to bother you again but your help is much appreciated.

Thanks

 

[lyanch]

I am not sure that you need a loop here. Do you always want the codes at the begining of the string?

If you do then here is an alternative:

stringvar array catheterTypes := "SUC", "URC", "REC";
numbervar i;

(instr({CONTACTS.INTERVENTIONS},"SUC&quot = 1 or
instr({CONTACTS.INTERVENTIONS},"URC&quot = 1 or
instr({CONTACTS.INTERVENTIONS},"REC&quot = 1 )

AND

{CONTACTS.DATE_OF_VISIT} in {?StartDate} to {?EndDate}
and {CONTACTS.PRIMARY_REASON_CARE} = "GUF"


One comment tho, instr will not pass to the DB as shown here. If you need to reduce the size of the dataset returning from the DB you will need to change your approach to this.

Lisa

 

[MadCatmk2]

I had it like this initially and yes it does work but i was thinking long term and if i wanted to search for many more strings. Its going to make the record selection formula much bigger in the future. Thats the reason for the loop. Is there anyway to make the loop work in this way?

thanks

 

[lyanch]

I still wouldn't use the loop.

Create a SQL expression (depends on the version of Crystal you are using and your db.. )

Something like

%StartIntervention

substring(CONTACTS.INTERVENTIONS,1,3)


Then change your selection formula to:

{%StartIntervention} in ["SUC", "URC", "REC"]

You have to create the list one way or another anyway.. this will work just as well and you could actually create the list as a parameter.

Lisa

 

[MadCatmk2]

Ok. I'll have a look into that.

Thanks for your help, its appreciated.