[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

Crystal Reports 2008 - Finding an array value in a string

I've got a report with an optional parameter for Lot # (ex. n498p).  The field in the database is a string field that could contain multiple lot numbers (ex. "n498p, n502p").  For the report parameter, I want to use multiple values so they can search for 1 or more lot numbers.  In the record selection formula, how do I loop through that array to check each value and see if it show up "in" the string field?

~bruno71
0
bruno71
Asked:
bruno71
  • 4
  • 4
  • 4
  • +1
1 Solution
 
Kurt ReinhardtCommented:
You don't.

You can simply use {table.field} = {?multiplevalueparameter}

Since you're using an optional parameter, however, you do need to account for the possibility that the parameter might be empty.  As such, use the following:

If
  HasValue({?multiplevalueparameter})
Then
  {table.field} = {?multiplevalueparameter}
Else
  True

~Kurt
0
 
Kurt ReinhardtCommented:
Never mind, I read it too quickly and missed the part about the actual database field.  The rest still stands, but I'll need to work something else out.

~Kurt
0
 
Kurt ReinhardtCommented:
The following code will work IF you input wildcards into your parameter entry...

If
  HasValue({?multiplevalueparameter})
Then
  {table.field} Like {?multiplevalueparameter}
Else
  True

Please note, that while this will work, it won't be particularly efficient, since using LIKE will force a full table scan, but at least it will be processed on the database as opposed to the client.

~Kurt

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Priya PerumpilavilCommented:
create a stored procedure like this from your table

declare @str as varchar(1000)
//set @str = '#E00001#, #E00002#, #E00003#'
select * from ppemp where
@str  Like '%#' + empno_k + '#%'  


and pass the parameter as  
 '#E00001#, #E00002#, #E00003#'
you will get the result




0
 
Priya PerumpilavilCommented:
crystal report has to made with the stored procedure
0
 
Kurt ReinhardtCommented:
It doesn't have to be made with a stored procedure.

~Kurt
0
 
bruno71Author Commented:
Kurt,

It's kind of working.  I'm using LIKE with the wildcards...

(not HasValue({?Block Lot #}) OR {GW_SCRAP.BlockLotNum} like "%" + {?Block Lot #} + "%")

...but it's not finding any fields that contain the value, only fields that are equal to it.  For example, it will find a record with "n498p", but not "n498p, n500p"

Mike
0
 
mlmccCommented:
Do you still need help with this?

mlmcc
0
 
bruno71Author Commented:
Yes...I never quite got it to work just right.  I have a report parameter that let's them input multiple values.  However, the report is only finding records EQUAL to the value, not CONTAINING the value.

~bruno71
0
 
mlmccCommented:
So you have a field that may contain say
   "n498p, n502p"

and a parameter that may contain
   "n498p"  or ""  or "n498p, n503p"

Is that correct?

mlmcc
0
 
bruno71Author Commented:
I may have the following fields...
1. "n498p"
2. "n498p, n502p"
3. "n502p"

For the parameter (which allows multiple values), I enter "n498p" and "n502p" (enter one...click the arrow to add it to the list...enter the second...click the arrow...etc).

The report will find records #1 & 3, but not #2.

Also, if I would enter "n498" for the parameter, it wouldn't find any of them.

I hope this is making sense...

~bruno71
0
 
mlmccCommented:
I'll see what I can do.

I assume there are other values that can occur.

mlmcc
0
 
mlmccCommented:
Create this formula
Name - CheckParams
Local StringVar Array strValues;
Local NumberVar paramIndex;
Local NumberVar ValueIndex;
Local BooleanVar boolResult;
boolResult := False;

strValues := Split({Q_26465201.LotNumbers},",");
For ValueIndex := 1 to UBound(strValues) do
   For paramIndex := 1 to UBound({?LotNumber}) do
      If Trim(strValues[ValueIndex]) = Trim({?LotNumber}[paramIndex]) then
         boolResult := True;

boolResult

Use that in your record selection

mlmcc
0
 
bruno71Author Commented:
Thanks!  I think that worked.  I'll continue to test it.

~bruno71
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now