DataTable¶

Name¶

A unique identifier for the control.

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

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.

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.

Save to¶

Select Multiline Plain Text field in the current SharePoint List to save Data Table data to. It will automatically render control in List View.

Alternatively create a new hidden field in editor. You can delete hidden fields by selecting “🖉 Manage” option in the dropdown.

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. You can change the order of the columns, remove columns from the Lis View, or change their titles.

Name¶

A unique identifier for the column.

Title¶

Allows to set the title of the column.

Required¶

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

Type¶

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

Width¶

Allows you to set the width of the column.

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

Min/Max¶

Only available for the Number column type.

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:

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.

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.

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:

You can enter either an integer or a decimal number.

Items¶

Only available for the Dropdown column type.

Add a list of options to the Items property. Each option should be on a new line.

Selection¶

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:

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;

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;

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.

This can be achieved with a simple validator:

//make at least one record required
fd.spRendered(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;
        }
    });
});

Populate DataTable with static data¶

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

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

Populate DataTable 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;
}
fd.spRendered(function() {
    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 DataTable control, use the following code:

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

Populate Dropdown column options¶

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

fd.spRendered(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:

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.spRendered(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¶

Calculate total for a row:

Here is the code:

fd.spRendered(function() {
    //Make LineTotal column noneditable
    fd.control('Control1').columns[3].editable = function() {
        return false
    };
    fd.control('Control1').$on('change', function(value) {

        //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;
                    }
                }
            }
        }
        //here we refresh the table
        if (modifiedValue) {
            fd.control('Control1').value = value;
            fd.control('Control1').widget.refresh();
        }
    });
});

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:

Calculate total for an entire table¶

Calculate total for the Amount and Line Total columns:

fd.spRendered(function() {
    //Disable TotalAmount and TotalPrice fields
    fd.field('TotalAmount').disabled = true;
    fd.field('TotalPrice').disabled = true;

    fd.control('Control1').$on('change', function(value) {
        //variable to count total
        var priceTotal = 0.0;
        var amountTotal = 0;
        //if there are records in the table
        if (value) {
            //go through each one by one
            for (var i = 0; i < value.length; i++) {
                //calculate total for columns
                priceTotal += parseFloat(value[i].LineTotal);
                amountTotal += value[i].Amount;

            }
        }
        //set the TotalAmount and TotalPrice fields to the calculated values
        fd.field('TotalPrice').value = priceTotal;
        fd.field('TotalAmount').value = amountTotal;
    });
});

Add a button to duplicate row¶

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

Use the following code:

fd.spRendered(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);
        }
    });
});