WireBootstrap
HomeDocsBuySupport
  • Introduction
  • Overview
  • Getting Started
  • Installation
  • Connecting to Data
    • Data Connectors
      • SQL Data
      • Custom Web Services
      • Local Data
      • Other Sources
    • Data Sources
    • Discovery
    • Building a Data Connector
  • Working With Queries
    • Select Queries
      • Query Extensions
    • Stored Procedures
    • Custom Web Services
    • Executing Queries
    • ORM
  • Writing Data
  • Updates
  • Deletes
  • Working with DataTables
    • Introduction
    • Filter and Sort
    • Select and Calculate
    • Joins
    • Rows
    • Columns
  • Working with DataSets
  • Introduction
  • Executing Queries
  • Transforms
  • Writing Data
  • Data Events
  • Working with Components
    • Introduction
    • Encapsulation
    • Configuration
      • Data
      • Events
      • Observables
      • Validation
      • Display
      • Vendor
      • Custom
    • Component
    • Web Frameworks
    • Building a Component
      • Hello World
      • Data Events
      • DataSets
  • Working With Data Events
    • Introduction
    • Event Basics
    • Event Data
    • Event Actions
  • Working with Themes
    • Introduction
    • Sample Data
    • Libraries
    • Pages
    • Building a Theme Project
  • Utilities
    • Formatting
    • Expressions
    • Collections
    • Spinners
    • Copy and Merge
    • Validation
    • Loading Assets
    • Service Calls
    • Download Files
    • Browser Location
    • Types
    • Promise
  • Reference
    • wire
      • wire.Collection
      • wire.download
      • wire.validate
    • wire.data
      • wire.data.DataEvent
      • wire.data.DataModel
      • wire.data.DataPromise
      • wire.data.DataSet
      • wire.data.DataSource
      • wire.data.DataTable
        • Columns
        • Rows
      • wire.data.StoredProcedure
      • wire.data.TableQuery
    • wire.ui
      • wire.ui.Component
      • wire.ui.validate
Powered by GitBook
On this page
  • Select
  • Format
  • Title
  • Number Formatting
  • Custom Formatting
  • Calculated Columns

Was this helpful?

  1. Working with DataTables

Select and Calculate

PreviousFilter and SortNextJoins

Last updated 3 years ago

Was this helpful?

A WireBootstrap allows rows of data to be selected, aggregated, and transformed.

Select

The following example selects two columns, product and price, from the data table and creates a new data table using the table function at the end of the method chain.

let data = [ 
    { product: "apples", order: "PRN001", quantity: 10, price: 5.5 },
    { product: "oranges", order: "PRN002", quantity: 15, price: 6 },
    { product: "bananas", order: "PRN002", quantity: 20, price: 7.5 }
];

let table = new wire.data.DataTable(data);
 
table = table.select("product", "price").table();

Format

Columns have a number of attributes that can be set in the select method chain. In order to set these attributes, reference an individual column using the column method and then use any number of the attribute methods to set it's values.

Use the as function to change the name of a field. In the example below, the field quantity will be named number_of_skus in the new table.

...

table = table.select().column("quantity").as("number_of_skus").table();

Title

Use the title function to give any column a descriptive title. This will be set into the Title property for the column in the resulting table.

...

table = table.select().column("product").title("Product Title").table();

A data table needs a descriptive Title value for any column. If one is not supplied, it will automatically create one. In doing so, it will parse camelCase or PascalCase field names and intelligently capitalize and space accordingly to come up with a friendly title.

In the example below, a title for the numberOfSkus column is not supplied. In the new table, the title will automatically be Number Of Skus.

...

table = table.select().column("quantity").as("numberOfSkus").table();

Number Formatting

To format numeric columns, use the format method with a format specification. Valid values are C[N], P[N], N[N] where [N] is the number of decimal places.

The example below sets the formatting for the price column to currency one decimal place using the C1 parameter in the format function. This value will be set into the Format property for the column in the resulting table.

...

table = table.select("product").column("price").format("C1").table();

Custom Formatting

The format method can also accept a custom function in order to format data in the column.

...

table = table.select().column("date_field").format((row) => {
    return moment(row["date_field"]).format("MM-DD-YYY");
}).table();

Calculated Columns

Data tables allow calculated columns to be created on the fly.

The following creates two columns in a new data table that are the aggregates of columns in an existing data table. In the new table, quantity will be aggregated using sum. It will have the same name and be formatted to numeric one decimal place using format N1. The price column will be averaged using avg and it's name changed to avg_price. It will be formatted to currency one decimal place using format C1.

...

table = table.select()
    .sum("quantity").format("N1")
    .avg("price").as("avg_price").format("C1")
    .table();

Aggregates can also be used with implicit grouping by selecting other columns to be included in the result. In the example below, the order column is included in the new data table, so the calculated column quantity is broken out by order.

...

table = table.select("order").sum("quantity").table();

Calculated columns can also be created using custom formulas using the calc method.

In the example below, a new column, more_quantity, is calculated as the sum of the quantity column times 10 and returned in a new table. This is done using a custom function and a delegate row. The function will also have the index position and all rows in the data table available as it is being evaluated.

...

table = table.select()
    .column("more_quantity")
    .sum().calc((row, index, rows) => {
        return row["quantity"] * 10;
    })    .table();

The following example formats a date field called date_field using the third party library .

DataTable
column
moment.js