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.rendered(function(){
    //can access the control using its Name:
    fd.control('Control1');
 });

For Ink Sketch, Data Table, and Likert Scale controls, the Name property is used to store data from submissions, and in Power Automate or Zapier automations.

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.

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

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.

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:

The Symbol property overwrites the form regional formatting.

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:

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 the drop-down list:

Source

Define the source from which the field fetches the list of options:

• Static list—the list of options is static and stored in the form.

• Excel—the list of options is populated from an Excel file stored in OneDrive

Static list

Here you can find properties specifically related to the Static list data source

Excel

The list of options is populated from an Excel file stored in OneDrive. The Excel file holds static information or can be dynamically updated with data from a SharePoint list, a SQL database, Dynamics 365, or any other source using Power Automate (MS Flow).

To retrieve options from an Excel file:

1. Log in to your Microsoft account to connect to OneDrive:

   

2. Select the path to the Excel file in the File property:

   

   Other properties are not available until the file is selected.

3. If there are multiple sheets in the Excel file, you can select which sheet to use:

   

   The first sheet is selected by default.

4. If the data is formatted as a table, select the table name in the Table property:

   

   If there are no tables, leave the property empty.

5. The display text and the actual value of the field may be different.

   In the Text column property, select the column containing the values you want to display in the dropdown:

   

   In the Value column property, select the column whose values will be saved when the form is submitted:

   

   These values will be used in Power Automate (MS Flow) or Zapier.

6. In the Caching property, specify how long the data will be stored in the cache:

   

   If the page with the form is refreshed within this time, new values will not be loaded.

   By default, the data is stored for 600 seconds (10 minutes).

7. In the Extra columns property, specify what other columns you want to have retrieved from Excel. Click More options to see the property:

   

   Select columns you want to be retrieved from Excel:

   

   These columns won’t be visible on the form, but can be retrieved with JavaScript.

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.

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:
var 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:
var 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
var htmlControl = fd.control('Control1').$el;

Hide/show 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

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: function(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: function(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
var dt = fd.control('Control1');

//specify information for rows using columns' Name property
var 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(function(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
var dt = fd.control('Control1');

dt.widget.bind('beforeEdit', function(e) {
    var 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:

var 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);
        }
    }
})

Retrieve extra columns from the Excel data source

Use the following code to retrieve Extra column values from Excel for the selected option in the dropdown column:

var dt = fd.control('Control1');
//get column values of the first row
var row1 = dt.value[0]
//get extra columns value in single selection mode
row1.__ColumnName.props.ExtraColumnName
//get extra columns value in multiple selection mode
row1.__ColumnName[0].props.ExtraColumnName

To populate other columns with extra columns value, use this code:

var dt = fd.control('Control1');
dt.$on('change', value => {
    if (value) {
        for (var i = 0; i < value.length; i++) {
            value[i].set('Column1', value[i].__ColumnName.props.ExtraColumnName || 0);
        }
    }
});

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
var merch = {};
//use bracket notation to accurately copy dropdown values
merch['Baseball cap'] = 9.99;
merch['T-shirt'] = 19.99;
merch['Key chain'] = 4.99;

var 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', function(value) {
    if (value) {
        for (var i = 0; i < value.length; i++) {
            // populate UnitPrice column
            value[i].set('UnitPrice', merch[value[i].Product] || 0);
        }
    }
});

Calculate total for a row

Calculate total for a row:

Here is the code:

var merch = {};
merch['Baseball cap'] = 9.99;
merch['T-shirt'] = 19.99;
merch['Key chain'] = 4.99;

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

// make LineTotal column (4th column) read-only
dt.columns[3].editable = function() {
    return false
};

dt.$on('change', function(value) {
    if (value) {
        // go through each row one by one
        for (var i = 0; i < value.length; i++) {
            // set the default values
            if (!value[i].Product) {
                value[i].set('Product', 'Baseball cap');
            }
            if (!value[i].Quantity) {
                value[i].set('Quantity',  1);
            }

            // populate UnitPrice column
            value[i].set('UnitPrice', merch[value[i].Product] || 0);

            // calculate total for the row
            value[i].set('LineTotal', value[i].Quantity * value[i].UnitPrice || 0);

        }
    }
});

If you’re getting an incorrect value in one of your fields, for example, in OrderTotal, make sure that the an appropriate precision is selected:

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;

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

dt.$on('change', function(value) {
    //variable to count total
    var priceTotal = 0.0;
    //if there are records in the table
    if (value) {
        //go through each row one by one
        for (var i = 0; i < value.length; i++) {
            //calculate total for columns
            if (value[i].LineTotal) {
                priceTotal += parseFloat(value[i].LineTotal);
            }
        }
    }
    //set the TotalPrice fields to the calculated values
    fd.field('TotalPrice').value = priceTotal;
});

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
var dt = fd.control('Control1');
var columns = dt.widget.options.columns;
var customRowDataItem = null;
var isCustomAdd = false;

//specify what the column will be like
columns.push({
    command: {
        text: 'Copy row',
        iconClass: 'k-icon k-i-copy',
        click: function(e) {
            e.preventDefault();
            customRowDataItem = this.dataItem($(e.currentTarget).closest('tr'));
            isCustomAdd = true;
            this.addRow();
        }
    }
});
dt.widget.setOptions({
    columns: columns
});
dt.widget.bind('edit', function(e) {
    if (isCustomAdd && e.model.isNew()) {
        isCustomAdd = false;
        for (var i = 0; i < columns.length; i++) {
            var 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:

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