Record Selection - Space delimited field 2

[glalsop]

Hello -

Using Crystal 8.5, SQL Server, ODBC

I have only one table - Courses. One field in the table is named competency. There are 15 choices for competency(Collaboration, Communications, etc.). My goal is to report on this field, i.e. show all courses that have a certian competency. The problem is that a course can have more than one competency, and if it does they are stored in the same field delimited by a space. So I need the user to be able to select from a parameter list one or more of the 15 possible competencies, and show the course if any of the parameter values are part of the competency string. I would like to be able to select records based on something akin to the following:

(
if {?Competency} <> &quot;ALL&quot; then
(
if UBound({?Competency}) > 1 then
(
for i := 1 to UBound({?Competency}) do
instr({course.competency}, ({?Competency}[x])) <> 0
)
else {course.competency} = {?Competency}
)
else true
)

This formula produces 0 errors but returns all records where {course.competency} is not null. The for loop is where I am attempting to say &quot;If the first value of {?Competency} is in the {course.competency} field then select it, if the second value of {?Competency} is in the {course.competency} field then select it, etc.

I hope this makes sense, and will be glad to provide more info if necessary.

One more thing - Our data is stored on an external vendors server and I cannot create views, change table format, etc. Can only use Crystal to report on what is already there

Gary

 

[synapsevampire]

First, contact the dba with a flame thrower.

I think that you're best served to use a Like predicate here and the users will need to add in a Crystal wildcard (or make it part of the default picklist), as in:

{course.competency} like {?Competency}

But if they enter *yo* they'll get anything with whatever they entered:

yo-yo
you

You can make the prompting text reflect that they enter the asterisks.

This is a BAD database design, and unfortunately I can't come up with a good Crystal solution.

I'd contact the vendor, complain about their incompetency and demand that they create a View which normalizes the data.

-k

 

[glalsop]

k,

Thank you for your reply... I was initially trying to use the like clause but unfortunately, as you mentioned, it is too broad. Communications and Communications Initiative, for example, are 2 possible (distinct) choices (and of course there is no way to distinguish between the space that delimits competencies and the space that seperates words in the same competency). I suppose I will just ctegorize the competencies and let the user select a 'category'. Thanks again for your input.

Gary

 

[lbass]

The following works when tested, although it is evaluated locally:

numbervar i = 0;
numbervar total;

for i := 1 to ubound({?competency}) do (
if instr({course.competency}, {?competency}) <> 0 then
total := total + instr({course.competency}, {?competency}) else
total := 0 );

if {?Competency} <> &quot;All&quot; then
total <> 0 else
true;

This doesn't address the issue presented by competencies of both &quot;Communications&quot; and &quot;Communications Initiative&quot;, where &quot;Communications Initiative&quot; would incorrectly be selected based on a parameter of &quot;Communications&quot; but perhaps you could deal with this by changing the name of the conflicting two-word competencies as in:

replace({course.competency}, &quot;Communications Initiative&quot;, &quot;Comm Init&quot

You can nest these for multiple replacements as in:

replace(replace({course.competency}, &quot;Communications Initiative&quot;, &quot;Comm Init&quot,&quot;Singing Solo&quot;, &quot;Sings Solo&quot

Then you would use the formula instead of {course.competency} in the select statement.

-LB

 

[glalsop]

LB -

Thank you also for your suggestion. Your resolution works great if I only select one choice from the parameter list. If I select Communication, I get all of the relevant strings. However, if I select multiple choices I only get the results for the last one entered (because the entire for loop is evaluated before the actual selection statement?). So if I select Communication and then also select Initiative, I only get courses with Initiative in the string.

Local evaluation is not really an issue (except perhaps for aesthetic purposes), there are only about 2350 total records on this table. Actually, I am currently in the process of pulling in ALL records and conditionally suppressing them based on parameter info.

Again, thanks to both of you for all your suggestions.

 

[lbass]

Sorry, I double-checked and you are right. The following isn't elegant and there might be a better way, but it seems to work. You'd have to adjust it to reflect the maximum number of competencies that would be selected as parameters:

numbervar i := ubound({?competency});

if {?Competency} <> &quot;All&quot; then
(if i = 4 then
instr({course.competency},{?competency}[4]) <> 0 or
instr({course.competency},{?competency}[3]) <> 0 or
instr({course.competency},{?competency}[2]) <> 0 or
instr({course.competency},{?competency}[1]) <> 0 else
if i = 3 then
instr({course.competency},{?competency}[3]) <> 0 or
instr({course.competency},{?competency}[2]) <> 0 or
instr({course.competency},{?competency}[1]) <> 0 else
if i = 2 then
instr({course.competency},{?competency}[2]) <> 0 or
instr({course.competency},{?competency}[1]) <> 0 else
if i = 1 then
instr({course.competency},{?competency}[1]) <> 0 ) else
true

-LB

 

[synapsevampire]

You might try to handle this the way I'd suggested by modifying the record selection a bit and adding a formula:

Formula:
left({?Competency},len({?Competency})-1)+&quot; &quot;

{course.competency} like {?Competency}
and
not({course.competency} like @leftformula)

This may raise new issues as Buiness Communications would be picked up for Communications because this is NOT a space delimited field..

Note that you changed the rules here, you stated that the field was space delimited, when it is not.

The field should be considered useless based on what you've now described and the potential problems that can arise.

-k

 

[synapsevampire]

I dawned on me that the last post won't work, since it isn't a space delimited field, and in fact isn't deleimited at all, I'd create a pick list of valid choices, or do the appropriate thing and correct the data.

-k

 

[glalsop]

LB -
Thanks again for your reply. Unfortunately I am away from my job and can't test your formula, but will do so on Monday. It seems though that k is correct in his assessment that I cannot get valid results from this structure. After all, if a course has competency Business Etiquette and and the user selects Business as a competency parameter, the course with Business Etiquette will always show up, rendering my results invalid.

k-
I did not purposely change the rules, just did not think my problem through thoroughly enough. The specs told me the field was space delimited, and of course this is technically incorrect. As a note, I did take the advice from your first post and my manager has been in contact with our vendor (I'm a low man on the totem pole, no vendor contact for me).

I would like to thank both of you again for your attempts to help me work through this problem. You might not believe how many problems I have worked through by sifting through threads on this site

 

[synapsevampire]

I didn't mean to imply that you'd purposely changed the spec., just that it did change, and hence no intelligent solution exists short of correcting the data.

Good luck with it.

-k