wire.data.DataTable

Properties

A data table holds an array of objects in the Rows property much like the rows in a relational database table. Each property in the object has a column that stores meta data about the property in the Columns property.

Name

Type

Default

Description

Columns

array

[ ]

Stores an array of column meta data about each property in the the list of objects stored in each row.

Rows

array

[ ]

Holds the raw list of objects in the data table.

Create a new data table by passing in an array of rows and an optional list of columns. If the columns aren't passed in, they will be created from the rows automatically.

let rows = [
    { UserName: "apeters", FullName: "Amy Peters", Quota: 1000, Active: true},
    { UserName: "jross", FullName: "John Ross", Quota: 1500, Active: true }    
]

let table = new wire.data.DataTable(rows);

Methods

Name

Description

cast

Static method that casts an object with the same data table properties into an instance of the DataTable class.

Select Methods

Use the select method in order to select data from the data table. The select method can take an optional list of comma delimited fields to be selected.

Name

Description

calc(function)

column(column)

Select an individual column name.

distinct

Return a distinct list of rows based on the columns being selected.

nonEmpty

Return only rows that contain no null values.

orderBy(column)

Return the list of rows sorted by column.

top(N)

Return on the top N number of records.

as(newName)

Change the name of the column selected from the column method to newName.

format(format)

Format selected column using the specification in format. This can also be a custom function.

Select Aggregate Methods

Name

Description

avg(column)

Aggregate the numeric column using an average or arithmetic mean.

count(column)

Aggregate the numeric column by counting the rows.

distinctCount(column)

Aggregate the numeric column using a discount count of the rows based on the selected columns.

max(column)

Return the largest numeric value in the column.

min(column)

Return the smallest numeric value in the column.

sum(column)

Aggregate the numeric column by totaling all of its values.

Select the UserName column from the data table. Also select the FullName field but change its name to DisplayName. Aggregate the Quota column using a sum and format it to a whole number with no decimals which will include thousand separator commas. The result will automatically be grouped by UserName and DisplayName.

table.select("UserName")
    .column("FullName").as("DisplayName")
    .sum("Quota").format("N0");

Filter Methods

Start a filter chain using the where method.

Name

Description

filter(function)

Use a custom function to filter the rows of the data table. As the function is evaluated, it will receive the current row, index position, and the list of all rows as delegate parameters.

between(column, value1, value2)

Filter rows to those that have a column value that is between value1 and value2. This comparison will handle numbers and dates and is case sensitive. It is also inclusive and will include of value1 and value2 if applicable.

contains(column, expression)

Filter rows to those that contain a specific expression inside a column. A value will be included in this filter if any part of the value contains the expression. This comparison is case insensitive.

eq(column, value)

Filter rows to those that have a column value that equals value. This comparison is case sensitive.

in(column, values)

Filter rows to those that are contained inside the array of values in column. This comparison is case sensitive.

ne(column, value)

Filter rows to those that have a column value that do not equal value. This comparison is case sensitive.

starts(column, value)

Filter rows to those that start with a specific expression inside a column. This comparison is case insensitive.

Filter the data table to UserName equal to apeters but only if her Quota is greater than 800.

table.select().where()
    .eq("UserName", "apeters")
    .filter((row, index, rows) => {
       return row.Quota > 800; 
    });

Evaluation Methods

Once fields are selected and filtered, use the following methods to evaluate the resulting rows.

Name

Type

Description

count

number

Returns the number row in the result set.

exists

boolean

Returns a true if any rows exist in the result set. Otherwise, returns false.

first

object | null

Returns the first row in the result set. If there are no rows, returns null.

firstOrDefault

object

Returns the first row in the result set. If there are no rows, returns a row with values for each column set to null.

rows

array

Returns all the rows in the result set.

scaler

string | number

Returns the first value in the first row. Assumes there is only one row and one column in the result set.

table

DataTable

Creates a new data table from the result set.

The following creates a new data table with just the UserName field from the original data table.

let newTable = table.select("UserName").table();

Visit Working with DataTables for more details and examples on using data tables.

Last updated