Record Supprerssion Quirk

[boardburner]

Using CR9
I have used the following formula in the detail section to suppress duplicate records ..BUT.. I also get a single extra record for each record that has an open PO condition. In each instance where there is a PO how can I get only the (1) record showing the PO issued?

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

It is almost like I need to re-run

{ipart.ippart}=previous({ipart.ippart})

again "after" to get rid of the extra record that doesn't have the PO ??

Thanks

 

[synapsevampire]

Since you didn't share what's in the formula ({@POIssue})your formula references, it's difficult to help.

Eliminating duplicates can m ean lots of things to different people, but a duplicate means that all columns are the same, which isn't the case here I think.

Anyway, post what's in any formulas that you reference...

To eliminate the duplicates at a higher level, use the Report->Edit Selection Formula and place the criteria in there, such as:

{table.field} = "Some value"

If this doesn't help, please post meaningful technical information:

Database/connectivity used
Example data
Expected output
Any formulas in use

-k

 

[boardburner]

ok..lets try this

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

from; 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 ({@OIssue})

//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