Community
Showing results for 
Search instead for 
Do you mean 
Reply

Using SQL to export notes from tables

Accepted Solution Solved
New Member
Posts: 2
Country: USA
Accepted Solution

Using SQL to export notes from tables

[ Edited ]

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


Accepted Solutions
Solution
Accepted by topic author tcouch
‎04-10-2019 02:08 PM
Platinum Elite Contributor
Posts: 6,853
Country: USA

Re: Using SQL to export notes from tables

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
Retired ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129

View solution in original post


All Replies
Solution
Accepted by topic author tcouch
‎04-10-2019 02:08 PM
Platinum Elite Contributor
Posts: 6,853
Country: USA

Re: Using SQL to export notes from tables

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
Retired ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 2
Country: USA

Re: Using SQL to export notes from tables

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. 

Copper Super Contributor
Posts: 70
Country: USA

Re: Using SQL to export notes from tables

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