Selection Criteria
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20262
Printed Date: 29 Apr 2024 at 4:52am
Topic: Selection Criteria
Posted By: jswartz
Subject: Selection Criteria
Date Posted: 29 Nov 2013 at 5:55am
|
I am a Crystal Report Novice, so please be gentle with me. I am trying to select records where Field 1 does not contain "HHGD" or is blank, AND Field 2 is not equal to "Storage". BOTH conditions must be true. If the record has "HHGD" in Field 1 and something other than "Storage" in Field 2, I want it in the report. There is also other criteria, but I can figure out how to add that. I'm using Version 14. Thanks for your help. Jayn
|
Replies:
Posted By: Sastry
Date Posted: 30 Nov 2013 at 6:43pm
|
Hi Try this in record selection Formula : {field1} = "HHGD" and {Field2} <> "Storage" Also on top of your formula editor window, you will find 'Exceptions for Nulls' make that as 'Default Values for Nulls'
-------------
Thanks,
Sastry
|
Posted By: lolly54
Date Posted: 09 Dec 2013 at 9:49pm
Or try...
({Field1} like "*HHGD*" or not(ISNULL{Field1}))
AND
not({Field2} = "Storage")
sometimes for excluding null value, I used the following also.
{Field1} <> ""
Try them out and see. :)
|
Posted By: jswartz
Date Posted: 10 Dec 2013 at 2:58am
Hello Sastry and lolly54,
Sastry, when I try your suggestion, I don't get any records where Field 1 is not HHGD or blank.
lolly54
When I try yours Crystal is asking for a ) but I can't figure out where to put it.
I am trying to select records where Field 1 does not contain "HHGD" or is blank, AND Field 2 is not equal to "Storage". BOTH conditions must be true. If the record has "HHGD" in Field 1 and something other than "Storage" in Field 2, I want it in the report
|
Posted By: Sastry
Date Posted: 10 Dec 2013 at 9:15pm
Hi
Try this :
(Not({Field1} like "*HHGD*") OR not(isnull({Field1})) or {field1} <>" ") and
not({Field2} = "Storage")
-------------
Thanks,
Sastry
|
Posted By: jswartz
Date Posted: 11 Dec 2013 at 3:11am
Hi Sastry,
Thanks for your help. I tried that formula. The problem is that I have a record where Field 1 = Not HHGD, Field 2 = Storage. I need that record to show on this report. When I apply the formula, that record is not selected.
|
Posted By: lockwelle
Date Posted: 11 Dec 2013 at 6:42am
(Not({Field1} like "*HHGD*") OR not(isnull({Field1})) or {field1} <>" " or {field1} <> "Not HHGD") and
not({Field2} = "Storage")
should work
|
Posted By: jswartz
Date Posted: 11 Dec 2013 at 8:59am
I'm sorry I'm not being clear. Select Records: If Field 1 is not equal to "HHGD" or if Field 1 is blank, AND Field 2 is not equal to "Storage". BOTH conditions must be true. If the record has "HHGD" in Field 1 and something other than "Storage" in Field 2, I want it in the report.
I got the isnull part. The issue is that I am trying to put two conditions together into one condition. BOTH PARTS of the condition must be true. When I put () around to try to say that, Crystal takes the () away and makes them like two separate conditions that are not dependent on each other.
|
Posted By: lockwelle
Date Posted: 11 Dec 2013 at 9:18am
that's just a more complex...
local stringvar x := {table.field1};
if isnull(x) then x := ""; //deal with the null value
x:= trim(x); //remove leading and trailing white space
if x = "HHGD" or x = "" then
if {table.field2} <> "Storage" then
true
else
false
else
false
this should get you what you are after, and I have been wrong before. I was just building on what Sastry had written.
HTH
|
Posted By: jswartz
Date Posted: 11 Dec 2013 at 9:37am
Yeah that is a little more complex :-)
OK I copied and pasted this into the Formula Workshop for Record Selection. Replaced the {field} with my fields. Crystal says "A field is required here" at
if isnull(x)
|
Posted By: Sastry
Date Posted: 11 Dec 2013 at 6:25pm
Hi
If I understand correct, you want field1 not of HHGD and field 2 should be equal to Storage.
Then use this :
(Not({Field1} like "*HHGD*") OR not(isnull({Field1})) or {field1} <>" ") and
({Field2} = "Storage")
-------------
Thanks,
Sastry
|
Posted By: lockwelle
Date Posted: 12 Dec 2013 at 4:44am
hmmm....I would probably take a look at x. Perhaps, a NULL value in the field automatically translates to an empty string when converted to a stringvar.
I guess I would probably write a new formula for testing this like:
local stringvar x:={table.field};
if x = "" then
"yes"
else
"no"
HTH
|
Posted By: jswartz
Date Posted: 12 Dec 2013 at 5:08am
Hello, Hopefully this will help clarify which records I would like to select.
Record # Field 1 Field 2 Select?
1 HHGD Storage No
2 Other data Storage Yes
3 Blank Storage Yes
4 HHGD Other data Yes
|
Posted By: lockwelle
Date Posted: 12 Dec 2013 at 5:36am
My original if statement, I believe will accomplish this...the last formula was purely for debugging purposes.
I thought that a null field would result in a null stringvar...and that isnull() would check the value. This is what would happen in SQL. Evidently though, CR only allows isnull() to operate on fields...so you could also, probably, replace the x in:
if isnull(x) then x:="";
with:
if isnull({table.field}) then x:="";
HTH
|
Posted By: jswartz
Date Posted: 12 Dec 2013 at 11:10am
lockwelle,
Yes! This works! I just need to figure out how to add my other criteria in now.
|
Posted By: jswartz
Date Posted: 13 Dec 2013 at 4:07am
lockwell,
I spoke too soon. The formula does not select Records 2 and 3 from my example above. I feel like we are getting close.
|
Posted By: lockwelle
Date Posted: 13 Dec 2013 at 4:47am
well, how about:
local stringvar x := {table.field1};
if isnull(x) then x := ""; //deal with the null value
x:= trim(x); //remove leading and trailing white space
if (x = "HHGD" or x = "") and {table.field2} <> "Storage" then
true
else
false
though the other should have worked...I thought...
|
Posted By: jswartz
Date Posted: 13 Dec 2013 at 10:14am
|
It still isn't pulling Records #2 and #3.
|
Posted By: lockwelle
Date Posted: 16 Dec 2013 at 5:19am
well, I think that Sastry and I have given you enough ideas to have you start to fiddle with the logic. You can see and alter the data/logic much faster than we can...and obviously we are getting something wrong.
As a general method of debugging a formula in CR, create 'test' formulas that just do part of the logic.
like:
{table.field} = something and {table.otherField} = somethingElse
this will display a true or a false, and from there you can start 'tuning' the logic to get what you want. Sometimes it is the data isn't exactly what you thought it was, sometimes you realize that what you thought you were doing, well, you weren't.
HTH
|
Posted By: DBlank
Date Posted: 16 Dec 2013 at 6:15am
|
MTC...
in the select expert formula set the pick list to use "default values for nulls"
({Field1} like "*HHGD*" and {Field2} <> "Storage")
or
((NOT({Field1} like "*HHGD*")) and {Field2} = "Storage")
Not({Field1} like "*HHGD*") OR not(isnull({Field1})) or {field1} <>" ") and
({Field2} = "Storage")
|
Posted By: jswartz
Date Posted: 30 Dec 2013 at 9:14am
|
Are these 3 separate options to try, or is each separate? I tried various combinations but nothing seems to work. Even though I use parenthesis to signify that two conditions go together, Crystal always removes them and acts like each formula is a stand alone condition.
|
Posted By: DBlank
Date Posted: 30 Dec 2013 at 9:49am
|
sorry
I think I double pasted on my post
try the below with the formula editor set to use defualt values for nulls
({Field1} like "*HHGD*" and {Field2} <> "Storage")
or
((NOT({Field1} like "*HHGD*")) and {Field2} = "Storage")
|
Posted By: jswartz
Date Posted: 30 Dec 2013 at 10:34am
You guys are awesome, thank you for all your help in getting this figured out. Here is the final formula which works:
not ({Field 1} = "HHGD" and {Field 2} = "Storage")
I guess it was just a matter of getting the syntax correct. I'm so excited that my report is running correctly now!!!!
|
|