## Removing duplicate orders if one order has 0 amount and another has an

## Removing duplicate orders if one order has 0 amount and another has an

(OP)

I have a document that includes all orders done for billback vouchers, this includes vouchers with values and without values. I need to be able to run the report so that the only information I receive are the orders with 0 amount, but if there was a voucher with the same order number and an amount the 0 voucher must not be shown.

For example, this is the original document =

Invono Orderno Amount

1747507 1472523 0.00

1747508 1472523 0.00

1748922 1472523 866.67

This is what I would want to see

Invono Orderno Amount

1747507 1472523 0.00

Is there a formula or another way I can do this in Crystal Reports?

For example, this is the original document =

Invono Orderno Amount

1747507 1472523 0.00

1747508 1472523 0.00

1748922 1472523 866.67

This is what I would want to see

Invono Orderno Amount

1747507 1472523 0.00

Is there a formula or another way I can do this in Crystal Reports?

## RE: Removing duplicate orders if one order has 0 amount and another has an

If your database supports using a Command, then a SQL query could be used:

## CODE

from MyTable

Where Amount = 0

and

Orderno NOT IN

Select Oderno

from MyTable

where Amount > 0;

To Paraphrase:"The Help you get is proportional to the Help you give.."

## RE: Removing duplicate orders if one order has 0 amount and another has an

You don't say whether you want details if all the invoices are zero. What you could do is make a summary total showing the Maximum for the amount. The use Group Selection to suppres those with a maximum of zero, if that's what you want.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

## RE: Removing duplicate orders if one order has 0 amount and another has an

I need the details to reflect and the grouping doesn't give what I need.

Thanks for the help though.

## RE: Removing duplicate orders if one order has 0 amount and another has an

-LB

## RE: Removing duplicate orders if one order has 0 amount and another has an

No, there isn't an error.

I receive all the invoices done for the vouchers. Some invoices have 0 amounts and some invoices have figures. When the order numbers are the same for an invoice with 0 amount and for an invoice with an amount, I don't want those 2 invoices to reflect. There will be times where the Order numbers are the same, but the invoice amounts are all 0 and I need all those invoices to show.

Does that make more sense?

Thanks J

## RE: Removing duplicate orders if one order has 0 amount and another has an

try this:

Link the table to itself ( CR will create an Alias for it) by OrderNo with a standard Equi-Join.

Set a selection formula

on the right-hand version of your table ( The Aliased one) of

Amount > 0

and

on the Original table

of

Amount = 0

This should only return records where the Amount is 0 and where no matching invoice has an amount > 0.

To Paraphrase:"The Help you get is proportional to the Help you give.."

## RE: Removing duplicate orders if one order has 0 amount and another has an

-LB

## RE: Removing duplicate orders if one order has 0 amount and another has an

Thanks

## RE: Removing duplicate orders if one order has 0 amount and another has an

Actually JustineK, LB has spotted a contradiction, you said

OrderNo 1472523 has 3 entries and one of them IS an Amount > 0, so you should not, according to your statement of requirements, show that 0 voucher at all.

To Paraphrase:"The Help you get is proportional to the Help you give.."

## RE: Removing duplicate orders if one order has 0 amount and another has an

The invoice with the 0 amount mustn't show if there is another invoice with an amount. If there is no invoice with an amount, then the 0 amount invoice must show.

Make sense?

I've looked at this from many angles, but somehow I don't think there is a way to get the information I'm looking for.

Thanks

Justine

## RE: Removing duplicate orders if one order has 0 amount and another has an

What would you expect to see for the following samples?

Invono Orderno Amount

1747507 1472523 0.00

1747508 1472523 0.00

1748922 1472523 866.67

1747512 1472521 0.00

1747513 1472521 300.00

1748914 1472521 500.00

1747515 1472526 0.00

1747516 1472526 0.00

1748917 1472526 0.00

-LB

## RE: Removing duplicate orders if one order has 0 amount and another has an

Here is what I would like to get out from your samples given =

Sample 1 =

Invono Orderno Amount

1747507 1472523 0.00

Sample 2 =

Invono Orderno Amount

NOTHING

Sample 3 =

Invono Orderno Amount

1747515 1472526 0.00

1747516 1472526 0.00

1748917 1472526 0.00

hope that helps.

Thanks

Justine

## RE: Removing duplicate orders if one order has 0 amount and another has an

You indicated that ONLY

1747507 1472523 0.00

should be seen on the report...

Why that one?

Invono 1748922 has an amount and the same Orderno as the others, so why show 174507, since an OrderNo 1472523

with an amountexists??Same with the results you show for LB's sample data..Multiple invonos same OrderNo but you select one

in Sample 1 even though it violates your stated rule.

To Paraphrase:"The Help you get is proportional to the Help you give.."

## RE: Removing duplicate orders if one order has 0 amount and another has an

-LB

## RE: Removing duplicate orders if one order has 0 amount and another has an

If there are two 0 value invoices (invoice nbrs are different) and one invoice without an amount, I still need once 0 value invoice to reflect. The one 0 value invoice and the one invoice with an amount contra each other. There isn't anything saying which 0 value invoice needs to show. Make anymore sense? If there were two 0 value invoices and two invoices with amounts, then no invoices must reflect.

Hope that helps.

Thanks

Justine

## RE: Removing duplicate orders if one order has 0 amount and another has an

Invono Orderno Amount

1747507 1472523 0.00

1747508 1472523 0.00

1747509 1472523 0.00

1748922 1472523 866.67

1747512 1472521 0.00

1747512 1472521 0.00

1747513 1472521 300.00

1748914 1472521 500.00

1747515 1472526 0.00

1747516 1472526 0.00

1748917 1472526 0.00

1749223 1472526 0.00

What would you expect to see? Is the rule that you want to see those zero amounts where this isn't a matching number of non-zero amounts?

-LB

## RE: Removing duplicate orders if one order has 0 amount and another has an

Option 1=

Invono Orderno Amount

1747507 1472523 0.00

1747508 1472523 0.00

Option 2=

Invono Orderno Amount

(nothing)

Option 3=

Invono Orderno Amount

1747515 1472526 0.00

1747516 1472526 0.00

1748917 1472526 0.00

1749223 1472526 0.00

Yep, basically, if there's one invoice with 0 and one with an amount, neither must show and so on as per the above options.

Thanks

JK

## RE: Removing duplicate orders if one order has 0 amount and another has an

if {table.amt} > 0 then 1

Also create a formula {@zero}:

if {table.amt} = 0 then 1

Then create a running total ({#cntwingrp} that counts {table.invno}, evaluates for each record and resets on change of group: {table.orderno}.

Then go into the section expert->details->suppress->x+2 and enter:

sum({@zero},{table.orderno}) <= sum({@nonzero},{table.orderno}) or

(

{#cntwingrp} <= sum({@nonzero},{table.orderno}) and

{table.amt} = 0

)

-LB

## RE: Removing duplicate orders if one order has 0 amount and another has an

That worked!! Thanks soooo much!

Justine