Notes fields in GoldMine are of unlimited length, which poses a challenge to users who want to work with them in reporting or analysis.
MasterMine makes is fairly easy to parse out values from longer fields, including the "Notes" windows of activity records, details records and some Opportunity, Project, and Case records. You can apply the usual SQL functions such as Left(), Substring(), and others, as well as MasterMine's provided udfMMParse() function to extract "reportable" data from long character fields.
Report using whole Notes (long) fields
In order to make them manageable, MasterMine extracts Notes fields from your data in sequentially numbered segments, each 253 bytes (characters) long. These segments, with names like “CS.Notes”, “CSNotes2”, “CSNotes3”, etc. must remain contiguous in the MasterMine query in order for MasterMine to handle them properly, as described below.
The user must decide how many of these segments to use for the job at hand. Although notes can be theoretically of unlimited length, consider what the maximum length of one of your notes might be in practice or, alternatively, how much of the longer notes you are willing to ignore for your report. A typical user might decide that everything they need is probably contained in the first 253 characters of the notes, and therefore would include only CS.Notes (not CS.Notes2, etc.) in their query.
If you need more than the first 253 characters, check on additional segments as necessary. Be sure to start at the first notes field and check on additional segments sequentially. Checking too many notes fields will make your report querying very inefficient and may overflow machine memory in extreme cases.
In your pivot table, these segments behave the same as any other field except as follows:
- If you display a notes field in your pivot table Row Area, the “Optimize Columns” button on the MasterMine ribbon sets the width of that column to 50 characters and wraps its content into multi-line cells for easier viewing.
- In the Row area, non-printable characters in the field such as carriage returns and line-feeds appear as square boxes (□) and do not cause an actual carriage return.
- When you drill down, all note segments are re-joined into a single column and re-formatted for easy reading.
Inexperienced users often don’t realize that seeing notes is very easy to do by drilling through a MasterMine report to “pop” the relevant GoldMine record. For efficiency in refreshing data, it is better to include notes only if you know you will need to see them in your report.
HTML code in Notes fields
GoldMine includes HTML formatting codes in notes fields in its default setup. SQL versions of MasterMine since 5.1 have automatically included an “HTML filter” to prevent these codes from appearing in reports. This filter is a user-defined SQL function of the form, udfDBO$.udfMMStripHTML(CS.NOTES).
For users of dBase-based GoldMine (Standard edition), no such filter is possible. However, we recommend turning off the HTML feature in GoldMine for any notes field you intend to use in reports. Remember, there are notes fields in several different tables: Contact1, ContHist, Cal, and ContSupp.
To turn off HTML coding (and thereby leave only text in your notes fields), add the following lines, as appropriate, to your GM.INI file in the [GoldMine] section:
This will disable HTML notes for whichever tables you include in the list (you need not do all four). Remember, this will affect only newly saved notes, entered after you make this change.
When finished editing GM.INI, log into GoldMine again. All future notes you create will be without HTML.