Parameter values isue with combined db strings

[dzavad]

hi,I have parameter in the report {?category}. That does the simple thing {Details.Category}={?category} and sopose to return records depending on one or multiple values in the parameter.
However, db field {Details.Category} has records that is actualy join values like "value1|value2|value3" in single record or can be like "value1|value2" or just "value1" in single record.
Parameter {?category}. has set number of parameters/choices : value1, value2 and value3.
Run parameter works only if db record has 1 value not any of the combinations of it. It will work if value1=value1 , value2=value2 or value3=value3. But will not work if value1|value2=value1 or vallue1|value3=value3 etc.
Is there way to run this parameters by finding the way to asosiate value1|value2=value1 , so this report will show the record just because one of the values in the string mach the parameter value?

 

[synapsevampire]

Try posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

Here's an example of how I handle poorly designed databases such as yours using SQL (Oracle):

select
SYSADMIN.CUSTOMER.CCBS_LAST_UPDATE_DATE
from sysadmin.customer, sysadmin.registry
where ','||translate(sysadmin.registry.registry_value,'^ ','^')||','
like '%,'||translate(customer.cust_type_tag,'^ ','^')||',%'
and sysadmin.registry.registry_key = 'ESP_CUST_TYPE'

I the above case, I have comma delimited values in the field, but the same logic applies.

So you might create a SQL Expression that delimits each of the values stored in the database, and then use a LILE predicate, and of course there are other solutions.

But not posting basic essential info, such as your software version leads me to believe that I'll just be wasting our time because I've probably already lost you.

btw, immediately fire your dba.

-k

 

[dzavad]

Ha, ha I agree with you on fireing dba, for sure. They make my life a living hell with this.
But, thank you for the tip....

 

[lbass]

This won't pass to the SQL, but you could try the following record selection formula:

numbervar i := 0;
numbervar j := ubound({?category});
numbervar k := 0;

for i := 1 to j do(
if {?category} in split({table.string},"|") then
k := 1
);
k = 1

-LB

 

[dzavad]

This is good, verry close for what i am looking for. However, it is only shows exact maches parameter to string. How about is i want to have partial mach of parameter to string and stil show the record in the report.
EX: if parameter has "Value1" and string has "value2| value3|value1". I want to be able search the string and find atlist one mach and show it in the report.
I hope I am clear on thet if not sorry. And thank you

 

[dzavad]

I think if I take the last k=1 out it works..iam testing it now.....

 

[dzavad]

no my bad....

 

[synapsevampire]

As LB mentioned, it won't pass this to the database.

I supplied an elegant SQL side solution, if you're going to suffer having Crystal pull all rows in and then filter them out, then just use suppression:

numbervar i := 0;
numbervar j := ubound({?category});
numbervar k := 0;
for i := 1 to j do(
if {?category} like +"*"+split({table.string},"|")+"*"+ then
k := 1
);
k = 1

Note that this is NOT an exact match, and that you may get bad data as it merely gets close.

-k

 

[dzavad]

Works good thanks, I just messed up "," with "|".

 

[synapsevampire]

Note that the solution will state that a category of A10 will match A100, etc.

You can get around this by cleaning it up, or going with my SQL solution:

Here's a more accurate approach:

numbervar h := ubound(split({table.string},"|"))
numbervar i := 0;
numbervar j := ubound({?category});
numbervar k := 0;
numbervar l;

for i := 1 to j do(
for l := 1 to h do(
if split({table.string},"|")[l] like "*"+{?category}+"*" then
k := 1
);
);
k = 1

SOmehting like that.

You need to check each of the values in the table seperately to be accurate.

-k