Our DataPoint tool for Microsoft PowerPoint is great to display in real-time database information on your slides. For databases, you use an SQL statement to retrieve the informations that you want. With the SQL statement you can select the data of one or more tables, where you indicate the relationships between the different tables. Next to this, you will specify the fields or columns that you want to use and you will set the sort order.
In case you want to limit the results, then you will set a filter on your SQL statements or for databases, this is called a WHERE clause.
Your SQL statement could look like:
SELECT [Last Name], [First Name], [Company] FROM Employees ORDER BY [Last Name], [First Name]
And when you want to filter for last names starting with an A only, then use:
SELECT [Last Name], [First Name], [Company] FROM Employees WHERE [Last Name] Like 'A%' ORDER BY [Last Name], [First Name]
This “WHERE” clause is great for databases with a SQL engine, but there are other data providers available in DataPoint that do not have a SQL engine. For example JSON, XML, Facebook info, etc. For those providers, you cannot send a filter command or WHERE clause to the server or file. You need to set a filter locally to filter the data after it came in. So this is called a client-side filter.
Let’s work out an example on calendar entries of a Google Calendar. In our calendar, we have a list of events on a given day.
Within the DataPoint, we will set up a connection to this Google Calendar. We will get all the events, 3 days back and 15 days in the future. And a data refresh rate of 5 seconds here.
A preview of all data can be found here.
Click the Edit query button to go back to the settings.
Click the Advanced button to open the advanced options form.
Check the Filter data option and enter your filter text. Click the Test button to check the validity of the filter and click OK to close.
We will use this custom filter below to filter for the event that is currently ongoing. There are a few functions that you can use as a variable date or time value.
- Now() returns the current date and time
- Today() returns the current date only
- UTCNow() returns the current date and time in Universal Time Coordinated value
- UTCToday() returns the current date in Universal Time Coordinated format
[Start] <= Now() and Now() <= [Stop]
The current writing time is 10:20 AM. Only one event was returned by the filter. As time passes, new/other information will returned by our custom filter.
You can also add or subtract some minutes from a datetime field. Use a filter text like;
[Start] > DateAdd("n",-15,Now())
This DateAdd() function will add the specific amount (-15 in this sample) of minutes (“n” stands for minutes) from the Now() variable. Please note that DateAdd(), Now() and Today() are case sensitive.
The data is now filtered and can be displayed on your slides.