AX1086

Editing and saving data using a Fixed Report

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

This feature works as follows:

You can configure one or more report columns as editable, so that users can edit the value in the report 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 report, and specify a target table and columns for the save. When a save-to-database is triggered in the form, any report rows with edited data will be saved to the database.

Configuring the report for editing and saving data

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

In the component properties, enable the Enable Saving option.
In the FixedReportColumns data source, for each column where you want the user to be able to edit values, enter True into the [IsEditable] column.
In the FixedReportColumns data source, for each calculated column that you want to update in response to user edits, enter True into the [IsLiveUpdate] column.
In the FixedReportColumns 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 report 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.
  IMPORTANT: The contents of the [HeaderColumn] cannot be saved to the database. Any entry for the [HeaderColumn] in the save column will be ignored without error. In most cases, you must set up the FixedReportColumns data source with hidden columns (meaning [IsVisible] is False) in order to bring in key columns to use for the save.
NOTES:
- If you want to save the value for a calculated column to the database, it must be visible in the report. 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 report, 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 report 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 report save works, and how it interacts with other save processes.

Limitations and requirements

The ability to edit and save data from a Fixed Report 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 report edit and save process:

The editable cells in the report 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 report 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 report, 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 report features are not supported when saving is enabled, and will either be ignored or cause an error:

Enable Row Selection

The following report features have limitations when saving is enabled:

Totals and Subtotals: Totals and subtotals for editable columns and live update calculations do not update in response to user edits. The subtotals and totals will continue to reflect the original report values. If the edits are saved to the database, the report is refreshed and the totals and subtotals will then reflect the current data.
Export to Excel: If an editable report 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 report, 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.
IsVarianceColumn: If a calculated column is configured for live updates, then it cannot be a variance column. If both live updates and variance column are enabled for a calculated column, the variance column designation is ignored. This means that the column will not invert values if [InvertVarianceColumn] is enabled, and the column will not be treated specially for designated contra accounts.

Additionally, note that the report 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 Fixed Report 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 report are saved to the database, using the target table and columns as configured in the FixedReportColumns 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 report 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 report, and that column is editable, then the calculation uses the current value of that column in the report 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 report save is processed as follows in the context of the form update cycle:

The report 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 report 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 report save—the form must contain an active Save Type 1 or Save Type 4 in order to trigger this refresh-after-save behavior.
The report is refreshed at the end of the cycle, so that it displays the most current data from the database. Any cells in the report 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 report when saving data from the report. If the report is dependent on a component, and that component triggers a form update, any unsaved changes in the report 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 report 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 Fixed Report 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 report 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 report 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 report 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 SalaryAdjPct and SalaryAdj columns have been configured as editable. Several calculated columns reference these editable adjustment columns, so the calculations have been configured to update live in response to user edits. Lastly, the save property has been configured to save the edits back to the LaborBud table. If desired, the calculations could also be saved back to the target table.

Example FixedReportColumns data source configured to allow edits and save data

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

Example Fixed Report component 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 dollar adjustment for the hourly Executive Assistant position was changed to $0.75. Because the calculated column RosterAdjTotal ("Proposed Base Pay") is configured for live updates, it has updated to show the new base pay using the edited adjustment value.

Example Fixed Report component 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 Fixed Report component after saving edits