Arrays - HELP

[piazzab]

Here is my problem. I have a report where I have to keep the invoice numbers together with the payments so I have the report grouped by invoice number. My client wants to gross billings for a given month and the payments for those billings in the following months. I can not group my report based on month and year because it separates the invoice from its relevant payments. Crystal Decisions recommends setting up a subreport in the report header to collect the invoice numbers for each month in arrays then create seperate arrays to hold the payments for each month if the invoice number is in the appropriate array.

Apr Invoice Array [stores the invoice numbers for arrays]
Apr Payment Array [if the invoice date is in month =4 and invoice number is in Apr Invoice Array then store amount of invoice in Apr Payment Array.]

I have never used arrays so how do I set this up to add values inside of an array.

 

[Ngolem]

what version of Crystal are you using...CR 8.0 or higher? Jim Broadbent

 

[IdoMillet]

Many ways to skin that cat but here's a simple approach:

Join Invoice Table to Payments table (by Invoice Number) and group on Invoice date (by month).
Create a 2nd-level grouping by {Invoice_Number}
(not required, but makes the last step simpler).

A simple sum of the {Payment_Amt} would get you the
total payments for all invoices in that month.

The only problem is that if you sum {Invoice_Amt},
an invoice that had 3 payments would get summed 3 times.
To solve that problem, create a Running Total of {Invoice_Amt}and in its 'Evaluate' option set it to evaluate "On Change of Group": {Invoice_Number}

This ensures the Invoice Amount gets summed only once for
each invoice.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[Ngolem]

Ok...I will warn you that this applies to CR 8.0 and above

to initialize an array use something like these following formulas:

@Initialize(A) (suppressed placed in the report header)

WhilePrintingRecords;

//add about 50% more array elements than what you expect to
//get in reality...ie If you expect 50 invoices plan for 75
//the ... is just to indicate more array elements of same
NumberVar array invoice := [0,0,0,0,0,0,...,0];

//same considerations for payments

NumberVar Array payment := [0,0,0,0,0,0,...,0];
NumberVar iCount := 0; // place holder for invoices
NumberVar pCount := 0; // place holder for Payments

@Initialize(B) (suppressed placed in a group header)

WhilePrintingRecords;

if not inRepeatedGroupHeader then
(
NumberVar array invoice := [0,0,0,0,0,0,...,0];
NumberVar Array payment := [0,0,0,0,0,0,...,0];
NumberVar iCount := 0;
NumberVar pCount := 0;

);

the difference between @initialize (A) and (B) is that (B) will be reset in the Group but you don't want that reset to occur unless it is a new group...it depends on what you want.

I am not sure how you want to use this but let us say that we want to store the sum of the payments for each invoice for later printout. Then Array element Invoice[1] will have all its payments summed in Payment[1]...etc

this can be done in a formula like this


Summing Payments would be done here

@addinvoice&SumPayments (Suppressed in detail or other
section you want)

WhilePrintingRecords;
NumberVar array invoice ;
NumberVar array payment;
NumberVar iCount ;
NumberVar pCount ;

if {Table.invoice} in invoice then
(
for pCount := 1 to ubound(invoice) do
(
if invoice[pCount] = {Table.Invoice} then
payment[pCount] := payment[pCount] + Table.payment};
exit for;
);
)
else
(
iCount := iCount + 1;
invoice[iCount] := {Table.invoiceNum};
payment[iCount] := payment[iCount] + Table.payment};
);

Display of invoices and payments would depend on the look that you want

If there are a lot of invoices/payments then a formula for each is probably justified...I usuall convert numbers to text for simplicity of reporting

if total characters is less than 254 use one formula if more then determine how many you can fit into one formula then add another formula or more in following sections which are enabled with "suppress Blank section"

@DisplayInvoices (enable the "can Grow" property)

WhilePrintingRecords;
NumberVar array invoice ;
NumberVar iCount ;
numberVar m;
StringVar result;

for m := 1 to iCount do
(
result := result + invoice[m] + chr(13) + chr(10);
);
result;

Note: m iCount also equals the number of Payment totals to I think.


Anyway, this may not really solve your problem but it does show how you can use arrays in formulas

Hope this helps you Jim Broadbent