Record Selection Formula Help

[ftbadolato]

I have a parameter field called Last. The user has the option of filling in this field, or leaving it blank. If they fill it in, the report works great, but if they leave it blank, I get no results. What I want to happen is that if they leave it blank, I get ALL the records in the table. I created a record selection formula for the report to try and do this, but I am having problems. Here is the formula:

IF {?Last} <> '' OR NOT(ISNULL({?Last})) THEN
{Table.LastName} = {?Last}
ELSE
.......

I tried creating this without the else, but I still get no records, so I think I need to somehow specify that ?Last equals everything in an ELSE. How do I do this? If I am way off on how this should be accomplished, please let me know.

Thanks!

 

[synapsevampire]

IF {?Last} <> '' OR NOT(ISNULL({?Last})) THEN
{Table.LastName} = {?Last}
ELSE
true

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

BTW, you should be using AND, not OR.

I tend to pre-populate the parm list with a generic '*ALL' or something to that effect to allow for coding as:

IF {?Last} <> '*All' AND
{?Last} <> ''
then
{Table.LastName} = {?Last}
ELSE
true

Thay way the user gets a choice as to how to select all of the rows.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ftbadolato]

Thank you for the quick reply, but it did not work. I did not change anything but the addition of TRUE after the ELSE. Did I miss a subtle change? Is it possible something else is wrong with my report? I received no results when I chose to Preview the report in Crystal and did not enter anything into the parameter field.

Thanks!

 

[ftbadolato]

OK, I'll try that!

 

[Ngolem]

That is because SV forgot and didn't place the test for NULL first....this is crucial.

IF NOT(ISNULL({?Last})) OR {?Last} <> '' THEN
{Table.LastName} = {?Last}
ELSE
true;

Jim Broadbent

 

[synapsevampire]

It's not crucial, it's pointless, Jim.

Try testing a string parm with isnull, you'll NEVER get a true from a Crystal prompt, even if you don't prompt for it...so your addition is meaningless, plus you used an OR instead of an AND, which would be wrong if a null could exist because a true null would cause the {?Last} <> '' to be correct and hence demonstrate the null rows.

Perhaps when setting parms externally and not passing anything to a parm might result in a null, dunno, but a null string parm doesn't exist within CR.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

Actually, I believe that any null checks do have to be carried out at the beginning of selection criterion.

But, it's better to avoid working with them at all. If you can, use the generic value for a wildcard as SynapseVampire has indicated.

Naith

 

[ftbadolato]

Well, I tried many solutions, but this one is what worked.

IF NOT(ISNULL({?Last})) AND {?Last} <> '' THEN
{Table.LastName} = {?Last}
ELSE
true

Hope this solves something.

Thanks for all your help!

 

[synapsevampire]

Naith: A parm CANNOT be null.

ftbadolato: Again, IF NOT(ISNULL({?Last})) is redundant, drop it...

IF {?Last} <> '' THEN
{Table.LastName} = {?Last}
ELSE
true

The post I had made required that you have a literal value of *All or the user enter nothing (blank, did I remember to mention that a parm can't be null!). The reason I suggest that is so the user understands how to pull All rows.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV....I rest my case....any testing for NULLS must have IsNull as the first test. Jim Broadbent

 

[Ngolem]

BTW...I understand your position on the ability of a parameter to be null...I don't see how this can be myself as CR will send a popup to demand a value.

But that doesn't have anything to do with the proper structure of the test for a null in a formula. Jim Broadbent

 

[ftbadolato]

Just curious if anyone is actually trying what they are writing b/c some of the suggestions work, and some do not. Not trying to throw wood on the fire here, but sometimes, as I am sure you are all aware, applications do not behave as you would expect, or as they should, so you have to recreate the situation at hand. Personally, it does not matter to me how they should work, all I know is that what I posted before works, and nothing else did.

Thanks again!

 

[TedLiu]

Ngolem is correct. As my understand, null checking always be the first should be a rule, at least for an asp or VB developer.

Sorry for adding more.

Ted

 

[Ngolem]

ftbadolato - Trouble shooting Crystal reports is an art not a science. You have the benefit of many pretty good Crystal report writers' opinions here. In my humble opinion, the group of regulars are as good or better than Crystal's help desk.

The delima is that often the person presenting the problem doesn't understand the problem well enough to give all of the clues to its solution. They give symptoms...not root cause.

Each of us that does respond does so based on how we read the problem + the response of the poster to all our potential solutions. This helps alot since by looking at what has been tried one of us can usually figure out the problem.

I venture to say if you did a detailed analysis of our responses you will find that each of the top 10 &quot;experts&quot; has a little niche where they shine. Some like Ken Hadamay don't shine...they glow

So in answer to your question...no...these solutions are not tested...how can they be? We don't have a copy of the report with the problem...they are simply the best guess of individuals here that like Crystal Report and like helping others with tricks/knowledge that you will often not find in any book. Jim Broadbent

 

[Naith]

Actually, now that I've paid attention to the thread, Ngolem and SynapseVampire are both correct in the points they've been making. In principle, null tests do have to be performed first, otherwise, for reasons best known to Crystal, null criteria are ignored.

What I didn't pay attention to before I charged in with my twopence was that the null test was being performed against a parameter - which, as SynapseVampire has correctly reminded me - can't possess a null value - only a ''; which is quite different.

The correct solution here would be:

IF {?Last} <> '' THEN
{Table.LastName} = {?Last}
ELSE
True

A better solution might be to include a definitive wildcard value as previously indicated, and trap the '' in addition.

IF {?Last} = &quot;*DefaultWildcardValue*&quot;
THEN True
ELSE
(IF {?Last} <> '' THEN
{Table.LastName} = {?Last}
ELSE True)

All the best,

Naith

PS: If I may force my opinion on you, the pool of knowledge in the Crystal forums is far more beneficial to me than the London Crystal helpdesk resource. The rate of helpdesk staff turnover is too rapid to provide any real advanced knowledge, other than what can be read over the phone from the knowledge link library.

And if Ken doesn't give you a star for that &quot;glowing&quot; remark, Jim, then there's no justice in the world.

 

[synapsevampire]

Careful, Naith, sometimes CR won't pass the SQL if the first part of the IF is True. Also, if the user wipes out the *All, it does no damage to ALSO check for '', which in my first post I addressed both.

ftbadolato: If you're having problems, try adding supporting information, such as the formula you used and the resulting SQL.

You posted this stating that it's tthe only thing that worked:

IF NOT(ISNULL({?Last})) AND {?Last} <> '' THEN
{Table.LastName} = {?Last}
ELSE
true

Which is basically what I instructed you to do (though I elaborated based on how I've been doing this to simplify the USer experience), but yours has a redundant NULL check in it.

The SQL I posted WAS tested, if you have a problem, please state what didn't work and as you've dicovered, someone will help you, perhaps not always in the most efficient manner, but we all might learn something as a result of the thread, and that's really the point of all this.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ftbadolato]

Sorry, the level of this has gone way beyond my experience with CR. This is the first time I have used Crystal for anything and hence the first formula I have ever written in Crystal. So, the lack of information from me is probably due to the fact that I do not know what would be beneficial to you. In other words, Jim, I have no idea what the &quot;root cause&quot; of my problem is, or was. I guess I was hoping to get that info from you all. Like when you take your child to the doc and you tell him or her the symptoms. I don't think the doc expects you to know the root cause.

As far as the place being helpful...it has been very helpful. I found it on Yahoo, and I am very happy with the responses I have received. Although I am not versed enough in CR to possibly benefit from all of them, my problem is solved and I will definitely be back when a new one surfaces, which reminds me...

Have a great weekend!