Solved

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

Posted on 2014-10-14
16
119 Views
Last Modified: 2015-01-06
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
0
Comment
Question by:wayy2be
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 3

Accepted Solution

by:
prequel_server earned 250 total points
ID: 40381365
Select column1, column2, ...

FROM Table

WHERE

column1 IS NOT NULL

AND

column2='Shipped'
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 40381409
In Crystal you can create a formula like

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

mlmcc
0
 

Author Comment

by:wayy2be
ID: 40383288
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 100

Expert Comment

by:mlmcc
ID: 40383477
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
 

Author Comment

by:wayy2be
ID: 40383487
Thanks for your reply. Actually I misstated the requirement, please see above.  Thanks.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40383533
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
 

Author Comment

by:wayy2be
ID: 40383596
The table has a field for both and we would need to set it to be = to VIP and product X.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40384167
{VipField} = 'VIP' and {ProductField} = 'ProductName'

mlmcc
0
 

Author Comment

by:wayy2be
ID: 40393422
So just the following will list all records with both fields equal to VIP and Product Name?

{VipField} = 'VIP' and {ProductField} = 'ProductName'
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40393542
It should.  Be sure to use your database field names in place of VipField and ProductField

mlmcc
0
 

Author Comment

by:wayy2be
ID: 40416559
Sorry I was away. No this does not work, returns no data.
0
 
LVL 35

Expert Comment

by:James0628
ID: 40417022
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
 

Author Comment

by:wayy2be
ID: 40434053
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40434269
Are they in the same record?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40434281
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
 

Author Comment

by:wayy2be
ID: 40484044
Sorry for the delay.  I will test it again and report back on Monday.  Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB RDLC Report 1 104
Tools to Output documents from DB based on selections 8 146
Possibly a grouping issue? 7 36
Join Number Parameter with commas 6 9
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question