Skip to main content
Version: v10.15

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​

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

Create a Database API Variable​

  1. To create a Database API variable, go to Variables, select New Variable and click Database APIs.

  2. 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.

Query-variable-creation

Create a Data Table​

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

Configure Actions​

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

check edit action in datatable

Create a Database CRUD Variable​

  1. 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.

  2. 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​

  1. 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.

onrecordupdate event

  1. 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.