Crystal 9- Formula to be used in a Sort.

[OrionStar]

Dear Esteemed Forum:

I need help on a formula please. Here's a sample of my data on the report.

ID# CODE INV# AMT
7765-123 DOG 100 100.00
7765-123 DOG 100 85.00
7765-345 DOG 101 200.00
7765-345 DOG 101 100.00
7765-345 CAT 101 50.00

An invoice can have more than ONE item. (ie DOG and CAT)

I need a formula that would identify all invoices that have BOTH (DOG and CAT) and then label them as "Both". I would like to do a SORT on this new item.

You help is greatly appreciated.

Mike

"A man is only as happy,... as he makes up his mind to be...

 

[OrionStar]

I'm sorry, let me separate the data MORE. Here is the example again.

ID# CODE INV# AMT
7765-123 DOG 100 100.00
7765-123 DOG 100 85.00
7765-123 DOG 102 100.00
7765-123 DOG 103 85.00
7765-345 DOG 101 200.00
7765-345 DOG 101 100.00
7765-345 CAT 101 50.00
7765-345 CAT 107 50.00

Our databases "key" off of the ID#, then INV#. ID# can have many INVOICE#'s under same ID.
Thanks again.
Mike

"A man is only as happy,... as he makes up his mind to be...

 

[Madawc]

Group invoices by ID. Then for each Group, do a pair of summary totals for Code. minimum and maximim. If it is only CAT and DOG, then you should get one of each.

Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total.. Or else use the Field Explorer, the icon that is a grid-like box.

You can then do a formula field that checks the group maximum and minimum.

if #MIN = "CAT" 
THEN IF #MAX = "DOG" THEN "BOTH" ELSE "CAT" 
ELSE "DOG"

This will definitely let each group be labelled. Hopefully it will also let you sort by them.

Madawc Williams (East Anglia, UK)

 

[OrionStar]

What is I'm trying to get that same result for INVOICE NUMBER? Group on INVOICE NUMBER?

Also I have numerous CODES to evaluate. I need to know of ALL of my invoices, how many had BOTH versus how many had ONE.

This is a simplified example of my data. We are in the legal field and have MANY, MANY codes.

THANKs again

"A man is only as happy,... as he makes up his mind to be...

 

[Madawc]

I'm assuming you group by the value shown as '7765-345'. As for codes, you'll need to do more elaborate tests, I suppose. Trial-and-error is usually necessary for Crystal.

Madawc Williams (East Anglia, UK)

 

[OrionStar]

Sure...thanks for your help... I'm trying your suggestion from previous post..and I'm making some advancement.

THANKS....MIke

"A man is only as happy,... as he makes up his mind to be...

 

[lbass]

What you are trying to do is still a little unclear. Are you saying you have to identify cats and dogs specifically, but that there might be other animals mixed in there, too? Or are you saying you need to identify any invoice that has more than one type of animal? You could do formulas like:

if distinctcount({table.code},{table.invoice}) = 2 then "both" else
if distinctcount({table.code},{table.invoice}) = 1 then
{table.code}//this assumes only two possible codes

Or, you could do formulas like {@code}:

if {table.code} = "dog" then 1 else
if {table.code} = "cat" then 100 else 0

Then create a second formula:

if sum({@code},{table.invoice}) > 101 and
remainder({@code},100) <> 0 then "both" else
if remainder({@code},100) = 0 then "cat" else
if sum({@code},{table.invoice}) < 100 then "dog"

A little more information would help determine the approach.

-LB