TableSheet Rules

In This Topic

TableSheet supports different data rules to help emphasize and visualize variations in data as well as control the type of data entered in a TableSheet.

Conditional Formatting and Data Validation

TableSheet supports data-bound conditional formatting and data validation in the TableSheet view.

You can specify the conditionalFormat and validator properties when defining the view column info. These properties accept different types of rule option arrays which are listed in the table below.

The following conditional format and data validation types are supported in TableSheet.

Property	Types
Conditional Format	AverageRule CellValueRule DataBarRule DateOccuringRule DuplicateRule FormulaRule IconSetRule SpecificTextRule ThreeScaleRule Top10Rule TwoScaleRule UniqueRule
Data Validator	DateValidator FormulaListValidator FormulaValidator ListValidator NumberValidator TextLengthValidator TimeValidator

Property

Types

Conditional Format

AverageRule

CellValueRule

DataBarRule

DateOccuringRule

DuplicateRule

FormulaRule

IconSetRule

SpecificTextRule

ThreeScaleRule

Top10Rule

TwoScaleRule

UniqueRule

Data Validator

DateValidator

FormulaListValidator

FormulaValidator

ListValidator

NumberValidator

TextLengthValidator

TimeValidator

The following code sample shows how to apply conditional formatting and data validation in a TableSheet.

JavaScript	Copy Code
// formula to apply Conditional Formatting var formulaRule = { ruleType: "formulaRule", formula: "@<10", style: { backColor: "green", foreColor: "lime" } }; // create a data validator var positiveNumberValidator = { type: "formula", formula: '@>0', inputTitle: 'Data validation:', inputMessage: 'UnitPrice cannot be less than 0.', highlightStyle: { type: 'icon', color: "red", position: 'outsideRight', } }; //bind a view to the table sheet myTable.fetch().then(function () { var view = myTable.addView("myView", [ { value: "stockItem", width: 300, caption: "Stock Item" }, { value: "unitPrice", width: 120, caption: "Unit Price", conditionalFormats: [formulaRule], validator: positiveNumberValidator }, // apply conditional formating and data validation here { value: "taxRate", width: 120, caption: "Tax Rate" }, { value: "=([@unitPrice] * [@taxRate])/100 + [@unitPrice]", caption: "Total Price", width: 150 } ]); sheet.setDataView(view); });

JavaScript

Copy Code

// formula to apply Conditional Formatting
var formulaRule = {
    ruleType: "formulaRule",
    formula: "@<10",
    style: {
        backColor: "green",
        foreColor: "lime"
    }
};
// create a data validator
var positiveNumberValidator = {
    type: "formula",
    formula: '@>0',
    inputTitle: 'Data validation:',
    inputMessage: 'UnitPrice cannot be less than 0.',
    highlightStyle: {
        type: 'icon',
        color: "red",
        position: 'outsideRight',
    }
};

//bind a view to the table sheet
myTable.fetch().then(function () {
    var view = myTable.addView("myView", [

        { value: "stockItem", width: 300, caption: "Stock Item" },
        { value: "unitPrice", width: 120, caption: "Unit Price", conditionalFormats: [formulaRule], validator: positiveNumberValidator }, // apply conditional formating and data validation here
        { value: "taxRate", width: 120, caption: "Tax Rate" },
        { value: "=([@unitPrice] * [@taxRate])/100 + [@unitPrice]", caption: "Total Price", width: 150 }
    ]);
    sheet.setDataView(view);
});

Style Rules

Style Rules are styles that are applied when a condition is met. You can add a state rule and formula rule using the GC.Data.View class method addStyleRule or using the addView method parameter viewOptions when adding a view to the Data Manager table.

State Rule – Apply style when a row or column state is triggered. For example, if you want to highlight a row with gray back color whenever the mouse hovers over it, or highlight the selected column as illustrated in the image below.
Formula Rule – Apply style to specific values that meet certain conditions. For example, if you want to highlight the lower price of a product, set formulas like "[@UnitPrice]<10" with a different fore color as shown in the image below.

The following code sample shows how to apply both state rule and formula rule in a TableSheet.

JavaScript	Copy Code
// Setting formula rule var formulaRule = { ruleType: "formulaRule", formula: "@<=10", style: { borderLeft: { color: "orange", style: "medium" }, borderRight: { color: "orange", style: "medium" }, borderTop: { color: "orange", style: "medium" }, borderBottom: { color: "orange", style: "medium" } } }; //bind a view to the table sheet var myView = productTable.addView("myView", [ { value: "Id", caption: "ID" }, { value: "ProductName", caption: "Name", width: 400 }, { value: "ReorderLevel", caption: "ReorderLevel", width: 120}, { value: "UnitPrice", caption: "Unit Price", width: 100, readonly: true, conditionalFormats: [formulaRule] }, { value: "UnitsInStock", caption: "Units In Stock", width: 100 }, { value: "UnitsOnOrder", caption: "Units On Order", width: 100 } ]); // Adding rules through addStyleRule method myView.addStyleRule("lowPrice", { backColor: "green", foreColor: "white" }, { formula: "[@UnitPrice] <= 10" }); myView.addStyleRule("HoverStyle", { backColor: "gray", foreColor: "white" }, { direction: GC.Data.StateRuleDirection.row, state: GC.Data.RowColumnStates.hover }); myView.addStyleRule("SelectedStyle", { backColor: "yellow", foreColor: "red"}, { direction: GC.Data.StateRuleDirection.column, state: GC.Data.RowColumnStates.selected }) myView.fetch().then(function () { sheet.setDataView(myView); });

JavaScript

Copy Code

// Setting formula rule
var formulaRule = {
    ruleType: "formulaRule",
    formula: "@<=10",
    style: {
        borderLeft: { color: "orange", style: "medium" },
        borderRight: { color: "orange", style: "medium" },
        borderTop: { color: "orange", style: "medium" },
        borderBottom: { color: "orange", style: "medium" }
    }
};

//bind a view to the table sheet
var myView = productTable.addView("myView", [
    { value: "Id", caption: "ID" },
    { value: "ProductName", caption: "Name", width: 400 },
    { value: "ReorderLevel", caption: "ReorderLevel", width: 120},
    { value: "UnitPrice", caption: "Unit Price", width: 100, readonly: true, conditionalFormats: [formulaRule] },
    { value: "UnitsInStock", caption: "Units In Stock", width: 100 },
    { value: "UnitsOnOrder", caption: "Units On Order", width: 100 }
]);

// Adding rules through addStyleRule method
myView.addStyleRule("lowPrice", { backColor: "green", foreColor: "white" }, {
    formula: "[@UnitPrice] <= 10"
});

myView.addStyleRule("HoverStyle", { backColor: "gray", foreColor: "white" }, {
    direction: GC.Data.StateRuleDirection.row,
    state: GC.Data.RowColumnStates.hover
});

myView.addStyleRule("SelectedStyle", { backColor: "yellow", foreColor: "red"}, {
    direction: GC.Data.StateRuleDirection.column,
    state: GC.Data.RowColumnStates.selected
})

myView.fetch().then(function () {
    sheet.setDataView(myView);
});

Product Support Forum | Print this page