Select Expert Formula 2

[WestView]

I have a report where I use the following formula in the Select Expert:


If {?Parameter1} <> "" Then {Table.Field1} IN {?Parameter1} Else
Not({Table.Field1} IN {?Parameter1}) Or
If {?Parameter2} <> "" Then { Table.Field2} IN {?Parameter2} Else
Not({Table.Field2} IN {?Parameter2}) Or
If {?Parameter3} <> "" Then { Table.Field3} IN {?Parameter3} Else
Not({Table.Field3} IN {?Parameter3}) Or
If {? Parameter4} <> "" Then { Table.Field4} IN {?Parameter4} Else
Not({Table.Field4} IN {?Parameter4}) And
{ Table.DateField5} >= {?Parameter5}


If I run them individually – like:

If {?Parameter3} <> "" Then { Table.Field3} IN {?Parameter3} Else
Not({Table.Field3} IN {?Parameter3})

No problem! I get the correct records returned. However, when I run them all together (which I must) it doesn’t return the all the matching records I should see. BTW, all parameter fields (except {?Parameter5} are arrays.

What am I doing wrong!

Any/all help would be greatly appreciated!!!!

Thank you,

Tom

 

[synapsevampire]

You want AND:

If {?Parameter1} <> "" Then {Table.Field1} IN {?Parameter1} Else
Not({Table.Field1} IN {?Parameter1})
AND
If {?Parameter2} <> "" Then { Table.Field2} IN {?Parameter2} Else
Not({Table.Field2} IN {?Parameter2})
AND
If {?Parameter3} <> "" Then { Table.Field3} IN {?Parameter3} Else
Not({Table.Field3} IN {?Parameter3})
AND
If {? Parameter4} <> "" Then { Table.Field4} IN {?Parameter4} Else
Not({Table.Field4} IN {?Parameter4})
And
{ Table.DateField5} >= {?Parameter5}

-k

 

[lbass]

Can you explain a little about what you're trying to do? If you really mean "or" then you need to explain whether the "and" clause belongs with any of the first four clauses, or whether it belongs with Parameter 4. You would indicate this by using parentheses around all the "or" clauses in the first case, or around the parameter 4 clause and the "and" clause in the second case.

-LB

 

[WestView]

K,

Thanks for your reply. But I tried that previously and it does not work. I only ger the first parameter returned. That's why I used "OR". I need ALL parameter values returned.

Thanks again,

Tom

 

[WestView]

Hello LB,

Thank you for your response.

What I’m trying to do is get ALL the records that meet ANY of the non-null parameter values. That’s why I went with the ‘OR’ Boolean. If the user populates values for the any of the 4 parameter fields, I would like the report to return those records.

However, {?Parameter4} is a code for a subscription product. Therefore, I would like {?Parameter5} (a date field indicating the date the subscription will expire) conditional on {?Parameter4}. That’s why I thought I should use the ‘And’ Boolean there.

It’s a mess.

Again, thanks for your reply!!!

Tom

 

[lbass]

First, your record selection formula should look like:

(
If {?Parameter1} <> "" Then {Table.Field1} IN {?Parameter1} Else
true
) Or
(
If {?Parameter2} <> "" Then { Table.Field2} IN {?Parameter2} Else
true
) Or
(
If {?Parameter3} <> "" Then { Table.Field3} IN {?Parameter3} Else
true
) Or
((
If {? Parameter4} <> "" Then { Table.Field4} IN {?Parameter4} Else
true
) And
{Table.DateField5} >= {?Parameter5})

Note the two pairs or parentheses for parm4 and parm5.

The problem with using "or's" here is that if any of the parameters are = "", then all records will be returned. If all parameters have selected values, then I think you will get the records you are looking for--those that meet at least one parameter criterion.

-LB

 

[WestView]

LB,

Once again, I am in your debt sir.

Thank you!!!

- Tom