[Imported] Writing to Data Grids in Group Template Instances

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: mchartrand, originally posted: 2018-10-19 17:46:05 Id:152
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

-------------------------------

**_Chadam:
Just wondering if it is okay to have a Data Grid in a group template. I have an ST Logic section that needs to write to a data grid within the group template. The Data grid has a unique tablename for each instance.
If i create a group instance and execute the ST Logic i get an error saying "Program Error: QP Error - Invalid Object"
If i convert the group template instance to a group and execute the same ST logic, it works fine and the Data Grid gets populated.
I have checked Property overrides and the Data Grid is available for editing within the instances. Any thoughts?_**

-------------------------------

AWoodland:
Have a look into data sets, they may do what you want to do with data grids but make your life so much simpler

_____________________________

**_Chadam:
Hi Adam. I have looked into Data Sets. However, it doesn't quite work for me in this instance. If i go with Data Set solution, it implies that within the Group Template i can only define a Data Set Row. This takes in a single Row of data only. The Data Rows from each group instance are then automatically collected into a Data Set which needs to sit outside the Group instance. Rather than building the table outside the group instance, i would like to build the table within the group instance.
As such it would be great if i could populate a Data Grid that sits within the Group template with Multiple rows. Using a Data Set Row in the Group template doesn't give me the multiple rows in a table that i need.
Feel free to correct me if i am wrong. Any more ideas are definitely welcome._**

____________

trentonite:
You _could_ but it doesn't mean you should. I'd look at having multiple data grids as having to manage multiple different tables. Things can get really messy really fast. If you NEED to have a data grid to do what you're trying to do, consider having a single data grid and using ST+SQL to query and insert data as needed. Writing logic to populate a single data grid and configure expressions accordingly isn't as daunting a task as it sounds; however, be mindful of the size of the datagrid and the complexity of each expression.

If this is for something that you don't need to reference items but rather just need static values, consider using a data table instead.

___________________________

bevanweiss:
You can have multiple Data Set Rows within a Template (or Template Instance) which would each contribute a row to the DataSet.

How did you get the DataGrid to be referenced within your Template Instance? It's unusual that you were able to just convert it to a Group and have the logic work. Since the SQL query would have needed a dynamic table reference (which doesn't work).

_____________________________

sbeadle:
Re: "You can have multiple Data Set Rows within a Template (or Template Instance) which would each contribute a row to the DataSet. "

There have been improvements in monthly releases which make this solution more scalable - DataSets with large row counts.

______________________

du5tin:
I am working on a system that used to do this. They had a datagrid for each tank in the system (there were over 2,000!!!!). Think about that.... 2,000 extra tables in the ClearSCADA database. We are re-building the database currently and we are using DataRows and DataSets. So now instead of 2,000 tables with a few rows each we have a few thousand rows in a single table.

Where we need multiple rows from a single instance we put in multiple row objects in to the template. Well Optimization is one of these where we have a DataRow for each of the past ten completed cycles.

We found a lot of efficiency improvements with DataSets/DataRows, both on the configuration side and on the data extraction side. With all the data in one table (which is good database design) we could query one site, or many sites in a single query, rather than two or using Unions. Plus it kept the ugly management of multiple DataGrid table names for what is the same data schema out of our scope.

Just my $0.05. 🙂

______________

**_Chadam:
Hi Dustin. How do you manage a situation where the number of data rows in a template is unknown i.e. it differs for each instance._**

__________

bevanweiss:
In your Template you have the number of DataSetRows that you need as a minimum.
Then inside your Template Instance you can just create new DataSetRows to get the number of rows that you finally need.

______________________

du5tin:
What Bevan said. If we need more rows we simply add the DataRow object to the instance after it is created.

If we are working with sites that have multiple meters or logical objects then we make those a template with the DataRow inside, like a meter template. If we have more meters we simply add more meter instances and those rows show up in the table, linked and ready to go.

_________________

bevanweiss:
DataSets and DataSetRows are a really amazing feature for reporting.

No longer do you need to do complicated SQL queries to identify what is or isn't a pump... just add a DataSet for pump statistics, and then DataSetRows for each Pump... job done 🙂

The only slight improvement I could see would be if it were possible within the DataSetRow object to have multiple rows, there are times when it would be nice to just be able to add new rows like a DataGrid.. but to still have it consolidate all such items into an inclusive DataSet.

___________________________________

**_Chadam:
I agree Bevan, additional rows in a data set "row" would have been excellent._**