IA Forum,
Can I bind an SQL Table value to a Tag in the Designer Tag Browser?
I have an MSSQL Table that has a "ChillerName". That Chiller has Inlet and Outlet Temperature transmitters that are in my Designer Tag Browser. I want to always bind the In and Out Temperature tags to their respective ChillerName in the from the SQL Table.
What is the best way to do this?
Thanks in advance,
Jeremy
I have 99 Chillers which equals 198 Temperature transmitters. So, I would like this to be clean and straight forward approach.
I am thinking of using a named query, but I am not sure if that is the best approach.
Your post is a little confusing. Do you want to display live values from the OPC tags or historical values from your database or are you asking about something else?
Are you only trying to get the latest value? Or are you attempting to graph the values of the inlet/outlet over a period of time?
Also what is writing the values to the database? Are you unable to connect directly to the chillers to get the values from them instead of a DB table?
Static values from the SQL "ChillerName" and Live Values from the OPC Tag browser.
All of the chillers have names that are in the database. The names never change. I want to bind the chillername to an OPC tag in my tag browser and never break the bind.
The end result is when someone clicks on a Tank (in Perspective), a popup comes up showing the Chiller that is associated with the Tank and show the In and Out Temperatures.
Create a single query tag that grabs all the names and their Id's. Make the poll rate reasonable (once every 30s if the table doesn't change that often, or maybe longer)
Create a chiller UDT that takes the chiller ID as a parameter and has a tag for display name, inlet, and outlet. You can also add any additional tags that may be associated with the chiller.
For the Chiller Display Name, make it an expression tag and use lookup pointed at the query tag to fetch the chiller name from the query result.
You can just manually address the inlet and outlet tags in the UDT. If your path to the chiller values are similar across your machines you might be able to parameterize their connections using UDT parameters.
On your interface you can provide the path to the UDT instance to the popup, and indirectly bind the UDT instance members to custom properties on the popup's root container. From there, bind whatever display items to the associated custom properties.
Show us the syntax of your chiller tag paths. It seems that this could be a simple UDT (user defined type) tag with structure:
udtChiller
+ Name
+ InletTempTagPath
+ OutletTempTagPath
Define a parameter on the UDT such as the chiller number 1, 2, 3, etc. and have it do the rest automatically.
For the Name you can look up the database using the same chiller number. For this you would set the Value Source to Query and fill in the remainder of the query fields appropriately.
Snap! @ryan.white
SELECT Chillers.ChillerName
FROM Chillers
That should be SELECT ChillerID, ChillerName FROM Chillers. Lookup needs some column to serve as an Id column to search against.
It isn't enough to just have a column of names in the database. You might as w well just put the name into a memory tag adjacent to the other tags, and not have a DB table at all.
As noted by ryan, you need something unique and unchanging to serve as the binding if not the name.
What is populating the database? (How are the temperatures getting in there?)
I'd expect the data table to have columns:
id
ChillerName
InletTemp
OutletTemp
You have two parameters for your query but you use neither in the SELECT. What are they for?
I have not told the whole story yet.
When I click on a tank to popup it's information, it runs a named query and finds the Tank Number and joins the ChillerName, Work Order, etc.. to be displayed on the popup. I also want the In & Out temps to be joined based on the ChillerName.
Here is a snip of what I am looking for.
This is my named query that binds the tank to the Chiller, Work Order, etc..
SELECT TOP(1) CH_Log.WorkOrderNum,
Chillers.ChillerName,
CH_Log.StartTank,
CH_Log.EndTank,
CH_Log.JobType,
CH_Log.WOStatus,
CH_Log.ID
FROM CH_Log
JOIN Chillers ON CH_Log.ChillerID = Chillers.ID
WHERE CH_Log.StartTank = :tank
ORDER BY CH_Log.ID DESC
The database does not receive In and Out temperatures.
Okay, I think that's what tripping up @Transistor.
If you follow the layout I provided for making a UDT and getting it's data to the popup, and use the parameters suggested by @Transistor on the UDT, you can reduce your active query on opening to just fetch the associated work order. The UDT would have the chiller name and temperature readings.
You can't join data in the database to live tag values in Ignition's tag tree.
The closest you can come is to include tag folder paths in your chiller table, and join to retrieve that in your UI. Then your UI can include nested views or numeric displays that use indirect binding to show the live temperature based on that tag path.
I will work on the option that @ryan.white suggested.
Thank you all for your time.