Table Object - SQL Data AND SQLtag Data >>> Make it dynamic

I would like to use the table object to display a list of parameters/setpoints for a system. Traditionally I would have to built this with a bunch of sting placeholders and real/integer placeholders. I would run a script that would query SQL for the text data and passed that into the string place holders. I would then target the real/integer placeholders and target the appropriate PLC locations. Parameters are passed to the “table” to index to the appropriate areas in the PLC and SQL query as different areas of my system will contain different parameter text and values. The idea is to create a single popup which I can pass appropriate parameters to specify the information I require.

To reproduce this in Ignition, I want to know how I can use the table object to achieve the same thing. Now, passing parameters and doing a SQL query is pretty easy to return the text. However, my issue is with the value column, and how to target the data in this column to read the corresponding SQLtags.

So, in my attached picture how can I make the value of 999.99 reference the appropriate SQLtag? And I must be able to edit the value.

Can I do this with a single table? Or will I have to create multiple tables, and align them side-by-side?
1st table to query the text for the parameter number and parameter description columns.
2nd table to assign the PLC SQLtag references for the value column.
3rd table to query the text for the unit column.

Finally, could I make this dynamic? I may have 50 parameters for one area of the system, but only 10 in another. So I would like the data returned in the table to only be reflective of what is available for a particular area. Again for the SQL query this is simple as it will only return what is in the SQL table, but for the SQLtag assignments I need to only make assignments based on the number of returned SQL query results of the text.

we have something pretty similar, but in our case, the data is not refreshing at a very fast rate, only once or twice per hour. so we just give the user the option to refresh the table or if we see an update for the device, we refresh it automatically.

we even color cells if the value is in alarm and can write setpoints, and do stuff like open a chart or table that plots the individual point. We also have a list of about 300 tags that we basically use on all of our devices. if the tag is not enabled or exists, then the script skips it and it never shows up. we also have tag groups, sort of like sticking all of the pressures together, or comm statistics, etc. Its definitely possible to do what you are looking to do in one table.

basically thru scripting you will need to generate a taglist, then use a readAll to see which tags are enabled. after you see which are enabled, you would grab the values and whatever other tag information like .documentation, or .alertactive for each of your tags using readAll. stick those values in a dataset and you can do alot with the table component. IMHO it is by far the most powerful component in the ignition library.

in your case, since you are mixing sql data and tag data, you would want an identifying column in your dataset that gets sent to the table so that you can let your cell edited script know what to do when someone edits the value column. so you might have a hidden column called “source”. in your script that creates the dataset that gets written to the table, a value of 1 would be data that comes from sql, 2 would be data from tags. then in your cell edited script, check the value of the “source” column of the selected row to see whether your script should write back to the database or do a tag.write command.

Thanks diat150, I follow what you’re saying, but with my limited experience with this platform I think I’ll need a little more of a direct example.

I agree the table function seems extremely powerful, I just need to learn all the ways it can be manipulated.

without knowing exactly what you want to do it will be hard for me to give you a good example.

Thank you for the followup diat150, let me as a question. In your initial response you mentioned that I would need to generate a “taglist”. Can you define “taglist” for me? And are the results of that taglist stored in a dataset which the table will then bind too?

I feel like I need to generate a custom dataset. One that gets data from SQL bindings, and another that gets data from SQLtag bindings.

The description of your table does indeed to be very similar to what I am trying to achieve.

everything that I am talking about doing is in scripting. no bindings at all. the taglist I am referring to is a list of possible tags that your devices can have. There are a couple of ways to handle this. you can either just store the tags for each device in a database and then query that list out or you can dynamically generate which tags are available for each device by having a master list of all tags in a list and then having the script check to see which is available, which is what I explained earlier. Bindings are nice but scripting works much better for being dynamic.

It sounds much more complex than it is. Ill try to throw together a quick example of what I am talking about tomorrow if I get a chance.

Just a followup, I was able to create the table through some scripting.

The following thread helped, particular Travis’ code (even with the minor syntax error :wink:)

viewtopic.php?f=70&t=8716&start=0&st=0&sk=t&sd=a

In summary, I am not directly binding the value column to SQLtags as I initially thought I would have to, instead when the window opens up I query SQL for the table text, and I also read the tag value and populate it into the cell for display. I have a timer object that refreshes the table every second for any updates. This is probably not required as these are all setpoints, and the only place they will/should change is from this popup. However it does provide the benefit of confirming changes.

I also have code in place for when the value is edited it will write the new value to the proper tag, and the subsequent refresh from the timer ensures the a read of the tag after I did a write for confirmation. I also have limit checks in place, so if the value entered is out-of-bounds the refresh will clear the edit value back to the current value in the PLC.

Not sure why, but when I re-visited this today things just made more sense. Must have been that vacation!

Thanks for the help diat150!