"Blob field" and "memo field" in formula not working! 3

[T3leTech]

I am trying to evaluate a database field that is (in SQL Server 2000) labelled as varchar, 2000. I assumed that this was just a very long string field, and tried to implement an if-then-else formula based on the contents of this field. However, when I try to run the formula, it comes up with an error message that says "blob fields and memo fields" are not valid field types (or something like that).

Does anyone know how to evaluate a field with a string length above 254 characters without changing the database field?

Here is the example if-then-else statement:
If "ACD" in {Table.filed}
Then 1
Else 0

Thanks in advance for any help!

 

[BobMarley]

I believe you can't evaluate it.

The way I did this was I converted the field to a char(255) in SQL Server.

 

[T3leTech]

That's what I was afraid of. I cannot change the database field without causing major havoc with other systems. Anyone else have advice?

 

[vidru]

Try creating a SQL Expression field that looks like this:

/*{%test}*/
{fn LOCATE('ACD', table.fieldname)}

Then your original formula would be:
if {%test} > 0 then
1
else
0

(fyi - the LOCATE function works like CHARINDEX in SQL Server)

-dave

 

[yehong]

just to add...
When you create a SQL Expression, you won't see the memo fields in the available fields. So, you select any other field and then manually replace its name with your memo field name and then check if your expression has no errors. Then you can use that expression into a formula.

 

[vidru]

Forgot to mention that. Thanks, alansaw.

-dave

 

[synapsevampire]

And you can forego fn <function> and just use straight SQL as well.

Check the Database->Show SQL Query to see how Crystal handles this.

BTW, CR 9 does not have this limitation.

-k

 

[vidru]

k,

I was getting an error for some reason when I was trying to just use CHARINDEX('ACD', table.fieldname), that's when I sort of stumbled on to the LOCATE function (which I've never used before).

Just tried it again and it works for me now. Must have been distracted by my 'real' work. Stupid phones...

-dave

 

[T3leTech]

Okay...you all are totally awesome! Thank you very much for all of your help! I had never used a SQL expression in CR, because I could never understand how it worked, which is frustrating as I am an experienced SQL user!

Anyway, thanks again for your help!

 

[T3leTech]

Okay...now that I have the counts in place, I've run into another problem. All of the criteria I am using to pull specific numbers is working great, but I now need to pull a sum of a specific field (int) where it meets all the &quot;if [criteria] then 1, else 0&quot;.

So, here is the formula for one of the fields that I need to now get a sum of the field where this statement is true. I can do this easily in SQL Query Analyzer, but can't figure out how it's done in Crystal.

IF {CallDetail.CustomNum1} <> 1
AND {CallDetail.LocalNumber} in ('6761' to '6775')
AND {%ACD} = 0
THEN 1
ELSE 0

The SQL statement (in Query Analyzer) I use to get the number and the sum of information is like this:

select count (*), sum(calldurationseconds)
from table
where datepart(mm, initiateddate) = 11
and datepart(dd, initiateddate) = 20
and datepart(yy, initiateddate) = 2003
and calldirection = 'Inbound'
and calleventlog not like '%%ACD%%'
and CustomNum1 <> 1
and calltype <> 'Intercom'
and (localnumber in ('6761', '6762', '6763', '6764', '6765', '6766', '6767', '6768', '6769', '6770', '6771', '6772', '6773', '6774', '6775')
or DNIS in ('6761', '6762', '6763', '6764', '6765', '6766', '6767', '6768', '6769', '6770', '6771', '6772', '6773', '6774', '6775'))

 

[vidru]

Not knowing your report layout (e.g. grouping, table structure, joins, etc.), try altering your formula a little...

IF {CallDetail.CustomNum1} <> 1
AND {CallDetail.LocalNumber} in ('6761' to '6775')
AND {%ACD} = 0
THEN {CallDetail.calldurationseconds}
ELSE 0

... and place it in your details section. Then just right click on it, Insert>Summary, and choose the appropriate summary option. If you don't want to show it on the detail line, then just suppress it.

-dave

 

[T3leTech]

Thank you Vidru!

I tried that, but I was putting in SUM in the THEN part, and it was failing.

Thanks again for all your help!

 

[T3leTech]

Now that I have the report all fixed, I have another problem. I am using Crystal 8.0 and Seagate Info 7.0. When I finished the report and tried running it in Seagate Info, it failed and the error message that appeared was &quot;Error in compiling SQL Expression. Invalid function found here.&quot; The SQL expression that it doesn't like is the one that makes my report work (CHARINDEX...). I also tried the {fn LOCATE(...)) and Seagate Info doesn't like that either.

I realize that this question should probably be in the Seagate Info Forum, but it is related to the report that y'all helped me create, so I thought I would start here first.

Thanks for any help you can offer.

 

[vidru]

I've never even seen Seagate Info, but I found this article on the Crystal Descisions site:

http://support.crystaldecisions.com/library/kbase/articles/c2002571.asp

Hope it helps you out. If not, you should probably take this to the Info forum.

-dave