AX1411

About default column values

Each column in a table has a defined default value. This default value is applied in cases where no value is specified for the column.

The default value for a column is determined by the Default Value field in the column properties.

  • When a column is created, the Default Value field is initially populated with the default value specified for the column's data type. These values are defined in the system configuration settings per data type, and can be changed for your installation.

  • If desired, you can change the default value for a particular column by editing the Default Value field for that column.

Configurable default values for columns by data type

The following system configuration settings control the default values for columns based on data type:

Setting Description

DefaultColumnValue_Boolean

Defines the default value for Boolean columns. By default, this is set to 0 (FALSE). You can change this to 1 for TRUE, or blank for null. However, it is not recommended to use null as the default for Boolean columns unless you have a particular use case that requires it.

DefaultColumnValue_Date

 Defines the default value for Date columns. By default, this is blank (null). You can change this to any valid default value.

DefaultColumnValue_DateTime

Defines the default value for DateTime columns. By default, this is blank (null). You can change this to any valid default value.

DefaultColumnValue_Number

Defines the default value for columns that hold numbers—Integer (all types) and Numeric columns. By default, this is set to 0. You can change this to any valid default value, including blank for null. However, it is not recommended to use null as the default for number-based columns unless you have a particular use case that requires it.

DefaultColumnValue_String

Defines the default value for String columns. By default, this is set to '' (an empty string). You can change this to any valid default value. Single quote marks are required to indicate the value is a string.

NOTE: Although it is possible to set the String column default to blank for null, Axiom Software does not differentiate between null values and empty string values within the spreadsheet environment. Therefore it is strongly recommended to leave the default of empty string if you do not want to use a specific string.

If any of these values are changed, the new value only applies to new columns created going forward. Existing columns will continue to use the default value that was set when they were created.

Setting the default value for a particular column

When you create a new column, the Default Value field is set to the default value defined for the appropriate data type. For example, if you create a new Integer column, the Default Value field is set to 0, which is the value set in the DefaultColumnValue_Number system configuration setting.

This inheritance from the system configuration settings only occurs when the column is created. If DefaultColumnValue_Number is later changed to a different value, this will not affect any existing columns, only new columns.

If desired, you can change the Default Value on a per column basis. For example, imagine that this new Integer column tracks the status of items based on an integer value from 1 to 5. If you want all new records to start out at 1 by default, then you can set the Default Value for this column to 1.

NOTE: For document reference tables, there is no means to "override" the system column value defaults. Document reference tables always use the default values as defined in the system configuration settings per data type.

How the default value is applied to records in the table

The default value is applied in the following circumstances:

  • When a new column is added to a table and that table has existing data, the default value is used for all existing records.

  • When a new record is added to a table and the column is not included in the save, the default value is used for that column.

  • When the column is included in a save (whether for a new record or an existing record) and the value to be saved is null (a blank cell in the spreadsheet), the default value is used for that column.

    NOTE: If the column is a non-validated String column, then the blank cell is interpreted as '' (empty string) instead of null, so the empty string is saved.

If the default value is used and the column is a validated column, the default value must either be a valid value in the lookup column or null (blank). Null default values should only be used when you intend for some records in the table to not have an assigned value from the lookup column. If the validated column is a key column, null values cannot be used. If the validated column is a String column, it is not recommended to use a null default value (see the discussion in the following section).

Allowing null values in a column

If you want to allow null values in a column, then the default value for the column must be null.

If the default value is not null, then there is no way to save null values to the column. For example, the system default for Boolean columns is 0 (False). If you save a blank value to a Boolean column that uses this default—for example, by clearing the cell in Open Table in Spreadsheet, or by using Save Type 1 with a blank cell for that column—this results in the default value of 0 (False) being applied to the column. However, if the default value for the column is blank (null), then saving the blank value to the column causes the default value of null to be applied to the column.

IMPORTANT: As mentioned previously, null default values are not recommended for String columns. When saving from a file, Axiom Software cannot differentiate between empty string and null. Depending on how the data is populated for the column, this can result in a mix of null values and empty string values, which are treated differently by data queries. String columns should not use a null default value unless it is intended for a very specific and narrow use case where the data considerations are well understood.

NOTES:  

  • If a column is a key column or an alternate key column, then null values or empty string values cannot be saved to that column, regardless of its default value.

  • Boolean columns should not have the default value set to null unless you need to track three Boolean states—True, False, and unset (null). If the column usage depends on every record having either a True or False value, then the default value must be either True (1) or False (0).

  • Number columns should not have the default value set to null if the column is part of a sequence that is used by a calculated column. Null values in the sequence will cause the calculated column to not calculate as expected.

  • If you change the default value of a DateTime column to something other than null, then you will not be able to save null values to that column—every record must have an assigned date. You should only do this if the field requires a date.

  • If you are adding a new column to a table that contains many records, this process can take some time as the default value must be populated into every record. In some cases it may be worthwhile to speed up this process by adding the new column to the table with a temporary default of null, then go back afterward and set the default value to the intended default value. However if you do this, you must be careful to later update the table to replace these null values with the intended values for each record. For example, if the column is a Numeric column and you expect records to contain 0 by default, then these records should be updated to actually contain zeros instead of null values (otherwise calculations such as average will not work as expected).

Validated columns and default values

When you make a non-key column a validated column, you can choose to set the default value using one of the following options:

  • Valid lookup value: If the default value is a valid value in the lookup column, then that value is used if the column is omitted, or blank.
  • Invalid lookup value: If the default value is not present in the lookup column, then an error occurs if the column is omitted or blank.
  • Null value: If the default value is null (blank), then the null value is used if the column is omitted or blank.

The omitted case only applies when creating new records. If you are updating existing records and the column is omitted from the save, then the existing value is left as is.

NOTES:  

  • If a validated String column has a null default value, then the null value is used if the column is omitted from the save, but not if it is included but blank (in this situation, an empty string is used). It is not recommended to use null default values with String columns.

  • When you are newly assigning a lookup column to a particular column, the default value must be either a valid lookup value or null in order to save the lookup assignment. However, once the lookup column assignment has been saved, you can change the default value to an invalid value if desired.

For example, imagine that you have a validated column with a lookup to a column that contains category codes. How you set the default value for the validated column depends on whether all records should be assigned a specific category, or whether it is acceptable for some records to be left blank or use a generic default.

  • If you want records to be assigned a default code if the save does not include a category, then you can set the default value of the validated column to that value (assuming that the value is present in the lookup column). For example, the lookup column may contain a category code of 0 which means "Not Applicable". In that case you can set the default value of the validated column to 0.

  • If you want records to be left blank if the save does not include a category, then the default value of the validated column should be set to null (blank). Generally speaking, you should only do this if the null value has a specific understood meaning for your data, such as unassigned or inapplicable. If instead you want all records to be populated with a category code, the default value should not be null.

  • If you want all records to be populated with a category code, and you do not want the code to be populated by default, you can optionally set the default value of the validated column to an invalid value (meaning a non-null value that does not exist in the lookup column). This will prevent the save from occurring if the category column is excluded from the save (for new records), or if it is included but left blank.

    Alternate ways of handling this situation include using custom save validation to verify that a category has been assigned before permitting the save, or setting the default value to some known lookup value such as 99 (which in this example would be a pseudo category code meaning "Needs Attention"). In the latter case you could have an automated utility that checks for this known value and then alerts someone if it is present in the validated column, so that it can be addressed.

This discussion does not apply to validated columns that are key columns, because key columns must always be included in saves and cannot be blank.

See also