Display text- and image-based trend annotations in a trend control

Use trend annotations to display additional text and images in a trend control during project run time. The annotations' content and settings are stored in the same database that stores the historical data.

Note: This feature is not supported in projects that are configured to run on the Embedded target platform. For more information, see About target platforms, product types, and target systems.

When you configure the data source for a Trend Control object, you can connect to an external database that stores the historical data that you want to display. The historical data might be generated by a Trend worksheet in the same project, or they might be provided by some other source, but in either case, they are typically stored in a single database table with one field (column) for each trend point.

A database can have many tables, however, so you can create another table to store your trend annotations. The table can be named anything (for example, TrendNotes), but it must have the following fields (columns):

With the table's fields (columns) configured like this, each record (row) in the table can store the content and settings for an single annotation. Then, in the trend control's Data Source settings, in the Ann. Source box, type the name of this table (for example, TrendNotes). For more information, see Data Sources.

With everything properly configured, the project runtime server will get the data stored in this table and use them to display your annotations in the trend control during project run time. For example, given a record (row) with the following values:

…the annotation will be displayed in the trend control like this:l

Please note that the red lines and captions in the example above are included only to highlight the borders (X1, X2, Y1, Y2) of the annotation box. They would not actually be displayed like this during project run time.

Add and edit annotations during project run time

Note: The following tips and code examples are for demonstration purposes only, and they are taken from the sample project named "Trend Annotation ID" that is available for download from our website (www.indusoft.com). If you want to add these features to your project, we recommend that you download and explore the sample project first, so that you can understand how all of the different parts of the project work together. Keep in mind that you will probably need to modify the code in order to have it work properly in your own project. In particular, make sure the database connection string specifies the correct provider, source, and credentials for your external database — the sample project uses a Microsoft Access database file (dbDemo.mdb) that is located in the project folder.

Your project's connection to an external database allows both reading and writing, so in addition to getting annotations from the database and then displaying them in the trend control, you can also add, edit, and even delete annotations during project run time.

In order to do so, however, you must make sure that the annotations table actually exists in the database. If you try to get annotations from a table that does not exist, it will not cause serious problems — the annotations simply will not be displayed. But if you try to add annotations to a table that does not exist, that information might be permanently lost.

You can use VBScript in your project to verify that the annotations table exists or create it if it does not. First, in the Procedures folder in the Project Explorer, define the following three procedures:

Function VerifyTableExists()

Dim Conn, TablesSchema, foundTable

On Error Resume Next

foundTable = 0

Set Conn = CreateObject ("ADODB.Connection")

' Open the connection to the database

Conn.Open $ConnectionString

' Open the database schema to query the list of tables. Extract the list in a Recordset object

Set TablesSchema = Conn.OpenSchema (20)

' Loop through the list and print the table names

Do While Not TablesSchema.EOF

If UCase(TablesSchema ("TABLE_Type")) = "TABLE" Then

If UCase(TablesSchema ("TABLE_NAME")) = "TRENDNOTES" Then

foundTable = 1

Exit Do

End If

End If

TablesSchema.MoveNext

Loop

'Create TrendNotes Table

If foundTable = 0 Then Call CreateTrendNotesTable()

' Close and destroy the recordset and connection objects

TablesSchema.Close

Conn.Close

Set TablesSchema = Nothing

Set Conn = Nothing

End Function

Function CreateTrendNotesTable()

Dim sql

On Error Resume Next

sql = "CREATE TABLE TrendNotes "_

& " ( ID Integer Not Null CONSTRAINT RestrictCamp PRIMARY KEY, "_

& " Type Integer Not Null, "_

& " AnnotationID Text(50) Not Null, "_

& " AnnotationContent Text(50) Not Null, "_

& " X1 datetime Not Null, "_

& " Y1 Integer Not Null, "_

& " X2 datetime Not Null, "_

& " Y2 Integer Not Null, "_

& " Z Integer Not Null, "_

& " ImageFitMode Integer, "_

& " TextBaseWidth Integer, "_

& " TextBaseHeight Integer, "_

& " Font Integer Not Null "_

& " ); "

$DBExecute("DB", sql)

End Function

Function AddComment(X,Y,FontSize,Comment)

Dim X1, X2, Y1, Y2, sql, numCur,numRows, uDate, uTime, uSec

On Error Resume Next

'The comment is written based on the upper left coordinate (X1, Y2).

Y1 = -1000000

Y2 = Y

X1 = X

X2 = DateAdd("d", 1, X1)

'Gets the next ID

sql = "SELECT * FROM TrendNotes"

numCur = $DBCursorOpenSQL("DB", sql)

numRows = $DBCursorRowCount(numCur)

If numRows<1 Then

numRows = 1

Else

numRows = numRows+1

End If

$DBCursorClose(numCur)

'Saves to DB and insert comment

$DBExecute("DB", "Insert into TrendNotes (ID,AnnotationID,X1,Y1,X2,Y2,Z,Type,AnnotationContent,ImageFitMode,[Font],TextBaseHeight,TextBaseWidth) Values (" & numRows & ",'Pens','" & X1 & "'," & Y1 & ", '" & X2 & "'," & Y2 & ",1,1,'" & Comment & "',0,'" & FontSize & "',0,0)")

End Function

For more information, see VBScript Global Procedures.

The first procedure opens a connection to the database and then searches it for the annotations table (for example, TrendNotes). If it does not find the table, it calls the second procedure, which subsequently creates a new table with the necessary fields (columns). The third procedure adds a new record (row) to the annotations table, according to the user's input (that is, cursor position, font size, comment text); it is called by the Add Comment screen, which is described later in this section.

Once you have defined these procedures, update your project's Startup Script to call the first procedure when the project is run:

$ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $GetAppPath() & "dbDemo.mdb"

Call VerifyTableExists()

For more information, see VBScript Startup Script.

Now you can create the project screens that will actually be used to add and edit annotations during project run time. (Please note that this example only shows how to handle text-based annotations. Image-based annotations could be handled in a similar manner, but they would require additional steps describing how to save and load image files, and those are beyond the scope of this example.) First, in the main screen that displays the trend control, insert buttons for Add Comment and Edit Comments.

Configure the Add Comment button to get the current position of of the cursor in the trend control and then open a pop-up screen that can be used to add a comment at that position. To do this, add the following VBScript to the button's On Up event:

$demoTrend.NotesY = $yCursorValue

$demoTrend.NotesX = $StrGetElement($xCursorValue,".",1)

$Open("AddComment")

Similarly, configure the Edit Comments button to open another pop-up screen that can be used to edit or delete existing comments in the annotations table:

$Open("EditComment")

For more information, see Command animation.

Tip: In most cases, you should add code like this to a button's On Up event and not its On Down event. The On Down event is immediately triggered when the user clicks or taps the button, even if they do so accidentally or they change their mind in mid-click. In contrast, the On Up event is triggered only when the button is fully released, which gives the user an opportunity to slide the cursor off the button and thereby abort the command.

In the Add Comment screen, configure the Comment and Font Size boxes to capture the user's input, and then configure the Apply button to call the AddComment procedure that you defined earlier:

Call AddComment($demoTrend.NotesX,$demoTrend.NotesY,$demoTrend.NotesFontSize,$demoTrend.NotesText)

$demoTrend.TrendReload = $Toggle($demoTrend.TrendReload)

$Close("AddComment")

In the Edit Comment screen, configure a Grid object to display the contents of the annotations table. As long as input is enabled for the grid, whatever the user types in the cells of the grid will actually be saved in the database. For more information, see Grid object.

Finally, configure the Delete button to delete any comment(s) that the user has selected in the grid:

Dim ret, i, sql, numCur, numRows

ret = MsgBox("Are you sure you want to delete this comment?",4)

If ret = 6 Then

sql = "SELECT * FROM TrendNotes"

numCur = $DBCursorOpenSQL("DB",sql)

numRows = $DBCursorRowCount(numCur)

$DBExecute("DB","DELETE FROM TrendNotes WHERE ID=" & $SelText)

For i = $SelText+1 To numRows

sql = "UPDATE TrendNotes SET ID=" & i-1 &" WHERE ID=" & i & ""

$DBExecute("DB",sql)

Next

End If

$demoTrend.TrendReload = $Toggle($demoTrend.TrendReload)

Any and all changes that the user makes to the annotations table during project run time should be immediately displayed in the associated trend control, as long as the values saved in the table are valid.