removing duplicate records from results???

[rbtnthmn]

i have a rather weird issue and without going into the nasty details of the report, ill try to overview it... the database is Btrieve and im connecting using the native Btrieve drivers (so no SQL expressions ).. i have the report finished except for one minor/major issue.. i need to remove the duplicate records in the report. while this would be normally fairly easy, its causing me some problems...

BKARINV table holds the invnum field and is left outer joined to WORKORD and WORKORD_1 (workdord alias) ... in the report i have my invnum's grouped by workstages... in workord table should be stage 1 items and if there is the item is in stage 2 then it will have another record in workord which i will pull from workord_1 ... i can get all of the records to display, but i only need to show the records that have a section and have a stage1 or stage2 ...

current report example

section1
item1 stage1
item2 stage1
section2
item1 stage2


what i need to have is

section1
item2 stage1
section2
item1 stage2


please let me know if there is anything that i can provide to explain/clear this up.. i know i didnt explain it very well...


TIA

 

[hilfy]

How are you linking your tables? Is it from BKARINV to both WORKORD and WORKORD_1? Or is it from BKARINV to WORKORD and from WORKORD to WORKORD_1?

I would set it up so that you're linking from WORKORD to WORKORD_1 as a left outer join. Then create a formula something like this:

{@Stage1 or 2}
if not isnull({WORKORD_1.invnum}) then
  {WORKORD_1.stage}
else
  {WORKORD.stage}

Use this formula in the details line instead of the fields.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[rbtnthmn]

thanks hilfy... ill give this a shot and see if it helps...

 

[rbtnthmn]

well that didnt help me much as the invum is never null.... but i did notice that if i take away the grouping on the stage.. i dont get any duplicates... if i put the group back, the dupes show up again..

although they are not true duplicates... they are the same invnum just in different stages, as the table has historical data tracking built into it.. (damn btrieve!!)

now i just have to figure out why the grouping is causing the duplicates.

 

[rbtnthmn]

im still not having any luck with this.. if anyone can help out, i can send you the report definition so you can see the big picture and how its laid out..

Thanks in advance.

 

[Madawc]

You want to show just the last record for each section? Then group by section, adjusting Report>Sort Expert. Suppress details. Put the record details in the Group Footer, which will show the last record in the group.

If it's more complex than that, an alternative is to suppress details, using suppression formulas like {your.field} = previous({your.field}). (Right-click and choose [Section Expert] to get to suppression options.)

Madawc Williams (East Anglia, UK)