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
*** m.fn is our note stored with RTF
lortf.TextRTF = m.fn
*** Return plain 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 AERROR(laError) MESSAGEBOX( laError, "Error " + TRANSFORM(laError)) RETURN .F. ENDIF RETURN .t. ENDPROC