CR 10 Array is greater than 1000 and Eliminating duplicate elements

[rmago]

Hello,
I'm a new user to Crystal and was hoping I could get help on how to build a proper array that would give me an extraction of a unique set of elements from a database field that has duplicate values plus is massively long (thousands of records).

The stringvar looks like this:
"06006","06006","06006","06412","11203",etc thousands of elements, and I'm getting the >1000 error. I need the stringvar to give me one instance of each unique element. Ultimately, the array should end up with approx 550 elements total.

Here's the code I'm attempting:
whileprintingrecords;
global stringvar array RefDad;
global stringvar array RefDadFinal;
global stringvar RefCodes;
global numbervar Counter;
global numbervar i;


Redim Preserve RefDad [if RefDad[1] = "" then 1 else Ubound(RefDad)+1 ];//this is an attempt to eliminate the first element, which is showing NULL
RefDad [Ubound (RefDad)]:={@RefDaddyID};
{@RefDaddyID};//this formula retrieves a specific field in records where the array elements I need are stored

For Counter:=1 to Ubound (RefDad) do
(if not (RefDad[Counter] in RefDadFinal) then (redim preserve RefDadFinal [Ubound(RefDadFinal)+1];
i:=i +1;
RefDadFinal:=RefDad[Counter];
RefCodes=Join(RefDadFinal,",");
));
RefCodes


Any help you can give would be greatly appreciated.

Thank you

 

[elsenorjose]

Hi,

I'm sorry but I'm a little confused. The database field has thousands of values? Does that mean there are thousands of rows in your table or there are literally thousands of those numeric strings in one field?

If the are individual rows, you simply need to create a group on that field in your report and you will get the unique values.

Can you clarify a bit more the layout of the report, the desired results, and how the data is structured in the database?

 

[Turkbear]

Hi,
In addition, please indicate what database is involved and how you are connecting to it.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rmago]

Thanks for replying so quickly, elsenorjose and Turkbear.
Sorry for the confusion, I'm quite green.
There are thousands of rows in the table, and I am extracting one field from each row (record) in the table. This is a large transaction table in Timberline - I am connecting using ODBC. So the extraction causes many duplicate values. For the main report, I only need one instance of each unique value.

I am then using this value to link to a subreport that is being created off of another different transaction table (this one also has thousands of rows). The field I am linking to in the subreport is not the same field as the table being used in the main report, but it has the same values and is the same type of record. When the match is found between the main report and the subreport, the result is transactions that are attributed to the value in the main report - which is what I'm after.

I appreciate your recommendation about grouping - and I did try this, although I may have not done it properly. I end up with multiple instances of the value in the main report, using the group header. I tried to suppress the duplicates (in section expert) and also suppress null values, but the empty space remains. As a result, when the subreport kicks in, it takes forever to calculate, as it is still going thru iterations on each instance in the group.

So that's when I attempted the array, in an attempt to get the unique value set I need in the main report. But the array is also giving me every instance of the value, which is making the elements >1000.

I hope this is making sense-thanks for your patience and your help!

 

[Turkbear]

Hi,
If the database supports the use of a SQL Command , use that as your main report source:

Select distinct field_I_need from first_table;

( This assumes you only need that field from the table.)

Use this to populate the main report and to link to your sub.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rmago]

Thanks very much Turkbear, I think I can do SQL commands so I'll try that. Thanks again!

 

[elsenorjose]

If that doesn't work, depending on your version of Crystal Reports, you can enable 'Select Distinct Records' in the report itself.