# Background color dynamically based on SQL database description row **URL:** \ **Category:** Ignition\ **Tags:** vision, ignition80\ **Created:** [July 28, 2021, 8:29pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326 "2021-07-28T20:29:30Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\ **Post date:** [July 28, 2021, 8:29pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/1 "2021-07-28T20:29:30Z")
Hi guys, I’ve been playing with this for a while and I couldn’t find the right solution for this, one of my customers wants to add background color to a few objects based on a description row coming from a workbench 8.0 database table, any thoughts on that? (Vision) I’ve been thinking on apply named queries, but I don’t have too much experience with those, any help will be really appreciated. Thank you, ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 28, 2021, 9:02pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/2 "2021-07-28T21:02:21Z")
Is the list of descriptions from the table more or less static? (i.e. You might add rows to it every so often but generally it’s the same otherwise) Would it make sense to add the colour into a new field in the table? I assume that to determine the description for a component and hence its background colour, you look it up based on a dynamic tag value or something? ---
**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\ **Post date:** [July 29, 2021, 9:33pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/3 "2021-07-29T21:33:07Z")
The list is not static, here is a brief description of what we are trying to accomplish. .- They have a database stored in workbench 8.0 in the database a table with the information of the conveyors is inside, conveyor name, last maintenance, conveyor fault, etc. .- They want to be able to search inside the table and if the conveyor name appears in the table ie: 350 is in one of the rows then display red or flashing red the object or piece of conveyor in the screen, we’re using the conveyor’s name dynamic and everything, is there any way to make this happens? Named queries could be a solution or something different? I tried to accomplish this adding and expression, but when I look inside the properties of the table only Data, Selected column, Selected row are the options I don’t see any search data or data on this row i.e = 350. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 29, 2021, 9:55pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/4 "2021-07-29T21:55:14Z")
Ok, so it’s a tool to search for conveyors to locate them on the page? I would use 2 client tags, one with the filter text, one that stores a dataset. When the filter text changes, fire an event using an onChange script on the filter text client tag that will run a script to query your database table (using a named query) and return a list of the conveyors filtered to that text. Write the results to the client dataset tag. Then in your conveyor templates, use the lookup function to check if that conveyor is in the list, if so, light up the disco ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [July 30, 2021, 12:12am UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/5 "2021-07-30T00:12:06Z")
Yes a named query can do the job. I would first add a field of where user may type, a text field. Add a internal property to your conveyor draw (hope to be a template) and an external property that will be the comparison field. On the first property (internal) use named query. Then use a background binding saying if your dynamic id is equal to your search field and extra conditions then be red. Write a property event script at the root and when it changes, do a system.db.refresh(). Finally bind the text property of text field to external property of your template. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 30, 2021, 12:45am UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/6 "2021-07-30T00:45:39Z")
> [@jespinmartin1](#): > > On the first property (internal) use named query I'd stay away from calling a query binding (even a cached named query) on potentially 10's/100's of components on the screen from a performance standpoint; queries are expensive! I would much rather do this query once in a client tag and reference that everywhere. Using a query binding on each conveyor will also be more difficult to handle multiple results returned and will increase client memory usage since every component will now need to store a dataset property with the results of the query. ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [July 30, 2021, 12:58am UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/7 "2021-07-30T00:58:44Z")
servers are meant to handle this load ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 30, 2021, 1:19am UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/8 "2021-07-30T01:19:24Z")
Better to design efficiency into your applications so that you don’t have to upgrade your server more often than necessary and potentially have unperformant clients ---
**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\ **Post date:** [July 30, 2021, 7:22pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/9 "2021-07-30T19:22:42Z")
First of all, thank you so much for all your comments those really helped me with this process. Here’s an update about what I have. I ended up using a named query for the table, I have a screen where I added a table on it displaying a named query, I used a dropdown list as well. Here’s how looks my table on the named query. SELECT Company\_AL.area, conveyor\_info.date, conveyor\_info.fault\_des, Company\_AL.Area, conveyor\_info.summary, conveyor\_info.cnvy\_name FROM Company\_AL INNER JOIN conveyor\_info ON Company\_AL.ID = Conveyor\_info.ID where area = :area; * * * on my main screen, I have the table plus the dropdown list with the area names, when I sort by area name I can see the conveyors affected on that area. ; therefore I added a label\_1 that shows me the row selected " expression **(try({Root Container.Table.data}[{Root Container.Table.selectedRow}, “cnvy\_name”], 0)**". The conveyor’s squares are templates where we have parameters one of the parameters is the conveyor name, we are doing this comparison to change the color to red. Expression: **IF({Root Container.Label 1.text} = {[AL]Conveyor 101/Parameters.Conveyor\_name},“255,0,0”,“255,255,255,0”)** * * * Obviously, this is only working when the row is selected and the name is the same as parameter conveyor\_number, I’m still looking for a way to do this automatically by searching in the column cnvy\_name and doing the functionality in the conveyor square. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 30, 2021, 9:11pm UTC](https://forum.inductiveautomation.com/t/background-color-dynamically-based-on-sql-database-description-row/49326/10 "2021-07-30T21:11:08Z")
> [@a.beltran](#): > > I’m still looking for a way to do this automatically That's where, as I said in my other post, you can use a client tag to store the area filter and another client tag bound to a named query which filters on the area client tag. If it's only ever supposed to return one conveyor, you can use a scalar query and only return cnvy\_name. So then you have 2 client tags, one with the filter and one that returns a string with the cnvy\_name. Then bidirectionally bind the dropdown selection value to the area filter client tag, and use the cnvy\_name client tag in here in place of the label 1 text: > [@a.beltran](#): > > Expression: **IF({Root Container.Label 1.text} = {[AL]Conveyor 101/Parameters.Conveyor\_name},“255,0,0”,“255,255,255,0”)**