Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84816members
354269posts

List functionality

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
6
1608

List functionality

Hi,

I have a number of questions about lists

 

1. Current1stColDataStr / Current1stColDataInt


I am working with a table that some else has created and they have of course used the first column for their purposes. I have to select information from another column (7) in the table when a row is selected.

 

SelectedRow = Mimic.Layers("Xxxxx").Item("Lst_2").Current1stColDataInt

 

What is the neatest way to do this?

Do I have write code something like - http://gtg.ddns.net:8080/file/help_en-US/Content/ScriptingGuide/Server.Query.htm or is there a more direct way.

 


2. Using Automation interface I would like to refresh lists from outside of Geo SCADA. Is this possible.

I could do it by code in Geo SCADA which would be run when I change a flag from the external source, but wondered if there is a more direct way.

 

I have looked at the Schema and I can't find specifically where information related to lists is, seems to be distributed.

(List is an object, I guess, so should be in schema?)

Thanks,


Accepted Solutions
AdamWoodland
Commander Commander
Commander
0 Likes
0
1603

Re: List functionality

What table in the database are you querying?

 

If you're querying a data table then options are quite limited. If you're querying a proper Geo SCADA table with an Id column or a data grid that maps the id, then you get more functionality for addressing columns and rows. The id is special and provides the row a direct database object behind the scenes to get more info.

 

You mention the first column is used, but you can add your own first column in the SQL query and alias it starting with an underscore and the column is hidden. I haven't tested some of the newer properties but previously those first column properties would pick up on that (e.g. SELECT Something As "_Something" FROM Table). It is a bit of a trick but might do what you need.

 

I have used scripting to do a Server.Query using the same SQL as shown on the list and then picking the right row, the issue is concurrency and making sure things haven't moved since the list was shown.

 

In terms of refreshing the table, again if the row as an id from an object in the database (not a case of a column just called id, has to be from the database) the row contents should automatically update. Beyond that you can usually trick it by added in a blank space to the end of the SQL in the animations. The list on a mimic isn't an object in the database so can't use the Client API, but the ViewX API should be able to do what you're after.

 

Both of those are really dodgy workarounds, even if they do work they might change in the future so be careful.

See Answer In Context

6 Replies 6
AdamWoodland
Commander Commander
Commander
0 Likes
0
1604

Re: List functionality

What table in the database are you querying?

 

If you're querying a data table then options are quite limited. If you're querying a proper Geo SCADA table with an Id column or a data grid that maps the id, then you get more functionality for addressing columns and rows. The id is special and provides the row a direct database object behind the scenes to get more info.

 

You mention the first column is used, but you can add your own first column in the SQL query and alias it starting with an underscore and the column is hidden. I haven't tested some of the newer properties but previously those first column properties would pick up on that (e.g. SELECT Something As "_Something" FROM Table). It is a bit of a trick but might do what you need.

 

I have used scripting to do a Server.Query using the same SQL as shown on the list and then picking the right row, the issue is concurrency and making sure things haven't moved since the list was shown.

 

In terms of refreshing the table, again if the row as an id from an object in the database (not a case of a column just called id, has to be from the database) the row contents should automatically update. Beyond that you can usually trick it by added in a blank space to the end of the SQL in the animations. The list on a mimic isn't an object in the database so can't use the Client API, but the ViewX API should be able to do what you're after.

 

Both of those are really dodgy workarounds, even if they do work they might change in the future so be careful.

BevanWeiss
Spock
Spock
0 Likes
4
1593

Re: List functionality

It does sound like a good opportunity for a feature request 🙂

 

I think the Current1stColDataStr/Current1stColDataInt was a bad API..

It would have really made so much more sense to have

CurrentRow.Col(col Idx).Str or CurrentRow.Col(col Idx).Int

 

and then you could have used any column index you wanted to get the value.

As Adam has said though, it sounds like if you can't swap out the first column with your own, then you'll pretty much have to script it around.  If the first column is a reference to a database object that is easier.  If it's custom data from the query, or from a datatable/datagrid/datagridset. then that's a lot more painful.  And you might just want to requery it to get your own local copy, and then index directly into that.  As Adam also said, concurrency is an issue, since the data you retrieve may not be the same as the data that the user is seeing (especially if row sort might be changed, so you'd definitely want to rule that out in some way).

 

Also as Adam said on item #2, Query Lists (on a mimic) aren't objects in the ClearSCADA database.  They are just a thing in a big binary blob that is the CMimic object as far as the ClearSCADA database is concerned.  It's only ViewX parsing the binary blob and reading between the bytes that makes it mean anything.

I'm less convinced that the ViewX API would help you here, since my experience has always been that it doesn't connect to an existing instance of ViewX but instead spawns its own instance, which means that the Query List you see won't be the Query List that any ViewX API instance would be issuing refreshes against.

Ways that I've worked around this kind of thing are:

1. Have the query be simple and tied to a DB intrinsic table (like CDPoint etc) in which case the refreshes are largely taken care of automagically

2. Have a horrible hack where the animation for the SQL query contains a reference to a property that you can control in some way (like a CValuexxx object, or a Registry field.. depending on if it should be a per user, or a per system refresh).

 

I'd really recommend trying to stick with path 1 if you can... it's a better performing and less ugly solution.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
3
1567

Re: List functionality

Hi,

this was my original idea, but I am not sure it is going to work.

I'll try to describe as best I can. Sorry if it doesn't make sense.

 

1. I have a table which I am able to select a work order number from. As this is in the first column in this table and MsgBox seems to confirm.

 

2. I then have another table which I planed to use as a scratch pad. In that I would fill it with notes related to the work order number selected. Then would be cleared / deleted of contents prior to selecting another work order number.

 

Using the insert into table. Which I am having issues of adding a "where" to at the moment. Which I hoping is because I am not using the correct syntax. But may be down to the same issue of the first column. (WorkOrderID is a string).

 

("INSERT INTO NOTESSP (WorkOrderID , Xx2, Xx3, Xx4, Xx5, Xx6, Xx7, Xx8, Xx9, Xx10) (SELECT WorkOrderID , Xx2, Xx3, Xx4, Xx5, Xx6, Xx7, Xx8, Xx9,Xx10 FROM CNOTES WHERE WorkOrderID = '" + SelectedRow + "'" + "")

 

On this, I'm I able to set out the columns of the NOTESP in a different sequence and the Insert into table, would it still work?

So I could put NoteNumber as the first column? Which is a long and indexed and has unique numbers.

 

Hope this makes sense, apologies if it doesn't.

It may not work anyway.

Thanks,

BevanWeiss
Spock
Spock
0 Likes
2
1556

Re: List functionality

2. I then have another table which I planed to use as a scratch pad. In that I would fill it with notes related to the work order number selected. Then would be cleared / deleted of contents prior to selecting another work order number.

 

I'm a bit confused by this part.  What kind of 'notes related to the work order number selected' are you refering to?

Do you mean notes for an individual, or that should be visible to everyone?

And if visible to everyone, is there a reason that you wouldn't want them to just be recorded against the original Work Order table, so that when the Work Order is deleted etc then the notes are also deleted? (if the notes need to stick around beyond the life of the Work Order, then it seems having another table that you just add note rows into which have an indexed column against the Work Order ID would do the job)


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
1
1552

Re: List functionality

Hi Bevan,

I totally agree "if notes need to stick around beyond the life of the Work Order, then it seems having another table that you just add note rows into which have an indexed column against the Work Order ID would do the job". Really glad you mentioned it.
It was my first suggestion. However, I think the intent is to have all notes of different sorts in one table.
It concerns me that both Adam and yourself highlighted issues with both previous mentioned work rounds.
Thanks,

DavidSkilbeck
Lt. Commander
Lt. Commander
0 Likes
0
1506

Re: List functionality

Hi,

I managed to get by for what I need to do with two lists, in the end.

I will mark Adams post as the answer as it was the first.

Thanks to both of you!

Thanks,