Looping through a multiple value parameter array

Question

I have a parameter that allows multiple values. Its for a name field in my database. What I want to be able to do is allow the user to put in a name and then have crystal find any name like any of the names they entered. So for example you could put in 4 last names and crystal would return anyone who had any of those names in the name field. I can get the "like" to work just fine, but only when there is one string in the array. Here is my select formula:

    numbervar counter := 1;
    numbervar positionCount:=count({?Customer Name}); //I'm not sure what to put
                                                        here. Count? UBound? 

    if {?Customer Name}[1] <> 'ALL'
    then
    (
         while(counter <= positionCount)
         do
         (       
            {NAMEFIELD} like  '*' & {?Customer Name}[counter] & '*';
            counter := counter + 1;
         );
    ) 
    else
    if {?Customer Name}[1] = 'ALL'
    then
    true
    )

This formula returns all of the names, not the ones in the parameter. Any ideas on what I'm doing wrong?

craig · Accepted Answer · 2012-04-11 12:13:34Z

4

Create a multi-value parameter ({?Customer Name}) with these properties:

Default Value: ALL
All multiple values: TRUE

Add a row to the parameter's pick-list grid; supply 'ALL' and 'ALL' (without single quotes)

Create a Custom Function (named 'Delimit') with this text:

// Delimit()
// enclose each value in array in **, returning an array
Function (Stringvar Array params)

  Split("*" + Join(params, "*,*") + "*", ",")

Modify the report's record-selection formula:

If {?Customer Name}<>"ALL" Then
    {TABLE.CUSTOMER_NAME} LIKE Delimit({?Customer Name})
Else
    True

Optionally, create a formula to display the parameter's values with this text:

//{@Customer Name}
Join( Delimit({?Customer Name}), ";")

answered Apr 11, 2012 at 12:13

craig

26.3k29 gold badges131 silver badges224 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ryan · Accepted Answer · 2012-04-10 19:11:10Z

2

Luckily CR can handle this situation automatically (at least in CR2008, where I just confirmed it). You can just do {?Customer Name}="All" or {NAMEFIELD} like {?Customer Name}.

It will be up to the end user to use the wild cards appropriately, but you can add a blurb to the parameter's help text or force the *Name* format with an edit mask.

answered Apr 10, 2012 at 19:11

Ryan

7,2971 gold badge19 silver badges32 bronze badges

2 Comments

Lee Tickett Over a year ago

What Ryan says is correct, or you could of course do {?Customer Name}="All" or {NAMEFIELD} like '*' + {?Customer Name} + '*' so the user doesn't have to enter the wildcard :)

Ryan Over a year ago

@LeeTickett Despite seeming correct, that actually won't work. The '+' operator works differently on string arrays than they do on strings. It is basically treating the two '*' strings as additional entries in the array, so you'll match every record. If you try using the '&' concatenation operator in the same way, CR will give you an error.

craig · Accepted Answer · 2012-04-11 18:44:56Z

0

Another approach (I thought it made sense to create a separate answer), if you have Oracle, is to make use of the REGEXP_LIKE expression.

Create a report that uses a Command.
Create a string parameter ({?QUERY}) in the Command's panel; set the default value to 'ALL' (without single quotes).
Add the query to the Command; reference the parameter:

SELECT customer_name FROM customers WHERE ( '{?QUERY}'='ALL' OR REGEXP_LIKE(customer_name, '{?QUERY}') )

Add fields to the report as usual
When prompted (running the report), enter a regular expression in the QUERY parameter. The pattern 'A|B|C' is equivalent to LIKE '%A%' OR LIKE '%B%' OR LIKE '%C%'.

answered Apr 11, 2012 at 18:44

craig

26.3k29 gold badges131 silver badges224 bronze badges

Collectives™ on Stack Overflow

Looping through a multiple value parameter array

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related