Click to Graph Bulk Pen Creation

Got the CTG to work, however, there are questions. In creating the bulk pens it seems that you have to go through every pen and create the description, name, pointid etc. It’s ok if there’s only a few but, how do you deal with it if you have 1500+ tags? Is there a way to dump the ctg_pens into a csv and visa versa? Also, the tags from the db is sorted by id not name so it makes it difficult to search through the tags to find similar ones from the same site to edit. Our tags are named generically for all 140 sites that we have i.e site#/tanklevel, site#/cl2level etc. so there are provisions for indirect addressing. Also, when looking at the sqlth_te, not using the ctg, there isn’t a column for description, that is if you want to use a name more descriptive to the physical site. Is there a config in ignition that can modify the columns without going into the db and adding a column? Can and alias name be included in the tag editor? Maybe I’m going doing down the wrong track here.
Basically what I want to do is click on a site and have the chart pop up with pens only for that site. I reviewed the posting “Dynamic Groups Code Snippet confusion” which is getting me closer to the objective. Seems that there are options to achieve this. The cell update could possibly work but what comes to mind is that some of our sites monitor multiple points while the other extreme would be a few points. If we use a generic cell update some of the points do not exist for some sites.
What would help is a 10 minute video that addresses dynamic pens. The two chart videos are very informative and the moderator suggested another one, more depth for dynamic pens, would possibly be made.

Such a video would be helpful, and we are currently making more tutorial videos.

In the meantime it’s important to remember to use the capabilities of the database to it’s full potential. Going from CSV to a database table or vice versa isn’t hard if you roll up your sleeves a bit and do some googling for DB tools and/or scripting (choose your poison). Also databases are great at sorting things however you want, so let them do the sorting.

That was a lot of questions in that paragraph - maybe an hour of design consultation would be very helpful to you.

I am an Electrical Engineer and not a programmer or DBA so consider this carefully before you implement this. I’m sure there are better ways to do this. This has been working for me and saves me a lot of time when I add a new production line.

I am using the easy chart and modified tables found in the click to graph goodie. I do not use the ctg_active client. These are the tables I use:

ctg_axes ctg_pens – added columns: point_id, line_id and equipment ctg_saved_pens Columns: Line_id Equipment Point_id saved_name user_name

The saved pens table allows the user to create their own sets of graphs

Windows:
Admin_CreatePens – Used for the bulk creation of pens
CTG_Select – Used to create a set of user and default pen groups

On the windows that contain the easy chart I have a list box that contains the users save pens that they can select as needed. As they do I have binding/scripting to drive the chart.

Below is a summary.

Pen creation - Using MSSQL

All data is stored using the default historical group
Each line uses a different connection to a separate database:
PMI, common tables
PMI_Line1
.
.
PMI_Line7

Each database has table names in the following pattern

L1Log_machinename, where L1Log indicates logging for line 1

This allows me to programmatically change connections and machines via scripting

In SQL
Created a stored procedure to get schema information
Note the where clause limits the query to the table name pattern.
Procedure name: ALL_LoggedPoints

SELECT table_catalog, table_name, column_name AS point_id FROM PMI.INFORMATION_SCHEMA.Columns WHERE table_name > 'SwitchgearLo' AND table_name < 'SwitchgearLogg' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line1.INFORMATION_SCHEMA.Columns WHERE table_name > 'L1Log_' AND table_name < 'L1M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line2.INFORMATION_SCHEMA.Columns WHERE table_name > 'L2Log_' AND table_name < 'L2M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line3.INFORMATION_SCHEMA.Columns WHERE table_name > 'L3Log_' AND table_name < 'L3M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line4.INFORMATION_SCHEMA.Columns WHERE table_name > 'L4Log_' AND table_name < 'L4M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line5.INFORMATION_SCHEMA.Columns WHERE table_name > 'L5Log_' AND table_name < 'L5M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line6.INFORMATION_SCHEMA.Columns WHERE table_name > 'L6Log_' AND table_name < 'L6M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line7.INFORMATION_SCHEMA.Columns WHERE table_name > 'L7Log_' AND table_name < 'L7M' UNION SELECT table_catalog, table_name, column_name AS point_id FROM PMI_Line8.INFORMATION_SCHEMA.Columns WHERE table_name > 'L8Log_' AND table_name < 'L8M'

Create another stored procedure to filter out pens that already exist in the pens table
Procedure name: ALL_LoggedNotInPens

SELECT dbo.ALL_LoggedPoints.point_id, dbo.ALL_LoggedPoints.table_name, dbo.ALL_LoggedPoints.table_catalog FROM dbo.ALL_LoggedPoints LEFT OUTER JOIN dbo.ctg_pens ON dbo.ALL_LoggedPoints.point_id = dbo.ctg_pens.point_id AND dbo.ALL_LoggedPoints.table_name = dbo.ctg_pens.table_name WHERE (dbo.ctg_pens.point_id IS NULL)

Window: Admin_CreatePens
The result of this procedure is used to fill a row selector component. The Data Out is used in the adjacent table. The table to the far right of the window contains the axis definitions (linked to the ctg_axis table).

Create pens button: creates a pen for each of the selected rows using the selected axis then adds it to the table at the bottom of the window.

I typically select the axis and then each pen I want to use that axis. I then do this for each axis.

Process pens button: reads all pens in the lower table and inserts them into the ctg_pens table. I have also added additional columns to the ctg_pens table for ‘Line’ and ‘equipment’.

Good Luck and have fun doing it!
BulkPens.vwin (66.5 KB)