Earlier this week a blog article was published on dynamic greetings in PowerPoint. Now we will go further on that with dynamic and real-time time-restricted information.

An information screen is showing much more than just a greeting like “Good Afternoon”. Many of the information screens we see include, next to the normal content like current date and time, also messaging and announcements and some other information like a menu of the day. Often or we should say; always, these messages are limited in time.

A specific message should popup on the screens at a given time. And the message should be removed automatically after a specific period. At a train station, the user will display the departing trains before they leave. And a while after the departure time, the user will consider it as completed and remove the train information of the information screen.

It is the same when the user welcome’s the visitors at the company, the user will start displaying the welcome message with a certain number of minutes before the appointment.  And e.g. 15 or 30 minutes after the start, the user will remove the welcome message or visitor’s name of the screen to make room for the next upcoming appointments or messages.

Let us build together a few cases in a Microsoft Access database.  With other databases this mechanism is completely identical. Basically, what the user has, is a base data table with all information and a query that filters out the relevant data for display purposes on the real-time information screens.

Things for today

The easiest way to select time-restricted information or messages, is to select all messages for today only. So in the database, the user should have a table where the user will enter all the information and announcements for several days.  Some for today, some for tomorrow, some for the rest of the week, or month etc.  A row with information have 2 elements, or fields as this is called in a database.  A field for the date and another field containing the message itself.

Enter whatever information you have available for now and in the future. This we consider as the raw unfiltered data.

On top of that data table, we will create a query where we do 2 things:

• Filter the data for today
• Sort the data in a logical, probably chronological, way.

The filter is logical. We will put on a filter on the raw data to filter out the data for today only.  First, add base table to the query TblToday. Then add 2 columns to the query grid; DisplayDate and Message. At the criteria cell of the DisplayDate column, I add the function =Date() which will return today’s date. When we already saved this query as QryToday and run it, the user will see that the user will only see today’s activities from the list of all activities of all days.

This query can now use with DataPoint in PowerPoint to display the announcements, messages, or activities of today only. While the slideshow is running we can edit and modify the content in our base table with typo corrections, additions etc, and our information screen will continuously update the information for today. A new activity that is added for today will be added in real-time to the screen. Whenever the system goes over midnight, then the list of activities is cleared and the new activities of the new day are shown on the screen, completely automatically.

Things for now

Now we will go one step further. Imagine that we have multiple activities or messages for today, way too much to be displayed at once on one screen. Or we want to display time-restricted information. An announcement targetted for the afternoon, should not be displayed already in the morning. Therefore we have to extend our table with more information. Add a column for the start time and the end time of my message. It could look like this;

So instead of a date field only as before, we will now have two fields or columns for the start and end time stamp of our message. The raw data could look like this;

Information with a start or from timestamp before now, will not be displayed yet. The first message here will be displayed from 2:00 PM on. The query now that we will apply on this raw data looks like this:

We will use the table with the raw data TblNow and add the columns From, To and Message to it. The From has a criteria of <=Now() and the To field has a criteria of >=Now(). The function Now() will return the current date and time.  So with this query we will make sure that the current date and time is between the From and To value of a message. When we run the query (it is 4:49 PM now), it looks like this;

We can now use this query in PowerPoint and DataPoint to display the current active message. As soon as the end time passes, the message is remove automatically from the screen.

Complex things for now

Now we can’t just display the messages between the start and end time, but we need something more complex. Imagine a train station. A train is scheduled for a given time and people are looking for more information on that train like delays and platform. When a train departs at 2:00 PM, we cannot use the previous query since that train info will popup at the departure time. That is too late. We need more complex time selection. This is the base table with a departure time and a destination;

And its data looks like this;

So with this train information, we want to display the information one hour in advance till maybe 20 minutes after departure time to signal the people that were too late. Set up query like this;

Add two columns DepartureTime and TrainTo to my query and put a criteria like Between DateAdd(“n”,-60,Now()) And DateAdd(“n”,+20,Now()) to my DepartureTime column. The DataAdd with parameter “n” adds a certain number of minutes to my current time. Make sure to use “n” as this is for minutes, while “m” is for months. So basically with this between function we select the trains 60 minutes prior to departure time till 20 minutes after departure time. When we run this query, we will get these results;

Conclusion

These 3 types of time-restricted information the user will often see. See for yourself what time-selection suits best for your restricted announcements or messages or content in general. Remember that DataPoint will display all information in real-time on the information screen based on the query definition. As time passes, new information is added and old information is removed from the information screen by using DataPoint software.