[Imported] Colour Coding Embedded List Rows Based on Name

[sbeadle]

>>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 ...

[sbeadle · Accepted solution]

>>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?