Using SQL to export notes from tables [ Edited ]

[tcouch]

Edit:

Version: Web Premium v21

Server is on shared DB

Installed on Server 2016

Let me preface this question with the fact that I am not a SQL admin, so apologies if I don’t user the proper vernacular.

I am exporting the notes from the ACT database and wanted to know if there was a “master” table that could be queried or if the notes contained in TBL_COMPANY_NOTE, TBL_GROUP_NOTE, and TBL_CONTACT_NOTE were all separate tables that need to be queried and exported.

I did see that there is a TBL_NOTE, but it seems to contain less rows than the 3 previously mentioned. So I wasn’t sure if there was some kind of dedupe going on, or if anyone had any insight to at least point me in the right direction.

Thanks

[Roy_Laudenslager · Accepted solution]

All notes are in the Notes table. The other tables you listed contains link records from the Contact, Company, etc. to it's actual Notes. For example a Note that's connected to a Contact and a company will have one entry in the Notes table and a separate entry in the Contact note and the Company note table but they will only be links, not the actual note.

[Roy_Laudenslager · Accepted solution]

All notes are in the Notes table. The other tables you listed contains link records from the Contact, Company, etc. to it's actual Notes. For example a Note that's connected to a Contact and a company will have one entry in the Notes table and a separate entry in the Contact note and the Company note table but they will only be links, not the actual note.

[tcouch]

Thank you. That's exactly the information I needed. It's a shame that there isn't a native way to export the note, but I'm learning more SQL than I ever wanted to. 

[alanmlee]

Another issue I hear about when someone looks at the raw data is the formatting codes in the note details field.  Fonts, Bold, underline... that all adds rich text formatting codes.  That extra info needs to be cleaned up to use the note as plain text. Many applications like importing it into another system require clean plain text.

I wrote a transformation routine to convert Rich Text Format to Plain Text for our import/ export utility: OAK!Merge for Act!  You apply that transformation to the details field for notes, history, and activities if you need plain text.  Anyone who needs to export info but does not what to dig into programming can do it with the addon tool: OAK!Merge.  

Alan

Alan Lee

1st ACC and still standing

www.OAKMerge.com