List functionality

[DavidSkilbeck]

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,

[AdamWoodland · Accepted solution]

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]

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.

[DavidSkilbeck]

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]

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)

[DavidSkilbeck]

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]

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,