How to specify Optional Criteria

[ashishh]

I have a situation where I need to accept about 10 parameteters. They are not mandatory, but if they are entered, I need to filter the records based on each parameter. Example.

Param 1 : Compound
Param 2 : Protocol
Param 3 : Site

I am currently doing it in Record Selection Formula Editor.
I check for empty string '' to find out if they were entered or not. The if however will be very big because I am not able to specify multiple if-endifs. Crystal forces me to use if then else if. Which means, I should be checking for all 10 params in one big if condition.

Am I missing anything? How can I do the following
If not {?Compound} = '' then
tbl_Compound.Compound = {?Compound}
end if

If not {?Site} = '' then
tbl_Site.Site= {?Site}
end if

and so on.

Apparently if does not have any end ifs in CR.

Could someone please help me.

Thanks,
Ashish

 

[synapsevampire]

You want to use a structure as in the following:

(
if {?Compound} <> &quot;&quot; then
{MyTable.Compound} = {?Compound}
else If {?Compound} = &quot;&quot; then
true
(
and
(
if {?Protocol} <> &quot;&quot; then
{MyTable.Compound} = {?Protocol}
else If {?Protocol} = &quot;&quot; then
true
)

etc.

There's some overkill in the design to try to assure SQL passthrough to the database to improve performance, Crystal is very touchy about how to get it to pass.

You might also add in a default value of 'All' toi the text based parms to allow users to select 'All', which makes a minor change to your selection criteria, as in:

(
if {?Compound} <> &quot;&quot;
and
{?Compound} <> &quot;All&quot;
then
{MyTable.Compound} = {?Compound}
else If {?Compound} = &quot;&quot;
or
{?Compound} = &quot;All&quot; then
true
(

Check the SQL Passthrough by selecting Database->Shoq SQL Query.

If blank or 'All' are selected, you shouldn't get anything passed, when a selection is made, you should see SQL generated.

If you allow multiple value parameters, then use:

(
if {?Compound}[1] <> &quot;&quot;
and
{?Compound}[1] <> &quot;All&quot;
then
{MyTable.Compound} = {?Compound}
else If {?Compound}[1] = &quot;&quot;
or
{?Compound}[1] = &quot;All&quot; then
true
(

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ashishh]

Thanks for the answer. There is another way I was told I could use and that is as following:

({?s_Compound}='' OR {TBL_COMPOUND.S_COMPOUND_NAME} = {?s_Compound}) and
({?s_Protocol}='' OR {TBL_PROTOCOL.S_PROTOCOL_NAME}= {?s_Protocol}) and
({?s_Country}='' OR {TBL_COUNTRY.S_COUNTRY_NAME}= {?s_Country}) and
({?s_Site}='' OR {TBL_SITE.S_SITE_NAME}={?s_Site})

This solution works really well.

Thanks for your help.

Ashish