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

[JustineK]

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?

 

[Turkbear]

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

Select Invono,Orderno,Amount
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.."

 

[Madawc]

An alternative method is to group by Invono, suppress the detail sections and show invoice details in the header or footer.

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

 

[JustineK]

Hi, unfortunately I can't use the Command option on my database.
I need the details to reflect and the grouping doesn't give what I need.
Thanks for the help though.

 

[lbass]

Your sample data does not make sense to me in relation to what you say you want to see. Is there an error in the explanation?

-LB

 

[JustineK]

Hi LB
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

 

[Turkbear]

Hi,
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.."

 

[lbass]

Please look again at your sample data. You are showing three different invoices all with the same order number--two have zeros and one has an amount, and yet you are saying you want to see this one.

-LB

 

[JustineK]

LB, That is exactly what I'm wanting to see, one invoice even there are 3 with the same order number.
Thanks

 

[Turkbear]

Hi,
Actually JustineK, LB has spotted a contradiction, you said

but if there was a voucher with the same order number and an amount the 0 voucher must not be shown.

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.."

 

[JustineK]

Hi Turkbear
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

 

[lbass]

Yes, there is a way to do this, but it still is not clear. Do you really just want to see one invoice and if it is non-zero show that one, otherwise show the zero one? What if there are multiple invoices with a zero amount and no non-zero amounts--still show just one or show all of them? What if there are multiple non-zero invoices and one zero invoice--show both non-zero invoices?

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

 

[JustineK]

Hi LB
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

 

[Turkbear]

Hi,still confused by your first example:

Invono Orderno Amount
1747507 1472523 0.00
1747508 1472523 0.00
1748922 1472523 866.67

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 amount exists??


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.."

 

[lbass]

I agree--still not clear.

-LB

 

[JustineK]

Hi
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

 

[lbass]

Okay, what about in these situations?

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

 

[JustineK]

This is what i'd expect to see =
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

 

[lbass]

Sort records by {table.amt} ascending. Then create a formula {@nonzero}:

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

 

[JustineK]

Hi LB
That worked!! Thanks soooo much!
Justine