icon DataTable

The DataTable is a control which allows you to add dynamic table to your forms.

DataTable control

You can set up how many columns the table has and their type, and the users will be able to add entries to this table.

Most configuration for DataTable can be done by editing individual column settings. To add a new column, simply click on the plus symbol:

Add column

DataTable Properties

Here you can find properties specifically related to the DataTable control.

Name

A unique identifier for the control.

Name property

Important

Do not change the control’s name after the form has been in use, or you might lose saved data or break your automation.

JavaScript

The Name property allows to work with the control via JavaScript code, like this:

fd.rendered(function(){
   //can access the control using its Name,
   fd.control('Control1').value = [{Product: "White T-shirt", Amount: 3, ExpectedDate: new Date(), Delivered: false}];
});

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.

Width property

New Line

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

Width and Grid Width property

Delete

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

Delete property

DataTable Column Properties

Here you can find properties specifically related to the DataTable columns.

Name

A unique identifier for the column.

Column Name property

Important

Do not change a column’s name after the form has been in use, or you might lose saved data or break your automation.

Width

Allows you to set the width of the column.

Column Width property

The column can still be dragged by the user on the form to manually resize it:

Column Width drag

Required

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

Column Required property

Title

Allows to set the title of the column.

Column Title property

Type

Allows to select the type of the data for the column — can be either String, Number, Boolean, Date or Dropdown.

Column Type property

Number Column Properties

Here you can find properties specifically related to the Number type columns.

Min/Max

Only available for the Number column type.

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

Column Min/Max properties

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

Column Range limit

Decimals

Only available for the Number column type.

Specify the number of decimal places to which the number will be rounded. The default property value is 0.

Column Decimals properties

Format

Only available for the Number column type.

Specify the number format that is applied when a user leaves the control. The default property value is n0.

Column Format properties

The number format is made up of format specifier and the number of decimals places. E.g. n3, c2.

Avaliable format specifiers:

  • ‘n’ — Renders a number.

  • ‘c’ — Renders a currency value.

  • ‘p’ — Renders a percentage (number is multiplied by 100).

  • ‘e’ — Renders exponential values.

Step

Only available for the Number column type.

Specify the interval for adjusting the current value when using up and down arrows:

Column Step properties

You can enter either an integer or a decimal number.

JavaScript

In this section, you can find basic examples of how to work with the control using JavaScript.

If you are not familiar with the JavaScript framework, get started with the JavaScript basics.

Note

The control is only accessible once the form is rendered, so all calls to the control must be inside fd.rendered event:

fd.rendered(function(){
    //hide the control
    $(fd.control('Control1').$el).hide();
    //show the control
    $(fd.control('Control1').$el).show();
});

Get or set control value

Get or set the DataTable 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 DataTable using columns' InternalNames:
var record = {Date: new Date(), Text: "Item C", Cost: 300 };
fd.control('Control1').value.push(record);

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;

Cоnfigure 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.

Make DataTable record required

Sometimes, you just need to ensure that at least one record is added to the DataTable.

Make DataTable record required

This can be achieved with a simple validator:

//make at least one record required
fd.rendered(function(){
    fd.control('Control1').addValidator({
        name: 'Control1 validator',
        error: 'Enter at least one record into the table',
        validate: function(value) {
            //change this number to make more records required
            if (value.length < 1) {
                return false;
            }

            return true;
        }
    });
});

Note

You can adjust the number in the code to make more records required or add other conditions for a more complex validation.

Prepopulating DataTable with rows

To prepopulate DataTable control with rows of data, use the following code:

fd.rendered(function() {
    //select the DataTable control to prepopulate
    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 DataTable
    dt.value = dtRows;
});

Prepopulating column value for new row

To prepopulate column values for each new row in a DataTable control, use the following code:

fd.rendered(function() {
  //select the DataTable control to automatically prepopulate 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', '[email protected]');
     }
  });
});

Populating Dropdown column options

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

fd.rendered(function() {
    fd.control('Control1').$on('edit', function(e) {
        console.log(e)
        if (e.column.field === "Column1") {
            //pass widget + current column value
            console.log(e.model);
            populateColumn(e.widget, e.model.Column1);
        }
    })

});

function populateColumn(widget, value) {

    widget.setDataSource({
        data: ['Category A', 'Category B', 'Category C']
    });

    //set value if one was select
    widget.value(value);
}

Set fields based on other fields

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

Set fields based on other fields

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;

fd.rendered(function(){
    //set unit price column (2nd column) to be non-editable, as we set it with code
    fd.control('Control1').columns[1].editable = function(){return false};
    fd.control('Control1').$on('change', function(value) {
        var modifiedValue = null;
        if(value) {
            for (var i = 0; i < value.length; i++) {
                if (value[i].UnitPrice !== merch[value[i].Product]) {
                    if (!modifiedValue) {
                        modifiedValue = Object.assign({}, value);
                    }
                    modifiedValue[i].UnitPrice = merch[value[i].Product];
                }
            }
        }
        if (modifiedValue) {
            fd.control('Control1').value = value;
            fd.control('Control1').widget.refresh();
        }
    });
});

Calculate total for a row and the whole table

Finally, we’re going to calculate total for a row, and for the whole DataTable.

Calculate total for the DataTable

Here is the code:

fd.rendered(function() {
    //Disable OrderTotal field
    fd.field('OrderTotal').disabled = true;
    //Make LineTotal column noneditable
    fd.control('Control1').columns[3].editable = function(){return false};
    fd.control('Control1').$on('change', function(value) {
        //variable to count Order Total
        var orderTotal = 0.0;
        //if there are records in the table
        var modifiedValue = null;
        if(value){
            //go through each one by one
            for (var i = 0; i < value.length; i++){
                //if this record has Amount and UnitPrice
                if(value[i].Amount && value[i].UnitPrice){
                    //set LineTotal to their product
                    var cost = value[i].Amount * value[i].UnitPrice;
                    if (value[i].LineTotal !== cost) {
                        if (!modifiedValue) {
                            modifiedValue = Object.assign({}, value);
                        }
                        modifiedValue[i].LineTotal = cost;
                    }
                }
                //add Total to the Order Total
                orderTotal += parseFloat(value[i].LineTotal);
                console.log(orderTotal);
            }
        }
        //here we refresh the table
        if (modifiedValue) {
            fd.control('Control1').value = value;
            fd.control('Control1').widget.refresh();
        }
        //we set Order Total field to sum of Totals
        fd.field('OrderTotal').value = orderTotal;
    });
});

If you’re getting an incorrect value in one of your fields, for example, in OrderTotal, make sure that the format is correctly configured and an appropriate number of decimals is selected:

Configure format for your fields

Add a button to duplicate row

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

Button to duplicate row

Use the following code:

fd.rendered(function() {
    //select the DataTable 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);
        }
    });
});