select greater of two different date fields

[koolskins]

Crystal 10 against an Informix database. I have a report grouping purchasing records by the expected delivery date so that the output is grouped by LESS THAN 30 DAYS, 30-60 DAYS, 60-90 DAYS, etc. The expected delivery date is subtracted from the data date to determine which group the record falls into. However, there are actually two different EXPECTED DELIVERY DATES that come into play, one from the purchase order and another one from an expediting record. How, do I select and group each row based on the greater date between these two tables? Also, the expediting record may or may not exist.

Thanks in advance.

Rw.

 

[dgillz]

You didn't really give a record selection formula, but the way you would pull this dat out is this:

If isnull({ExpeditingDate}) then {PurchaseOrderDate} else maximum({ExpeditingDate},{PurchaseOrderDate})

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[koolskins]

Thanks. Appreciate the quick reply.

rw

 

[koolskins]

Okay, that fixed the field to display the greater of the two dates. The next request is to now make the displayed date part of the group criteria. The report now is grouping by the PO's original expected delivery date so that each record returned falls into the 30 day, 30-60 day, etc grouping. The formula for this group is:

if isnull({rp_head_line_700.exp_del_date}) then "Not Due" else
if {rp_head_line_700.exp_del_date}>currentdate then "Not Due" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) < 30 then "Less Than 30 Days" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) >29 and
(({rp_head_line_700.exp_del_date}-currentdate)*-1) <= 60 then "30-60 Days" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) > 60 and
(({rp_head_line_700.exp_del_date}-currentdate)*-1) <= 90 then "60-90 Days" else
if (({rp_head_line_700.exp_del_date}-currentdate)*-1) >90 then "Greater than 90 Days"


I attempted to replace the {rp_head_line_700.exp_del_date} with the new formula {@expd_del} but now get a "grouping on a non-recurring field" error???