Column properties

The name of the column. This name is used in Axiom queries and Axiom functions to query data, so it should be descriptive and short. See Table and column naming requirements.

This setting only applies to systems with installed product packages, and is only present if Is Variable Column is set to True. It defines an alternate display name for the column. This setting is intended to be configured during system implementation as needed. You should not change this setting afterward without consulting Axiom Support.

Optional. A description of the column.

Specifies the type of data that can be stored in the column. See Column data types.

Specifies the type of numeric data held in the column, for formatting purposes when column values are displayed in certain areas of the software. Only applies to Integer columns (all types) and Numeric columns. Available options are:

• Number (default): Column values are treated as plain numbers.
• Currency: Column values are treated as currency values.
• Percent: Column values are treated as percentage values.
• Date: Column values are treated as date values.
• DateTime: Column values are treated as date time values.

NOTE: Date and DateTime are placeholder values for future features. Currently these options do not have any effect.

For more information where this formatting is applied and how each option is treated, see Using numeric type for column formatting.

Defines the maximum length of a string column in characters. Only applies to String columns.

The default length is 50. You should set the length to the smallest size necessary to accommodate the anticipated contents of the column. Setting the string length unnecessarily long can severely impact system performance. You can specify a length from 1 to 4000.

NOTE: It is possible to set the column to an unlimited length by leaving the Max String Length field blank. (In Open Table in Spreadsheet, this setting displays as Unlimited.) This should only be done if absolutely necessary. Make sure that you understand the potential ramifications of having an unlimited length column. Key columns cannot be set to unlimited length.

Specifies whether the string column is Unicode-compliant. Only applies to String columns.

• By default, this is False, which means the column is limited to storing extended ASCII characters. This is equivalent to the SQL varchar data type. The database collation determines the specific set of valid characters.
• If True, then the column can store any Unicode data. This is equivalent to the SQL nvarchar data type.

If you do not need full Unicode support for a column, leaving this option disabled can save space in the database.

Specifies whether the column is a key column for the table (True/False). See About key columns. If True, a yellow key icon displays next to the column name.

Key columns can only be the following data types: Identity, Integer (all types), String, or Date. Other data types are not valid for key columns.

Reference tables can only have one key column. Data tables can have as many key columns as needed.

Specifies whether the column is an alternate key column for the table (True/False). See the discussion of alternate key columns in About key columns.

A table can have as many alternate key columns as needed. Alternate key columns can be validated columns (with an assigned lookup column) or not validated. The same data types that can be key columns are eligible to be alternate key columns.

NOTE: Once a column has been designated as an alternate key column, the System.IndexMaintenance job in Scheduler must be run before any data can be saved to the table. This job is used to create the constraint that enforces unique values in the column. By default this job is configured to run nightly. It can be run manually if you need to save data before the next scheduled execution.

Optional. Specifies another column that defines the valid values for this column. The specified lookup column must be a key column in an existing reference table, and it must have the same data type as the current column. The following additional requirements apply:

• For string columns, the string length and Unicode status must also match.
• Integer columns can optionally look up to Identity columns. The size of the columns must match (Integer to Identity, or Integer 32 to Identity 32).

If a database column has an assigned lookup column, it is known as a validated column, and a blue key icon displays next to the column name. See About lookup columns.

NOTES:  

• You can assign a lookup column to a column that already contains data, but only if all of the data in the column is valid in relation to the lookup column. When you attempt to do this, Axiom Software will first validate the data. If the validation is successful, the lookup column assignment will be accepted. If the validation is not successful, a warning will display and you will need to correct the data before you can assign the lookup column. Note that null (blank) values are only considered valid if the default value of the validated column is null, and the validated column is not a key column.

• If you are creating a new table, and you want to create a "self-referencing" lookup (where the assigned lookup column is the key column of the current table), then you must save the table first and then go back and edit the column to add the lookup assignment. Until the table is saved, its key column will not display in the drop-down list.

Optional. The table partition scheme assigned to the column. This property only applies to key columns. This property does not apply to identity key columns or string key columns with Unicode enabled.

The configured partition scheme may or may not be currently applied to the table. The Current Partition Scheme property indicates the currently applied partition scheme. When you save the column properties, Axiom Software will attempt to apply the configured partition scheme if the configured and current partition schemes do not match. If the table contains data that exceeds a configured limit (2 million rows by default), then Axiom Software cannot apply the partition scheme as part of saving the column properties, and will instead give you the option to process the partition change using Scheduler. If you say Yes, then the table will be processed using the System.RebuildPartitionTables Scheduler job. If you say No, then the configured partition scheme will be saved in the table properties but the partition scheme will not be applied to the table.

If a partition scheme is configured and applied to a table, then the table is partitioned within the database according to the boundary values defined for the scheme. Partitioning large tables may improve performance when querying the table and filtering the query based on the partition key column.

Table partitions are primarily defined and configured by product developers and system implementers, and delivered with product packages. If you have a custom system and believe that you may have a need for table partitions, please contact Axiom Support for more information.

The currently applied partition scheme for the table. This is a read-only, system-controlled property. If this value does not match the Configured Partition Scheme, then Axiom Software will attempt to apply the configured partition scheme as the current partition scheme when you save the column properties.

Specifies whether cascading deletion of data is supported for this table, based on the lookup relationship for this column. By default, this property is set to False. This property only applies when the column has an assigned lookup column, and that lookup column is an identity column.

If this property is set to True, then when data is deleted from the lookup table, any corresponding data will automatically be deleted from this table. For example, if identity record 47 is deleted from the lookup table, then all data relating to record 47 in this table will also be deleted.

If you want to use the Delete Plan Files command to delete on-demand plan files, then you should set this property to True. Otherwise, plan files cannot be deleted if linked tables contain data associated with those plan files. For more information, see Deleting plan files from an on-demand file group.

This setting only applies to systems with installed product packages, and cannot be modified. This setting is controlled at the product level.

Defines a "friendly" display name for the column. The display name may be helpful to clarify the contents of the column to users in various areas of the software.

The display name is used as follows:

• When the column is included in a hierarchy and displayed in the hierarchy view of the Quick Filter, Filter Wizard, or in the drill-down menu.
• When users are prompted to select a value for the column as part of creating a new plan file for an on-demand file group.
• When the column is included in a data set for use in the Data Explorer.

Specifies whether the column displays in the Filter Wizard. By default, this is True, which means the column is available to be selected in the Filter Wizard advanced view.

If False, then the column is hidden in the Filter Wizard. The column is still a valid filter column for existing or manually created filters, it simply does not display in the Filter Wizard.

Specifies whether the column contains hyperlink data. Only applies to String columns.

When text is placed here, the column is identified as a hyperlink column and behaves as follows:

• The column can contain URL strings starting with http or https, or file paths in the Axiom file system.
• When this column is queried via Axiom query, the contents will be automatically converted to either a Hyperlink function (for URL strings) or to a GetDocument function (for Axiom file paths). The Hyperlink Label is used as the display text.

Specifies whether the column contains descriptions for the key column values. For example, most tables have a column named Description which holds a description for each key code.

If True, then the column is treated like a description column, which means that it will be included automatically in most dialogs as a description for the key. You can specify multiple columns as description columns if appropriate.

All other non-description columns in the table should be left at the default of False.

NOTE: If the table is a reference table, and no column has been designated as the description column, then a warning will display before the table is created. It is recommended to designate a description column for reference tables.

Specifies the data classification of the columns. Columns are automatically assigned a classification by default; you can override this classification if appropriate. Select one of the following:

• Default: The column uses the default classification for this type of column, either Dimension or Value.
• Dimension: The column is a dimension or grouping. These types of columns are typically used to group data in reports, or to display information about dimensional records.
• Value: The column holds data such as actuals, budget, payroll, or other data. This type of data is typically summed based on the dimension or grouping level of the report.

For more information, see About column classifications.

This column property refers to a deprecated feature. Enabling the setting has no impact in the system.

Specifies whether data in the column can be edited. By default, this is set to False, which means that the column data can be edited.

Key columns, and columns in document reference tables are always set to False and cannot be changed. Other columns can be set to True.

If a column is set to True, then the data in that column cannot be edited by Axiom Software processes such as Copy Table Data, Open Table in Spreadsheet, or save-to-database. If new rows are inserted, the column will have the default value.

Columns designated as read-only can be deleted.

Defines the default value for the column. When a new column is created, this value is set to the default value defined for this column's data type in the system configuration settings. You can change this value for this column to any valid default value for the data type, including:

• A string literal in single quotation marks, such as '' or 'n/a' or '_'
• A number, such as 0 or 1
• A SQL function, such as GetDate() or GetUTCDate()—the result is applied as the default value
• Blank for null values

For more information, see About default column values.