Here Is the Solution: Syteline Notes in SQL Versions - Overview and Data Structures

Here is the solution: SyteLine Notes in SQL versions - overview and data structures

Overview

In SyteLine 7 and higher, you have the ability to add notes to a specific record in any table that will be associated only with that record. You also have the ability to create reusable notes that can also be linked to any record. Therefore, the list of notes associated with any record may consist of any number of specific and/or reusable notes. Each note can have a description and a body which may consist of either text or an attachment (but not both).

Reusable notes can either be used only by the user who created it ("user notes") or can be made available for all users to utilize ("system notes"). You can create reusable notes by selecting View, System Notes while any form is open. Doing so just creates the reusable note. It does not associate it with the current record in the active form. When adding the note, the System checkbox controls whether the note will be a user note or a system note. Leaving it unchecked means the note will be a user note.

To access the notes window for a specific record, select the notes icon in the toolbar or choose "Notes for Current" from the right-click of Actions menu. You can then add a specific note or attach a reusable note. When either is done, you have the option of marking the note as internal. There are also checkboxes that will indicate if the note is reusable and if it is a system note

You can also create notes that are associated with a table (e.g. CO line items) which will then be associated with all records in that table ("class notes"). You can add or access class notes by selecting "Notes for All" from the right-click or Actions menu. Class notes must be a reusable note and can be either a user or system note.

Supporting Tables:

The NoteHeaders table holds a maximum of two records per SyteLine table and is mainly used to indicate which notes are internal and which are external. Each record has a token (NoteHeaderToken), the table name to which it relates (ObjectName), and a NoteFlag which holds either 0 for external notes and 1 for internal notes. For example, when the first external note is added to a CO line item in a database a record will be created with a token (e.g. 30), an ObjectName of coitem, and a NoteFlag of 0. The first time an internal note is then added to a CO line, a second NoteHeaders record will be created with a different token (e.g. 78) and NoteFlag of 1.

The NoteTypes table holds available note types. There are currently two records in this table for the available types of note (for text) or attachment. The record for text has a NoteType of 0 and a NoteDesc of "Note". The other record has a NoteType of 1 and a NoteDesc of "Attachment".

Tables that Store the Notes:

The various types of notes are stored in the following three tables:

SpecificNotes - Holds non-reusable notes added to a specific record

UserNotes - Holds reusable notes for which the System box is unchecked

SystemNotes - Holds reusable notes for which the System box is checked

Each of those tables has a token field which uniquely identifies the record (SystemNoteToken, UserNoteToken, and SystemNoteToken). Each also has two fields that hold the note description (NoteDesc) and the text of the note (NoteContent).

Tables that Link Records to Their Notes

The ObjectNotes table links a specific record to its notes. Included in this tables columns are the following:

ObjectNoteToken - Identifies each record in the table

RefRowPointer - The link between a record and its notes. Coincides with the RowPointer value in the record to which the note is tied (the RowPointer field exists in all tables and is the Sequel record identifier).

NoteHeaderToken - It holds the token value from the NotesHeader record that corresponds to whether the note is internal or external. Using the above example, the ObjectNotes records that relate to all notes tied to any CO line item will have either 30 or 78 in this field, depending on whether they are an internal or external note.

The following three columns are used for linking an ObjectsNotes record to the record in the table where the notes are stored:

SpecificNoteToken

UserNoteToken

SystemNoteToken

As described above, a note may be either a specific note, reusable user note, or reusable system note. Which it is controls in what table the note is stored. Therefore, in any given ObjectNotes record, one of the above fields will be populated and the other two will hold the NULL value. For example, if a note added to a CO line is a reusable user note, the UserNoteToken field will hold the token value of the note's record in the UserNotes table and the other two will be NULL.

The ClassNotes table links a table to its notes. Records are created in this table when you select Notes for All while a form is open and then attach a reusable note. Like the ObjectNotes table, the ClassNotes table has a token field to identify its records and a NoteHeaderToken field. It also has UserNoteToken and SystemNoteToken fields that point to the appropriate records in those tables. There is SpecificNoteToken field since class notes must be a reusable note.

NoteExistsFlag - All tables in the database have a field called NoteExistFlag. It is set to 0 when the record is created and changed to 1 when notes are linked to the record.