Superscript Formula 1

[strom99]

I have the following field

3M(TM) Scotchlite(TM) Glass Bubbles K1 coming into my report. I have figured out that the following formula

{sp_pp_grid_body;1.product_description}[1 to 2] & chr(153)

will superscript the Trademark symbol after the 3M. The field will always contain the 3M with the superscripted TM, but the following product description will vary in length. How can I rewrite this formula to make put in the TM after the first 2 characters and after the next word (scotchlite or flourad or novac for example). Using 8.5. Thank You.

 

[dgillz]

Wow that was a challenge. I am insterestred if there is an easier way of doing this. I would guess a UFL may be in order if this is soimething you would do often. At any rate, here is my solution:

//returns the left 2 characters, plus the TM symbol
left({3m.Desc},2)+chr(153)+" "+

//returns the middle piece of the streing plus the TM symbol. The test for the existence of a space in the second piece of the string was necessary in case there is no more description beyond the second word.

if instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot= 0 then right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)+Chr(153) else
left(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot),instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot-1)+Chr(153)

//returns the last piece of the string, or a null string if there is no description beyond the second word.
+if instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot= 0 then "" else " "+right(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot),length(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot))-Instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot)

I have a mini Access database and a crystal report (v7) I wrote to test this. If interested let me know and I can email it to you.

dgilsdorf@altavista.com
Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[dgillz]

Wow that was a challenge. I am insterestred if there is an easier way of doing this. I would guess a UFL may be in order if this is something you would do often. At any rate, here is my solution:

//returns the left 2 characters, plus the TM symbol
left({3m.Desc},2)+chr(153)+" "+

//returns the middle piece of the streing plus the TM symbol. The test for the existence of a space in the second piece of the string was necessary in case there is no more description beyond the second word.

if instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot= 0 then right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)+Chr(153) else
left(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot),instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot-1)+Chr(153)

//returns the last piece of the string, or a null string if there is no description beyond the second word.
+if instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot= 0 then "" else " "+right(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot),length(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot))-Instr(right({3m.Desc},length({3m.Desc})-instr({3m.Desc}," &quot)," &quot)

I have a mini Access database and a crystal report (v7) I wrote to test this. If interested let me know and I can email it to you.

dgilsdorf@altavista.com
Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[strom99]

Worked great, thank you. Have one additional question. I also have a product description field on my page header that needs to do something similar. For instance I could have the following fields from a SQL database

Scotchlite(TM) Glass Bubbles
Flourad(TM) Flourochemical Acid
Flourad(TM) Flourosurfactants

I have been playing with your formula and cannot quite get it. What I would need to do is trim the (TM) from first name of the product and add chr(153) and then display the rest of the product name. Thanks for the help...

 

[dgillz]

Much Easier

Replace({desc},"(TM)",chr(153))

You might be able to use replace and wildcards to make the first formula easier too. Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[mbarron]

dgillz,

Is there a bug in CR8 that causes this formula not to work? For some reason the chr(153) caused a truncation of the field. I used the "3M(TM) Scotchlite(TM) Glass Bubbles K1" from above and the result was 3M(TM) Scotchl. (the first "TM" was superscripted), but the rest of the field was gone.

Mike

 

[mbarron]

Here's a loop that should work. It allows for multiple (TM)'s - limited only by the text field size limit.

local stringvar origtext:={@3M};local numbervar loop;local stringvar output;
For loop:=1 to length(origtext) do
(
local stringva inc:=origtext[loop];
local stringvar outc;
if origtext[loop to loop+3] = "(tm)"
then (loop:=loop+3;outc:=inc[loop-4]+chr(153))else
outc:=inc;
output:=output+outc);
output


Mike

 

[strom99]

mbarron,

I used the formula, but changed the -1 to -5. Also the Replace() formula also worked great, easier than the other.

 

[mbarron]

I agree, the Replace() is much easier, but for some reason it was truncating my results when I used the CHR(153). It worked fine if I used a string as the replacing value. Maybe it's a bug with version 8.

 

[dgillz]

I tested the Replace solution in version 8.5. I went straight from 7 to 8.5 so I cannot test on version 8 Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine