How to display a result only if two fields meet a certain criteria

Hi,

I am trying to create a formula that will only display results if one field is not equal to null and the other field is = to shipped. I am not quite sure on how to do this. How can I accomplish this?

Thanks
wayy2beAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prequel_serverCommented:
Select column1, column2, ...

FROM Table

WHERE

column1 IS NOT NULL

AND

column2='Shipped'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
In Crystal you can create a formula like

If IsNull({Table.field1})  AND {Table.field2} = 'Shipped'  then
    42

mlmcc
0
wayy2beAuthor Commented:
Thanks for the replies. I don't believe I stated the question correctly. If I needed a formula to only display records that had two attributes in common how can I accomplish this?  For example I want to display records for customers who are in our VIP club and who have purchased X product.  I apologize for the confusion.  Also I am very new to Crystal so if you can provide a step by step answer explaining where and how to insert the formula into the report that would be great.  Thanks so much.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

mlmccCommented:
Try this

Open the report
Click REPORT  --> SELECTION FORMULAS --> RECORD
Enter the formula

IsNull({Table.field1})  AND {Table.field2} = 'Shipped'  

Click SAVE AND CLOSE
Run the reort

mlmcc
0
wayy2beAuthor Commented:
Thanks for your reply. Actually I misstated the requirement, please see above.  Thanks.
0
mlmccCommented:
That is what the record selection is for.

How do you determine membership in VIP club?

How do you know they ordered a product?

mlmcc
0
wayy2beAuthor Commented:
The table has a field for both and we would need to set it to be = to VIP and product X.
0
mlmccCommented:
{VipField} = 'VIP' and {ProductField} = 'ProductName'

mlmcc
0
wayy2beAuthor Commented:
So just the following will list all records with both fields equal to VIP and Product Name?

{VipField} = 'VIP' and {ProductField} = 'ProductName'
0
mlmccCommented:
It should.  Be sure to use your database field names in place of VipField and ProductField

mlmcc
0
wayy2beAuthor Commented:
Sorry I was away. No this does not work, returns no data.
0
James0628Commented:
mlmcc's suggestion sounds good, but we don't really know anything about your data.  For one thing, you mentioned customers and products -- Are the customer and product fields that you're checking both in the same table, or separate tables?  If they're in separate tables, how are they linked?

 If mlmcc's suggestion didn't give you any records, what happens if you try each part separately?

 Try {field1} = "VIP" and see what you get, then try {field2} = "productX" and see what you get.  Obviously, replace the field names and values with your field names, and the values you're actually looking for.

 You are entering those tests in the record selection formula, correct?  For example, Report > Selection Formulas > Record.

 James
0
wayy2beAuthor Commented:
The fields are in the same table. I may not be explaining correctly. What I need is that if a field has VIP and another field has Product X then display those records that meet that criteria. For example VIP and Product B, no data is displayed.
0
mlmccCommented:
Are they in the same record?

mlmcc
0
James0628Commented:
If the two values that you're looking for are in the same record, then it really should be just as simple as the formulas that mlmcc and you posted earlier.  You should be able to just go to Report > Selection Formulas > Record and enter a formula like this:

{field1} = "some value" and {field2} = "some other value"

 If the fields could be null, you may need to test for that, but apart from that, it should be just that simple.  Which is why we keep asking these other questions (how many tables, are the values in the same record, and so on).  What you described seems pretty basic, so if it's not working, we're missing something.

 James
0
wayy2beAuthor Commented:
Sorry for the delay.  I will test it again and report back on Monday.  Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.