Can I use my array values(codes) to look up and return a description?

[ainkca]

I'm using Crystal XI

Looking to use my array values to look up and return descriptions instead of the codes stored.

Here is a sample of the values captured from a multiselect box.

Field value: PacerIcddibMurmerPalpAnginaNote
My array : Pacer,Icddib,Murmer,Palp,Angina,Note

Need to end up with:
Pacemaker, ICD - Defibrilator, Murmur, Palpitations, Chest Pain, See comment
(stored in a table called "legendchoice", with code and name)

legendchoice table sample
code name
Pacer Pacemaker
Icddib ICD - Defibrilator



This is how I created the array:
stringVar strInput := {casevisitresultlistwt.result_value};
numberVar i;
stringVar strOutput;

strOutput :=strInput[1];
for i := 2 to len(strInput) do
(
if asc(strInput) = asc(ucase(strInput)) then
strOutput := strOutput & "," & strInput
else
strOutput := strOutput & strInput
);

strOutput;

Any suggestions on how to take the pieces of the array and return the names instead of the codes?

Thanks in advance. Sorry if the answer is in this forum, I tried to find what I needed before posting.
(my array coding doesn't allow for any numbers to be part of the codes. Current data set that is not an issue, but it could be in the future so if anyone has ideas feel free to let me know if I could have done this a better way)

Thanks again

 

[briangriffin]

Is this McKesson data?

 

[lbass]

You could write a formula like this:

stringVar strOutput;
stringvar array y := split(strOutput,",");
stringvar array z := "";
local numbervar i;
local numbervar j := ubound;
for i := 1 to j do(
redim preserve z[j];
z := (
select y
case "Pacer" : "Pacemaker"
case "Icddib" : "ICD - Defibrilator" //etc.
|
|
V
case "Note" : "See Comment"
));
join(z,", ")

-LB

 

[kenhamady]

The method that LBass is using will require that you put all the codes / descriptions into the formula. That might not be practical if the list is long or if the table changes.

An alternative would be to pass the list down to a subreport and have the subreport read the legend table and return descriptions of the records that have a code in the list field.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[ainkca]

Thanks all,

Brian, yes I'm recreating the preadmit testing record data for HSM

Ken you are correct, I was going to say the same, there could be too many. I just confirmed that there will be changes to the list. My concern is if they make a change to the legend list it won't pull the data correctly. (very likely it would change and I wouldn't be told)

To use a subreport I'd have to emulate a nested one somehow, since the separate sections of the chart are already in subreports. We're already having issues where variables aren't passing when run through the viewer(CR4E 2.0.1) the vendor uses to run the reports from the app.

Should I attach my report so far?

 

[briangriffin]

It looked awfully familiar - I had this same issue a couple of weeks ago. I think the best approach, if possible, is to compile a function on your database server. Since this issue will probably appear on multiple reports, a function will be a big time saver. You can then base your reports on either a stored procedure or command object that calls the function, or else use a SQL Expression within the report.

If and when you use numeric values in your lookup this would need some tweaking, but I would avoid that if possible.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: 10/6/2011>
-- Description: Returns formatted lookup values for multivalue data fields.
-- =============================================
CREATE FUNCTION YourHealthSystemMultiLookup (@v_multivalue varchar(500))

RETURNS varchar(500)
AS
BEGIN

declare @v_returnval varchar(500)

declare @v_counter int
set @v_counter = 1

declare @v_result varchar(100)
set @v_result = ''

while @v_counter <= len(@v_multivalue)

BEGIN
set @v_result = @v_result + substring(@v_multivalue,@v_counter,1)
if ascii(substring(@v_multivalue,@v_counter + 1,1)) between 65 and 90
	BEGIN
	set @v_result = @v_result + ','
	END
set @v_counter = @v_counter + 1
END

declare 
@v_lookup varchar(50),
@v_display varchar(200)

set @v_lookup = ''
set @v_display = ''


while len(@v_result) > 0

BEGIN
set @v_lookup = substring(@v_result,1,charindex(',',@v_result) - 1)
set @v_display = @v_display + (select name from YOURSERVER.YOURDATABASE.dbo.legendchoice where code = @v_lookup) + ', '
set @v_result = substring(@v_result,charindex(',',@v_result) + 1,len(@v_result))
END


set @v_returnval = substring(@v_display,1,len(@v_display) - 1)

return @v_returnval

END
GO

 

[ainkca]

Thank you so much, I will try your suggestion this afternoon.

The OR folks tell me there won't be numbers in the 'codes' (for now there aren't, at least in prod... test is a whole other animal)