What information contains the NOTES table and what do the RECTYPES mean?
While it seems to be obvious what information the NOTES table contain, but there appear records with different RECTYPE values which are not obvious and also seem not to be documented.
Can you explain RECTYPE values in the NOTES table?
- Since GoldMine Premium Edition 8.5.x several notes were merged into a separate table NOTES. The nature of the notes can be determined by the record's RECTYPE value.
- The following RECTYPES are possible
C1 – Contact Notes >> These notes are available on the Contact record's Notes tab
CS – Case Notes >> These notes are available on a Cases' Notes tab
CR – Case Resolution Notes >> These notes are available on a Cases' Resolution tab
OP – Opportunity Notes >> These notes are available on an Opportunity's Notes tab
PR – Project Notes >> These notes are available on a Project's Notes tab
- The ACCOUNTNO column contains the linked primary contact's ACCOUNTNO
- The LOPRECID column contains the unique identifier of the specific items RECID, for example the Cases' RECID for Case Resolution Notes or Cases' Notes, the Opportunity's RECID for Opportunity Notes etc.
- As most other notes, also the real note is stored in a binary format and needs to be converted into a readable format when a note will be used outside of GoldMine for example within reports, SQL Queries etc. While there exist further generic or specific related Knowledge Articles for example Knowledge Article #15831 : Argument data type varchar is invalid for argument 2 of like function error when searching for notes via SQL query within GoldMine or Knowledge Article #14442 : Certain data like contact notes and e-mail bodies appear in Crystal reports or other reporting applications or after exporting as encrypted, how can those be decrypted? a generic query for converting the notes from the Notes table would be
Select CAST(CAST(NOTE AS varbinary(MAX)) AS varchar(MAX)) as Note from NOTES
- Further Notes within GoldMine which are not displayed in own tabs are still stored in there related table like Activity notes (CAL->NOTES, CONTHIST->NOTES) etc.