Need help suppressing a record !!

[boardburner]

using CR9
pulling from VFP (6.something) thru ODBC

from current report; database > Show SQL Query

 SELECT DISTINCT `ipart`.`ippart`, `ipart`.`ipdesc`, `ipart`.`ipclas`, `ipart`.`ipqoh`, `ipart`.`ipqal`, `ipart`.`ipminq`, `ipart`.`iptype`, `hpodt`.`hdpo`, `hpodt`.`hdpum`, `hpodt`.`hdrecd`, `hpodt`.`hdinvc`, `hpodt`.`hdhist`, `hpodt`.`hdircd`, `hpodt`.`hdpqty`
 FROM   `ipart` `ipart` LEFT OUTER JOIN `hpodt` `hpodt` ON `ipart`.`ippart`=`hpodt`.`hdpart`
 WHERE  (`ipart`.`ipclas`='ALUMI' OR `ipart`.`ipclas`='CARBO' OR `ipart`.`ipclas`='TOSTL') AND `ipart`.`ipqal`>0
 ORDER BY `ipart`.`ipclas`, `ipart`.`iptype`, `ipart`.`ippart`

Stage 1; the report is calling in all part #'s {ipart.ippart} of specific class {ipart.ipclas} from an inventory table {ipart} that have an allocations {ipart.ipqal} to a project.
Here is code from "selection expert"

{ipart.ipclas} in ["CARBO", "ALUMI", "TOSTL"] and
{ipart.ipqal} > 0.00000

Stage 2; the report is calling in from the purchase detail table {hpodt} the PO# based on the formula ({@POIssue})

//allocation >0
if {ipart.ipqal}>0
//PO not Rev'd Complete
and {hpodt.hdrecd}=false
//Inv not Rec'd Complete
and {hpodt.hdinvc}=false
//History not closed
and {hpodt.hdhist}<>"H"
//Inv Qty Recv'd
and {hpodt.hdircd}=0
//return PO #
then {hpodt.hdpo}
else 0

The {ipart} table is left outer join to the {hpodt} table by part #.

The report is grouped by class.

Today this report generates appr 425 records, of which I need to see only 32 that have an allocation or open PO. I get multiple records because of various conditions in the PO table referencing each part #. I was suppressing the duplicates by using this formula in the details section,

//check for open PO
if {@POIssue}=0
// suppress duplicate part records
then{ipart.ippart}=previous({ipart.ippart})

This method gives me what I want "except" I get an extra record for each part that has an actual PO issued. I need a way to get only the one entry, the one showing the PO#.

I have tried to use some of the conditions stated in "stage 2" in the "selection expert" but I loose part#'s that have allocations.

If this is not enough/right info...let me know

Thanks

 

[Madawc]

In Crystal 8.5, there is an option to use a formula to suppress printing of a section. Does this help?

Madawc Williams
East Anglia, Great Britain

 

[QueSpr04]

You might want to upgrade to CR10. In CR10, there is an option there to suppress any record or formula displayed on your report.