Help With Multi-Value Parameter In Record Selection Formula 1

[ztruelove]

I'm trying to query a table on computer names and associated applications. This I don't have a problem with. My roadblock is that I want to provide the user with the ability to provide some keywords to exclude from the search of the application name.

I've created three parameters: Computer (single value string), Application (single value string), and Exclusions (multi-value string).

Here is the record selection formula that doesn't work because I haven't indexed my Exclusions array:

{vComputer.Name} like "*" + {?Computer} + "*" and
{Inv_AeX_OS_Add_Remove_Programs.Name} like "*" + {?Application} + "*" and
not(InStr({Inv_AeX_OS_Add_Remove_Programs.Name}, {?Exclusions}) = 0)


How can I accomplish what I'm trying to do?

 

[lbass]

Try this:

whilereadingrecords;
numbervar i;
numbervar j := ubound({?Exclusions});
stringvar x;
for i := 1 to j do(
if ucase({?Exclusions}) in ucase({Inv_AeX_OS_Add_Remove_Programs.Name}) then
x := x + {Inv_AeX_OS_Add_Remove_Programs.Name} + ","
);
instr(x,{Inv_AeX_OS_Add_Remove_Programs.Name})= 0 and
{vComputer.Name} like "*" + {?Computer} + "*" and
{Inv_AeX_OS_Add_Remove_Programs.Name} like "*" + {?Application} + "*"

-LB

 

[ztruelove]

Close but no cigar! I'm learning something though.

The problem with your suggestion is that as exclusion matches are hit, my x value becomes increasingly large, and eventually I end up with the error...

A string can be at most 65534 characters long.

...pointed at the line...

x := x + {Inv_AeX_OS_Add_Remove_Programs.Name} + ",

 

[lbass]

Another way of doing this is to hardcode based on a certain number of exclusions. You would have to instruct the user in the prompt text that they can enter up to n values. You can do it like this:

{vComputer.Name} like "*" + {?Computer} + "*" and
{Inv_AeX_OS_Add_Remove_Programs.Name} like "*" + {?Application} + "*" and
(
(
if ubound({?Exclusions}) >= 1 then
not(ucase({Inv_AeX_OS_Add_Remove_Programs.Name}) like "*"+ucase({?Exclusions}[1])+"*") else true
)
and
(
if ubound({?Exclusions}) >= 2 then
not(ucase({Inv_AeX_OS_Add_Remove_Programs.Name}) like "*"+ucase({?Exclusions}[2])+"*")
else true
) and
(
if ubound({?Exclusions}) >= 3 then
not(ucase({Inv_AeX_OS_Add_Remove_Programs.Name}) like "*"+ucase({?Exclusions}[3])+"*")
else true
))

Add as many clauses as the number of parameter values you will allow.

-LB

 

[ztruelove]

Perfect! Thank you very much!!

 

[ztruelove]

How would I allow for the possibility of no Exclusion value being provided? When I attempt this scenario, I get the error on the parameter form - "The value is not valid.

 

[lbass]

Add a default value to the Exclusion parameter of "None," and instruct users to enter values to exclude or otherwise, select "None."

-LB