Having problems with IsNull

[ashishh]

Thanks for reading the question.

I am having a problem trying to use dynamic conditions. I need to check if a certain field is NULL, if it is then I have use a different record selection.

Crystal is saying that my syntax is incorrect. Here is what I am trying to do.

if IsNull({?i_Compund_ID}) then
{TBL_PROTOCOL.I_COMPOUND_ID}={?i_Compund_ID}
and CR displays "a Statement expected here.

Could Naith or someone else please help me.
I am using CR 8.5

 

[synapsevampire]

Your formula:

If IsNull({?i_Compund_ID}) then
{TBL_PROTOCOL.I_COMPOUND_ID}={?i_Compund_ID}

Is not checking a field, it's checking a parameter to see if it's null.

If it is, it states that all of the {TBL_PROTOCOL.I_COMPOUND_ID} must be null, but doesn't offer an alternative if it isn't null.

I assume that you're placing this in the record selection criteria? If not, you probably should be.

You might try:

if {?i_Compund_ID}>0 then
{TBL_PROTOCOL.I_COMPOUND_ID}={?i_Compund_ID}
else
true

Prefill your {?i_Compund_ID} parameter default with a zero.

This will return the records with the same ID as prompted for, otherwise it won't limit the records and will return everything. The SQL will probably get passed too.

If you only want rows that match the criteria enetered, omit the check entirely and just place:

{TBL_PROTOCOL.I_COMPOUND_ID}={?i_Compund_ID}

into the record selection criteria

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ashishh]

Thanks for you response.

I'll try to make my question little more clear. In the "Record Selection" section, I want to create filters dynamically based on 4 parameters which may or may not be filled by the user. If they fill the values, I need to include it in my selection criteria.

Using a default 0 value will not have any errors but will not filter the data correctly. Sorry about not making my question clear enough in the first place.

That is why I need to check if the parameters are entered or not (they are not mandatory).

If var1 is not null then
whereclause = 'table.col1=var'
If var2 is not null then
whereclause = 'table.col2=var2'
and so on.

Your help is highly appreciated.

Thanks,

 

[Naith]

Hey Ashishh,

SynapseVampire has made a point worth your thoughts in his post, namely:

If IsNull({?i_Compund_ID}) then
{TBL_PROTOCOL.I_COMPOUND_ID}={?i_Compund_ID}

will syntactically only return records where {TBL_PROTOCOL.I_COMPOUND_ID} is null. Is that really what you're asking for?

It seems to me that what you want is to pay heed to the parameter values if someone uses the parameter, otherwise just get everything.

If that's the case, you'd really want to be moving in the direction of:

If IsNull({?i_Compund_ID})
Then True
Else
{TBL_PROTOCOL.I_COMPOUND_ID}={?i_Compund_ID}

I don't really like working with multiple null scenarios in the selection criteria, because Crystal can get a bit rambunctious with how it tries to deal with the data.

If your Compound ID is a numeric field, I would be inclined to use the approach SynapseVampire gave you, rather than use isnull all the time, though the sql won't get passed when you leave the default value of 0. What do you mean when you say that the data "isn't filtered properly" when you try to do this?

Naith

 

[Ngolem]

I don't think that Crystal will let the user continue if a parameter has not been entered.

What you should do is create a default value and assign it to the parameter in question. By this means the user does not have to enter a parameter unless they want something other than the default.

For example: For a person's last name you might want one last name or "ALL" last names. You could make the Default value of {?LastName} = "ALL"

Jim Broadbent

 

[Naith]

Fair point just brought up.

The field looks like a numerical field, so you probably can't use 'All', but you can use 0 as a numerical stand in like in the posts before.

But that also means that you can't have null as a default value. In a string parameter, you can enter '', as your default, but you can't do that with a numerical parameter.

Anyway, even if you could, Crystal would recognise that default as being equal to '', not as being null.

Naith

 

[Ngolem]

Hi Naith...thanks I forgot about numbers when I wrote this

I either give it a commonly used number or a ridiculous number like -999999

then in the record select I look for that ridiculous number and select all records

Something like this

...<other selection filters> and
if {?numParam} = -999999 then
true
else
{table.fieldNum} = {?numParam};

I use a similar formula for strings as well when testing &quot;ALL&quot;

Jim Broadbent

 

[Naith]

I would certainly concur with that approach (I assume that there are no ids equal to zero, but something like -999999 is even more dependable) - but then Ashishh says he gets 'filtering issues' using this method. I'm not terribly sure what that means.

 

[Ngolem]

&quot;Using a default 0 value will not have any errors but will not filter the data correctly. Sorry about not making my question clear enough in the first place.&quot;

Naith - My guess is that zero is not a reliable filter since it possibly is a valid result. That is why I use the &quot;ridiculous&quot; number approach. Jim Broadbent

 

[ashishh]

Thanks Ngolem, SynapseVampire, Naith and others who have responded. I found that using 0 works for me. 0 is not possible as it is the primary key in my case. I use that as default value and use nested if's to dynamically build the record selection filter.

Thansk to everyone for taking time to answer.

Ashish