Vision - Comments Panel

Component Palette Icon:

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. It expects three tables in the database, and that they are queried properly on the data property.

You can opt out of this three-table default system by simply making use of the Extension Functions on the component. See below for more details.

note

The following section assumes the default configuration: all Extension Functions on the component are disabled.

Three-Table (Default) Configuration

Required Database Tables

The default behavior of the component expects three database tables be present under the same database connection, and each table needs to have certain columns with specific names.

Table: Notes

Stores all of the notes across the board.

Column Name	Description	Data Type
id	An auto-incrementing integer that is the primary key. This maps to the ID field in the dataset.	Integer
whoID	A mapping to the Username field in the dataset	Integer
tStamp	A mapping to the Timestamp field in the dataset	Date or Datetime
note	A mapping to the NoteText field in the dataset	Varchar
filename	A mapping to the AttachmentFilename in the dataset	Varchar
sticky	A mapping to the Sticky field in the dataset	Boolean or Integer
attachment	A column to hold the attachment data. LongBlobs do not exist in MSSQL, so a varbinary type must be used	LongBlob or Varbinary (depending on database)

Table: ItemNotes

Used to associate notes with other things. This allows you to have different sets of notes for different screens/objects.

Column Name	Description	Data Type
accountId	An automatically generated UUID for the Comment Panel instance. You can use the accountId in a WHERE clause on the data property so that the component only shows notes from a particular Comments Panel in the project.	Varchar
noteId	An integer that maps to the ID column on the Notes table

Table: Users

A user mapping table that assigns an ID to each user on the table. This is easiest to do if a database authentication profile is used as the _users table automatically creates the required columns, but non-database authentication profiles can be used as long as the table is manually created and maintained.

Column Name	Description	Data Type
id	An integer that is inserted into the whoID column on the Notes table	Integer
username	The username of the user that created the note	Varchar

Configuring the Component

This component expects that its data property is populated with the following columns. The dataset in the Data property is very specific, and expects certain datatypes at precise positions. The order of expected column positions is listed below. Should the order of datatypes in the dataset differ from the order below, the names of the columns must match the column names below. Aliasing can be used to modify the names of the columns in the dataset.

The names do need to be exact, but different names can be used as long as the query that builds the dataset uses aliases. The data type for each column in your notes table must match the table below.

Column Name	Description	Data Type	Expected Column Position
id	an integer that should be the primary key for the notes table. Used for deleting and looking up attachments	integer	0
username	the user who added the note	string/varchar	1
timestamp	when the note was added	dateTime	2
notetext	The text of the note itself	string/varchar	3
attachmentname	filename for a file attached to the note	string/varchar	4
issticky	0 or 1 indicating whether or not the note is "sticky", which means it gets highlighted and put at the top	boolean or integer	5

Example

The following query returns note data from the above tables, and displays the data on a Comments Panel component. This query should be placed in a SQL Query binding on the Data property

 SELECT
    notes.id,
    users.username as whoid,
    notes.tstamp,
    notes.note,
    notes.filename,
    notes.sticky
     
FROM
    notes
    JOIN users
    ON notes.whoid = users.id
     
ORDER BY
    notes.tstamp DESC

By default, users can remove their own comments, and comments can have files attached.

Custom Configuration

Enabling the Extension Functions on the component will allow for custom functionality on the component. Some examples are:

• Store all note data on a single database table - modify each Extension Function to run queries against a single database table
• Save the attachment to a shared drive instead of a database column - modify insertNote to save the attachment to a hard drive.
• Allow users to delete all notes by role - check the role of the user in canDelete and return True if the user has a specific role.

Properties

Property	Description	Property Type	Scripting	Category
Add Note Text	The word(s) used for the "Add Note" button.	String	.addNoteText	Appearance
Attach File Text	The word(s) used for the "Attach File" link.	String	.attachText	Appearance
Attachments Enabled	Controls whether or not files can be attached to notes.	boolean	.attachmentsEnabled	Behavior
Border	The border surrounding this component. Options are No border, Etched (Lowered), Etched (Raised), Bevel (Lowered), Bevel (Raised), Bevel (Double), and Field Border. Note: The border is unaffected by rotation. Changed in 8.1.21 As of 8.1.21, the "Button Border" and "Other Border" options are removed.	Border	.border	Common
Cancel Text	The word(s) used for the "Cancel" button.	String	.cancelText	Appearance
Data	Fill this DataSet in with the notes for the desired entity. Columns are: ID, Username, Timestamp, Note, Filename, IsSticky.	Dataset	.data	Data
Database Connection	Name of the database connection to run the queries against. Leave blank to use project's default connection.	String	.datasource	Behavior
Date Format	The format string to use for the date of the note.	String	.dateFormat	Appearance
Display Mode	Horizontal display mode will layout so that the comment header will be positioned to the left of the comment. Vertical display mode will have the comment header above the comment.	int	.displayMode	Behavior
Enabled	If disabled, a component cannot be used.	boolean	.componentEnabled	Common
Font	Font of text on this component.	Font	.font	Appearance
Foreground Color	The foreground color of the component. Can be chosen from color wheel, chosen from color palette, or entered as RGB or HSL value. See Color Selector.	Color	.foreground	Appearance
Header Color	The background color of the header notes. See Color Selector.	Color	.headersColor	Appearance
Maximum Attachment Size	The maximum attachment size in bytes that will be accepted. A value of 0 means no limit.	long	.maxAttachmentSize	Behavior
Mouseover Text	The text that is displayed in the tooltip which pops up on mouseover of this component.	String	.toolTipText	Common
Name	The name of this component.	String	.name	Common
Note Color	The background color for notes. See Color Selector.	Color	.noteColor	Appearance
Padding	The amount of padding between the notes.	int	.padding	Appearance
Quality	The data quality code for any Tag bindings on this component.	QualityCode	.quality	Data
Skip Audit	If true, update queries originating from this component will skip the audit system. Can be important when attachments are turned on.	boolean	.skipAudit	Behavior
Sticky Header Color	The background color of the header for sticky notes. See Color Selector.	Color	.stickyHeaderColor	Appearance
Sticky Note Color	The background color for sticky notes. See Color Selector.	Color	.stickyNoteColor	Appearance
Sticky Text	The word(s) used for the "Sticky" checkbox.	String	.stickyText	Appearance
Touchscreen Mode	Controls when this input component responds if touchscreen mode is enabled.	int	.touchscreenMode	Behavior
Touchscreen Keyboard Layout	New in 8.1.28 Sets the touchscreen keyboard layout to use for this component.	String	.keyboardName	Behavior
Visible	If disabled, the component will be hidden.	boolean	.visible	Common

Deprecated Properties

Property	Description	Property Type	Scripting	Category
Data Quality	The data quality code for any Tag bindings on this component.	int	.dataQuality	Deprecated

Scripting

See the Vision - Comments Panel Scripting Functions page for the full list of scripting functions available for this component.

Event Handlers

Event handlers allow you to run a script based off specific triggers. See the full list of available event handlers on the Component Events page.

Customizers

• Vision Component Customizers

Examples

caution

The following examples may need to be modifed to match the table and column names in your database. These examples are written for a MySQL database connection. If you are using a different database, some things may need to be changed. For example, using MS SQL Server requires:

• The python value None may not be used when inserting into a byte array. NULL must be used in its place.
• Binary data must be converted to a varbinary type when inserting. See the examples below

insertNote: using default table configuration

# Inserts a note using the three default tables: notes, users, and itemNotes. 
# Also stores only the file name in the database instead of the full path to the file.
# Assumes a User ID is used in the notes table.
  
# determine the ID for the logged in user
user = system.db.runScalarPrepQuery("SELECT id from users where username = ?", [system.security.getUsername()])
 
# determine if a file is being attached
if filename is None:
    # a file was not attached, provide a blank for the bytes
    attachmentBytes = None
else:
    # get the bytes of the file at the path the user selects
    attachmentBytes = system.file.readFileAsBytes(filename)
     
    # splits the file name from the file path. This way we can show just the file name on the component
    # Using '\' as a delimiter, but python requires 2 since it's an escape character
    pathAndFile = filename.rsplit('\\', 1)
    filename = pathAndFile[1]
  
# build the query and the arguments
query = "INSERT INTO Notes (note, whoid, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?)"
arguments = [note, user, attachmentBytes, filename, sticky]
# insert the note
insertId = system.db.runPrepUpdate(query, arguments)
 
# insert a row onto the itemNotes table 
# replace 'MYID' with the proper code to fetch your id
myId = 'MYID'
system.db.runPrepUpdate("INSERT INTO ItemNotes (AccountId, NoteId) VALUES (?, ?)", [myId, insertId])

insertNote: using a single table

# Similar to the above example, but only a single database table is required.
# Assumes a User Name is used in the notes table.
 
# determine the name for the logged in user
user = system.security.getUsername()
 
# determine if a file is being attached
if filename is None:
    # a file was not attached, provide a blank for the bytes
    attachmentBytes = None
     
else:
    # get the bytes of the file at the path the user selects
    attachmentBytes = system.file.readFileAsBytes(filename)
     
    # splits the file name from the file path. This way we can show just the file name on the component
    # Using '\' as a delimiter, but python requires 2 since it's an escape character
    pathAndFile = filename.rsplit('\\', 1)
    filename = pathAndFile[1]
         
# insert the note. You may need to modify this query depending on how your database handles binary data.
 
 
#MySQL query
query = "INSERT INTO Notes (note, whoid, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?)"
 
#MSSQL Server query
#We're converting the binary data into a VARBINARY datatype on this line, which is the prefered datatype for binary data in MSSQL.
#query = "INSERT INTO Notes (note, whoid, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, CONVERT(VARBINARY(MAX),?), ?, ?)"
 
arguments = [note, user, attachmentBytes, filename, sticky]
system.db.runPrepUpdate(query, arguments)