Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help With Multi-Value Parameter In Record Selection Formula 1

Status
Not open for further replies.

ztruelove

Technical User
Oct 3, 2008
13
US
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?
 
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
 
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} + ",
 
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
 
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.
 
Add a default value to the Exclusion parameter of "None," and instruct users to enter values to exclude or otherwise, select "None."

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top