The Data Grid widget allows users to edit values in the grid and send OData requests when the edits are saved. This functionality allows users to update existing values or create new entries in the data grid that are saved to the database using an HTTP PUT or POST request, respectively. OData requests are configured using JavaScript Object Notation (JSON) in the Advanced tab of the Widget Editor. When properly configured, users can select a row in the data grid and edit the values, and then the OData requests will execute when the user saves the edit.
This tutorial provides examples on how to configure the Data Grid Widget to send OData requests upon saving an edited row. See the OData in the Data Grid Widget article for additional documentation details.
Note: To implement this tutorial, a REST API License must be applied to the Enterprise site and a user needs to:: •Be a member of the “EQuIS Enterprise – REST API” license role. •Be a member of a properly configured EQuIS ALS (application-level security) role. •Understand the REST API OData endpoints. |
Overview
The purpose of this tutorial is to provide step-by-step instructions of example applications of using OData in the Data Grid widget. The tutorial uses an Analytical Results II report, configured with three additional fields: DT_SAMPLE.CUSTOM_FIELD_1, DT_TEST.CUSTOM_FIELD_2, and DT_RESULT.CUSTOM_FIELD_3.
|
Tutorial Objectives |
•Configure Operation Object – Single Request
•Configure Operation Object – Multiple Requests
•Configure Operation Object – Error Handling
Each of the tutorial objectives builds on the understanding and tasks of the previous tutorial objective and should be performed in order, starting with the Setup section. Also see the full example in the final section.
Setup
1.Add a Data Grid widget to an Enterprise dashboard.
2.Open the Widget Editor by selecting the More Options 
icon in the upper right corner of the widget to display the drop-down menu and then select the Edit menu option.
3.Select the Report tab.
4.Click the Choose a Report button and select the Analytical Results II report, configured as described in the Overview section.
5.The widget will run the report and display the output columns for the report in a grid table. Use the table columns to configure the grid displayed by the widget as desired (see Data Grid Widget article for display options). Ensure that the CUSTOM_FIELD_1, CUSTOM_FIELD_2, and CUSTOM_FIELD_3 report output fields are checked to display in the data grid. In addition, any fields (aka columns) targeted in an OData request must be included in the data grid for the request to succeed. The tutorial examples below require including SAMPLE_ID, TEST_ID, FACILITY_ID, and CAS_RN. To hide these columns from display, set the Width to 0.
Note: Any fields (aka columns) that are being targeted in an OData request must be marked as a visible column in the data grid for the request to not throw an error. |
Objective: Add rowEdit Property
To enable editing in the Data Grid widget, the rowEdit property must be added to the JSON editor on the Advanced tab. This rowEdit object consists of two properties: a fields array and the operation object. The fields array is an array of objects informing the Data Grid which fields are editable. The operation object is the OData operation(s) that will be executed when the edits are saved in the Data Grid.
1.Select the Advanced tab.
2.Add the rowEdit object in the main JSON object between the defaultSorting and fields properties.
"rowEdit": {
"fields": [],
"operation": {}
},
After inserting the rowEdit object, text in the JSON editor will look like this:
….
"defaultSorting": "",
"rowEdit": {
"fields": [],
"operation": {}
},
"fields": { }
….
3.Click the Save 
icon. The Widget Editor screen will close after the save operation is complete.
Notice that no changes are evident in the Data Grid widget. Although the rowEdit object was added, the fields and operation properties were not set to define which fields should be editable. Thus, no new functionality was added to the widget.
Objective: Configure Fields Property
"rowEdit": {
"fields": [],
"operation": {}
},
The fields array signals which fields (aka columns) are editable in the data grid and consists of field objects. There are three "Edit Types" for editing a column: Numeric, List, and Text.
To enable editing, the fields property needs to have field objects defined that match the fields in the report output. In this tutorial, field properties will be defined for the CUSTOM_FIELD_1, CUSTOM_FIELD_2, and CUSTOM_FIELD_3 report output fields. Each custom field will be configured to use a different edit type as follows:
CUSTOM_FIELD_1 – This field is from the DT_SAMPLE table that was added when configuring the report. The edit type that will be assigned to this field is Numeric to inform the grid that the value will be a number. This type of edit also requires the property of decimalPlaces, which informs the grid of how many decimal places to round the number when saving to the database as well as how to display the number. For this tutorial, CUSTOM_FIELD_1 is being defined to have two decimal places.
CUSTOM_FIELD_2 – This field is from the DT_TEST table that was added when configuring the report. The edit type that will be assigned to this field is List to inform the grid that the editable cell should be a drop-down with options. Fields with the List edit type need to have an additional property called listValues, which is an array of objects with two keys: “value” and “text”. The value for the “value” property is applied when the option is selected and saved to the database. The value for the “text” property is used to display the option in the drop-down list in the data grid. For this tutorial, the drop-down list will contain "Yes" and "No", which will be saved to the database as "Y" and "N", respectively.
CUSTOM_FIELD_3 – This field is from the DT_RESULT table that was added when configuring the report. The edit type that will be assigned to this field is Text, which will be a string edit and does not require any additional properties.
1.Open the Widget Editor by selecting the More Options icon in the upper right corner of the widget to display the drop-down menu and then select the Edit menu option.
2.Select the Advanced tab.
3.Add the following field objects to the fields array:
{
"field": "CUSTOM_FIELD_1",
"editType": "Numeric",
"decimalPlaces": 2
},
{
"field": "CUSTOM_FIELD_2",
"editType": "List",
"listValues": [
{
"value": "Y",
"text": "Yes"
},
{
"value": "N",
"text": "No"
}
]
},
{
"field": "CUSTOM_FIELD_3",
"editType": "Text"
}
4.Click the Save icon. The Widget Editor screen will close after the save operation is complete.
Notice that a toolbar has been added (above the data grid) to enable editing. The toolbar has three options: Edit (
), Update (
), and Cancel (
). Although the toolbar has been enabled and users can edit the three defined fields, any edited values will not persist as no OData requests have been defined in the operation object to update values in the database.
Objective: Configure Operation Object – Single Request
"rowEdit": {
"fields": [],
"operation": {}
},
The operation object is where the OData request(s) are defined. The operation object needs to be configured so that an edited value in the data grid will be saved back to the database. These OData requests can use values in the current edited row by wrapping the desired column field in %% (e.g., %FACILITY_ID% would replace the current edited row’s FACILITY_ID with the placeholder). Three properties are required for the operation object: method, uri, and body. See the OData in the Data Grid Widget article for additional information about operation objects.
For this tutorial, the operation object will send an OData request (PUT) to update CUSTOM_FIELD_1 in the DT_SAMPLE table with the value that was entered in the grid for the record being edited.
1.Open the Widget Editor by selecting the More Options icon in the upper right corner of the widget to display the drop-down menu and then select the Edit menu option.
2.Select the Advanced tab.
3.Add the following object to the operation object property:
"method": "PUT",
"uri": "api/odata/DT_SAMPLE(%SAMPLE_ID%)",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
}
4.Click the Save icon. The Widget Editor screen will close after the save operation is complete.
5.Select a row in the data grid to edit.
6.Click the Edit icon in the toolbar.
7.Update the CUSTOM_FIELD_1 field with a desired numeric value.
8.Click the Update icon in the toolbar or press the Enter key to execute the OData request. A loading indicator will appear and when it disappears, the value should be updated in the database.
9.Verify the database update by refreshing the Data Grid widget (or querying the record in Professional). If the updated value is still present in the data grid after the refresh, the value has been updated in the database.
Objective: Configure Operation Object – Multiple Requests
This tutorial objective builds on the previous "Configure Operation Object – Single Request" section.
The operation object has an optional success property is an operation object like the parent operation that allows additional OData requests to be executed if the parent operation ends successfully. This operation object has the same required properties of the parent operation (method, uri, and body) as well as the optional properties success and error. This allows users to chain multiple OData requests if they all succeed.
For this tutorial objective, additional OData requests will be executed to update values in DT_TEST.CUSTOM_FIELD_2, and DT_RESULT.CUSTOM_FIELD_3. The top-level request is a PUT request to the DT_SAMPLE table using the SAMPLE_ID from the selected row and updating CUSTOM_FIELD_1 for the given row (as performed in the previous section). If that request ends successfully, the next request will be a PUT request to the DT_TEST table using the TEST_ID from the selected row and updating CUSTOM_FIELD_2 for the given row. If that request ends successfully, the next request will be a PUT request to the DT_RESULT table using the expected keys for updating the table being populated from the grid data and updating CUSTOM_FIELD_3 for the selected row.
1.Open the Widget Editor by selecting the More Options icon in the upper right corner of the widget to display the drop-down menu and then select the Edit menu option.
2.Select the Advanced tab.
3.Add the following success object properties after the existing operation object property associated with DT_SAMPLE.CUSTOM_FIELD_1 (in yellow highlighting):
"method": "PUT",
"uri": "api/odata/DT_SAMPLE(%SAMPLE_ID%)",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
},
"success": {
"method": "PUT",
"uri": "api/odata/DT_TEST(%TEST_ID%)",
"body": {
"CUSTOM_FIELD_2": "%CUSTOM_FIELD_2%"
},
"success": {
"method": "PUT",
"uri": "api/odata/DT_RESULT(FACILITY_ID=%FACILITY_ID%,TEST_ID=%TEST_ID%,CAS_RN='%CAS_RN%')",
"body": {
"CUSTOM_FIELD_3": "%CUSTOM_FIELD_3%"
}
}
}
4.Click the Save icon. The Widget Editor screen will close after the save operation is complete.
5.Select a row in the data grid to edit.
6.Click the Edit icon in the toolbar.
7.Update the CUSTOM_FIELD_1 field with a desired numeric value.
8.In the CUSTOM_FIELD_2 field, select either "Yes" or "No".
9.In the CUSTOM_FIELD_3 field, enter some text.
10.Click the Update icon in the toolbar or press the Enter key to execute the OData requests. A loading indicator will appear and when it disappears, the values should be updated in the database if all OData requests were successful.
11.Verify the database updates by refreshing the Data Grid widget (or querying the record in Professional). If the updated values are still present in the data grid after the refresh, the values have been updated in the database.
Objective: Configure Operation Object – Error Handling
Each request defined in the operation object can have the error property defined. The error property allows users to catch when an operation ends in an error and send off a replacement request.
For this tutorial objective, an error property will be configured and a mock request will be sent that will end in an error to demonstrate error handling for the OData requests in the widget. The uri property for the first PUT will be altered to remove the SAMPLE_ID. This request will not be successful intentionally because it does not have the expected key used to identify the row to be updated. The error operation will then be executed, which contains the expected key and will update CUSTOM_FIELD_1 successfully. This scenario is for illustrative purposes only, but it demonstrates what the error operation object is so it can be used in a more plausible way.
1.Open the Widget Editor by selecting the More Options icon in the upper right corner of the widget to display the drop-down menu and then select the Edit menu option.
2.Select the Advanced tab.
3.Remove %SAMPLE_ID% from the uri property for the first operation object property. The revised operation object should look like the following:
….
"method": "PUT",
"uri": "api/odata/DT_SAMPLE()",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
},
….
4.Click the Save icon. The Widget Editor screen will close after the save operation is complete.
5.Select a row in the data grid to edit.
6.Click the Edit icon in the toolbar.
7.Update the CUSTOM_FIELD_1 field with a desired numeric value.
8.Click the Update icon in the toolbar or press the Enter key to execute the OData request.
9.The request should end in an error and a popup should appear confirming that the request ended in an error. The edited value should be reverted.
10.Re-open the Widget Editor and select the Advanced tab.
11.Add an error object property to the DT_SAMPLE request by updating the JSON as follows:
….
"method": "PUT",
"uri": "api/odata/DT_SAMPLE()",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
},
"error": {
"method": "PUT",
"uri": "api/odata/DT_SAMPLE(%SAMPLE_ID%)",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
}
}
}
….
12.Click the Save icon. The Widget Editor screen will close after the save operation is complete.
13.Select a row in the data grid to edit.
14.Click the Edit icon in the toolbar.
15.Update the CUSTOM_FIELD_1 field with a desired numeric value.
16.Click the Update icon in the toolbar or press the Enter key to execute the OData request. No popup should appear and the edit should persist because the first request will error, but then the error operation object will execute, which is configured correctly.
Example
Here is example JSON that combines all of the above objectives:
"rowEdit": {
"fields": [{
"field": "CUSTOM_FIELD_1",
"editType": "Numeric",
"decimalPlaces": 2
}, {
"field": "CUSTOM_FIELD_2",
"editType": "List",
"listValues": [{
"value": "Y",
"text": "Yes"
}, {
"value": "N",
"text": "No"
}]
}, {
"field": "CUSTOM_FIELD_3",
"editType": "Text"
}],
"operation": {
"method": "PUT",
"uri": "api/odata/DT_SAMPLE()",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
},
"error": {
"method": "PUT",
"uri": "api/odata/DT_SAMPLE(%SAMPLE_ID%)",
"body": {
"CUSTOM_FIELD_1": "%CUSTOM_FIELD_1%"
}
}
},
"success": {
"method": "PUT",
"uri": "api/odata/DT_TEST(%TEST_ID%)",
"body": {
"CUSTOM_FIELD_2": "%CUSTOM_FIELD_2%"
},
"success": {
"method": "PUT",
"uri": "api/odata/DT_RESULT(FACILITY_ID=%FACILITY_ID%,TEST_ID=%TEST_ID%,CAS_RN='%CAS_RN%')",
"body": {
"CUSTOM_FIELD_3": "%CUSTOM_FIELD_3%"
}
}
}
},