The Data Table is a control which allows you to add dynamic table to your forms.
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 Data Table can be done by editing individual column settings. To add a new column, simply click on the plus symbol:
This page contains a detailed description of the control properties and JavaScript samples, which you can use with this control.
Here you can find properties available for the Data Table control.
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:
JavaScript
This code will allow you to get or set the control’s title dynamically:
//returns the control's title as a string
fd.control('Control1').title
//sets the control's title
fd.control('Control1').title = 'Last Name'
A unique identifier for the control.
The Name property is used in JavaScript to select a specific control.
JavaScript
The Name property allows to work with the control via JavaScript code, like this:
fd.spRendered(function(){
//can access the control using its Name:
fd.control('Control1');
});
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:
JavaScript
This code allows you to get and set control’s required status
//returns the control's required status as true/false
fd.control('Control1').required;
//sets the control's required status
fd.control('Control1').required = true;
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.
Allows to select where the new line will be added — at the Top or at the Bottom of the table.
Allows to select where the delete button will appear — in the first or in the last column.
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.
Define whether a user can view or edit the contents of the control:
The property contains a JSON object that defines how the data is displayed in the list view.
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.
Here you can find properties specifically related to the Data Table columns.
A unique identifier for the column.
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.
Allows to set the title of the column.
Allows to set the column as mandatory for the record to be added.
The property defines the input type for the column. Available input types are:
String
Number
Currency
Boolean
Date
Date and Time
Dropdown
Masked Text
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:
Here you can find properties specifically related to the Number type columns.
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:
Specify the number of digits to the right of the decimal point a user can enter.
Specify the interval for adjusting the current value when using the spin buttons:
You can enter either an integer or a decimal number.
Here you can find properties specifically related to the Currency type columns.
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:
Specify the number of digits to the right of the decimal point a user can enter.
Specify the currency symbol:
Specify the interval for adjusting the current value when using the spin buttons:
You can enter either an integer or a decimal number.
Here you can find properties specifically related to the Dropdown type columns.
Only available for the Dropdown column type.
Add a list of options to the Items property. Each option should be on a new line.
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:
Note
In single selection mode the value is stored as a string.
In multiple selection mode the value is stored as an array of strings.
Here you can find properties specifically related to the Masked Text type columns.
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.
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.spRendered event:
fd.spRendered(function(){
//hide the control
fd.control('Control1').hidden = true;
//show the control
fd.control('Control1').hidden = false;
});
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);
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 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;
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.
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 a control required or optional:
//make control required
fd.control('Control1').required = true;
//make control not required
fd.control('Control1').required = false;
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;
}
});
Note
You can adjust the number in the code to allow more records or add other conditions for a more complex 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)
}
})
Note
You can adjust the number in the code to make more/less characters required or add other conditions for a more complex validation.
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;
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.
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');
}
});
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);
}
}
})
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:
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 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; });
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 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!');
});