With the upgrade to version 22 of Act! CRM comes the migration of notes and other fields from Rich Text Format (RTF) to HTML, this is probably a good thing, but made mess of old notes. The most obvious issue – edited notes from previous versions of Act! were double spaced. It doesn’t seem like a big deal until you use the product. Act! isn’t the first CRM to migrate from RTF to HTML, indeed my pleas for help on various tech forums were frequently greeted with requests that I publish my solution.
Initially, I thought the obvious answer was to convert the RTF data into plain text. I don’t care about formatting in my notes, indeed, I prefer that all notes be plain text with a font specified in options. As a side note, we found a commercial solution for Act!, but being a geek, I wanted to solve this for myself. If you are looking to solve this issue, I recommend you reach out to ActToday for their solution. This solution is for geeks that don’t mind getting in database weeds. Also, you will need Microsoft licenses for these developer tools.
It turns out three lines of code was all that was needed to use Microsoft’s RichTx32 RTF component to convert our notes from RTF to text:
*** Define the RichText object
lortf=CREATEOBJECT("richtext.richtextctrl")
*** m.fn is our note stored with RTF
lortf.TextRTF = m.fn
*** Return plain text
return ALLTRIM(lortf.Text)
Things get a bit more complicated if you want to convert all Act! notes to text, indeed after doing so, I found, what I really wanted done was to convert the notes to HTML. With a little help from Google, Reddit and a RentACoder, I ended up with this solution:
*** June 1,2020 - Katy Computer Systems https://katycomputer.com
*** Project name
*** Update SQL table using Visual FoxPro
***
*** Issue
*** I have a CRM SQL database, one of the fields is formatted using RTF
*** I wanted to strip the RTF and convert it to plain text using VFP9
*** After conversion, I realized, conversion to text, then HTML was better for my purposes
***
*** Requires
*** RichTx32.Ocx in c:\windows\System32 & c:\windows\SysWOW64
*** reg add HKCR\Licenses\DC4D7920-6AC8-11cf-8ADB-00AA00C00905 /ve /t REG_SZ /d iokouhloohrojhhhtnooiokomiwnmohosmsl /f
*** regsvr32 /s c:\windows\system32\Richtx32.ocx c:\windows\SysWOW64\Richtx32.ocx
***
*** Research
*** https://www.stonefieldquery.com/onlinehelp/sdk/_2dy0k4j6o.htm
*** https://www.one-tab.com/page/kGBMa_-sTQ-3rJ_xTx0pMg
*** https://www.one-tab.com/page/IFkHH5pjQW2aS594tttI_w
*** https://www.freelancer.com/u/kalpesh2804
***
*** Updates
*** TBL_ACTIVITY.details
*** TBL_HISTORY.details
*** TBL_NOTE.notetext
***
*** Notes
*** https://acttoday.com.au/ offers a utility to convert Act! fields into plain text
PUBLIC lnConn, lortf
lortf=CREATEOBJECT("richtext.richtextctrl")
DO OPENDB WITH "localhost", "Test2"
DO updatetable WITH "TBL_ACTIVITY", "details", "activityid"
DO updatetable WITH "TBL_HISTORY", "details", "historyid"
DO updatetable WITH "TBL_NOTE", "notetext", "noteid"
RETURN SQLDISCONNECT(lnConn)
PROCEDURE updatetable
LPARAMETERS tbl, fld, id
SQLEXEC(lnConn,"select &id, &fld, &fld as ConvertText FROM [dbo].[&tbl]","myCurs")
SELECT mycurs
SCAN FOR LEN(myCurs.&fld) > 1
lcNoteID = myCurs.&id
lcNoteText = R2H(myCurs.&fld)
SQLEXEC(lnConn,"Update [dbo].[&tbl] set &fld = ?lcNoteText Where &id = ?lcNoteID ")
replace ConvertText WITH lcNoteText
ENDSCAN
*** Used during testing: BROWSE FIELDS &fld, n=LEFT(&fld, 200):25, t=LEFT(ConvertText, 250):150, u=LEFT(R2H(&fld),250):50, a=OCCURS(CHR(10), ConvertText), d=OCCURS(CHR(13), ConvertText)
RETURN .t.
ENDPROC
FUNCTION R2T
lparameter m.fn
lortf.TextRTF = m.fn
return ALLTRIM(lortf.Text)
ENDFUNC
FUNCTION R2H
lparameter m.fn
ht=m.fn
IF LEN(ALLTRIM(m.fn)) > 1
style = '<span style="font-family: Tahoma;font-size: 12px;">'
ht = R2T(m.fn)
ht = STRTRAN(ht, CHR(13)+CHR(10), "<br>")
ht = STRTRAN(ht, CHR(10), "<br>")
ht = STRTRAN(ht, CHR(13), "<br>")
ht = STRTRAN(ht, CHR(9), " ")
DO WHILE " <br>" $ ht
ht = STRTRAN(ht, " <br>", "<br>")
ENDDO WHILE
DO WHILE "<br><br><br>" $ ht
ht = STRTRAN(ht, "<br><br><br>", "<br><br>")
ENDDO WHILE
DO CASE
CASE LEFT(ht, 4) = "<br>"
ht = SUBSTR(ht,5)
CASE RIGHT(ht,4) = "<br>"
ht = LEFT(ht, LEN(ht)-4)
ENDCASE
ht = IIF(style$ht, '', style) + ht
ENDIF LEN
return ht
ENDFUNC
PROCEDURE OPENDB
LPARAMETERS svr, db
lcConnStr = "driver=SQL Server;server=&svr\act7;database=&db;Trusted_Connection=yes;"
lnConn = SQLSTRINGCONNECT(lcConnStr, .T.)
IF (lnConn < 0)
LOCAL ARRAY laError[1]
AERROR(laError)
MESSAGEBOX( laError[2], "Error " + TRANSFORM(laError[5]))
RETURN .F.
ENDIF
RETURN .t.
ENDPROC