record selection formula 1

[mbDutch]

Alrighty – this one is fairly similair to my previous question the only difference now is that I need to apply it to record selection rather then a sum formula:

I have 1 group which is Group 1: Branch
The detail line contains 3 fields which are Type - Code - Amount

Some sample data of the detail section:

Type Code Amount

Customer ? 500
Customer ? 20
Customer Customer 0
Customer ? 40
Customer Customer 250

Now what I want to do is only display the records where Type and Code are both Customer. Easy enough. However, there are instances where the detail section will look something like this:

Type Code Amount

Customer ? 400
Customer ? 80
Customer ? 0
Customer ? 0
Customer ? 10

Now because none of the records have both Type and Code as "Customer" I want it to display ALL the records. So what I want my selection formula to do is look in the detail section to see if there is a match between Type and Code where they are both "Customer" and if so display just the records where there is a match, however, is there is no match between Type and Code I want it to display all the records. Possible?

Any help would be greatly appreciated.
Thanks

 

[PatriciaObreja]

You can do it in database:

if exists (select * from Customer
where Type='Customer'
and Code='Customer')
(select * from Customer where Type='Customer')
else
(select * from Customer)

 

[mbDutch]

Thank you. Unfortunately that's not an option for me. All I have is a crystal reports odbc to a progress database.

 

[PatriciaObreja]

Can you create a Command with this SQL statement?

 

[musini]

Do you have the option of using sql expression in the field explorer. If so look at this thread may be this will help you.

http://www.tek-tips.com/viewthread.cfm?qid=1443556&page=2

M

 

[lbass]

From that previous thread:

Create a formula {@match}:

if {table.type} = {table.code} then {table.amt}

Then go to report->selection formula->GROUP and enter:


sum({@match},{table.branch}) = 0 or
(
sum({@match},{table.branch}) <> 0 and
{table.type} = {table.code}
)

-LB