Uncategorized

Data Validation

November 8, 2024January 28, 2025 by Melanie van Wyk

Introduction

Data validation in Formatted Reports ensures that users enter data accurately and consistently by enforcing specific rules and formats. Validation can be applied to individual cells, entire columns, or specific ranges within a report. These validation rules guide user input and help maintain data integrity by restricting invalid values and providing visual feedback for valid, invalid, or changed entries.

Example Script for Data Validation

The following IronPython script demonstrates how to create a formatted report with various validation rules applied to editable cells.

# IronPython

data = Qx.ToMatrix([
  ["Upload Example", "", "Date", Qx.Date("2024-01-01"), "", "Interim"],
  ["", "", "", "", "", ""],
  ["Code", "Name", "Weight", "Value", "Colour", "Comment"],
  ["DELL", "DELL STOCK",  0.45, 1000.00, "Red", ""],
  ["MSFT", "MICROSOFT STOCK",  0.30, 2500.00, "Green", ""],
  ["APPL", "APPLE CORPORATION",  0.25, 2000.00, "Blue", ""],
  ["", "", "", "", "", ""]
])

sheet = Qx.MakeStyleSheet(data)

# Define the styles for changed and invalid editable cells
editableBlock = sheet.Box(3,2,3,3)
editableBlock.Style.Font.Italic = False
editableBlock.Style.Font.Bold = False
editableBlock.Style.Font.Color = "Black"
editableBlock.ChangedStyle.Font.Italic = False
editableBlock.ChangedStyle.Font.Bold = True
editableBlock.ChangedStyle.Font.Color = "Blue"
editableBlock.InvalidStyle.Font.Italic = True
editableBlock.InvalidStyle.Font.Bold = False
editableBlock.InvalidStyle.Font.Color = "Red"

Defining Validation Rules

Weight Column
In the Weight column, cells are constrained to display percentages, restricted to numeric values only, and cannot be left empty.

   weightColumn = editableBlock.Column(0)
   weightColumn.Format = "0%"
   weightColumn.Type = "Number"
   weightColumn.IsNullable = False

Value Column
The Value column restricts entries to positive numbers between 1 and 10,000. If an invalid value is entered, the cell resets, and a message informs the user of the constraints.

   valueColumn = editableBlock.Column(1)  # The value column is the second column in the editable block
   valueColumn.Format = "0.00"
   valueColumn.Type = "Number"  # Constrains cell values to numbers only
   valueColumn.IsNullable = False  # Stops cells from being empty
   # The rule is Python code that will execute when the user enters a value.
   valueColumn.Validator = """
   if Cell.Value < 1 or Cell.Value > 10000:
       Cell.ResetValue()
       result = "Cell value must be between 1 and 10000."
   else:
       result = True
   result
   """

Colour Column
The Colour column provides a dropdown with predefined color choices to limit user input to five options.

   colourColumn = editableBlock.Column(2)
   colourColumn.Format = "Red|Green|Blue|White|Black"

Callback-Level Validation

In addition to individual cell validation, validation can be applied at the callback level to ensure that certain conditions are met before data is sent. In this example, a rule ensures that the total weight of shares across the Weight column equals 100%.

sheet.Callback("DataFeed","Upload Analyst Comment").Validator = """
total = Sheet.Cell(3,2).Value + Sheet.Cell(4,2).Value + Sheet.Cell(5,2).Value
if total != 1:
    Messages.Error("Total for weight column must equal 100%")
    result = False
else:
    result = True
result
"""

Creating a Callback for Comments

The script also includes a callback setup for the Comment cells to upload user-entered comments alongside other data. This example demonstrates additional settings for configuring the datafeed callback.

sheet.Cell(0,5).Name = "AttributeValue"

# Create a callback for the 3 comment cells in the report
# Each cell in the callback selection will result in one row sent to the datafeed
# The columns sent to the datafeed can be populated using the SetThis and SetRelative methods
callback = sheet.Box(3,5,3,1).Callback("DataFeed","Upload Analyst Comment")  # Specify DataFeed as the callback type and provide the name for the feed
callback.Data.SetThis("Analyst Comment")  # Use the SetThis method to specify under which column name the data in this selection should be passed to the feed
callback.Data.SetRelative("Instrument Code", 0, -5)  # Use the SetRelative method to specify additional data columns to be sent to the feed
callback.Data.SetAbsolute("Value Date", 0, 3)  # All rows sent to the feed will refer to the date in row 1, column 4
callback.Data.SetNamed("Attribute","AttributeValue")  # Assign a value to the attribute column by referring to a cell in the stylesheet that was given a name

This setup allows for comprehensive data validation by controlling input formats, setting constraints, and providing user feedback. Data validation at both cell and callback levels ensures that only complete, accurate data is uploaded or processed in Quintessence.