Showing colors only if data is entered in excel

Topics on various features of the software.
Post Reply
hutchcb
Forum Contributor
Forum Contributor
Posts: 6
Joined: Tue Oct 29, 2013 3:58 pm

Showing colors only if data is entered in excel

Post by hutchcb »

I am trying to build a screen that shows what colors need to be checked for when equipment leaves the building.

The best solution for this would be to setup the noventri suite so if there is data in a excel cell it would show a red circle or square on the screen. If there is no data then it shows the black background. I have the initial design setup so it shows all the colors on the screen. Just need help getting the coding right to make them appear or disappear with data present.

Is this a option? If so, how can I code it to show this.

Thanks

Chris
Sergey K

Re: Showing colors only if data is entered in excel

Post by Sergey K »

Hello Chris,

One way to handle this is with SELECT statements. They can be entered in the Select Table dialog. First click the lower green refresh button in the top Database section, this will load in all available tables from your database.

If you click on a Table Name below, a select statement will be generated that will display all data in this table.

Basic Select Statement:
SELECT * FROM [Tablename$]
Clicking the Advanced radio button at the top will enable the Select Statement textbox entry for editing. This will allow you to enter more advanced select statements.

You will need to replace the red column names and the blue table name below with ones used in your database, then enter this select statement all in one line:
SELECT "ColumnName1", "ColumnName2", "ColumnName3", "ColumnName4", IIf("ColumnNameX" <> '', 'RedCircle.png', 'Black.png') AS Image FROM [Tablename$]
This select statement will create a new column labeled "Image", and check for data in ColumnNameX. If data is available, a RedCircle.png will be set for each row of the Image column. If no data is found, a Black.png will be set for each row of the Image column. You can now click the refresh button at the end of the select statement to see your results in the window below.

You will then need to have an image region that will be linked to the same database that the excel file uses and contain a path to the image, followed by the formatter to complete the path.

If your images were located in C:\Noventri\Database\Images you would use the following format for each image region:

Example:
Image 1 = C:\Noventri\Database\Images\[1:Image]
Image 2 = C:\Noventri\Database\Images\[2:Image]
Image 3 = C:\Noventri\Database\Images\[3:Image]
Image 4 = C:\Noventri\Database\Images\[4:Image]
Once the new select statement is applied, the path will be completed and display the following:

Example:
Image 1 = C:\Noventri\Database\Images\RedCircle.png
Image 2 = C:\Noventri\Database\Images\RedCircle.png
Image 3 = C:\Noventri\Database\Images\Black.png
Image 4 = C:\Noventri\Database\Images\RedCircle.png

Please let me know if you have any questions or if something is not clear for you.
We can also schedule a remote desktop connection if you need any assistance settings this up.

Kind Regards,
Sergey
hutchcb
Forum Contributor
Forum Contributor
Posts: 6
Joined: Tue Oct 29, 2013 3:58 pm

Re: Showing colors only if data is entered in excel

Post by hutchcb »

Thanks for the input. That looks pretty straight forward. I am wondering if that can be broken down to just a specific cell, and not a column?

Thanks

Chris
Sergey K

Re: Showing colors only if data is entered in excel

Post by Sergey K »

Chris,

To break the selection down even further, you can use a WHERE clause.

Example:
SELECT "ClientID", "ColumnName1", "ColumnName2", "ColumnName3", "ColumnName4", IIf("ColumnNameX" <> '', 'RedCircle.png', 'Black.png') AS Image FROM [Tablename$] WHERE ClientID="10"
This will now look at row 10 in the column labeled ClientID and then apply the rest of the select statement to only this row.

You can look at some additional examples from w3schools.com below:
http://www.w3schools.com/sql/sql_where.asp

Let me know if this makes sense or if you would like any assistance setting this up for you.

Kind Regards,
Sergey
Post Reply