• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 953
  • Last Modified:

Crystal Reports: Formula for multiples value lookup

Experts,

Thank you in advance, as this issue has been puzzling me for a while.
Here is what I am trying to do.  I have a field name "COMBINED_RESOURCES", which can contain multiples resources that are speperated by a comma and a space so ", ".  Here is an example output:
NOC Operations, Critical Engineering, Video Operations, Maryland Headend
or it can be just a single resource without a comma like:
NOC Operations

More details:
I want to be able add multiple resources to a parameter and return the values if any of those values in the parameter is in the "COMBINED_RESOURCES".

so, lets say I add in the parameter:
NOC Operations
Critical Engineering

It should return the following records:
RECORD#   COMBINED_RESOURCES
1                NOC Operations
2                NOC Operations, Video Operations
3                Critical Engineering, Video Operations


Thank you, so much!
0
Maliki Hassani
Asked:
Maliki Hassani
  • 8
  • 3
  • 3
1 Solution
 
mlmccCommented:
I can't think of a way to make that work.
Have you tried
     InStr({CombinedResources},{?Parameter}) > 0

mlmcc
0
 
Maliki HassaniAuthor Commented:
hmm..

Maybe this can help you some how:

Well what I have been doing in the past with my reports for "assigned groups" is to create 2 parameters, one for listing multiple groups I_ASSIGNED_GROUPS, which has a parameter called DELIMITER, that has the selection for |,. etc. I then use these 2 paramaters to retrieve records that match any of the assigned groups I listed.  This works fine but what I am trying to do is actually opposite because the "COMBINED_RESOURCES" has muliple values.

My Parameter looks like this:
National-Analysis & Development|National-Carrier Operations|National-Surveillance

{COMBINED_GROUP} in split(join({?I_ASSIGNED_GROUPS},{?I_DELIMITER}),{?I_DELIMITER})

It would seem like I would need to do something like this:
split(join({?I_Combined Resources},{?I_DELIMITER}),{?I_DELIMITER}) in split(join({COMBINED_RESOURCES},{?I_DELIMITER}),{?I_DELIMITER})
0
 
Maliki HassaniAuthor Commented:
I believe this does the trick:
{Combined Resources} in split(join({?I_RESOURCED_GROUPS},{?I_DELIMITER}),{?I_DELIMITER})
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Maliki HassaniAuthor Commented:
No, that isn't working
0
 
Maliki HassaniAuthor Commented:
Your InStr({CombinedResources},{?Parameter}) > 0

is making progress..  

 InStr({Combined Resources},"NOC Operations") > 0  works!

If I do:
  InStr({Combined Resources},{?I_RESOURCED_GROUPS}) > 0 I get the following error:

This array must be scripted. For example:Array "i"

If i can figure this out I will have it working.
0
 
mlmccCommented:
Try it this way
(
Local NumberVar index;
Local BooleanVar Found := False;
For Index := 1 to UBound({?I_RESOURCED_GROUPS}) do
     Found  := Found OR  InStr({Combined Resources},{?I_RESOURCED_GROUPS}[Index]) > 0
)

Open in new window


mlmcc
0
 
Maliki HassaniAuthor Commented:
Not making any progress here. I am still searching.

Your formula only returns True to all records.
0
 
Maliki HassaniAuthor Commented:
Trying a different route:
If {?I_RESOURCED_GROUPS} = "Headend" then
(
InStr({@F-Combined Resources},"Central Florida-East Headend") > 0
or
InStr({@F-Combined Resources},"Central Florida-West Headend") > 0
  )
else if {?I_RESOURCED_GROUPS} = "BSS" then
(
InStr({@F-Combined Resources},"NSO-BSS") > 0
  )
etc
0
 
James0628Commented:
You need to add Found (the variable) to the end of mlmcc's formula.  Otherwise, you just get the result of the For loop, which is always True.  Try this:

(
Local NumberVar index;
Local BooleanVar Found := False;
For Index := 1 to UBound({?I_RESOURCED_GROUPS}) do
     Found  := Found OR  InStr({Combined Resources},{?I_RESOURCED_GROUPS}[Index]) > 0;
Found
)

Open in new window

0
 
mlmccCommented:
Actually it isn't.  It will be true only if there is a match.

mlmcc
0
 
James0628Commented:
As far as I can tell, a For loop just produces a True result.  For example, the following gives me a true result:

Local NumberVar i;
Local BooleanVar found;

for i := 1 to 10 do
  found := false

Open in new window

0
 
Maliki HassaniAuthor Commented:
I went with the following:
If {?I_RESOURCED_GROUPS} = "Headend" then
(
InStr({@F-Combined Resources},"Central Florida-East Headend") > 0
or
InStr({@F-Combined Resources},"Central Florida-West Headend") > 0
etc

I appreciate the help!
0
 
Maliki HassaniAuthor Commented:
Assisted my final solution
0
 
James0628Commented:
FWIW, if a formula like that works, then it might be a better solution than a loop, like the one that mlmcc posted.  Assuming that you're doing these tests in a record selection formula, a loop won't be passed to the server, but tests like the ones that you're using might.

 James
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now