Solved

Crystal Reports: Formula for multiples value lookup

Posted on 2013-12-03
14
838 Views
Last Modified: 2013-12-07
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
Comment
Question by:Maliki Hassani
  • 8
  • 3
  • 3
14 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39692519
I can't think of a way to make that work.
Have you tried
     InStr({CombinedResources},{?Parameter}) > 0

mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 39692554
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
 

Author Comment

by:Maliki Hassani
ID: 39692607
I believe this does the trick:
{Combined Resources} in split(join({?I_RESOURCED_GROUPS},{?I_DELIMITER}),{?I_DELIMITER})
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:Maliki Hassani
ID: 39692637
No, that isn't working
0
 

Author Comment

by:Maliki Hassani
ID: 39692757
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39693083
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
 

Author Comment

by:Maliki Hassani
ID: 39693750
Not making any progress here. I am still searching.

Your formula only returns True to all records.
0
 

Author Comment

by:Maliki Hassani
ID: 39693783
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
 
LVL 34

Expert Comment

by:James0628
ID: 39697876
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39698264
Actually it isn't.  It will be true only if there is a match.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39698356
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
 

Author Comment

by:Maliki Hassani
ID: 39701435
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
 

Author Closing Comment

by:Maliki Hassani
ID: 39701446
Assisted my final solution
0
 
LVL 34

Expert Comment

by:James0628
ID: 39702970
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 48
SubQuery link 4 35
RAISERROR WITH NOWAIT 2 15
SQL Recursion schedule 13 14
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question