Link to home
Start Free TrialLog in
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)Flag for United States of America

asked on

Crystal Reports: Arrays and Filter

CR 8.5

i am reading an example of using filter on an array:
filter(["abc abc abc abc abc","hjjh","hj abc hj"],"abc")[1]

what does the [1] mean at the end?

this is what i'm trying to do:
shared stringVar array qm3Array1;
shared stringVar array qm9Array1;
local stringVar qm3t1;
local stringVar qm9t1;
local numberVar qm3sizeArray1:=ubound(qm3Array1);
local numberVar qm9sizeArray1:=ubound(qm9Array1);
local numberVar cnt;
if cnt=0 then cnt:=1;
local stringVar result;

if qm3sizeArray1>1 then

if i remove the [1] from the end of the filter statement, i get an error about needing a subscription on the array.  if i replace [1] with [2] i get the same result as i have for [1].
i am trying to use filter to find/match the string in qm9t1 with an element in the array qm3Array1

so, for example, qm3Array1:

and qm9t1=nail

i want to return 5.
Avatar of Marcus Aurelius
Marcus Aurelius
Flag of United States of America image

The "[1]" is identifying the FIRST item in the ARRAY.

As an example...IF you had a STRING like this:


And if you used the SPLIT function to split it out you would receive the following:

split(table1.StringField),",") [1]

Would return the FIRST item in the STRING:      ABC

split(table1.StringField),",") [2]

Would return the SECOND item in the STRING:    DEF

and so on...

Hope it helps

You are getting the same result because the SECOND ITEM [2] in the array is the same as the FIRST [1]
Avatar of Mike McCracken
Mike McCracken

Is FILTER a function you wrote or is it in Crystal?

What is FILTER supposed to do?

Avatar of zephyr_hex (Megan)


filter is a function in crystal, and it is the function that i have a question about how the [1] works.  i understand how it works with split, but it seemed to be working differently with filter because i was getting the same result regardless of whether i used a [1] or [2] (unlike split...which would give me the first or second part of a string)

filter is supposed to filter an array and return results that match.  i'm not sure if there is another function that would accomplish what i'm trying to do.  basically, i have a string and i need to find the match in an array.

i've looked up the syntax of filter, and i'm not seeing an explanation of the [1] on the end, although the example i came across shows that [1] (and i get an error if i don't use it in my formula)

here is the site where i found that example:
CRXIuser2005  - the [2] item is not the same as the [1] item in my array (example element in my array:  hammer, 4).  if i use split, [1] returns the item name (hammer) and [2] returns the quantity (4).  filter is not doing that.  maybe because my delimiter is "," and not a space?  the syntax for split allows me to declare my delimiter, and i'm not seeing that option in the syntax for filter.  filter is returning hammer,4 regardless of whether i use [1] or [2] and i can't figure out how to identify the delimiter (i originally thought filter was just returning hammer, but it was because my field was too short... i stretched it out and i can see it is returning the entire element from the array).  maybe i need to use split in combo with filter?  but then why does filter require the array subscription at the end of it?  i get an error if i dont have that subscription.

What the "filter" function does it to take out part of the string and leave a remaining string.  So by using filter you are taking characters out of the string.  The (1) gives you the first group of characters left after you filter out what you don't need.  The (2) gives you the second group of characters left after you filter out what you don't need.  

My question is what you're trying to accomplish - are you sure FILTER is the function you really want?

i am not sure that filter is what i want

i am trying to do the following:

i have two arrays:
qm3Array1 and qm9Array1

both arrays hold elements of the following format:  hammer,4  (a part name followed by a quantity)
but the items in the two arrays are not necessarily in the same order, and it is possible that there are items in one array that are not in the other

i need to match the item in qm9Array1 with the same item in qm3Array1, and then subtract the quantity of the item in qm3Array1 from the quantity of the item in qm9Array1

for example:


so i want to iterate through the items in qm3Array1 and see if there is a match in qm9Array1.  if a match is found, then subtract the two quantities.

paint would not find a match, and return 0
screw would find a match, and return 4 (8-4)
saw would find a match, and return 1 (3-2)

if filter is not the right function, can you please suggest a better one.  thanks :)
I think this must be a two step process.  Sounds like you want to use SPLIT on the first set to separate the items and quantities into an array and then you want to loop through the array and use INSTR to find the matching item in the second set.

Avatar of frodoman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Futher information: Depending on the size of your array, this might take a while to execute.  If it's unruly then it can be done more efficiently by joining the second array into a single long string and using the INTSR function - this is what I was originally thinking - it's more efficient on longer arrays but also more complicated.

what you have makes sense...
qty1 := cdbl(split(qm3[counter],",")[2]);

what does cdbl do?
also: all_results := all_results & " " & cstr(result)
what does cstr do?

i initially tried using a single long string, but the issue i ran into was the limit of 254 characters for string size in CR 8.5.

the arrays in my test data are about 60 elements long.  the actual data will eventually be much, much larger (about 10,000 items or so)
if i run into issues with my array size, i may break the report down and run it on smaller groups of items (so there would be multiple reports)

i'll let you know if i run into issues using the method you've suggested.
cdbl - converts text to number
cstr - converts number to text

Here's the potential issue with the size of your actual data - it's an exponential growth pattern.  You have one loop inside another loop so if there are 60 elements in each array then you have 60 passes through the inner loop for each pass through the outer loop.  Total of 60 x 60 = 3600.  When you have 10,000 in each array then you have 10000 x 10000 = 100,000,000  -- that's going to take a while to complete so smaller groups may make sense.  The technique is sound and it should work, it's just not going to be very responsive.