AX1124

Editing and saving data using a Data Grid

You can configure a Data Grid component so that users can edit values in the grid and then save changed data back to the database. This feature provides limited editing and saving functionality from within the structured grid.

This feature works as follows:

You can configure one or more grid columns as editable, so that users can edit the value in the grid by typing into the cell.
You can configure calculated columns to update live, so that if the calculation references an editable column, the value in the calculated column updates in response to the user's input.
You can enable saving data for the grid, and specify a target table and columns for the save. When a save-to-database is triggered in the form, any grid rows with edited data will be saved to the database.

Configuring the grid for editing and saving data

To configure a Data Grid component to allow user inputs and save data, do the following:

In the component properties, enable the Enable Saving option.
In the DataGridColumns data source, for each column where you want the user to be able to edit values, enter True into the [IsEditable] column.
In the DataGridColumns data source, for each calculated column that you want to update in response to user edits, enter True into the [IsLiveUpdate] column.
In the DataGridColumns data source, configure the [Save;TABLENAME] column as follows:
- Replace TABLENAME with the name of the target table for the save-to-database. For example, change the column tag to [Save;BGT2021] if you want to save edited grid data to that table.
- For each column in the grid to be saved, enter the name of the target column in the target table. All key columns must be included in the save (and alternate key columns, if applicable), as well as any other columns for which you want to save changed data.
NOTES:
- If you want to save the value for a calculated column to the database, it must be visible in the grid. Hidden calculations are ignored.
- Database columns used for the save-to-database can be hidden. For example, if you have a key column that is necessary for the save, but you do not want to display it in the grid, you can configure it as not visible.

In the form, the save-to-database can be triggered using either of the following:

An interactive component with Save on Submit enabled. Typically this is a Button component, however, the save can be triggered by any form component that supports the Save on Submit property.
The save button in the gray Task Bar The gray bar that displays across the top of browser-based Axiom files, as well as various feature pages of the Web Client. The Task Bar provides access to various tasks that are specific to the current area, page, or document., if this button is enabled for the form.

NOTE: The grid save does not occur at the same point in the form update process as other save-to-database processes using Save Type 1 or Save Type 4. See the following sections for more details on how the grid save works, and how it interacts with other save processes.

Limitations and requirements

The ability to edit and save data from a Data Grid component is intended to meet a narrow use case. You can query data from the database, edit values in designated columns, and then save the edited values and/or any calculated values that reference the edited values.

The following limitations apply to the grid edit and save process:

The editable cells in the grid are system-controlled and cannot be customized. For example, it is not possible to present a drop-down list of valid entries. The edit controls are limited to editable text boxes for string and numeric fields, calendar selectors for date fields, and check boxes for Boolean (True/False) fields.
Only existing records in the grid can be edited. Adding new records and deleting records is not supported. Note that it is technically possible to add a new record by editing a key column value in the grid, but this is not an intended use case and not a good user experience for creating new records.
Although identity columns can technically be edited and saved, it is not especially useful in this context and not intended to be supported.

The following grid features are not supported when saving is enabled, and will either be ignored or cause an error:

Paging
Show Hierarchical Data or using a HierarchicalGrid data source
Enable Row Selection

The following grid features have limitations when saving is enabled:

Total Row: If the total row is enabled for the grid, totals for editable columns and live update calculations do not update in response to user edits. The totals will continue to reflect the original grid values. If the edits are saved to the database, the grid is refreshed and the total row will then reflect the current data.
IsFilterable: Columns can be configured to allow end-user filtering, however, this should be done with caution. If a user makes an edit to the grid, and then applies a column filter, the edit will be lost.
Export to Excel: If an editable grid is exported to Excel, any unsaved user edits will not be reflected in the export. The export will contain the original data for the editable columns and any calculated columns that are configured to update live.
Generate PDF: If a PDF is generated of a form that contains an editable grid, any unsaved user edits will not be reflected in the PDF. The PDF will contain the original data for the editable columns and any calculated columns that are configured to update live.
IsPreAggregationCalculation: If a calculated column is configured for live updates, then it cannot be calculated pre-aggregation. If both live updates and pre-aggregation are enabled for a calculated column, the calculation will be made post-aggregation.

Additionally, note that the grid save is not supported within an embedded form. Depending on the embedded form setup and how it is configured to save, the save-to-database may not work at all or it may cause unexpected behavior within the form.

How the data save works

When the Data Grid component is rendered in the form, any columns that are configured as editable display with their values in editable cells. For consistency, these cells use the same formatting as other editable form components, such as the Text Box component or editable cells in a Formatted Grid component. The editable cells are outlined and have a light blue background.

To edit a numeric or string cell value, the user can click into the cell and then type. If the cell does not currently display the full numeric value due to the number format of the column, the full value displays when the user clicks into the cell.

For example, if the queried value from the database is 567.87, but the number format of the column does not display decimal places, then the cell displays 568. When the user clicks into the cell to edit, the full value of 567.87 is displayed, and the user can change it as needed. If the user's edit contains decimal places, those decimal places are recorded and used for the save, even though the number format does not display them.
To edit a date cell value, the user can click the calendar icon to open a calendar control, then select a date.
To edit a Boolean cell value (True/False), the user can select or clear the check box.

When the user clicks or tabs out of the cell after editing the value, the cell now displays with a light yellow background. This is intended as a signal to the user that the value has been changed, but has not yet been saved.

When the user triggers a save-to-database in the form, the data to be saved is determined as follows:

Any edited rows in the grid are saved to the database, using the target table and columns as configured in the DataGridColumns data source. If no user edits were made to a row, that row is ignored during the save.
For [Column] rows that are configured to save to the database, the current value in the grid is saved.
For [CalculatedColumn] rows that are configured to save to the database, the calculation is performed to determine the value to be used for the save. If the calculation references a column that is visible in the grid, and that column is editable, then the calculation uses the current value of that column in the grid as opposed to the original value queried from the database. This occurs regardless of whether the calculated column is enabled for live updates (however, it is recommended to do so).

The grid save is processed as follows in the context of the form update cycle:

The grid save is performed at the start of the cycle, before any other form processing begins. If an error occurs during the save, the error is displayed to the user and the form update cycle is aborted.
The remainder of the form update cycle occurs as normal. If an Axiom query, data lookup, or GetData function within the source file references the target table of the grid save, the updated data is available to the query. However, keep in mind that Axiom queries that are configured to Refresh After Save Data are not triggered by a grid save—the form must contain an active Save Type 1 or Save Type 4 in order to trigger this refresh-after-save behavior.
The grid is refreshed at the end of the cycle, so that it displays the most current data from the database. Any cells in the grid that were previously formatted as changed are now restored to their original formatting.
If Save Data Confirmation is enabled in the form properties, a confirmation dialog displays to the user about the data save.

NOTES:

Once the value in a cell has been edited, there is no way to "revert" to the original value other than to reload the form using the browser's refresh button. In this case all other changes will be lost.
Tabbing does not work to move through editable cells. The user must click into the cell to edit it.
Because only edited rows are saved, this means that the data save cannot consist solely of performing a calculation and then saving the calculated data to a target column. A user must edit a value in the row before the data is saved.
Use caution before specifying one or more Component Dependencies for the grid when saving data from the grid. If the grid is dependent on a component, and that component triggers a form update, any unsaved changes in the grid will be lost (unless that component also triggers a save-to-database). It is not necessary to list the Save on Submit button as a component dependency—the grid will automatically refresh after it saves data.

Combining multiple save types

You can configure an Axiom form so that it contains a save from a Data Grid component and a save configured in the spreadsheet source file (such as Save Type 1). If the form contains both types of saves, then both saves will be processed when a save-to-database is triggered in the form. Note the following:

If the grid save errors, the form update cycle is aborted and the errors display to the user. This means that the spreadsheet save in the source file will not be processed, because spreadsheet saves occur near the end of the form update process.
If the spreadsheet save errors, the form update cycle finishes and then the errors display to the user. There is no way to stop or revert the grid save in this case, because it occurred at the start of the form update process.
Both saves honor the Save Data Confirmation form-level property to determine whether a confirmation dialog displays to the user after executing a save-to-database. If both types of saves are processed in the form, only one confirmation dialog displays, at the end of the process.
If save locking is enabled for the form, the save lock controls the ability to save data using either type of save process. Whether the form contains just a grid save, or just a spreadsheet save, or both, the user must have the save lock in order to execute a save.

Example

In the following example, the BilledHours and Rate columns have been configured as editable. A calculated column is used to show the results of BilledHours*Rate, and this column is configured for live updates, so that it responds to edits in either of the source columns. Lastly, the save property has been configured to save edits and the calculated value back to the ProjectTime table.

Example DataGridColumns data source configured to allow edits and save data

When the form is rendered, the editable column values display in editable cells:

Example form with editable columns

When an edit is made to a value, the background color of the cell changes to yellow, to indicate that the grid contains an unsaved change. In this example, the hours for Project A were changed to 7.25. Because the calculated column Total is configured for live updates, it has updated to show the new total based on the edited value.

Example form after making an edit

Once the save button has been used to save changes to the database, the grid updates to display the latest data from the database. The background color of the edited cell reverts back to blue, because now it is showing the queried value from the database.

Example form after saving edits