Comparing one field to the next fields.

[numina]

For the last formula I need on my report, this a rather long question. The task is to convert a program into Crystal. The code stores the result of a query into multiple arrays, then does some calculations on the values stored in them and prints the results. To make the question simpler, I'll only use the required arrays.

Actions[X] and Dates[X]

Actions[1] = "DTA" Dates[1] = "04/01/02"
Actions[2] = "PAY" Dates[2] = "01/01/02"
Actions[3] = "PRO" Dates[3] = "10/01/01"
Actions[4] = "PAY" Dates[4] = "07/01/01"
Actions[5] = "JRC" Dates[5] = "04/01/01"
Actions[6] = "PAY" Dates[6] = "03/01/01"

The goal is to calculate the number of months between last pay rate change when action=PAY and the currently positioned record otherwise print "No Change in Pay".

The results from the sample above should look like this:
Results[1] = "No Change in Pay"
Results[2] = "# of Months: 6"
Results[3] = "No Change in Pay"
Results[4] = "# of Months: 4"
Results[5] = "No Change in Pay"
Results[6] = "No Change in Pay"

My question is, is there a way of storing data into an array, then access it later. I used a global array to store the values (WhilePrintingRecords), then in another formula, the current record is compared to each value in the global array. However, the results I get:

Results[1] = "No Change in Pay"
Results[2] = "# of Months: 0"
Results[3] = "No Change in Pay"
Results[4] = "# of Months: 0"
Results[5] = "No Change in Pay"
Results[6] = "No Change in Pay"

I know that this occurs because even the global array is only returning the value of the current record and is not pre-storing all values.

I would greatly appreciate any pointers.

numina

 

[dgillz]

There are the functions aprevious({field}) and next({field}) in Crystal. Does this help your thinking any?

Also, please post your formula and please tell mw where on the report your formula is placed. Variables in CR evaluate differently depending on where they are placed on the report. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[numina]

Here are the formulas with @Set Arrays placed in 'Details a' and @Calculate Months in 'Details b':

//@Set Arrays

WhilePrintingRecords;
Global StringVar Array Actions;
Global dateVar Array Dates;

redim Actions[6];
redim Dates[6];

// Store Actions into Actions Array
if RecordNumber < 6 then
Actions[RecordNumber] := {JOB_HISTORY.ACTION}
;
// Store Dates into Dates Array
if RecordNumber < 6 then
Dates[RecordNumber] := {JOB_HISTORY.EFFDT}

//@Calculate Months

EvaluateAfter({@Set Arrays});

Global StringVar Array Actions;
Global dateVar Array Dates;

Local NumberVar Temp_Index;
Local NumberVar diff;
Local StringVar text;

// Compare data and set output

for Temp_Index := RecordNumber + 1 to 6 do
(
if (Actions[Temp_Index]) = 'PAY' then
diff := Dates[RecordNumber] - Dates[Temp_Index];
Exit For
);

if diff = 0 then
text := &quot;No Change in Pay&quot;
else
text := &quot;# of Months: &quot; + toText(diff);

Correction to my previous message, all results show as:
&quot;No Change in Pay&quot;.

Many Thanks!
numina

 

[datamonkey]

This looks a lot like the data from our PeopleSoft system. I don't know what your exact scenario is but you may concider simply using a formula with some variables and nested IF logic. You will find that when you place formulas on the detail section of a report it has the effect of stepping through a recordset (this is why the previous() and next() function work). As your formula steps through the records you can store and perform your date calculations using variables and a nested 'IF'. Use the previous() function to evaluate when you have moved to the next employees data. Once you master this approach you will write a lot less code.

Good Luck

 

[numina]

Using the next function I will only have access to the record directly following the current record, whereas I need access to all records following the current record (for comparison). I thought this is possible because during the second pass, all rows are retrieved and can be accessed if I use WhilePrintingRecords.

Please let me know if there is actually a way of doing this. If there is, I would greatly appreciate it if you could elaborate.

Thanks and Regards.

numina.