Pull out additional data using minimum date

[kazcadman]

Can anyone help before i go mad...

I have the following data and i need to get a minimum of the dates, which i have done. However i then need to get the corresponding order and price to the min date to show in the group footer.

Detail lines:
13/06/2005 R000038 3,500
03/06/2005 R000035 3,500
20/05/2005 R000035 3,500
31/12/9999 R000008 3,500

Group footer:
20/05/2005

(31/12/1999 Is a dummy date to replace the nil value so i can get a minimum).

The minimum above from the detail section is shown as 20/05/2005 so i need to show R000035 on the group footer line. I wrote a formula as follows:

If Minimum ({@PODueDate}, {MBG110.CUSTNAME}) = {@PODueDate} then {MBD070.PORDER_ITEM}

If there are no dates like 31/12/9999 in the group then it pulls out the correct order number. However for the example above the result is blank.

Can anyone suggest a formula to solve this?

 

[kskid]

If you only want the 20/05/2005 record to show on your output, then simply click on Select Expert->Show formula->Group Selection->Formula Editor and add the following

Minimum ({@PODueDate}, {MBG110.CUSTNAME})

The results will be one record

20/05/2005 R000035 3,500

Cheeers,
-LW

 

[lbass]

If you need to show the detail records, also, then if you sort descending by {@PODueDate}, you could copy the detail fields into the group footer and they would show the correct values.

If you don't want to change the sort, then you could use a formula like the following to return the item number. Then you could create another formula:

whileprintingrecords;
stringvar minrecord;

if minimum({@PODueDate}, {MBG110.CUSTNAME}) = {@PODueDate} then
minrecord := {MBD070.PORDER_ITEM} else
minrecord := minrecord;

Then in the group footer use:

whileprintingrecords;
stringvar minrecord;

minimum({@PODueDate}, {MBG110.CUSTNAME}) + " " + minrecord

-LB