Legacy Comments Panel

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.

• Sticky - 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.
  

The comments panel also has a set of default queries to handle adding, deleting, and unsticking comments. The default queries expect certain database tables and columns to be set up. If your database is set up differently, or your dataset is different than the default, remember to change the default queries.

Table:Notes 

• Id - An auto-incrementing integer that is the primary key. This maps to the ID field in the dataset.
• WhoID - A string or varchar mapping to the Username field in the dataset
• TStamp - A Date or DateTime mapping to the Timestamp field in the dataset
• Note - A string or varchar mapping to the NoteText field in the dataset.
• Filename - A string or varchar mapping to the AttachmentFilename in the dataset
• Sticky - A boolean or int mapping to the Sticky field in the dataset.
• Attachment - A blob to hold the attachment data. Blobs don't exist in MSSQL, so a varbinary() type must be used.

Table: ItemNotes

• AccountId - A user-defined field for use with Insert Query 2.
• NoteId - An integer that maps to the ID field in the dataset.
  

Tables: Users

• Username - A string or varchar that is used by Insert Query 1 to populate Notes.WhoID

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.

There are many ways to set up the Comments Panel component, but the same first few steps exist in all cases to get started. To set up a simple Comments Panel example, here are the basic steps:

1. Add the Comments Panel component to a window.
2. Create or locate a database table to store notes in. You can follow the sample table in the "data" property of the Comments Panel Component.
3. Add a SQL query to the "data" property of the component to fetch the notes. Make sure to sort the records by timestamp so your newest comments are on top.
4. Modify the "Insert Query 1" property to match your notes table definition. You can remove the string from the "Insert Query 2" property.
5. Modify the "Delete Query", "Unstick Query", and "Download Attachment Query" properties to match your notes table definition.
6. Save your project and test it out! Check out the University video for a more thorough walk-through.

This component does not have any custom properties.

There are no examples available for this component.