Array Parameter in Record Select 2

[Jen123]

Is there anyway you can pass an array parameter into the record selection formula in Crystal R XI.

Basically I have a field in my database that stores values such CA, ME, UK ({a.CatPartMarketCode} below)

I need a parameter where a user can select multiple markets. If the db field stored just one market it would be ok but as it stores multiple values comma separated i'm struggling.

The below works when a user selects just one market but goes wrong when a user tries to select more than 1 market. When there are multiples I can't seem to get an 'or' in.

Can this be done (or something like it!!)?

NumberVar iIndex;
(
if UBound({?Market})= 1 then
instr({a.CatPartMarketCode},{?Market}[1]) > 0
else if UBound({?Market})> 0 then
for iIndex:=1 to UBound({?Market}) do
if iIndex>1 then
instr({a.CatPartMarketCode},{?Market}[iIndex]) > 0
)

 

[lbass]

Try setting it up like this:

numbervar i;
numbervar j := ubound({?Market});
stringvar x;

for i := 1 to j do(
if {?Market} in {a.CatPartMarketCode} then
x := x + {a.CatPartMarketCode}
);
{a.CatPartMarketCode} in x

This won't be fast, but it should work.

-LB

 

[Jen123]

Thanks LB for the suggestion. It's not really working though. {a.CatPartMarketCode} could have any combination of 2 digit character codes, separated by commas such as

[ME,CN,GB] or [CN] or [CZ,ME,CN]

Similarly, the parameter array will have any code, any order.

I was thinking about making it less slick and just informing the user they can only have 5 options and coding in 5 interations of

instr({a.CatPartMarketCode},{?Market}[1]) > 0 or
instr({a.CatPartMarketCode},{?Market}[2]) > 0
etc

what do you think?

 

[Jen123]

Hi LB think I've got this cracked now.

I created a formula field {CatPartMatch} with this code

numbervar i;
numbervar j := ubound({?Market});
stringvar sCatPartMarket;

sCatPartMarket:= 'No Match';

for i := 1 to j do
(
if instr({GMESRptBEGetHierCatPartDualLangCatSelect.CatPartMarketCode},{?Market}) > 0 then
sCatPartMarket := "Match";
);
sCatPartMarket;


Then in the record selection formula I added where {CatPartMatch} = 'Match'

Thanks for the idea!

 

[lbass]

I think my formula should have worked, although looking at it again, I would have changed the one line to:

x := x + {a.CatPartMarketCode}+","

It worked when I tested it here. Can you explain why it didn't work for you?

-LB

 

[Jen123]

I can see why it should have worked, perhaps it would have done if I'd added in the +",".

Cheers

 

[shams100]

Thanks for lbass and Jen123.
I used this code for an array parameter into the record selection formula in Crystal R XI.


numbervar i;
numbervar j := ubound({?Age});
stringvar x;

for i := 1 to j do(
if trim({?Age}) in trim({a.arrAgeDesc}) then
x := x + {a.arrAgeDesc}+","
);
{a.arrAgeDesc} in x

It work fine but there is a problem it is returning the values for every record has {?Age} even as a part of it;

example:
if {?Age}=Adults

the report will return every record has
Adults and Mature Adults, ....

I want only to get the records for {?Age}="Adults"

I really will appericate any help

 

[lbass]

Please show some samples of how {a.arrAgeDesc} displays if you place it in the detail section.

-LB

 

[shams100]

Thanks for you respond.

It should be like:

Adults,Infants & Toddlers,General Population


What I am having now which is wrong:

Adults,Mature Adults,Infants & Toddlers,General Population

Thanks,

 

[lbass]

Please show what the field looks like in the detail section with multiple rows.

-LB

 

[shams100]

I will explain my situation and really will appreciate any help:

We need to manage everything from the report side. I am using Crystal Version 11. I am using Stored Procedure without any parameters.

I add to the report 5 parameter. All of them Multiple values parameters.

3 of the parameters represent field array and data come as :

Field 1:{arrAgeDesc}
Infants & Toddlers ,Children, Adults ,Mature Adults,….

Field 2:{arrRace}
African American or Blak,White,Asian….

I used your formula for every Parameters ( the 3 fields) such:

numbervar i;
numbervar j := ubound({?Age});
stringvar x;
for i := 1 to j do(
if {?Age} in {a..arrAgeDesc} then
x := x + {a.arrAgeDesc}+","
);
{a.arrAgeDesc} in x


Under the Report Formula Selection record I added the formula name.
{@ArrAge_Array}

This is not working at all.
I really will appreciate any help in that matter.

 

[lbass]

Try changing the formula to:

numbervar i;
numbervar j := ubound({?Age});
stringvar x;

for i := 1 to j do(
if {?Age} = split({a.arrAgeDesc},", ") then
x := x + {a.arrAgeDesc}+", "
);
{a.arrAgeDesc} in x

Make sure the comma-space matches the way the field displays, i.e, if the comma-delimited string has no space after the comma, remove it in the formula.

-LB

 

[shams100]

Thanks lbass for your respond and help.

When I added the 3 parameters to the reprot and modified the formula like your modification and run the report I got this message

<<A string can be at most 65534 characters long.>>>

 

[shams100]

To Make it more clear this message related to the line

x := x + {a.arrAgeDesc}+", "

This happend when i pick up more than 3 values for the Multiple values parameters

 

[lbass]

Add a clause to ensure only distinct values are saved in the string:

numbervar i;
numbervar j := ubound({?Age});
stringvar x;

for i := 1 to j do(
if instr(x,{a.arrAgeDesc}) = 0 and
{?Age} = split({a.arrAgeDesc},", ") then
x := x + {a.arrAgeDesc}+", "
);
{a.arrAgeDesc} in x

-LB

 

[shams100]

Thanks so much for you assistance with that issue.
I do appreciate your help.

I am testing the report now.

 

[shams100]

It is working but display more records:

Example:

?Age=Adult
?Ethnicity=General

I am getting 3 records

Age Ethnicity

Adult General
Adult General
General General

I suppose to get only the first 2 records.

 

[lbass]

Please post your entire record selection formula.

-LB

 

[shams100]

{a.state} = {?State} and
{a.program} = {?Program} and
{@ArrEthnicity_Array} and
{@ArrAge_Array}and
{@ArrRace_Array}

 

[lbass]

Please show the contents of your nested formulas.

-LB