Testing for List of Text Strings in Database Field 1

[cmarshal]

Working with data from our telephone system, I am tring to select records where any one of several telephone exchanges is included in the Dialed Number.
I have gotten the formula to work where I enter a single area code and exchange combination as a parameter (ie: 603555) using the following select criteria:
{?Number} in {Call.DialedNumber} and
{Call.StartTime} >= {?Start Date} and
{Call.StartTime} <= {?End Date}

Now, however, I have to look for calls to any of about 20 different area code and exchange combinations at the same time, in the same report. The area code/exchange combinations are static, so they do not need to be entered as input parameters, tehy can be specified by any other means.
How do I create an array, if that's what's necessary and, how do I write a formula to determine if any of my multiple area code/exchange combinations are included in the field I'm comparing against?
Thanks for your help!

 

[dcm]

cmarshal: Try this:

stringvar array numbers_to_test := ['603555','605988','708333','99999'.............];
{Call.Dialled Number} in numbers_to_test

David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[cmarshal]

Thanks, David!
Where do I enter the stringvar array numbers_to_test := ['603555','605988','708333','99999'.............]; ??
Is this a parameter, formula, or part of the select formula itself?
Also, I'm really trying to test the opposite way -- to seek if any of the array items are in the {call.dialednumber} field, which is an eleven character field including the area code, exchange and local number (ie: 16035551212). Can I simply reverse the formula? I fear tehre's some more complicated syntax to get Crystal to search for each of the items in the array within the dialed number field.

 

[kenhamady]

if you have access to edit the selection formula directly you can use:

{Call.DialedNumber} like [&quot;*123456*&quot; , &quot;*234567*&quot; , &quot;*345678*&quot;]
and {Call.StartTime} >= {?Start Date}
and {Call.StartTime} <= {?End Date}

This is how the select expert would build mulitple values using a wildcard. Using the Asterisk on each end is the same as &quot;IN&quot; but allows multiple values. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dcm]

cmarshal: You could enter the values in your array as part of a formula (that's the process I illustrated earlier where stringvar etc. are the start of a formula to test for Dialled Number) or you could establish a parameter with multiple responses (v8 is easiest for this) and then your formula would commence with:

stringvar array numbers_to_test:={?param};

As for reversing the test - Not so easy!!!

You'll need to count the number of entries in the array:

numbervar count_of_elements := Count({?param});

for example

then you'll need to select each element in turn:

stringvar element_to_test:={?param}[element_number];

then use Instr() to test for content:

Instr({Calls.Dialled Number},element_to_test)>0

Probably best to switch to Basic syntax (again only with v8) and use For Next loop to loop through the number of elements David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner