With most Lists, the color that ClearSCADA uses for displaying each entry in the List is determined by that entry’s state and, in the case of a Queries List, any actions that are being performed on, or are associated with, the item (see The Color of Entries in a List other than the Alarms List).
With such a List, the Foreground, Background, and Blink fields are used to control the color of the entries in the List. These fields are ‘special’ fields that are not displayed as separate columns in the List (see Fields that Perform Special Functions). By including these fields in the SQL for a List, this enables the whole row for each record to change color according to that item’s state, or the activity associated with that item.
If required, you can ‘customize’ a List, so that the entries in an individual column change color according to a particular expression. To do this, you include a special attribute column in the List’s SELECT Clause, to control the color of the entries in the next column that is listed in that clause. You assign the special attribute column an alias that either comprises, or commences with, Foreground, Background, or Blink. The syntax for this is:
<expression> AS "{<alias>}", "<field>"
where:
- <expression> is the expression that evaluates either:
- To an integer that is interpreted as a color (if the alias is, or begins with, either Foreground or Background)
Or:
- To a Boolean (if the alias is, or begins with, Blink).
- <alias> specifies which color attribute the above expression determines for the entries in the next column that is specified in the SELECT Clause. Specify Foreground, Background, or Blink, as appropriate. Where more that one column is assigned a color alias, specify an alias that begins with one of these three strings.
- <field> is the field that represents the column that is being assigned the above alias.
NOTE: You can only assign one column per List to a particular alias. As such, if more than one column is to change foreground color, for example, the Foreground alias has to be suffixed with different letter(s) or number(s) for each column that requires such an alias. For example, {Foreground1} for the first column that is to have such an alias, and {Foreground2} for the second column that is to have a Foreground alias.
If the SQL for a List does not include the Foreground, Background, and Blink fields as separate fields in the SELECT Clause, only the entries in those column(s) that are assigned a color alias will have their color determined by a color property in the SQL. The workstation’s Windows® settings will determine the foreground and background colors of entries in those columns that are not assigned a color alias.
If the SQL does include the Foreground, Background, and Blink fields as separate fields in the SELECT Clause:
- The color expression that is assigned to any column that uses a color alias will determine the color of the entries in that particular column.
- The entries in those columns that are not assigned a color alias will be determined by the Foreground, Background, and Blink fields, as per most Lists.
The examples below demonstrate how color aliases can be used to control the color or blink element of entries in specific column(s) in a List.
Example: 1
The states of certain digital points on a particular ClearSCADA system are assigned Custom colors. A custom List is produced, based on the Points List. The List queries the Points table CDBPoint. Rather than include the Foreground, Background, and Blink fields in the SQL, it has been decided to limit the colors of the entries. The foreground color of the entries in the ‘State’ and ‘Last Updated’ columns will be determined by each point’s state color; the color of the entries in other columns will use the Windows® defaults.
The SQL query shows that ‘State’ and ‘Last Updated’ are the display names of the CurrentStateDesc and CurrentTime fields, respectively. (A display name is the string that is displayed in the language that is associated with the logged on user’s specified Locale.)
The database schema is used to look up the CDBPoint table. This indicates that a point’s State Color is determined by its CurrentStateColour field.
To enable the entries in the two columns to be displayed using the appropriate state color, the SELECT Clause is modified to include this syntax:
"CurrentStateColour" AS "{Foreground1}", "CurrentStateDesc", "CurrentStateColour" AS "{Foreground2}", "CurrentTime"
(As more than one column is assigned a Foreground color alias, a digit is appended to each ‘Foreground’ entry, to ensure that each alias is unique.)
Example: 2
A custom List is produced, based on the Objects List for a particular Group, ‘Eastern Region’. The List queries the table CDBOBJECT. The List is to show which of the items in the Group do not have valid configuration.
The Foreground, Background, and Blink fields are omitted from the List.
The database schema shows that the CDBOBJECT table includes a ConfigValid field. This field is included in the SQL for the List. The List’s SELECT Clause is modified to include this syntax:
"FullName" AS "~FullName", "Id", "TypeDesc",
CASE "ConfigValid" WHEN TRUE THEN FALSE ELSE TRUE END AS "{Blink}", CASE "ConfigValid" WHEN TRUE THEN 51200 ELSE 255 END AS "{Foreground}", CASE "ConfigValid" WHEN TRUE THEN 'Yes' ELSE 'NO' END AS "ConfigValid"
This results in a List in which the ConfigValid entry displays:
- A green ‘Yes’ for those items in the Group that have valid configuration
- A flashing red ‘NO’ for those items that have invalid configuration.
Further Information
Custom Colors: see Defining a Custom State Color in the ClearSCADA Guide to Core Configuration.
Using the Database Schema: see Working with the Database Schema in the ClearSCADA Guide to the Database.
Display a Queries List from the Queries Bar.
Ascertain which SQL Query is Presently used by a List.
SQL Query Structure in the ClearSCADA Guide to SQL