| Author |
Message |
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
   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
|
 IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
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
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
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.
|
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
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.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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...
|
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
Posted: 13 Dec 2013 at 10:14am |
|
It still isn't pulling Records #2 and #3.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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")
|
|
IP Logged |
|
|
|
