Data Table

Title

Specify text of the title that will appear next to the control.

Can also toggle Title’s visibility on/off:

Title can be changed directly on the form after a double click:

// returns the control's title as a string
fd.control('Control1').title;

// sets the control's title
fd.control('Control1').title = 'Last Name';

Name

A unique identifier for the control.

The Name property is used in JavaScript to select a specific control.

fd.spRendered(() => {
    // can access the control using its Name:
    fd.control('Control1');
 });

Required

Define whether the control will be required to submit the form or not:

Required status can also be changed directly on the form via the Asterisk button:

// returns the control's required status as true/false
fd.control('Control1').required;

// sets the control's required status
fd.control('Control1').required = true;

Width

The property defines the width of the control in pixels.

If left blank, the control takes up the entire available width in the current grid cell.

New Line

Allows to select where the new line will be added — at the Top or at the Bottom of the table.

Delete

Allows to select where the delete button will appear — in the first or in the last column.

Save to

Select an existing Multiline Plain Text column or create a new one in the current SharePoint List to store control’s data. It will automatically render control in SharePoint list view.

Read-only

Define whether a user can view or edit the contents of the control:

Formatting

The property contains a JSON object that defines how the data is displayed in the list view.

Font

Change the formatting of the control’s title using these settings:

• font size,

• font color; use the color picker or enter the Hex color code,

• font style: normal, bold, or italic.

Name

A unique identifier for the column.

Width

Allows you to set the width of the column.

The column can be still resized manually on a form:

Title

Allows to set the title of the column.

Required

Allows to set the column as mandatory for the record to be added.

Type

The property defines the input type for the column. Available input types are:

• String

• Number

• Currency

• Boolean

• Date

• Date and Time

• Dropdown

• Masked Text

Total

Define an aggregation method for the column:

Available methods are:

• Count: for all column types.

• Sum, Min, Max, Average: for number and currency column types.

Total text

Specify the text to be shown in the total row:

The {0} placeholder will be replaced with the calculated result based on the selected aggregation method.

Min/Max

Define the minimum and maximum values that a user can enter:

When a user enters a value outside the range, the value changes to the nearest valid value:

Precision

Specify the number of digits to the right of the decimal point a user can enter.

Percents

Specify whether to show the percent symbol next to the number:

Example:

Spin buttons

Specify whether to show spin buttons for the control:

Example:

Spin step

Specify the interval for adjusting the current value when using the spin buttons:

You can enter either an integer or a decimal number.

Min/Max

Specify the minimum and maximum values that a user can enter:

When a user enters a value outside the range, the value changes to the nearest valid value:

Precision

Specify the number of digits to the right of the decimal point a user can enter.

Symbol

Specify the currency symbol:

Spin buttons

Specify whether to show spin buttons for the control:

Example:

Spin step

Specify the interval for adjusting the current value when using the spin buttons:

You can enter either an integer or a decimal number.

Start/End date

Set the minimum and maximum dates a user can enter:

Interval

The property defines the minimum step by which the time can be changed in minutes:

Start/End time

Set the minimum and maximum time a user can enter:

Interval

The property defines the minimum step by which the time can be changed in minutes:

Start/End date

Set the minimum and maximum dates a user can enter:

Start/End time

Set the minimum and maximum time a user can enter:

Options

Only available for the Dropdown column type. The list of options is static and stored in the form.

Add a list of options to the Options property:

This property allows to customize each option’s color and specify the default option:

Enable Allow user value to allow users to enter a custom value that is not included in the list of options.

Multiple

Defines whether a user is allowed to select a single option or multiple options from the drop-down list:

Single Selection allows a user to select only one option from the drop-down list:

Multiple Selection allows a user to select multiple options from a drop-down list:

Mask

Specify the input mask:

The following mask rules are supported:

• 0 - Digit. Accepts any digit between 0 and 9.

• 9 - Digit or space. Accepts any digit between 0 and 9, plus space.

• # - Digit or space. Like 9 rule, but allows also (+) and (-) signs.

• L - Letter. Restricts input to letters a-z and A-Z. This rule is equivalent to [a-zA-Z] in regular expressions.

• ? - Letter or space. Restricts input to letters a-z and A-Z. This rule is equivalent to [a-zA-Z] in regular expressions.

• & - Character. Accepts any character. The rule is equivalent to S in regular expressions.

• C - Character or space. Accepts any character. The rule is equivalent to . in regular expressions.

• A - Alphanumeric. Accepts letters and digits only.

• a - Alphanumeric or space. Accepts letters, digits and space only.

• . - Decimal placeholder. The decimal separator will be gotten from the current culture.

• , - Thousands placeholder. The display character will be gotten from the current culture.

• $ - Currency symbol. The display character will be gotten from the current culture.

For more information and examples, check out Kendo MaskedTextBox rules.

Formula

The property contains a formula for dynamic calculations:

The formula editor works in two modes:

• Formula—calculation based on expression including other columns within the row.

• JavaScript—calculation base on JavaScript code where the contexts of the current row and the entire form are available.

// get value of the field on the form
fd.field('Field1').value

// get value of the Column1
data.Column1

if (fd.field('Field1').value === 'Option 1') {
    return data.Column1 * data.Column2
} else {
    return data.Column1
}

Result

After the calculation, the value is converted to the specified type. Available types are:

• String

• Number

• Currency

• Boolean

• Date

• Date and Time

Precision

The property is available for the Number and Currency result type.

Specify the desired number of decimal places for rounding:

Percents

The property is available for the Number result type.

Specify whether to show the percent symbol next to the number:

Symbol

The property is available for the Currency result type.

Specify the currency symbol:

Get or set control value

Get or set the Data Table control value. The control value is stored as an array of JavaScript objects, with properties for each column.

fd.control('Control1').value; // returns an array

// set value with array of objects:
let records = [
    { Date: new Date(), Text: 'Item A', Cost: 100 },
    { Date: new Date(), Text: 'Item B', Cost: 200 }
];
fd.control('Control1').value = records;

// add new record to the Data Table using columns' InternalNames:
let record = { Date: new Date(), Text: 'Item C', Cost: 300 };
fd.control('Control1').value.push(record);

Get aggregation results

Get the aggregation results. Returns an object containing total calculations for all columns where the total method is specified.

// get aggregates for all columns as an object
fd.control('Control1').aggregates;

// get the sum for Column1
fd.control('Control1').aggregates.Column1.sum;

Get HTML element

Access HTML element inside the control in order to modify it, hide it, or do something else.

//access control's HTML
const htmlControl = fd.control('Control1').$el;

Hide control

Hide a control from a user. The control value can still be accessed and changed with JavaScript.

// hide control
fd.control('Control1').hidden = true;

// show control
fd.control('Control1').hidden = false;

Handle control events

Use the $on() function of the DataTable control to execute code in response to the control events. The available events are:

• Before edit

• Edit

• Change

• Remove

• Sort

• Save

const dt = fd.control('Control1');

dt.$on('beforeEdit', e => {
    if (!e.model.isNew()) {
        e.preventDefault(); // prevent modifying existing rows, only allowing for adding new ones
    }
});

const dt = fd.control('Control1');

dt.$on('edit', e => {
    const container = e.container;
    if (dt._editMode === 'incell') container.css('background-color', '#90EE90'); // highlight the edited cell
});

const dt = fd.control('Control1');

dt.$on('change', e => {
    if (e.length > 10) {
        dt.disabled = true; // disable the control if there are more than 10 rows in the table
    }
});

const dt = fd.control('Control1');

let removedItems = [];

dt.$on('remove', e => {
    removedItems.push(e.model); // add all removed items to an array
});

const dt = fd.control('Control1');

dt.$on('sort', e => {
    if (e.sort.field === 'Name') {
        alert('Sorting by name is disabled');
        e.preventDefault(); // prevent sorting by a certain field
    }
});

dt.$on('save', e => {
    if (!e.model.id && e.values.Name === 'New') e.preventDefault(); // prevent changing the status of an existing item to 'New'
});

Configure widget

You can access the widget used by the control. The widget is based on Kendo UI Grid.

// get the widget
fd.control('Control1').widget;

// change the widget's configuration
fd.control('Control1').widgetOptions = {
    sortable: false
};

widgetOptions is the same as widget.setOptions({}) but can be defined before widget initialization.

Filter control values

It is possible to filter displayed values dynamically with the following code:

fd.control('Control1').widget.dataSource.filter({
    field: 'Column1',
    operator: 'eq',
    value: 'Test'
});

The supported operators are:

• eq (equal to)

• neq (not equal to)

• isnull (is equal to null)

• isnotnull (is not equal to null)

• lt (less than)

• lte (less than or equal to)

• gt (greater than)

• gte (greater than or equal to)

• startswith

• doesnotstartwith

• endswith

• doesnotendwith

• contains

• doesnotcontain

• isempty

• isnotempty

The last eight are supported only for string fields.

Based on Kendo’s dataSource filter property, check it out for more options.

Make control required

Make a control required or optional:

// make control required
fd.control('Control1').required = true;

// make control not required
fd.control('Control1').required = false;

Data Table validation

You can add a validation for a Data Table to verify the control’s data.

For instance, to make sure that a user has not added more than 10 records to the Data Table:

// make at least one record required
fd.control('Control1').addValidator({
    name: 'Control1 validator',
    error: 'No more than 10 records are allowed',
    validate: value => {
        // change this number to allow more/less records
        if (value.length > 10) {
            return false;
        }

        return true;
    }
});

Column validation

You can add a validation for a Data Table column to verify the input data.

For instance, to limit the number of characters a user can enter:

fd.control('Control1').addColumnValidator('Column1', {
    error: 'No more than 20 characters are allowed',
    validate: value => {
        return (value.length <= 20)
    }
})

Populate Data Table with static data

To populate Data Table control with rows of data, use the following code:

// select the Data Table control to populate
const dt = fd.control('Control1');

// specify information for rows using columns' Name property
let dtRows = [{
        Product: 'Forms for SharePoint Online',
        Price: 599,
        Subscription: true,
        Date: new Date()
    },
    {
        Product: 'Charts for Office365',
        Price: 399,
        Subscription: false,
        Date: new Date()
    }
];

// assign rows to Data Table
dt.value = dtRows;

Populate Data Table from external data source

You can populate the data table from a file that is publicly available:

async function externalFile() {
    const data = $.get('https://plumsail.com/assets/forms/data/source.json')
    return data;
}
externalFile().then(data => {
    fd.control('Control1').value = JSON.parse(data)
})

The same way, you can populate the control from any external data source: a web service or a file.

Populate column value for new row

To populate column values for each new row in a Data Table control, use the following code:

// select the Data Table control to automatically populate new rows
const dt = fd.control('Control1');

dt.widget.bind('beforeEdit', e => {
      let model = e.model;
      if (model.isNew()) {
          model.set('Name', 'John Smith');
          model.set('Email', 'jsmith@mycompany.com');
      }
});

Populate Dropdown column options

To populate dropdown column of a Data Table control dynamically, use the code:

const dt = fd.control('Control1');

const populateDropDown = widget => widget.setDataSource({
    data: ['Apple', 'Banana', 'Pear']
});

dt.$on('edit', e => {
    const editMode = e.sender.getOptions().editable.mode;
    // populate drop-down in a pop-up window for mobile devices
    if (editMode === 'popup') {
        const dropDown = e.container.find('input[name=Column1]').data('kendoDropDownList');
        populateDropDown(dropDown);
        // populate drop-down for desktop/tablet
    } else {
        if (e.column.field === 'Column1') {
            populateDropDown(e.widget);
        }
    }
});

Set fields based on other fields

Sometimes, you might want to set fields automatically, without direct user input:

For example, the product prices are not something a user should be able to change. Instead, they can be set dynamically, depending on the selected product.

We’ll use a simple JS Object to store product prices, and automatically set unit price on product selection. Here is the code:

// we store prices in JS object
let merch = {};
// use bracket notation to accurately copy dropdown values
merch['Baseball cap'] = 9.99;
merch['T-shirt'] = 19.99;
merch['Key chain'] = 4.99;

const dt = fd.control('Control1');

// get a column by its name
const unitPriceColumn = dt.columns.find(c => c.field === 'UnitPrice');
// make column read-only
unitPriceColumn.editable = () => false;

dt.$on('change', value => {
    if (value) {
        for (let i = 0; i < value.length; i++) {
            // populate UnitPrice column
            value[i].set('UnitPrice', merch[value[i].Product] || 0);
        }
    }
});

Calculate total for an entire table

Calculate total for the Amount and Line Total columns:

// make TotalPrice fields read-only
fd.field('TotalPrice').disabled = true;

const dt = fd.control('Control1');

dt.$on('change', value => {
    // set the TotalPrice fields to the calculated sum
    fd.field('TotalPrice').value = dt.aggregates.LineTotal.sum;
});

Add a button to duplicate row

You can add a button to Data Table rows, which will allow you to duplicate them, like this:

Use the following code:

// select the Data Table control to add new column to
const dt = fd.control('Control1');
let columns = dt.widget.options.columns;
let customRowDataItem = null;
let isCustomAdd = false;

// specify what the column will be like
columns.push({
    command: {
        text: 'Copy row',
        iconClass: 'k-icon k-i-copy',
        click: e => {
            e.preventDefault();
            customRowDataItem = this.dataItem($(e.currentTarget).closest('tr'));
            isCustomAdd = true;
            this.addRow();
        }
    }
});
dt.widget.setOptions({
    columns: columns
});
dt.widget.bind('edit', e => {
    if (isCustomAdd && e.model.isNew()) {
        isCustomAdd = false;
        for (let i = 0; i < columns.length; i++) {
            let field = columns[i].field;
            if (field) {
                e.model.set(field, customRowDataItem[field]);
            }
        }
        e.sender.closeCell(e.container);
    }
});

Add custom button to toolbar

Add a custom button to the toolbar of the Data Table control by changing the Kendo UI Grid toolbar configuration:

const dt = fd.control('Control1');
dt.widget.setOptions({
    toolbar: ['create', {
        name: 'custom-button',
        text: 'Button Name'
    }]
});
$('.k-grid-custom-button').click(e => {
    alert('Button clicked!');
});