Top  | Previous | Next

Comments Panel

The database-powered Comments Panel<br>
helps operator collaboration.

The database-powered Comments Panel
helps operator collaboration.

Description

The comments panel is used to power a blog-style comments system within your project. This can be useful for ad-hoc collaboration and communication between shifts, remote users, etc. This component is driven by a dataset that should be bound to a SQL query. Unlike most components, this component has built-in functionality to alter an external database. This is how the Add Note functionality works. You have the opportunity to alter the queries that the components uses by changing their properties.

 

The schema that typically drives this component involves up to two tables. One table (by default: Notes)  stores all of the notes across the board. The second table (by default, ItemNotes) is used to associate notes with other things. This allows you to have different sets of notes for different screens/objects. Typically you'd bind the data to a query that joined these tables together restricting the second identifier in the ItemNotes table to the value appropriate for the window you're on. You'll also need to alter Insert Query 2's "YOURID" placeholder so that new notes get put in the right spot. You can opt out of this two-table system by simply clearing out Insert Query 2.

 

Users can be given the choice to remove their own comments, and comments can have files attached. To allow attachments, make sure you have a BLOB field in your notes table.

 

This component expects that its dataset is populated with the following columns. The names do not need to be exact, but the data type in your notes table must match.

ID - an integer that should be the primary key for the notes table. Used for deleting and looking up attachments.
Username - the user who added the note. Must be a string/varchar.
Timestamp - when the note was added. Must be a Date or DateTime data type.
NoteText - The text of the note itself. Must be a string/varchar.
AttachmentFilename - filename for a file attached to the note. Must be a string/varchar.
Stick - 0 or 1 indicating whether or note the note is "sticky", which means it gets highlighted and put at the top. Must be a boolean or integer.

 

A short explanation for each of the queries and what is passed into them automatically. Note that the column names here do not need to match the ones in the Data property.

Insert Query 1: INSERT INTO Notes (Note, WhoID, TStamp, Attachment, Filename, Sticky) VALUES (?, (SELECT Id FROM Users WHERE Username='%s'), CURRENT_TIMESTAMP, ?, ?, ?)

 This query will insert into your note table using the runPrepStmtGetKey() function and will be given four variables in the following order: note text, attachment blob, attachment filename, and sticky value. Also, it will pass in one string denoted by the %s. This is the name of the user that entered the note and does not need to be placed in any specific spot. If you WhoID field is a string, you can replace (SELECT Id FROM Users WHERE Username='%s') with '%s' to pass the username in directly.

Insert Query 2: INSERT INTO ItemNotes (AccountId, NoteId) VALUES (YOURID, %d)

 This query is optional and will insert the note id from Insert Query 1 into a mapping table of your choice. You must replace YOURID with something meaningful for your mapping table. This is most commonly done by binding this query to an expression. The reason for this second query is to have a mapping table to be joined to the note table to filter out which notes belong to a particular Comment Panel component.

Delete Query: DELETE FROM Notes WHERE Id=%d

 This query will use the note id from the component to delete the selected note.

Unstick Query: UPDATE Notes SET Sticky=0 WHERE Id=%d

 This query will use the note id from the component to set the sticky value to 0.

Download Attachment Query: SELECT Attachment FROM Notes WHERE Id=%d

 This query will use the note id from the component to download the attachment blob from the database.

 

Sample queries for the Data property binding:

Note that the data types in the database must be correct and the columns must be in this order

 

Assuming WhoID is a string that contains the username:

SELECT ID, WhoID as 'Username', TStamp as 'Timestamp', Note as 'NoteText', 

 Filename as 'AttachmentFilename', Sticky as 'Stick'

FROM notes

ORDER BY TStamp DESC

 

If WhoID is a foreign key linked to the Users Table:

SELECT n.ID, u.Username, n.TStamp, n.Note, n.Filename, n.Sticky

FROM notes n

INNER JOIN users u ON u.ID = n.WhoID

ORDER BY TStamp DESC 

 

If WhoID is a string and your ItemNotes table links AccountId to NoteId

SELECT n.ID, n.WhoID, n.TStamp, n.Note, n.Filename, n.Sticky

FROM notes n

INNER JOIN ItemNotes i ON i.NoteId = n.ID

WHERE i.AccountID = 5

ORDER BY TStamp DESC 

 

Properties

Appearance

Font

Font of text of this component

Scripting name

font

Data type

Font

Foreground Color

The foreground color of the component.

Scripting name

foreground

Data type

Color

Sticky Header Color

The background color of the header for sticky notes

Scripting name

stickyHeaderColor

Data type

Color

Sticky Note Color

The background color for stick notes

Scripting name

stickyNoteColor

Data type

Color

Header Color

The background color of the header notes

Scripting name

headersColor

Data type

Color

Note Color

The background color for notes

Scripting name

noteColor

Data type

Color

Date Format

The format string to use for the date of the note.

Scripting name

dateFormat

Data type

String

Add Note Text

The word(s) used for the "Add Note" button.

Scripting name

addNoteText

Data type

String

Cancel Text

The word(s) used for the "Cancel" button.

Scripting name

cancelText

Data type

String

Sticky Text

The word(s) used for the "Sticky" checkbox.

Scripting name

stickyText

Data type

String

Attach File Text

The word(s) used for the "Attach File" link.

Scripting name

attachText

Data type

String

Padding

The amount of padding between the notes.

Scripting name

padding

Data type

int

Behavior

Database Connection

Name of the database connection to run the queries against. Leave blank to use project's default connection.

Scripting name

datasource

Data type

String

Insert Query 1

This insert query will insert a new note into a notes table.
The placeholder %s will be replaced with the current username.
The query will be run as a prepared statement, so the query also needs to accept parameters by using the ? placeholder.
If attachments are enabled it will use four parameters: Note Body, Attachment Bytes, Attachment Name, Sticky (0/1)
When attachments are disabled, it will use two parameters: Note Body, Sticky (0/1)

Scripting name

insertQuery1

Data type

String

Insert Query 2

This optional insert query inserts the mapping for a new note into a mapping table.
%d will be replaced with the ID of the new note.
To disable this behavior, simply set this property to a blank string.

Scripting name

insertQuery2

Data type

String

Delete Query

This query is used for deleting a note. %d is replaced with the note's ID

Scripting name

deleteQuery

Data type

String

Unstick Query

This query is used for changing a note's status to be not sticky. %d is replaced with the note's ID

Scripting name

unstickQuery

Data type

String

Download Attachment Query

This query is used for downloading binary attachments. %d is replaced with the note's ID

Scripting name

getAttachmentQuery

Data type

String

Delete Mode

Controls if anyone can delete any note, noone can delete a note, or only owners can delete their notes

Scripting name

deleteMode

Data type

int

Flags

expert

Values

0

No Deletes

1

Owner Deletes

2

Any Deletes

Attachments Enabled

Controls whether or not files can be attached to notes.

Scripting name

attachmentsEnabled

Data type

boolean

Maximum Attachment Size

The maximum attachment size in bytes that will be accepted.

Scripting name

maxAttachmentSize

Data type

long

Download Mode

What to do when an attachment is downloaded.

Scripting name

downloadMode

Data type

int

Values

0

Save

1

Open

Skip Audit

If true, update queries originating from this component will skip the audit system. Can be important when attachments are turned on.

Scripting name

skipAudit

Data type

boolean

Flags

expert

Touchscreen Mode

Controls when this input component responds if touchscreen mode is enabled.

Scripting name

touchscreenMode

Data type

int

Flags

expert

Values

0

None

1

Single-Click

2

Double-Click

Common

Name

The name of this component.

Scripting name

name

Data type

String

Flags

bindable

Enabled

If disabled, a component cannot be used.

Scripting name

componentEnabled

Data type

boolean

Visible

If disabled, the component will be hidden.

Scripting name

visible

Data type

boolean

Flags

bindable

Border

The border surrounding this component. NOTE that the border is unaffected by rotation.

Scripting name

border

Data type

Border

Mouseover Text

The text that is displayed in the tooltip which pops up on mouseover of this component.

Scripting name

toolTipText

Data type

String

Data

Data

Fill this DataSet in with the notes for the desired entity. Columns are:
Id, Username, Timestamp, NoteBody, Filename, IsSticky

Scripting name

data

Data type

Dataset

Flags

bindable

Data Quality

The data quality code for any tag bindings on this component.

Scripting name

dataQuality

Data type

int

Flags

bindable | expert

Scripting

Events

The following event sets are fired by this component. See Component Event Handlers to learn more.

mouse
mouseMotion
propertyChange

 

Scripting Functions

This component has no special scripting functions.