Insert a field from the next record into current Detail line

[MyComputerGuy]

I am working on a report listing release dates and quantities for products (see thread149-1041264)

Currently, these fields exist in the database:
Job#, Release_Date, Release_Qty, Remaining_Qty. I have created a sub-report and calculated a subtotal for 'Remaining_Qty' within each Job#. I want to add a column to see the NEXT release date for each job if the 'Release_Qty is less than the 'Total_Remaining_Qty'.

Sorted by Date now shows:
Job # Release_Date Release_Qty Total_Remaining_Qty
(from Sub-Report)
12345 01/01/2005 100 400
12999 01/02/2005 200 800
12345 03/01/2005 100 400
12999 03/02/2005 200 800
12345 06/01/2005 100 400
12999 06/02/2005 200 800
12345 06/15/2005 100 400
12999 12/02/2005 200 800

I would like the report to show:
Job # Release_Date Release_Qty Total_Remaining_Qty Next-Release
(from Sub-Report)
12345 01/01/2005 100 400 03/01/2005
12999 01/02/2005 200 800 03/02/2005
12345 03/01/2005 100 400 06/01/2005
12999 03/02/2005 200 800 06/02/2005
12345 06/01/2005 100 400 06/15/2005
12999 06/02/2005 200 800 12/02/2005
12345 06/15/2005 100 400 (blank, no more releases)
12999 12/02/2005 200 800 (blank, no more releases)


The purpose of this is to quickly see that by 06/01 we need to produce 100 of Job# 12345 but it makes sense to produce the additional 100 for release on 06/15 at the same time , however releases for jobs may be separated by several pages in this report.

Thanks All....

 

[IdoMillet]

Use a linked subreport, an SQL expression, or use a command with a correlated subquery as the data source.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MyComputerGuy]

Thanks for the reply but I'll need more detailed info. I've done these types of reports years ago but have gotten rusty. The real trick will be selecting data from the next record to be included onto the same data line as the current record. All help is appreciated.

 

[Madawc]

Crystal allows you to look at Next or Previous. Try

If {Job.no} = next({Job.no}) 
then totext(next({Release_Dat})
else "blank, no more releases"

Madawc Williams (East Anglia, UK)

 

[IdoMillet]

The NEXT() function wouldn't apply to this case because the situation requires access to a "next" record that could be several records away...

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Naith]

You can utilise the NEXT approach if you group your on your job number, and order by release date ascending first.

 

[MyComputerGuy]

Thanks, I'll have to play with CR a bit more to figure out how & where to enter the code into a sub-report, but this helps.

 

[MyComputerGuy]

Sorry All, I'm still stumped.
I've created a subreport, Grouped by Job.Job .
I am using 3 tables; CUSTOMER, JOB, & DELIVERY.
Links are CUSTOMER.CUSTOMER - JOB.CUSTOMER, JOB.JOB - DELIVERY.JOB

I'm trying to add the folowing SQL Expression field within the subreport:
If {Delivery.Job} = next{Delivery.Job}
then totext(next({Delivery.Promised_Date}))
else "blank, no more releases"

This results in 'Malformed GUID' error. Where am going wrong?

 

[lbass]

Don't use a SQL expression, instead create the formula in the formula editor. Link the subreport to the main report on job #. Try:

If {Delivery.Job} = next({Delivery.Job})
then totext(next({Delivery.Promised_Date}),"MM/dd/yyyy")
else "blank, no more releases"

-LB