| Author |
Message |
JS99
Newbie
Joined: 29 Aug 2012
Online Status: Offline
Posts: 5
|
  
Topic: Select Expert Not Filtering Correctly
Posted: 29 Aug 2012 at 9:08am |
|
Hi,
New to the forum (and to Crystal IX), working through a simple & straightforward report pulling from a Raiser's Edge export and hitting a snag.
I'm using Select Expert to filter results from a "Prt_Response" field as follows:
{Prt.Prt_Response} in ["Golf and Dinner", "Golf Only"]
Despite using "is one of" to filter out any results other than those two, I'm still getting results that leave Prt_Response blank.
Any ideas why this problem is happening?
Thanks for any help!
|
 IP Logged |
|
|
|
Schugs
Newbie
Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
|
Posted: 29 Aug 2012 at 9:14am |
|
Not sure why it would be giving you those nulls, but a work around would be to in the select expert choose "Formula editor" and try using this
Not(IsNull({Prt.Prt_Response}) and
{Prt.Prt_Response} in ["Golf and Dinner", "Golf Only"]
|
| IP Logged |
|
JS99
Newbie
Joined: 29 Aug 2012
Online Status: Offline
Posts: 5
|
Posted: 29 Aug 2012 at 9:38am |
|
Hmm--thanks for the suggest, but no luck. Still getting the nulls
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Aug 2012 at 10:13am |
maybe...
in the select expert in the formula editor make sure to use the 'Default values for nulls' and add a trim() on your statement.
trim(Prt.Prt_Response}) in ["Golf and Dinner", "Golf Only"]
|
| IP Logged |
|
JS99
Newbie
Joined: 29 Aug 2012
Online Status: Offline
Posts: 5
|
Posted: 29 Aug 2012 at 10:31am |
|
At present I've got
Not(IsNull({Prt.Prt_Response})) and trim({Prt.Prt_Response}) in
["Golf and Dinner", "Golf Only"]
and 'Default values for nulls' set in my formula editor, but it doesn't seem to have made any changes in my preview--nulls still showing up.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Aug 2012 at 10:37am |
the NOT is null is redundant once you use defualt values for nulls
I would start doing a little testing using formulas to see what is being read per line to see what is happening.
I woula also make sure that the field you are displaying is the field you think you are displaying
try using formuals to do row data checking inside the report
add a formula as
IsNull({Prt.Prt_Response})
place it on the detail row and see if it is TRUE or False per row
add another one for
trim({Prt.Prt_Response}) in ["Golf and Dinner", "Golf Only"]
Try other things that might be in your
trim({Prt.Prt_Response}) =""
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 31 Aug 2012 at 8:10am |
|
have you tried setting set nulls to defaults in the report options?
also have you encapsulated the statement
(Not(IsNull({Prt.Prt_Response})) and trim({Prt.Prt_Response}) in
["Golf and Dinner", "Golf Only"])
also, in the options/formula editor, you have null treatment - make sure "exception for nulls isn't set"
Edited by comatt1 - 31 Aug 2012 at 8:13am
|
| IP Logged |
|
JS99
Newbie
Joined: 29 Aug 2012
Online Status: Offline
Posts: 5
|
Posted: 04 Sep 2012 at 5:49am |
|
I did a little testing and found the source of my problem, now I'm working on a new challenge. I have the following scenario:
There is an upcoming tournament. There are team captains, and team members. Team captains have sponsors. I would like to display team captain's sponsors on the appropriate team members' lines.
Is a formula the best way to accomplish this?
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 04 Sep 2012 at 8:50am |
|
well yes, but how is the data organized. If the players and sponsors are two tables i would do this in sql expression
(select top 1 a.sponsorname from sponsor a inner join
players b on
a.teamcap = b.teamcap)
This is making MANY assumptions though, you need to provide more data.
|
| IP Logged |
|
JS99
Newbie
Joined: 29 Aug 2012
Online Status: Offline
Posts: 5
|
Posted: 04 Sep 2012 at 9:19am |
|
OK, so--my key fields are as follows:
{Prt.Prt_Name} is the registered event particpant
{Prt.Prt_Sport_Position} indicates a players' classification as Captain or Golfer
{Prt.Prt_Guest_Of} indicates the team captain a player has been assigned to.
{Prt.Prt_Sponsored_By} indicates a team captain's sponsor
The difficulty here is that my tables have EITHER a {Prt.Prt_Sponsored_By} field or, as replacement, a {Prt.Prt_Guest_Of} field. I've assembled this questionable If-Then statement as a workaround (hoping to list "see [team captain]" under the sponsor field for golfers), but even this is giving me trouble:
if {Prt.Prt_Sponsored_By} = "" then "see {Prt.Prt_Guest_Of}" else ""
is there a problem with my syntax here? Or could it be that your suggested SQL solution is the more efficient way to go here?
Thanks!
|
| IP Logged |
|
|
|
