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
84248members
353348posts

[Imported] Query List of Template Values

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
sbeadle
Janeway Janeway
Janeway
0 Likes
1
988

[Imported] Query List of Template Values

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: Tanquen, originally posted: 2019-07-17 00:43:50 Id:473
Like if you had a pump template with pump speed and wanted to list all the pumps speeds.

I have a list object on a screen and can query some values but not all.

This gives me a list of some of the tags in the group, Variables tags but not Internal tags in a template instance in the same group.

SELECT
"FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "ValueFormatted", "LastUpdated", "TypeDesc", "MemoryUsage"
FROM
CVARIABLE
WHERE
"FullName" LIKE '%C-01%'
ORDER BY
"~FullName" ASC

The original template:
~Config.~ RTU Site.Direct eNET Outstation


If I use From CDBPOINT it will list the Internal tags I want **but no values**.


Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
987

Re: [Imported] Query List of Template Values

>>Responses imported from previous forum


Reply From User: BevanWeiss, posted: 2019-07-17 00:49:02
This is a limitation of the ClearSCADA Schema. If you're putting together queries like this, then you should get familiar with it 🙂

What you'll need is a UNION for what you want.

SELECT FullName, Id, Foreground, Blink, Background, ValueFormatted, LastUpdated, TypeDesc
FROM CVariable
UNION CDBPoint
Where FullName LIKE '%C-01%%
ORDER By FullName ASC

If you're looking in a template, then it will have no values. It's a template (an 'abstract' thing, not a representation of anything in the real world).


Reply From User: Tanquen, posted: 2019-07-17 00:59:44
This gives me the two types of tags but only if I remove the ValueFormatted and LastUpdated columns. Both tag types are part of the same template but I can only see the values of the Variables (yellow) and not the Internal (Dark Blue)?

Don't need to even see the Variable type I just noticed an example that would let me see the Variable type tags and their values.
____________________________________________
Looks like CurrentValueFormatted works. 🙂


Reply From User: BevanWeiss, posted: 2019-07-17 03:16:32
I didn't have the schema infront of me, I was hoping that ValueFormatted would exist for CDBPoint also (and would have mapped to CurrentValueFormatted internally).

If you want them both then you may have to do sub-queries.
SELECT Id, FullName, ValueFormatted
FROM
CVariable
UNION
SELECT Id, FullName, CurrentValueFormatted As "ValueFormatted"
FROM CDBPoint

^^ untested, there may be ClearSCADA QP limitation which makes this not so workable (I can't remember if I've tried using the SELECT beneath a UNION... I would have thought so, but memory and old age 👅 ).

 

Reply From User: sbeadle, posted: 2019-07-17 08:04:29
Check the SQL guide. You'll see that UNION can be done in two ways. One as a list of tables, the other as a list of queries - each separated by UNION.


Reply From User: Tanquen, posted: 2019-07-17 15:37:00
I'll keep the UNION option in mind if I need to list the different types together.

Any idea why the different tables (CVARIABLE or CDBPOINT) show in different colors in the list object? Can I just make them all black text?

 

Reply From User: BevanWeiss, posted: 2019-07-19 09:10:16
The different colours are because in your query your telling it to use different colours (with Foreground, Background, Blink). Leave them out and they will be black on white (default). You can set these to any RGB number you want (R*256*256+G*256+B) [R,G,B between 0 and 255]


Reply From User: Tanquen, posted: 2019-07-19 15:18:19
Thanks. I had taken those out but it just went from pink to red. I'll see if I can find how to add the RGB number in.

Ok, removed the "Foreground, Background, Blink" and then set the TextColour to black in the list objects animation. Not sure why "Foreground, Background," make different stuff different colors or why removing them still gets you red but oh well.


Reply From User: BevanWeiss, posted: 2019-08-06 02:51:41
Foreground and Background make stuff different colours because it's the special way ViewX handles query results with these column labels.
This is detailed within the help.
It makes sense if you want to easily animate the list colours (which I normally do) for objects. It's very handy to show if an outstation in a list is disconnected, or has a certain alarm against it etc etc. With the 'native' colour from the CS database configuration (i.e. Colour Palette, and Alarm Severities).


Reply From User: Tanquen, posted: 2019-08-08 23:40:18
Ok, it's a bit more complicated. It was very hard to find everything but'

The sites have a screen that they all use and it's linked to a set of real or indirect tags. So when you select a certain site a script in that sites object runs and gets the values from the correct PLC and puts them into retentive script variables that are then temporally linked to the real tags that are linked on the screen.

Variables like these:
VAR RETAIN
AO_ConfigID1: REAL;

End up in something like this:
CFG_AI_01_Eng_Max

I spoke with the guy that setup this craziness and he doesn't think you can query the script variables. He said they did this because of some kind of limitation on the total number of tags. I'm guessing you would need to add a real tag into each of the site objects if you wanted to be able to query all the sites and see the value?

I was able to find all the objects with these tags using this query but have no idea how to get at the retentive script variables in each.

SELECT
"FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "ValueFormatted", "LastUpdated", "TypeDesc", "MemoryUsage"
FROM
CVARIABLE
WHERE
( "~FullName" LIKE '%.CFG\\_VENTURI%' ) AND ( "ValueFormatted" = 'True' )
ORDER BY
"~FullName" ASC


Reply From User: Tanquen, posted: 2019-08-12 01:42:17
"Your terminology of 'retentive script variables' is incorrect. You are refering to a RETAINed variable within a Logic Program. Logic != Script."

I could not disagree less. Why do you do stuff like this?


Reply From User: BevanWeiss, posted: 2019-08-12 04:22:27
[at]Tanquen said:
I could not disagree less....

What do you disagree with?
You understand the difference between 'script' and 'logic' within the ClearSCADA system right?
![]((see attachments below) pn/e8zo9xejd3u0.png "")
![]((see attachments below) km/acvnkw1i7tsh.png "")

They behave very differently, and there are a number of critical differences to them.
The distinction between them is very important.

Attached file: (editor/pn/e8zo9xejd3u0.png), Logic.png File size: 9177

Attached file: (editor/km/acvnkw1i7tsh.png), Script.png File size: 9297


Reply From User: du5tin, posted: 2019-09-27 14:15:23
Ick... sounds like they tried to save a few dollars on the point license or support and spent way more on configuration.

Definitely look into setting things up with proper points and not variables.

The DataSets/DataRows do work wonders! You can combine data from different sources and put it in a custom table that is easy to query and work with. This includes values from variables, parameters, points, configuration and more! If there is any list style navigation we use DataSets/DataRows.

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
988

Re: [Imported] Query List of Template Values

>>Responses imported from previous forum


Reply From User: BevanWeiss, posted: 2019-07-17 00:49:02
This is a limitation of the ClearSCADA Schema. If you're putting together queries like this, then you should get familiar with it 🙂

What you'll need is a UNION for what you want.

SELECT FullName, Id, Foreground, Blink, Background, ValueFormatted, LastUpdated, TypeDesc
FROM CVariable
UNION CDBPoint
Where FullName LIKE '%C-01%%
ORDER By FullName ASC

If you're looking in a template, then it will have no values. It's a template (an 'abstract' thing, not a representation of anything in the real world).


Reply From User: Tanquen, posted: 2019-07-17 00:59:44
This gives me the two types of tags but only if I remove the ValueFormatted and LastUpdated columns. Both tag types are part of the same template but I can only see the values of the Variables (yellow) and not the Internal (Dark Blue)?

Don't need to even see the Variable type I just noticed an example that would let me see the Variable type tags and their values.
____________________________________________
Looks like CurrentValueFormatted works. 🙂


Reply From User: BevanWeiss, posted: 2019-07-17 03:16:32
I didn't have the schema infront of me, I was hoping that ValueFormatted would exist for CDBPoint also (and would have mapped to CurrentValueFormatted internally).

If you want them both then you may have to do sub-queries.
SELECT Id, FullName, ValueFormatted
FROM
CVariable
UNION
SELECT Id, FullName, CurrentValueFormatted As "ValueFormatted"
FROM CDBPoint

^^ untested, there may be ClearSCADA QP limitation which makes this not so workable (I can't remember if I've tried using the SELECT beneath a UNION... I would have thought so, but memory and old age 👅 ).

 

Reply From User: sbeadle, posted: 2019-07-17 08:04:29
Check the SQL guide. You'll see that UNION can be done in two ways. One as a list of tables, the other as a list of queries - each separated by UNION.


Reply From User: Tanquen, posted: 2019-07-17 15:37:00
I'll keep the UNION option in mind if I need to list the different types together.

Any idea why the different tables (CVARIABLE or CDBPOINT) show in different colors in the list object? Can I just make them all black text?

 

Reply From User: BevanWeiss, posted: 2019-07-19 09:10:16
The different colours are because in your query your telling it to use different colours (with Foreground, Background, Blink). Leave them out and they will be black on white (default). You can set these to any RGB number you want (R*256*256+G*256+B) [R,G,B between 0 and 255]


Reply From User: Tanquen, posted: 2019-07-19 15:18:19
Thanks. I had taken those out but it just went from pink to red. I'll see if I can find how to add the RGB number in.

Ok, removed the "Foreground, Background, Blink" and then set the TextColour to black in the list objects animation. Not sure why "Foreground, Background," make different stuff different colors or why removing them still gets you red but oh well.


Reply From User: BevanWeiss, posted: 2019-08-06 02:51:41
Foreground and Background make stuff different colours because it's the special way ViewX handles query results with these column labels.
This is detailed within the help.
It makes sense if you want to easily animate the list colours (which I normally do) for objects. It's very handy to show if an outstation in a list is disconnected, or has a certain alarm against it etc etc. With the 'native' colour from the CS database configuration (i.e. Colour Palette, and Alarm Severities).


Reply From User: Tanquen, posted: 2019-08-08 23:40:18
Ok, it's a bit more complicated. It was very hard to find everything but'

The sites have a screen that they all use and it's linked to a set of real or indirect tags. So when you select a certain site a script in that sites object runs and gets the values from the correct PLC and puts them into retentive script variables that are then temporally linked to the real tags that are linked on the screen.

Variables like these:
VAR RETAIN
AO_ConfigID1: REAL;

End up in something like this:
CFG_AI_01_Eng_Max

I spoke with the guy that setup this craziness and he doesn't think you can query the script variables. He said they did this because of some kind of limitation on the total number of tags. I'm guessing you would need to add a real tag into each of the site objects if you wanted to be able to query all the sites and see the value?

I was able to find all the objects with these tags using this query but have no idea how to get at the retentive script variables in each.

SELECT
"FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "ValueFormatted", "LastUpdated", "TypeDesc", "MemoryUsage"
FROM
CVARIABLE
WHERE
( "~FullName" LIKE '%.CFG\\_VENTURI%' ) AND ( "ValueFormatted" = 'True' )
ORDER BY
"~FullName" ASC


Reply From User: Tanquen, posted: 2019-08-12 01:42:17
"Your terminology of 'retentive script variables' is incorrect. You are refering to a RETAINed variable within a Logic Program. Logic != Script."

I could not disagree less. Why do you do stuff like this?


Reply From User: BevanWeiss, posted: 2019-08-12 04:22:27
[at]Tanquen said:
I could not disagree less....

What do you disagree with?
You understand the difference between 'script' and 'logic' within the ClearSCADA system right?
![]((see attachments below) pn/e8zo9xejd3u0.png "")
![]((see attachments below) km/acvnkw1i7tsh.png "")

They behave very differently, and there are a number of critical differences to them.
The distinction between them is very important.

Attached file: (editor/pn/e8zo9xejd3u0.png), Logic.png File size: 9177

Attached file: (editor/km/acvnkw1i7tsh.png), Script.png File size: 9297


Reply From User: du5tin, posted: 2019-09-27 14:15:23
Ick... sounds like they tried to save a few dollars on the point license or support and spent way more on configuration.

Definitely look into setting things up with proper points and not variables.

The DataSets/DataRows do work wonders! You can combine data from different sources and put it in a custom table that is easy to query and work with. This includes values from variables, parameters, points, configuration and more! If there is any list style navigation we use DataSets/DataRows.