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
84650members
353989posts

[Imported] Colour Coding Embedded List Rows Based on Name

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
481

[Imported] Colour Coding Embedded List Rows Based on Name

>>Message imported from previous forum - Category:General Items<<
User: kvacola, originally posted: 2019-09-13 21:38:57 Id:513
I have an embedded list that has a SQL query bringing back a number of items like name, various values, datetime, etc. The embedded list relies on a custom function in the mimic script to fill the list. I need to colour code the background of the rows 3 different colours based on the following conditions: colour 1 if it is well_1, colour 2 if it is well_2, and white for all other wells.

Here is the code I have so far - it only codes the first column:

SQLString = _
" SELECT " & vbCrLf &_
" DISTINCT REPORTDATETIMEUTC AS ""_RECORDTIME"", " & vbCrLf &_
" CASE WELL WHEN 'Well_1' THEN 10001389 WHEN 'Well_2' THEN 15571864 ELSE 16777215 END AS ""{Background}"", CASE WELL WHEN 'Well_1' THEN 'Well_1' WHEN 'Well_2' THEN 'Well_2' ELSE WELL END AS ""Well Tested"", " & vbCrLf &_
" MODE AS ""Well Mode"", " & vbCrLf &_
" STARTDATETIMEUTC AS ""Start Test Time"", " & vbCrLf &_
" FORMATVALUE(HOURS using '#0.00') AS ""Test Flow Hours"", " & vbCrLf &_
" FORMATVALUE(VOLUME using '#0.00') as ""Volume on 24h"", " & vbCrLf &_
" TESTSTATUS AS ""Test Status"", " & vbCrLf &_
" COMMENT, " & vbCrLf &_
" ID AS ""_LOCN"" " & vbCrLf &_
" FROM " & vbCrLf &_
" TEST_HISTORY " & vbCrLf &_
" WHERE " & vbCrLf &_
" ""_RECORDTIME"" BETWEEN {TS '" & sStartDateTime & "' } AND {TS '" + sEndDateTime + "' } AND " & vbCrLf &_
" ""_LOCN"" = '" + iWell_Id + "' " & vbCrLf &_
" ORDER BY " & vbCrLf &_
" ""_RECORDTIME"" Desc " & vbCrLf

The CASE statement above works perfectly and we get that first column to colour code for those 2 wells. I've looked high and low through the documentation on how to do this, and all it gives me is how to do the columns. I've tried a second CASE on the next time with the following syntax:

CASE MODE WHEN WELL = 'Well_1' THEN 10001389 WHEN WELL = 'Well_2' THEN 15571864 ELSE 16777215 END AS "{Background2}", CASE MODE WHEN WELL = 'Well_1' THEN MODE WHEN WELL = 'Well_2' THEN MODE ELSE MODE AS "Well Mode"

But this doesn't work. The MODE isn't specific to a well name - in fact none of the rest of the columns are either.

Any ideas?

I've spend ages searching through Google ...


Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
480

Re: [Imported] Colour Coding Embedded List Rows Based on Name

>>Responses imported from previous forum


Reply From User: BevanWeiss, posted: 2019-09-18 21:38:36
The part that is setting the background colour is this..
AS ""{Background}""

If you want to do this for another column, then you will need to use {Background1}, {Background2}, {Background3} etc... you can't use the same reference twice.

 

Reply From User: kvacola, posted: 2019-09-24 14:50:18
So I finally got this working. The resulting query required me to join a dataset where I store the integer code for the background. The resulting query looks something like this:

SQLString = _
" SELECT " & vbCrLf &_
" DISTINCT A.REPORTDATETIME AS ""_RECORDTIME"", " & vbCrLf &_
" B.BACKGROUND AS ""{Background1}"", " & vbCrLf &_
" A.WELL AS ""Well Tested"", " &vbCrLf &_
" B.BACKGROUND AS ""{Background2}"", " & vbCrLf &_
" FORMATVALUE(A.HOURS using '#0.00') AS ""Test Flow Hours"", " & vbCrLf &_
" B.BACKGROUND AS ""{Background3}"", " & vbCrLf &_
" FORMATVALUE(A.VOLUME using '#0.00') AS ""Volume on 24h"", " & vbCrLf &_
" B.BACKGROUND AS ""{Background4}"", " & vbCrLf &_
" A.ID AS ""_LOCN"" " & vbCrLf &_
" FROM "
" TEST_HISTORY AS A " & vbCrLf &_
" LEFT JOIN DSWELLTESTLIST AS B " & vbCrLf &_
" ON A.WELLID = B.SITEID " & vbCrLf &_
" AND A.WELLNUM = B.SITEINDEX " & vbCrLf &_
" WHERE ""_RECORDTIME"" BETWEEN {TS '" & sStartDateTime & "' } " & vbCrLf &_
" AND {TS '" + sEndDateTime + "' } AND " & vbCrLf &_
" ""_LOCN"" = '" + iSiteLocn + "' " & vbCrLf &_
" ORDER BY ""_RECORDTIME"" DESC " & vbCrLf

Which colours the rows perfectly, except when viewed in ViewX. This script is contained in a template and it works for one instance that colours perfectly according to the integer code. Everywhere else in the dsWellTestList the 'Background' is set to white (16777215). I even tried changing it to some other code like bright red (255), but it still shows black in WebX. I don't understand how it's working for one instance of the display and not for the others. I've triple-checked that when I right-click and go to the template it goes to the exact same one.

I have no idea how to get this working. Is this maybe a case to bump up to Schneider?

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
481

Re: [Imported] Colour Coding Embedded List Rows Based on Name

>>Responses imported from previous forum


Reply From User: BevanWeiss, posted: 2019-09-18 21:38:36
The part that is setting the background colour is this..
AS ""{Background}""

If you want to do this for another column, then you will need to use {Background1}, {Background2}, {Background3} etc... you can't use the same reference twice.

 

Reply From User: kvacola, posted: 2019-09-24 14:50:18
So I finally got this working. The resulting query required me to join a dataset where I store the integer code for the background. The resulting query looks something like this:

SQLString = _
" SELECT " & vbCrLf &_
" DISTINCT A.REPORTDATETIME AS ""_RECORDTIME"", " & vbCrLf &_
" B.BACKGROUND AS ""{Background1}"", " & vbCrLf &_
" A.WELL AS ""Well Tested"", " &vbCrLf &_
" B.BACKGROUND AS ""{Background2}"", " & vbCrLf &_
" FORMATVALUE(A.HOURS using '#0.00') AS ""Test Flow Hours"", " & vbCrLf &_
" B.BACKGROUND AS ""{Background3}"", " & vbCrLf &_
" FORMATVALUE(A.VOLUME using '#0.00') AS ""Volume on 24h"", " & vbCrLf &_
" B.BACKGROUND AS ""{Background4}"", " & vbCrLf &_
" A.ID AS ""_LOCN"" " & vbCrLf &_
" FROM "
" TEST_HISTORY AS A " & vbCrLf &_
" LEFT JOIN DSWELLTESTLIST AS B " & vbCrLf &_
" ON A.WELLID = B.SITEID " & vbCrLf &_
" AND A.WELLNUM = B.SITEINDEX " & vbCrLf &_
" WHERE ""_RECORDTIME"" BETWEEN {TS '" & sStartDateTime & "' } " & vbCrLf &_
" AND {TS '" + sEndDateTime + "' } AND " & vbCrLf &_
" ""_LOCN"" = '" + iSiteLocn + "' " & vbCrLf &_
" ORDER BY ""_RECORDTIME"" DESC " & vbCrLf

Which colours the rows perfectly, except when viewed in ViewX. This script is contained in a template and it works for one instance that colours perfectly according to the integer code. Everywhere else in the dsWellTestList the 'Background' is set to white (16777215). I even tried changing it to some other code like bright red (255), but it still shows black in WebX. I don't understand how it's working for one instance of the display and not for the others. I've triple-checked that when I right-click and go to the template it goes to the exact same one.

I have no idea how to get this working. Is this maybe a case to bump up to Schneider?