Create a table view

Note:

The content in this help topic applies to SuiteScript 2.x.

In this step, you will create a table view based on the data in our dataset. Table views let you analyze your data in a tabular format using specified columns and optional formatting rules. The following sections describe different aspects of creating a table view.

Conditional formatting rules

You can use conditional formatting rules to change the format of a table view cell based on conditions you specify. You can change a cell’s background color, font size, font style, and other formatting properties.

In this tutorial, we use two conditional formatting rules:

A rule that changes a cell’s background color to yellow when the cell value is less than 10.
A rule that changes a cell’s background color to pink when the cell value is greater than or equal to 0.

These two rules may seem to conflict, but only the first matching rule is applied to a cell. Subsequent matching rules do not overwrite the formatting applied by the first matching rule. When we use these rules to create a conditional format, we add the rules in the order specified above. This approach means that if a cell matches both rules (for example, if the cell’s value is 5), only the formatting for the first rule is applied and the cell background color is changed to yellow.

Use workbook.createConditionalFormatRule(options) to create the conditional formatting rules. This method accepts two parameters:

filter – The filter used to determine whether a cell matches the rule. We use workbook.createTableColumnFilter(options) to create each filter.
style – The style to apply when a cell matches the rule. We use workbook.createStyle(options) and workbook.createColor(options) to create the colors for each filter.

After the rules are created, use workbook.createConditionalFormat(options) to create the workbook.ConditionalFormat object that includes both rules.

            var yellowRule = nWorkbook.createConditionalFormatRule({
    filter: nWorkbook.createTableColumnFilter({
        operator: "LESS",
        values: [10]
    }),
    style: nWorkbook.createStyle({
        backgroundColor: nWorkbook.createColor({
            red: 255,
            green: 255,
            blue: 0,
        })
    })
});

var pinkRule = nWorkbook.createConditionalFormatRule({
    filter: nWorkbook.createTableColumnFilter({
        operator: "GREATER_OR_EQUAL",
        values: [0]
    }),
    style: nWorkbook.createStyle({
        backgroundColor: nWorkbook.createColor({
            red: 255,
            green: 192,
            blue: 203
        })
    })
});

var conditionalFormat = nWorkbook.createConditionalFormat({
    rules: [yellowRule, pinkRule]
});

Table columns

Now that we have a conditional format, we can use it to create the columns to include in the table view. Use workbook.createTableColumn(options) to create table view columns. To use this method, you need to specify the column in the dataset that you want to include. To do so, use the column alias that we set when we created the dataset.

            var credit = nWorkbook.createTableColumn({
    datasetColumnAlias: 'Credit',
    conditionalFormats: [conditionalFormat]
});
var debit = nWorkbook.createTableColumn({
    datasetColumnAlias: 'Debit',
    conditionalFormats: [conditionalFormat]
});

Final table view

Finally, we use workbook.createTable(options) to create the final table view. Make sure you specify the dataset and columns to use.

            var tableview = nWorkbook.createTable({
    id: 'view',
    name: 'Table',
    dataset: dataset,
    columns: [credit, debit]
});

At this point, your script file should look similar to the following:

            define(['N/workbook', 'N/dataset'], function(nWorkbook, nDataset){
    return {
        createWorkbook: function (context) {
            var dataset = nDataset.load({
                id: 'customscript_transaction'
            })

            var SORT = {
                ASCENDING: nWorkbook.createSort({
                    ascending: true
                }),
                DESCENDING: nWorkbook.createSort({
                    ascending: false
                })
            }

            var yellowRule = nWorkbook.createConditionalFormatRule({
                filter: nWorkbook.createTableColumnFilter({
                    operator: "LESS",
                    values: [10]
                }),
                style: nWorkbook.createStyle({
                    backgroundColor: nWorkbook.createColor({
                        red: 255,
                        green: 255,
                        blue: 0,
                    })
                })
            });

            var pinkRule = nWorkbook.createConditionalFormatRule({
                filter: nWorkbook.createTableColumnFilter({
                    operator: "GREATER_OR_EQUAL",
                    values: [0]
                }),
                style: nWorkbook.createStyle({
                    backgroundColor: nWorkbook.createColor({
                        red: 255,
                        green: 192,
                        blue: 203
                    })
                })
            });

            var conditionalFormat = nWorkbook.createConditionalFormat({
                rules: [yellowRule, pinkRule]
            });

            var credit = nWorkbook.createTableColumn({
                datasetColumnAlias: 'Credit',
                conditionalFormats: [conditionalFormat]
            });
            var debit = nWorkbook.createTableColumn({
                datasetColumnAlias: 'Debit',
                conditionalFormats: [conditionalFormat]
            });

            var tableview = nWorkbook.createTable({
                id: 'view"',
                name: 'Table',
                dataset: dataset,
                columns: [credit, debit]
            });
        }
    }
});

Previous: Set up the workbook

Next: Create a pivot

Conditional formatting rules

Table columns

Final table view

Related Topics