For those of you who use arrays in Crystal Reports you are most likely familiar with grabbing a certain item from the array. Or you may have a large varchar field in your database that you'd like to Split up into an array and then return an item in a certain position of the array. Or you'd like to add items from your Details section into an array and then parse that array out later in the report.
In some cases you may have run into the array limitation of 1000 values. There are workarounds to some of these problems caused by the array limit of 1000 values, and one workaround is to not use an array but use a String Running Total instead. This blog will hopefully provide you a solution when you encounter this issue.
First of all, download the attached file, extract the "txt" contents and then change the "txt" extension to "rpt" instead. This is a sample Crystal Report that contains an example of the steps below including the Custom Function. You can easily add the Custom Function to your Repository should you wish to avoid step 2 below.
Steps on How to Use a StringToken Custom Function to Get Around the 1000 Value Array Limit
1) Instead of adding values (text or numeric) to an array, consider rolling up these values into a String Running Total instead. For example, if you've got an ID field in your Details section and you want to add these to an array, but you run into the 1000 values limit, try rolling up the ID's into a New Formula which is a string running total such as:
whileprintingrecords;
stringvar srtIDs:= srtIDs + totext({youridfield},0,"") + "|";
The pipe "|" character will be used later on when you want to parse a certain ID out of the string running total.
Place this new formula on your details section and then suppress the formula.
2) Now you will want to create a new Custom Function named StringToken in the Formula Expert as Basic Syntax and paste in the following code:
Function StringToken (string_input as string, character as string, number_of_characters as number)
dim token as number
dim incrementor as number
dim output as string
dim ender as number
ender = number_of_characters + 1
do until token = ender or incrementor = length(string_input)
incrementor = incrementor + 1
if string_input(incrementor) = character then token= token + 1
if ender - token = 1 and string_input(incrementor) <> character then output = output + string_input(incrementor)
loop
StringToken = output
End Function
This custom function will allow to grab any Nth value from the string running total. E.g. you can grab the 10th value or the 2000th value.
NOTE: You shouldn't let your string running total get too big or you'll run into an output limit of 64k. There are ways around that limit as well (such as using multiple stringvars or putting up to 1000 large stringvars into an array) but this blog post won't get into that.
3) To test the Custom Function out create a new formula to put in your Report Footer with syntax similar to the following:
whileprintingrecords;
numbervar tokenid:= 10; // 0 will bring back the first value from the string running total
stringvar srtIDs;
StringToken (srtIDs, "|", tokenid)
4) Now if all goes well, you should be able to grab values that are in a position that is greater than 1000. Please go to the last page of the sample report and you'll see that it is displaying the 1400th values for a Customer Name and the matching 1400th value for an Order Number ID.
I hope that you find this tecnhique helpful. If you are looking for any other solutions or workarounds for Crystal Reports, jamie.wiseman.
