AX1065

Using custom save validation

You can set up custom save validations for your save-to-database processes, to check for issues in addition to the built-in data validation performed by Axiom Software. These custom validations can be used to check for data that is "invalid" due to a rule that your organization wants to enforce. For example, you can use custom save validations to:

  • Require a comment to be entered if a particular condition is met for a row.
  • Check for values that do not meet expected parameters, such as an expense that exceeds a specified limit.

The custom save validation consists of a condition to be checked, and an error message to be displayed if the data does not meet the condition. For example, if the custom save validation is intended to impose an expense limit, you can define a formula that tests the value in a particular cell against the defined threshold (say, $5000). If the value exceeds the limit, you can display a custom message to the user such as "Travel expense cannot exceed $5000." The user would then have to adjust their travel values in the spreadsheet before they could save the data in the file.

Validation conditions are defined in a reserved column that is labeled with the tag [SaveError]. When a save-to-database is performed, Axiom Software checks this column for errors. If any content is found in this column for rows that are configured to save to the database, then the save-to-database process is stopped and the errors are displayed to the user. Therefore this column should be set up with conditional statements that return blank if the data meets your organization's rules, and return an error message if the data does not. For example:

=IF(F25<=5000,"","Travel expense cannot exceed $5000")

In this example the cell is blank if the value in F25 is less than or equal to $5000, so the save-to-database process is not stopped. If F25 is greater than $5000, then the error message is displayed in the cell and therefore stops the save-to-database process. The error message displays to the user in the Save Errors task pane.

Requirements and limitations

  • Custom save validation only applies to Save Type 1 and Save Type 4.

  • Custom save validation is defined per save process (meaning per [Save2DB] or [SaveStructure2DB] tag). Each save-to-database control row can have one or more [SaveError] columns to validate the data for that particular save process.

  • Validation conditions are only valid on rows that are configured to save to the database (either as saved rows or deleted rows). Therefore, custom save validation is appropriate to test data that is row-specific, but not aggregate data.

    For example, imagine that you have five rows of expense data in a file, and each row is configured to save to the database. You can test a value on each individual row to see if that value exceeds a threshold, but you can't test the sum of all five rows (unless you are saving that summed row to the database, or creatively using formulas).

Setting up custom save validation

To set up custom save validation for a save process:

  1. On the save-to-database control row for the save process that you want to validate, enter the tag [SaveError] in any column.

    If desired, you can append a column letter to the tag, to indicate the column that contains the data you are validating. For example: [SaveError;F]. This will be used to provide cell-specific error detail to the user; otherwise just the row with the error will be indicated. See the following section for more details.

  2. Within the [SaveError] column, enter a conditional formula on each row that you want to check for save errors. The formula must return blank if the data is acceptable, and return an error message if the data is unacceptable. You can use any formula that you wish to test the data.

    If you are configuring save validation in a plan file, then in most cases these formulas will be defined within the individual calc methods, so that the validation conditions are present in the appropriate column when these calc methods are brought into the plan file.

    NOTE: Custom save validation will only be checked for rows that are configured to save to the database. If a row does not contain a valid save-to-database tag such as [Save], [Delete], or a custom save tag, then that row will be ignored for purposes of save validation.

You can define multiple [SaveError] columns on the control row, and each one will be checked during the save-to-database process. For example, you might have two different conditions that you want to check for a particular row: did the user define a comment, and does the value for the row exceed $5000? Instead of having to define a complex IF statement to test both conditions and return the appropriate error message, you can place the comment validation formula in the first [SaveError] column, and the value threshold validation formula in the second [SaveError] column. If the row violates both conditions, then both error messages will display for that row.

Communicating errors to the user

In the Windows Client and the Excel Client, save errors are displayed in the Save Errors task pane. If an error is present within a [SaveError] column, the error will be communicated to the user as follows:

  • If no column letter is indicated in the [SaveError] tag, the error message will be displayed with a link to the row that contains the error.

    For example, if the error message is from row 25, the user can navigate to row 25. Since the user is not directed to a particular cell, the error message should be detailed enough so that the user can easily find the problem and correct it.

  • If a column letter is indicated in the [SaveError] tag, the error message will be displayed with a link to the cell in that column (for the row that contains the error).

    For example, if the error message is from row 25 and the column letter in the tag is E, the user can navigate to cell E25. The intent is that if a column letter is specified, the user will be taken directly to the cell that needs attention.

If you want to use custom save validation with Axiom forms, keep in mind that specific row and cell references are not relevant in Axiom forms, and the presentation of save errors is different. In the Axiom form environment, only the error message will be displayed to the user. The error message should be specific enough so that the user can easily find the problem area within the form without having direct navigation. You may also want to use conditional formatting on the cell so that it changes in some way when the error is triggered (for example, a red border around the problem cell).

Example of custom save validation

The following example shows a simple save-to-database process with a [SaveError] tag to the right of the [Save2DB] tag. The validation condition, shown in the formula bar, tests whether the value in cell D8 exceeds 500. Because the value is currently 500, the value meets the condition and the [SaveError] column is blank. When the user saves, no error will be triggered.

In the following screenshot, the value has been changed to 600, which exceeds the threshold. An error message now displays in the [SaveError] column, so the save-to-database process is stopped and the error message is displayed to the user in the task pane. Because the [SaveError] tag contains the column letter D, the error directs the user to the specific cell with the problem: D8. If no column was indicated in the tag, then the user would be directed to row 8.