Friday 11th September, 2015

Multi-row bulk editing

Editor 1.5 has now been available for almost a month and although the new features are documented in the release notes and shown on the examples pages, I'd like to use a couple of blog posts to explore some of these new features in depth with code examples. This will be of interest to those of you are upgrading from a previous version and also if you are just interested in checking out Editor's feature set.

There are three major new features in Editor 1.5:

Multi-row (bulk) editing
Server-side events
Data submission controls

Multi-row (bulk) editing

Editing a single row of data at a time is perfect for many use cases, and Editor is designed to make this as simple as possible, but it can also be a great time saver to be able to update values in multiple rows at the same time - for example marking items in a todo list as Done or recategorising items in a database.

Editor 1.5 supports multi-row editing out of the box - no additional options need be specified. Furthermore, the interface that your end user sees is almost identical to the single row interface, so they'll immediately feel at home. All the user needs to do is select multiple rows in the table which they can do with cmd/ctrl and shift clicking the table. All of the examples on the Editor site support multi-row editing if you want to try it out.

Interface

An important point when considering multi-row editing is to allow some fields to retain their individual values while allowing other fields to be set to a common value across rows. Consider for example a parts database where you wish to add a label to multiple items - the part number and description should not change, but the label field should.

When the user selects multiple rows to be edited, Editor will modified its display slightly for fields which have different values; it will show a "multiple value" message and invite the user to click the message if they wish to edit that field. If they do not, the individual field values are retained. If they do, a common value is set for all rows being edited. The user also has the option of restoring the individual values if they clicked the field by mistake.

API

Editor's default interface allows rapid bulk editing of items in a table, but where it gets really interesting when you use the API for multi-row editing. While the UI allows the original values to be retained or a common value set, the API also provides the ability to set individual values for each field in each row.

This API introduces the ability to present helpful bulk editing tools such as auto filling data and row reordering to the end user, all without just a couple of lines of code on your part.

You may also wish to develop your own bulk editing tools using the API. The following methods are presented by Editor for this type of interaction:

multiGet() - Get the field values for one or more fields
multiSet() - Set the field values for one or more fields
field().multiGet() - Get the field value for one or more rows
field().multiSet() - Set the field value for one or more rows
field().isMultiValue() - Determine if the field has individual values for the rows being edited

To illustrate, consider a table of customers which will display financial information about each customer, including a currency exchange rate. An Editor instance for the table has a field called exchange-rate that we wish to update with a new value.

To edit the selected rows setting a common value we can use the set() method on the field:

var rate = 1.41459; // Could come from an API, or user input

editor
    .edit( table.rows( { selected: true } ).indexes() )
    .set( 'exchange-rate', rate )
    .submit();

Note the use of rows( { selected: true } ) to get the selected rows. This is a feature of the Select extension.

Now consider that we might wish to add a commission to the exchange rate for the customers, where the commission is defined by another field in the table, commission. The commission value is now unique to each customer and we need to loop over each row adding the commission to the new exchange rate:

var rate = 1.41459; // Could come from an API, or user input
var rows = table.rows( { selected: true } ).indexes();

editor.edit( rows );

$.each( rows, function (i, rowIdx) {
    var row = table.row( rowIdx );

    editor.field( 'exchange-rate' ).multiSet( row.id(), rate + row.data().commission );
} );

editor.submit();

In this case we use the field().multiSet() to set the individual values (one for each row that was selected, obtained using row().id()). The row's data is retrieved for the calculation using row().data().

The bulk edit is then submitted to the server in a single Ajax call using submit().

The data formats used in multi-row editing are detailed in the Editor manual.

Upgrading

There are two important points that we need to take into account if you are updating from previous versions of Editor to make use of the new multi-row editing features:

The new Buttons and Select extensions
Updates to the data interchange format

Buttons and Select

You may have noticed that the code example above, and all of the Editor examples, use two extensions for DataTables called Buttons and Select. These extensions were introduced at the same time as Editor 1.5 and provide a common library for button display in a DataTables and row, column and cell selection options that are fully integrated with the DataTables API, respectively.

Buttons and Select replace the functionality of the old TableTools extension which has now been retired. TableTools will still operate correctly with Editor 1.5, but it does not support multi-row editing.

In order to use multi-row editing with Editor and you currently use TableTools you must update the software to use Buttons and Select instead. This is a simple process and involves including the Buttons and Select software in your app (see the download builder) and updating your initialisation.

For example if, with TableTools, you used the following in your table initialisation to create the three editing buttons and enable row selection:

tableTools: {
    sRowSelect: "os",
    sRowSelector: 'td:first-child',
    aButtons: [
        { sExtends: "editor_create", editor: editor },
        { sExtends: "editor_edit",   editor: editor },
        { sExtends: "editor_remove", editor: editor }
    ]
}

You would now achieve the same using:

select: true,
buttons: [
    { extend: "create", editor: editor },
    { extend: "edit",   editor: editor },
    { extend: "remove", editor: editor }
]

This is the basic initialisation of Buttons and Select, there are of course a wide range of other options such as buttons for data export (CSV, Excel and PDF) and column visibility, plus also row selection behaviour controls. See the Buttons and Select documentation for details.

Data interchange format change

Introducing multi-row editing into Editor has required that the client / server data interchange format that Editor uses be updated.

If you are using the provided PHP or .NET libraries for Editor, you should not need to make any changes unless you are intercepting the data directly. However, if you access the data directly or have written your own server-side libraries for Editor the code must be updated for this, or multi-row editing can be disabled and the legacy protocol used (legacyAjax). An upgrade document document is available if this effects you.

This completes the introduction of Editor's multi-row editing abilities. Hopefully your users will find a big benefit in this new ability! If you have any feedback or questions about this feature, please feel free to ask in the DataTables forums.