How do I get a specific value from several records 1

[BradW]

I am completing a report that uses two tables with a one to many relationship.

The primary table is a list of orders, with one record per order.

The second table is a list of actions on each order and has many records per order. One record tells when the order was placed, one for when the order was filled, one when the delivery truck left the depot, one when the delivery truck completed the delivery.

Each of these action records has a record type and date and time for when the action was taken.

I have a report that has a group by order number with details about the order in the group header and then the action records in the details section.

I need to pick out two records to calculate a time difference. For example, I need to determine the time that the truck left the depot and when the order was delivered and calculate the difference.

I have tried to do this using two variables, but it does not work for me. Can anyone please point me in the right direction?

Thanks much for your help! Have a great New Year!

 

[kenhamady]

There are a couple of ways to do this with and without variables. Show your formula and tell us what didn't work.

Also include:
1) The version of CR
2) How you identify the two records you need.
3) If the difference can be after the details or if it needs to be before. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[BradW]

Ken,

Thanks for your quick response!

1) I am using CR 8 Pro

2) There is another field in the record that identifies the type of record. The 3rd and 4th characters of that field identify what type of record it is. In this case, if the 3rd and 4th characters are EN then delivery is "en-route", if the 3rd and 4th characters are "AR" then the delivery has arrived.

3) My preference would be to put the difference in the group footer, but I can probably work around that if I need to.

One other kicker that I did not mention is that the delivery may be split onto more than one truck, I need to identify the FIRST truck to leave and the first truck to arrive and use that calculation time, so there could be multiple EN and AR records. The time field is called AUDSECS and is a decimal time that I can do calculation to determine the difference. I can then convert that to a time format.

The way that I have been approaching this is to do one formulat to figure the en-route time and one formula to figure the arrival time, this is as far as I've gotten. Once I can get these to work, I can do the math on the two values and format them to be readable as a time.

The formula that I have that determines the en-route time is:

NumberVar DISP:=0;

If {Audit.AUDSTATTXT}[3 to 4]="EN"
Then DISP:={Audit.AUDSECS}

The formula to determine the arrival time is:

NumberVar ARR:=0;

If {Audit.AUDSTATTXT}[3 to 4]="AR"
Then ARR:={Audit.AUDSECS}

Thanks again for your help, I really appreciate it!

 

[kenhamady]

I will assume that you determine the first by the lowest value of the AudSec field. You could do this to get Arrival:

WhilePrintingRecords;
NumberVar Arr;
If {Audit.AUDSTATTXT}[3 to 4]="AR"
and {Audit.AUDSECS} < Arr
then Arr := {Audit.AUDSECS}
else Arr := Arr
//you many not need this else line but I always use an else

Create the equivalent for DISP and place these formulas on details (suppressed probably).

In the group header you will need another formula to reset Arr and Disp to zero for the each group:

WhilePrintingRecords;
NumberVar Arr:= 0;
NumberVar Disp:= 0;

In the group footer you can write a formula that uses the variables to get the net. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[BradW]

Ken, worked like a charm, thanks so much for the help and have a great New Year!