While the official database documentation is nearing completion, I wanted to put together a post to help our community make use of this feature.

What is a database?

A database is a storage device for information. Databases come in many forms and sizes. They can be anything from a simple text file, to an excel file, to an advanced SQL or MYSQL database. The Noventri Digital Signage Suite is capable of connecting to any ODBC compliant database.

The database connectivity features in the Noventri Digital Signage Suite are very powerful, and extremely useful in creating and maintaining your digital signage content. In many cases the data that users wish to display on their signage already exists in a database. So why re-type all of this data when you can simply create a live link to the data? With this setup, when the data in the database changes, so does your digital signage!

View a video tutorial covering the basics of database driven content in Noventri Suite.

Here's How:


CONNECTING TO YOUR DATABASE

Step 1: In the Noventri Digital Signage Suite, on the "Design" tab, select or create the region in which you desire to populate with information from your database.

Step 2: In the bottom left corner of the screen you will see the "Properties" window. Check the "Database" checkbox.



Step 3: You will see a new tab appear in this window labeled "Advanced". Click on this tab.



Step 4: Click the radio button beside "ODBC".



Step 5: Click on the "Connect" Button.



Step 6: A new window will open. Choose the appropriate driver from the drop down, and fill in the applicable information in the text fields.



Step 7: Click "Connect". Figure 1 is an example when connecting to excel. Figure 2 is an example when connecting to SQL or MYSQL.

TIP: When connecting to Excel, the excel file must be closed so that Excel does not hold a lock on the file.

Figure 1:


Figure 2:


Step 8: Click the "Select" button.



Step 9: When using a file-based database such as excel (Figure 1):
1. Make sure the "Basic" radio button is selected.
2. Next, from the "Table Name" column, choose the desired sheet name (which is represented as tabs on the excel file when opened in excel).
3. Then, click the refresh button (seen in arrow #3) that resides in the center right side of the dialog at the end of the "Select Statement" field. After a few seconds you should see this chosen table's data populate in the dialog.
4. Finally, press the "OK" button.

When using a database server such as SQL or MYSQL (Figure 2):

1. Choose the desired database from the "Database" drop down.
2. Then, make sure the "Basic" radio button is selected.
3. Next, from the "Table Name" column, choose the desired table where the data you wish to display resides.
4. Then, click the refresh button (seen in arrow #4) that resides in the center right side of the dialog at the end of the "Select Statement" field. After a few seconds you should see this chosen table's data populate in the dialog.
5. Finally, press the "OK" button.

NOTE: If no data appears, the chosen table is either empty, or the connection credentials entered do not allow you access to the data.

Figure 1:


Figure 2:


Step 10: We have established the connection to the database, and chosen what data to pull. Now we are ready to display it in our region. Back in the "Properties" window, switch over to the "Text" tab.





MAKING THE DATA APPEAR IN YOUR REGION

Step 11: In order to make the data appear in the region, we must enter the formatter referring to the particular record we want to pull in. In the example below, we used [1:RoomID]. The brackets and colon will always stay the same; the values in blue will be what you change. The first value (1 in our example) refers to the row of data. The second value (RoomID in our example) refers to the column of data. This can be swapped out for a numerical value if your table does not have column names ( [1:2] for example, which would be row 1, column 2).



To further illustrate this, take the table below as our data set. To display the record "Event 1" in our region, we would use the formatter [1:EVENT_NAME] OR [1:5]. This is because the record "Event 1" is in the first row and fifth column (column headers are not counted as rows). Of course, we don't have to worry about it being 5 columns over if we use [1:EVENT_NAME] because we are replacing "5" with the column name, "EVENT_NAME".

To display the record "Event 10" in our region, we would use the formatter [10:EVENT_NAME] OR [10:5]. This is because the record "Event 10" is 10 rows down and 5 columns over.



Step 12: Now press the "Refresh Now" button, and in a few seconds your region should populate with the referenced data.



Step 13: You can also add multiple formatters into the same region. As shown below, we have 4 different formatters:

[1:ROOM_ID]\n[2:ROOM_ID]\n[3:ROOM_ID]\n[4:ROOM_ID]



In this case the formatters are seperated by "\n" which is the return character. This is inserted when you press the return / enter key on your keyboard. Therefore, based on the example data we used in Step 11, the data output will be:

A
B
C
D

The return characters are not necessary, and can be removed or replaced with whatever you wish. Take the following two examples for reference:

Example 1:

[1:ROOM_ID][2:ROOM_ID][3:ROOM_ID][4:ROOM_ID]

Will output:

ABCD

Example 2:

[1:EVENT_NAME] will take place in room [1:ROOM_ID] beginning [1:BEG_TIME].

Will output:

Event 1 will take place in room A beginning 9/20/2010 6:00pm.

TIP: You can Copy and Paste database regions and simply change the formatter to save re-connecting to the database each time.


FORMATTING TIME AND DATE DATA

Step 14: When bringing in time and date, if the database stores these values in a time/date data type, we can modify the way the data outputs. Pulling from the SQL database used in Step 9 Figure 2, we will use the formatter [1:TimeEventStart] to pull the beginning date/time field.



The data comes in as 02-03-2011 because the default time format is so. To change this we go back to the "Advanced" tab in the "Properties" window. You will see the default for "Time Format" is set to "Custom" in the drop down menu. If you know how to use time formatters you can enter the desired format in the text field to the right of the drop down. If you do not, then you may simply choose the desired format from the drop down.



Step 15: Once you choose or enter the desired format, the data will change in your workspace.





MATCHING DATABASE REGIONS

The Noventri Server and SF-100e players run most efficiently when all database regions are matched. The reason for this is that any mis-matched region causes a separate individual query to the database(s) and separate content updates to the SF-100e. If all regions are matched in their update interval, even if they go to different databases, they will still only generate one content update to the SF-100e. It is only when there are different update Intervals in play that separate content updates to the SF-100e are generated. To fix this issue and make the system operate at peak efficiency, we can use the "Match Database Region" functionality. This is not always possible if you have your regions changing at different intervals for a reason. If this is the case then that is fine; however, if possible we recommend matching database regions. Here's How:

Step 16: Let's first make a copy of our current region. This will keep us from having to make a new region and re-connect to the database. Right-click the database region, and click "copy."



Step 17: Now paste the region. Again, this will make it easy for us because the region is already connected to the database, and all we have to change is the formatter.



Step 18: Once pasted, you can move the region to the desired location in your workspace.



Step 19: Now change the formatter to something different. In our example we have changed it to [1:EventName]. Once changed, you will see that the region has switched over to the data that your formatter is pointing to. In our example, the data has changed to "Training Photoshop".



Step 20: Now we are going to intentionally mis-match the two regions. To do this change the "Update Interval" to something other than the default 10. The Update Interval is the interval (in minutes) in which the Noventri Server checks the database for updates.



Once changed, you will see that our data disappears and we are left with the formatter displaying. Now the software acknowledges this region as a different database connection. To re-match this region to our original one; right click in the workspace and choose "Match Database Regions".



Step 21: A new dialog will open. On the left side of the dialog we have a list of all Database regions in the project. These are labeled "Source Regions". On the right side of the dialog is a list of "Destination Regions". Click on the Source Region (the region with the database settings you want to keep) and any un-matching regions will appear on the right in Destination Regions.



Step 22: You can then click on the destination region you wish to match to your source region and press "OK". If there are multiple Destination Regions you wish to match to a single source region, you can hold Ctrl on your keyboard and click the desired destination regions. You can also Shift-click and all regions between your first and second click will be chosen.



Step 23: You will now see that the formatter in your workspace is replaced with the correct data. This is because the regions are now matched, and again both regions are listed as a single database connection. Now you're ready to publish your project!




I hope this post was helpful!

View a video tutorial covering the basics of database driven content in Noventri Suite.

Please feel free to post any questions.

Hi Tim

thanks for this info. Well written. I just started playing with this. A few suggestions/questions

In the advanced query dialog, the query string needs to be way more than a single line of about 30 visible characters. It's very rare that a query will be a simple one liner (unles you make views for everything), so it would good to make it a much bigger multi-line area.

Ditto for the text box. I need to do [1:name]\t[2:name]\t[3:name]\t ... [30:name]\t. a much bigger editing area would be useful

I have a list I need to display and it will contain about 30 names (could be anywhere from 20-30). I really don't want to do [1:name]\n[2:name]\n repeat 28 more times. Isn't there a way to just use "name" and have it dispay all rows?

It also doesn't seem to support variable data. If I have [1:TempName]\t[2:TempName]\t[3:TempName]\t[4:TempName]\t[5:TempName]\n[6:TempName] and only have 5 names in the data set, I get "[6:TempName]" in place of the missing record. Anyway to prevent this?

I'd also like to display the names in table-like structure, with 5 columns per row:
[1:TempName]\t[2:TempName]\t[3:TempName]\t[4:TempName]\t[5:TempName]\n
[6:TempName]\t[7:TempName]\t[8:TempName]\t[9:TempName]\t[10:TempName]\n

Is there a way to set tabstops on the text region of have equal width columns so the names all align?

Regards

Mark

Hello Mark,

The answers to your questions can be found below:



There is not currently a way to automatically display all rows inside of a single region; however, you can use dynamic pages to cycle through all available information on multiple pages once you've made the first set of regions and formatters.



While inside of Noventri Suite, the empty "[6:TempName]" will appear, it should not appear once the content is playing on an SF-100e Digital Signage Player. Noventri Suite simply displays the empty records so that you know there is a formatter there.



When trying to align several columns of information, we generally suggest that you make a region inside of Noventri Suite for each column. For example, you would make a tall and narrow region that would be purely for displaying your first column (it would contain [1:TempName]\n[6:TempName]) etc. Once you have the first region set up, you can copy/paste and adjust the positioning and formatters for subsequent columns of information - then just make sure all the queries are the same. While tab stops are not currently available, this will ensure that all columns are lined up properly.

Let me know if there is anything else I can do for you.

Kind Regards,

When trying to align several columns of information, we generally suggest that you make a region inside of Noventri Suite for each column. For example, you would make a tall and narrow region that would be purely for displaying your first column (it would contain [1:TempName]\n[6:TempName]) etc. Once you have the first region set up, you can copy/paste and adjust the positioning and formatters for subsequent columns of information - then just make sure all the queries are the same. While tab stops are not currently available, this will ensure that all columns are lined up properly.

It would be helpful if there was a way to select multiple text fields and set the size an alignment for all of them collectively as well as set an alignment. As you suggest, I can set up one field for product name and the next field for price. I want to align each of these columns as well as set the size for field to match.

Hello icevault,

We have added the ability to select multiple regions in the latest version of Noventri Suite. Click on a region in the page, then ctrl-click any additional regions to add them to the selection. Once all the desired regions are grouped together, you can use your mouse or keyboard arrow keys to move all the regions at once. Future versions of Noventri Suite will allow you to change region properties as well, making this process even easier.

If you have any additional questions, please let us know.

Kind Regards,
Sergey