Need to split string so user can enter parameter on the split value 1

[CanadianDesigner]

Hi there... using Crystal 11. Basically, I need to split a string field into separate entries. Our customers entered a number of interests when they registered on our site and our system was designed to store that data as follows, "9;1;10;9;8;6;5;4;3;2" (where 9 = Accounting, 1 = Marketing, 10 = Finance, etc).

However, what I need to do is have each of these values come up with the same set of details. For instance, based on the string above, I want my final report details to end up looking like this:

John Smith johnsmith@EMail.com OrgName 9
John Smith johnsmith@EMail.com OrgName 1
John Smith johnsmith@EMail.com OrgName 10
John Smith johnsmith@EMail.com OrgName 9
John Smith johnsmith@EMail.com OrgName 8
...Etc.

In the end, the report user needs to be able to use a parameter to pull a list of all users who entered a specific interest area (so they would choose say Accounting, and it would pull all users who have a nine in that string). I believe there should be no more than 9 different entries in the string, but when I browse through the data, there are duplicates in there for some reason, so there could be more than nine output values in the string.

Please let me know if this is unclear.

Thanks in advance for any assistance you can offer!

 

[lbass]

Why not just test the presence of the number in the string if a parameter will be used to limit to one interest, instead of trying to create separate records for each interest?

-LB

 

[CanadianDesigner]

Thought of that, but how would I do that for something like the 1... if I do an "if 1 is in the string", I'll also get the 10s, no? And I can't do 1; since some of them may only have a 1, so would have no semicolon.

 

[Turkbear]

Hi,
If the User is entering a parameter ( I assume you have it defined as a value/description pair like 9 Accounting) then testing for it would be something like:

If {?parameter} = 1 
then
  ((Instr({TableField},"1") > 0 and Instr{TableField},"10") =0 )
Else 
If {?parameter} <> 1 
then
(Instr({TableField},{?parameter}) > 0

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CanadianDesigner]

Hmmmm... definitely closer.

The problem that is happening now is when someone chooses 1 - Accounting, it is not pulling records where the user chose 1 - Accounting AND 10 - Production... If the user chose both of those interests, I still need them come up when someone chooses Accounting as the interest.

Any ideas? Everything else is pulling beautifully.

 

[lbass]

So this is a multiple value paramater? You aren't expecting one record to appear in multiple interest groups are you?

You could try something like this:

stringvar x;
numbervar i;
numbervar j := ubound({?parm});
stringvar array y := split({table.string},";");
numbervar k;
numbervar m := ubound;
for i := 1 to j do(
for k := 1 to m do(
if {?parm} = y[k] then
x := x + {table.string}+","
));
{table.string} in x

-LB

 

[CanadianDesigner]

No, I likely won't make it a multiple value parameter. The problem is only when I search for 1 - Accounting.

I've used the formula Turkbear gave above, but it is now eliminating records from the results for "1" (Accounting) that also have "10" (Production) in the string.

For example:
If I now do a search for interest group "10", I get

John Smith johnsmith@EMail.com OrgName 1;10;9;8;6;5;4;3;2

but if I do a search for interest group "1", I don't get that same record because the above formula is telling it that if there is a 1 in the string, show the record as long as there are no 10s, correct?

I just want to be able to show that record under either interest group search.

Make sense?
Thanks so much for your help.

 

[lbass]

Okay, then you could use:

stringvar x;
stringvar array y := split({table.string},";");
numbervar j := ubound;
numbervar i;
for i := 1 to j do(
if {?parm} = y then
x := x + {table.string}+","
);
{table.string} in x

-LB

 

[Turkbear]

Hi,
Nice formula LB, should work for him like a treat - star for you....





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CanadianDesigner]

Firstly Turkbear... him is a her.

But that aside, stuck the formula in and it seemed to be chugging along, then I got an error message that says "A string can be at most 65534 characters long."

I don't really understand why I'd be getting this error since the data in that field shouldn't be more than a maximum of 20 characters or so.

Thanks for your continued help... I really appreciate it!

 

[lbass]

I'm not sure this will resolve the issue, but try:

stringvar x;
stringvar array y := split({table.string},";");
numbervar j := ubound;
numbervar i;
for i := 1 to j do(
if {?parm} = y and
not ({table.string} in x) then
x := x + {table.string}+",");
{table.string} in x

-LB

 

[CanadianDesigner]

That did it lbass! You've made my day!!!!!!!!

Thank you so much!

 

[Turkbear]

Hi,
Sorry CanadianDesigner, I am usually good about not assuming gender with my pronouns....



To Paraphrase:"The Help you get is proportional to the Help you give.."