Dynamic Pages - Handling No Records

Working with Text Files, Image Lists and more.
mark_anderson_us
Trusted Contributor
Trusted Contributor
Posts: 61
Joined: Fri Mar 16, 2012 12:21 pm

Dynamic Pages - Handling No Records

Post by mark_anderson_us »

I am building a visitor board. At the top left it has the company logo and centered on the top it has "ACME Welcomes"

It's all working well with dynamic pages; however, on many days we have no visitors and in these cases it would display the page with just the logo and "ACME Welcomes"

Is there a way to have it not generate ANY of the dynamic pages when there are no records returned (it would just show other pages in sequence) or have something display instead of the text on the blank dynamic page
mark_anderson_us
Trusted Contributor
Trusted Contributor
Posts: 61
Joined: Fri Mar 16, 2012 12:21 pm

Re: Dynamic Pages - Handling No Records

Post by mark_anderson_us »

I solved my problem

I use an excel file as an ODBC data source and a SQL query

I have one tabe with visitor names and one with images

The page has a text box for image boxes and on top of it is a picture box (also linked to ODBC source). The image box will be empty when there are visitors (Query returns no records)

My SQL Query (not real SQL below, just giving an idea: if you're interested I can post the actual SQL) for visitors is

Select * FROM Sheet Where Date = Today

The Query for the images is essentially

Select * from ImagesSheet (the where clause returns no records if the count of visitors is > 0 and records if count of visitors is 0)

Regards

mark
gpriddy
Regular Contributor
Regular Contributor
Posts: 10
Joined: Thu Jun 27, 2013 4:18 pm

Re: Dynamic Pages - Handling No Records

Post by gpriddy »

I have the same basic question as Mark, but I'm not able to parse his solution.

I have a dynamic "today's birthdays" page that pulls text from an Excel file. The Select statement is basically 'where birthdate = today'. It works fine as long as there is at least one record retrieved. However, if the Select statement retrieves no records, a blank page is still displayed.

When the Select statement retrieves no records, I'd like the Dynamic page to generate no pages. Can't seem to find a way to suppress the page entirely unless at least one record is retrieved.

-Greg
mark_anderson_us
Trusted Contributor
Trusted Contributor
Posts: 61
Joined: Fri Mar 16, 2012 12:21 pm

Re: Dynamic Pages - Handling No Records

Post by mark_anderson_us »

Hi Greg

What I did was put an image over the text box (containing the visitor names) and make that a dabatbase source too. (As I don't want a blank screen if there are no visitors). (Could be another text box with a message instead of image box)

my sql (Excel doc using ODBC) then does the following for the overlaid box

SELECT Image FROM Images
WHERE (SELECT COUNT(*) FROM Visitors WHERE VisitDate = Date()) = 0

This will return a list of images (from a sheet called "Images") that cycle (dynamic pages)

To select just one image, you could use

SELECT 'Image.jpg'
WHERE (SELECT COUNT(*) FROM Visitors WHERE VisitDate = Date()) = 0

Hope this helps

regards

mark
Sergey K

Re: Dynamic Pages - Handling No Records

Post by Sergey K »

Greg,

A dynamic page will still be in the sequence, so it will generate at least one page. When this page is connected to a data source, is can grow or shrink as its data changes. Currently the best way to avoid having a blank page is to place something over it, like a full page image region in the example above. This can become a welcome image, a slideshow, or just plain text with a background color selected.

Kind Regards,
mark_anderson_us
Trusted Contributor
Trusted Contributor
Posts: 61
Joined: Fri Mar 16, 2012 12:21 pm

Re: Dynamic Pages - Handling No Records

Post by mark_anderson_us »

Would be nice to have a "Skip if 0 records" option for dynamic pages
gpriddy
Regular Contributor
Regular Contributor
Posts: 10
Joined: Thu Jun 27, 2013 4:18 pm

Re: Dynamic Pages - Handling No Records

Post by gpriddy »

Mark, that's a very clever trick, and one which I almost have working. I created an alternate text region that pulls from a different sheet in the same Excel file if the count of records selected from the main 'birthday' sheet is zero. This is working: at least one birthday today, birthday(s) display; no birthdays today, alternate text displays.

I created a third sheet in the Excel file that contains a listing (in Column1) of UNC paths and filenames to images, along with a numeric index column. I created an image region that uses this SQL:

SELECT Top 1 Column1 FROM [Sheet3$] WHERE (SELECT COUNT(*) FROM [Sheet1$] where Month_Num = Current_Month and Date = Current_Day) = 0 ORDER BY rnd(-(1000*index)*time())

The Formatter for the Image region is [1:1]. I tried embedding the UNC path in the Formatter as well as putting both the UNC path and the filename in Excel.

Here is the problem: in the Design view (Page and Sequence Preview) the image pulls correctly based on the data for both conditions (Count = 0/Count <> 0). But in the Manage tab, and on the actual display, the image renders as a solid square of the region's background color, even when Count = 0

I even tried stripping down the SQL to "SELECT Column1 FROM [Sheet3$]" and I get the same result. What am I doing wrong?

The other problem I have is that the Noventri server locks this Excel file as soon as the project is published. I can't access the Excel file with Suite, nor with Excel unless I first restart the Noventri Server.

-Greg

P.S. +1 on the "Skip if 0" option for Dynamic Pages. I would be done with this page by now if that option existed.
David L
Noventri Digital Signage Expert
Noventri Digital Signage Expert
Posts: 34
Joined: Thu Dec 10, 2009 2:00 pm

Re: Dynamic Pages - Handling No Records

Post by David L »

Hi Greg,

The file lock is a known Microsoft driver issue. Download the following driver and then change your Noventri Suite project to use the excel driver labeled, "Microsoft Excel Driver (*.xls, *.xlsx,...)" . It is important that you choose that exact option as you will now have different excel drivers to choose from.

http://www.microsoft.com/downloads/info ... Engine.exe

http://www.noventri.com/forum/viewtopic ... =815&e=815

Someone else will have to chime in regarding your other question.

David
mark_anderson_us
Trusted Contributor
Trusted Contributor
Posts: 61
Joined: Fri Mar 16, 2012 12:21 pm

Re: Dynamic Pages - Handling No Records

Post by mark_anderson_us »

gpriddy wrote: The Formatter for the Image region is [1:1]. I tried embedding the UNC path in the Formatter as well as putting both the UNC path and the filename in Excel.

Here is the problem: in the Design view (Page and Sequence Preview) the image pulls correctly based on the data for both conditions (Count = 0/Count <> 0). But in the Manage tab, and on the actual display, the image renders as a solid square of the region's background color, even when Count = 0
I use UNC paths and [1:1] in the page. Only thin is to make sure the Noventri server has access tot he path. i.e. if you're running suite on PC A and server is on PC B, PC B must be able to access the UNC path
gpriddy
Regular Contributor
Regular Contributor
Posts: 10
Joined: Thu Jun 27, 2013 4:18 pm

Re: Dynamic Pages - Handling No Records

Post by gpriddy »

David: Thanks for the pointer to the updated driver. That seems to have solved that portion of the problem.

Mark, et al: I suspected an access permission issue as well. The images are in the project's media directory. The images render correctly when referenced from a non-database image region. It's only when a database image region attempts to display them that the problem appears.
Post Reply