Conditional Formatting
The following example shows how you can dynamically change the appearance of data based on conditions.
Conditional formatting is applied to the calculated dip deviation per metre between adjacent downhole survey readings using a script. The data is formatted according to the thresholds shown.
The following SQL was used in this Flow design and can be adapted to your requirements:
Conditional Formatting SQL
SELECT PROJECT
,SITE_ID
,INSTANCE
,READING_NO
,DEPTH
,ROUND(AZIMUTH, 3) AS AZIMUTH
,ROUND(DIP, 3) AS DIP
,CASE (DEPTH - LAG(DEPTH) OVER (PARTITION BY PROJECT, SITE_ID, INSTANCE ORDER BY DEPTH, READING_NO))
WHEN NULL
THEN NULL
WHEN 0
THEN NULL
ELSE
ROUND(
ABS(DIP - LAG(DIP) OVER (PARTITION BY PROJECT, SITE_ID, INSTANCE ORDER BY DEPTH, READING_NO))
/ (DEPTH - LAG(DEPTH) OVER (PARTITION BY PROJECT, SITE_ID, INSTANCE ORDER BY DEPTH, READING_NO))
,3)END AS Dip_Deviation_per_m
FROM dbo.GB_DOWNHOLE_SURVEY_DATA
ORDER BY PROJECT DESC, SITE_ID, INSTANCE, DEPTH, READING_NO;About the Example
The code (MainForm.cs) contains comments to explain how formatting is applied in response to different user actions:
private void format_dip_deviation_cell(DataGridViewRow row)
{
// This function applies formatting to the provided row based on the value of the Dip_Deviation_per_m cell.
// Dip_Deviation_per_m is at index 7 in the row.
DataGridViewCell cell = row.Cells[7];
Double dip_deviation;
Double error_threshold;
Double warning_threshold;
var cell_value = cell.Value;
// Set cell's background color to default.
cell.Style.BackColor = Color.White;
if (cell_value != null)
{
// Parse the cell value, the error level, and warning level.
Double.TryParse(cell_value.ToString(), out dip_deviation);
Double.TryParse(error_level_textBox.Text, out error_threshold);
Double.TryParse(warning_level_textBox.Text, out warning_threshold);
// If the dip deviation value exceeds the error threshold, set the cell's background color to red.
if (dip_deviation >= error_threshold)
{
cell.Style.BackColor = Color.Red;
}
// If the dip deviation value exceeds the warning threshold, set the cell's background color to orange.
else if (dip_deviation >= warning_threshold)
{
cell.Style.BackColor = Color.Orange;
}
}The actual formatting changes take place in the method named format_dip_deviation_cell which takes a DataGridViewRow object as input. The purpose of this method is to apply specific formatting to a cell in the provided row.
The method starts by selecting the cell at index 7 of the row (the Dip_Deviation_per_m column is at index 7), which is stored in a DataGridViewCell object. Then, it declares three Double variables dip_deviation, error_threshold and warning_threshold.
The current value of the selected cell is assigned to a variable named cell_value. The background colour of the cell is then reset to its default value, which is white.
If the cell_value variable is not null, the Double.TryParse method is used to try to convert it to a Double, and the result is stored in the dip_deviation variable. Similarly, the values of the error_level_textBox and warning_level_textBox controls are converted to Double and stored in the error_threshold and warning_threshold variables, respectively.
Depending on the value of dip_deviation, the background colour of the cell is set to red or orange, if it is above the error_threshold or warning_threshold, respectively. Otherwise, the background colour remains white.
You can download the Conditional Formatting example Flow design from the link provided.
General Application of Conditional Formatting
This logic can be adapted to any kind of condition against which to compare data and set formatting appropriately. In addition to BackColor, other properties such as Visible, ForeColor, BorderStyle and Font.Bold can be set. Similar principles can be used to format text or values in controls such as TextBox or ComboBox.
Other Events can also be used, such as the Paint event which occurs more frequently and covers a broader range of changes to data.