Edit DataTable records bound to Query API

Typically when the Data Table widget is bound to a query API, the Edit and Update operations are not supported by default. However, you can still achieve this functionality by customizing the Edit and Update properties using the following approach.

note

In this example, we are using an Employee table from the HRDB sample database.

Create a Query

Go to the database design and create a query to get the employee details.

Create a Database API Variable

To create a Database API variable, go to Variables, select New Variable and click Database APIs.
Select the HRDB database from the dropdown and choose the API Type as Query APIs. Select Query the executed-query from the dropdown. Name the variable and click Done.

Create a Data Table

Drag and drop the Data Table widget and bind the Database API variable created above.

Configure Actions

Go to the Data Table's Advanced Settings, navigate to the Actions tab, and check the Edit action.

Create a Database CRUD Variable

Create another variable for the Employee table to update the records. To create a Database CRUD variable, go to Variables, select New Variable and click Database CRUD.
Select the HRDB database from the dropdown and choose the Employee table (in this case). And, set the operation as Update from the dropdown. Name the variable and click Done.

update record database crud

Configuring Events

Ensure to click the Data Table and navigate to the Events tab from the Properties panel. Go to the On Record Update event, and select Javascript from the dropdown.

Go to the Script tab of the page, and write the following script to update the record in the On Record Update method.

note

The following example code will update the record.

Page.executeGetempdataTable1Rowupdate = function($event, widget, row) {
    // row data is the newly edited data
    // UpdateEmployeeData is the update variable of the employee table
    var lv = Page.Variables.UpdateEmployeeData;
    lv.updateRecord({
        row: {
            "empId": row.empId,
            "firstname": row.firstname,
            "lastname": row.lastname,
            "username": row.username
        }
    }, function(data) {
        // to remove the edit mode of the datatable widget after the edit action is performed. 
        widget.hideEditRow();
    });
};

This way, you can customize the update action of the Data Table bound to the query variable.