Running Totals Previous record

[turtlepatch]

I am working in Crystal 9.2 with an ODBC database. I have data that displays Job-id's, Location-id's, and Date of location change. Each job will have several location id's and I am trying to count the number of jobs in each location at the time of the prompted date. For example:

job-id location-id date
1 26 05/01/2006
1 28 05/04/2006
2 28 05/01/2006
2 90 05/02/2006
2 90 05/04/2006
3 28 05/04/2006

The following is an example of a Running Total that I have that is counting the number of jobs in the Preflight location (28). If a job is in the On Hold location (90) I want to be able to count the location that precedes it.
----------------------------------------------
#Preflight
{Job.Job-ID}<>next({Job.Job-ID}) and
{JobLocationChange.Locate-ID}="28" or

{Job.Job-ID}<>next({Job.Job-ID}) and
{JobLocationChange.Locate-ID}="90" and
{Job.Job-ID}=previous({Job.Job-ID}) and
previous({JobLocationChange.Locate-ID})="28"
----------------------------------------------

So if you follow the example above I should have:
#Preflight = 3
I am having two problems: It isn’t counting JobID 3 when it is the last job in the list and when I have two occurances of On Hold in a row. It can’t do a previous(previous(LocateID)).

I'm not sure how to fix these two problems. Any help is greatly appreciated.

Thanks
turtle_patch

 

[lbass]

Try:

(
(
onlastrecord or
{Job.Job-ID}<>next({Job.Job-ID})
) and
{JobLocationChange.Locate-ID}="28"
)or
(
{JobLocationChange.Locate-ID}="90" and
{Job.Job-ID}=previous({Job.Job-ID}) and
previous({JobLocationChange.Locate-ID})="28"
)

This assumes that the location IDs occur in ascending sequence and that "90" is the maximum value that can occur for this field. If this is true, then the formula can safely evaluate without the location ID being the last in the job ID group.

-LB

 

[synapsevampire]

What does " I am trying to count the number of jobs in each location at the time of the prompted date." mean, and how would we know the prompted date? Do you mean a date parameter?

There's no reference to one in your code...

You posted example data and one exampleof the expected output, except that you pseak of dates and then disregard them.

I'll assume that the dates have nothing to do with anything, you're just referencing them in the record selection and we can ignore that.

Since there are 3 28's, I also don't understand what the 90's have to do with anything.

If you're just going to ignore the 90's and take the previous ID, why not eliminate the 90's from the data in the record selection?

If you group by the job, and in the Report->Selection Formula->Record and place:

{Job.Job-ID} <> "90"

Then in the Report->Selection Formula->Group place:

{Job.MyDate} = maximum{{Job.MyDate},{Job.Job-ID})

You'll only see the latest of each.

Now to count those in 28, create a formula for the group footer of:

whileprintingrecords;
numbervar count28;
if {Job.Job-ID} = "28" then
count28:=count28+1

Then in the report footer show the total:

whileprintingrecords;
numbervar count28

-k

 

[turtlepatch]

Thanks to both of you for your help. I added in the onlastrecord and that worked perfectly. I also excluded the On Holds...never thought to remove them. Thanks both.
turtle_patch