×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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?

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

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

CODE

Select Invono,Orderno,Amount
from MyTable
Where Amount = 0
and
Orderno NOT IN
Select Oderno
from MyTable
where Amount > 0;


 

profile

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

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.

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

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

(OP)
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.

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

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

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

(OP)
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

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

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.

 

profile

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

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

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

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

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

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

Quote (JustineK):


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.

 

profile

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

(OP)
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

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

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

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

(OP)
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

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

Hi,still confused by your first example:

Quote (JustineK):


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.

 

profile

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

I agree--still not clear.

-LB

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

(OP)
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

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

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

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

(OP)
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

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

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

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

(OP)
Hi LB
That worked!!  Thanks soooo much!
Justine

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close