How to print one field from one to many link in the detail section

[BobtheDog]

I hope I can explain this.
I have a table of dates for the main record. I need to print these dates on the detail line.
example: for one Shippment order there are up to five records in the date table,
EstimatLoadDate1
EstimateLoadDate2
EstimateDelivery1
EstimateDelivery2
ActualDeliveryDate
The detail line would have the Shippment number, EstimateLoadDate1, EstimateDelivery1, and ActualDeliverydate.
I guess I need to know how to capture the values if they are there and save them for print time.
Thanks for any help.
Bill

 

[lbass]

Link your tables with a left join from the main table to the dates table. This will allow orders to appear even if there are empty date fields.

Since the dates appear to be stored in different fields, if you place the fields in the detail section, they should display in one line per order. If this does not give you the results you want, it would be helpful if you would show how the current report displays and how you would like it be.

-LB

 

[BobtheDog]

I do have a left join set up. The report is grouped by Customer and I would like just one line per order with the dates found in the date file for that order. I keep thinking that I need to capture the values in the group section and save them to print in the detail line.
I'm kinda new to Crystal yet and I am wading through the functions looking for a way to do this. I tend to get sidetracked this way though.
Bill

 

[lbass]

Please put the fields in the detail section like this:

{Main.ShipNo} {Date.EstLoadDate1} {Date.EstDelivDate1} {Date.ActDelivDate1}

And then tell us what your results look like, because right now it is hard to tell what the problem might be. Thanks.

-LB

 

[BobtheDog]

Okay, I get a value for {Main.ShipNo} and{Date.EstLoadDate1} but the other two dates are blank. The reason I get EstLoadDate1 is that it is the last date record found. I should add that each date record has a code attached to indicate the type of date. I have to use a conditional to determine which date it is so the if statement is only giving me whatever the last date record is.
Sorry about not giving all the info at once. Thanks for bearing with me.
Bill

 

[lbass]

So there is only one date field? When you say each date record has a code attached I assume you mean there is a separate field which indicates type of date like this:

Type of Date Date
EstLoadDate 4/11/03
EstDelivDate 4/15/03

I think the easiest approach might be creating this as a crosstab report:

1-Select {Main.shipno} for your row and {Date.type} for your column.

2-For your summarized field, select {Date.date} and then select "change summary" and select "Nth most frequent" with N = 1. This will result in display of the date.

If you want to get rid of the grid lines, right click in the upper left corner of the crosstab and then click on format crosstab->customize style tab->format grid lines and uncheck "show grid lines."

Please let me know whether this works for you, or if you need further assistance.

-LB

 

[Ngolem]

Ok...so your recordlooks something like this

{shipmentNo}{DateField}{DateType}

Ok...you can show all the dates this way....in the Shipment footer. In the details section you will trap each date if it exists.

You need the Left outter join in case there are NO DATES entered at all.

You start Grouping your report by

Group 1 header - customer
Group 2 header - Shipment No (place an initialization formula...suppressed)
Group 3 header - Datefield
Details - (Suppressed) (place data accumulation formula)
Group 3 footer - (Suppressed)
Group 2 footer - (Place the display formula)
Group 1 footer -

In Group 2 header You place the following formula that is suppressed

@initialization

WhilePrintingRecords;

if not inRepeatedGroupHeader then
(
StringVar EstimateLoadDate1 := "";
StringVar EstimateLoadDate2 := "";
StringVar EstimateDelivery1 := "";
StringVar EstimateDelivery2 := "";
StringVar ActualDeliveryDate := "";
)

These are made strings since they are easier to work with later.

Now in your Details section (suppressed) you put your accumulation formula

@AccumulateDates

WhilePrintingRecords;

StringVar EstimateLoadDate1 ;
StringVar EstimateLoadDate2 ;
StringVar EstimateDelivery1 ;
StringVar EstimateDelivery2 ;
StringVar ActualDeliveryDate ;

If {table.DateType} = "EstimateLoadDate1" then //whatever the code is
EstimateLoadDate1 := totext({table.date},"dd/MM/yyyy"&quot
else If {table.DateType} = "EstimateLoadDate2" then
EstimateLoadDate2 := totext({table.date},"dd/MM/yyyy"&quot
else If {table.DateType} = "EstimateDelivery1" then
EstimateDelivery1 := totext({table.date},"dd/MM/yyyy"&quot
else If {table.DateType} = "EstimateDelivery2" then
EstimateDelivery2 := totext({table.date},"dd/MM/yyyy"&quot
else If {table.DateType} = "ActualDeliveryDate " then
ActualDeliveryDate := totext({table.date},"dd/MM/yyyy""

This traps and puts the date in whatever format you like

Then you just create display formulas for each date to be placed in The Shipment No. footer

@Display EstimateLoadDate1
WhilePrintingRecords;
StringVar EstimateLoadDate1 ;

EstimateLoadDate1 ;

Do one like this for each variable.

This should work fine.


Jim Broadbent

 

[BobtheDog]

Thanks everyone.