Start Time for Refresh interval?

Using Excel,SQL,Oracle, and other ODBC Compliant Databases
Post Reply
gpriddy
Regular Contributor
Regular Contributor
Posts: 10
Joined: Thu Jun 27, 2013 4:18 pm

Start Time for Refresh interval?

Post by gpriddy »

I have a Quote of the Day page that pulls a random entry from an Excel file containing a number of quotations. I have the refresh interval on the region set to 1440 (24 hours * 60 minutes). The page displays a new quote every 24 hours. However, the refresh seems to be happening in the middle of the day, so we get two quotes per day instead of one.

What controls when the refresh occurs? I tried restarting the server and rebooting the player, thinking that would reset the counters, but it didn't display a different quote on restart. Ideally, I'd like to force the refresh to happen around midnight (off hours) so the same quote is displayed during business hours.

-Greg
Sergey K

Re: Start Time for Refresh interval?

Post by Sergey K »

Hello Greg,

The time of publish will be the starting point for the projects update interval. This means a project with a 15 minute interval, published at 6pm, will check for its next update at 6:15pm. This will continue unless the server is restarted setting the new interval start time at the time the server comes online. I will talk to our development department to see if we can craft a select statement that will pull a random entry from the database once for the entire day.

If you have any further questions, please let me know.
Sergey K

Re: Start Time for Refresh interval?

Post by Sergey K »

Hello Greg,

The following example shows how to randomly select a quote from the excel file below and display it for the entire day (24 hours).


Image
The excel file has 2 columns, QuoteID and TodaysQuote. We can use the [1:TodaysQuote] formatter to output the data.


Image
Once the select statement has been added and the Refresh Now button is pressed, the data should display on the screen.


Use the following select statement in the Select Table dialog:

Code: Select all

SELECT TOP 1 * FROM [Quotes$] ORDER BY rnd(-(1000*QuoteID)*Date())
This will display only the first row from the excel sheet called Quotes. The order is randomized using the QuoteID column and the current date to show a different result each day.

If you need any questions or need assistance setting this up, please let me know.
gpriddy
Regular Contributor
Regular Contributor
Posts: 10
Joined: Thu Jun 27, 2013 4:18 pm

Re: Start Time for Refresh interval?

Post by gpriddy »

Thanks. I had the random part working; was mostly looking for the refresh trigger (not obvious with very long refresh times).

BTW, if the formatter is [1:somefield] then the 'TOP 1' in the SELECT statement is redundant.

-Greg
Sergey K

Re: Start Time for Refresh interval?

Post by Sergey K »

Greg,

Glad to hear you were able to get your project working!

We are excited to have different types of questions on our forum and everyone's answers help our community grow with new ways to use the SF-200!

You are absolutely correct, we are not required to use the 'TOP 1' in the select statement. Projects that use Dynamic Pages and have large data sources can load slowly if they use the entire table. This allows users to limit the number of rows that are queried by the server and is generally best practice for larger databases.

Again, Thanks for your quick response and welcome to the Noventri Community!

Sergey
Post Reply