Best Practices for Table Properties

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The following table describes the Microsoft Dynamics AX standards and best practices (rules) for setting table properties. The properties are in the same order as the UI and related properties are grouped together.

For a description of each property, see Table Properties.

Property	Rules
Name	Prefix with the module name. For example, Cust, Vend, or Invent. Infix with a logical description of the contents. For example, CustCollectionLetterTrans, where CollectionLetter is the infix. For temporary tables, infix with Tmp. For example, CustTmpLedger. Postfix with the type of contents. For example, Trans, Jour, Line, Table, Group, Parameters, or Setup. The primary tables for the major business areas are postfixed with Table. For example, CustTable, InventTable, and VendTable.
Label	Mandatory unless the table has the Visible property set to No. An error occurs if: You do not set this property. –and– Visible is not set to Yes. The text value of the label must be unique across all tables (excluding temporary tables), views, and maps, in all languages.
FormRef	For tables where the FormRef property is set to a display menu item, the display menu item must exist.
TitleField1, TitleField2	Mandatory unless: The Visible property for the table is set to No. –or– The TableGroup property is set to Parameter. Don't set this property if there are not enough fields (according to your needs) in the table. (Fields of type real or integer should not be used as title fields.) Specify the two best fields for TitleField1 and TitleField2 according to the following: TitleField1 – The key field for the records in the table. Use a descriptive title if the key field has information for the user. TitleField2 – A descriptive field for the records in the table. For example, for the Table InventItemGroup table, TitleField1 is ItemGroupId, and TitleField2 is Name. If the value of TitleField1 and TitleField2 is the same, an error occurs.
TableType	Starting in Microsoft Dynamics AX 2012, all tables have the TableType property that replaces the Temporary property found in Microsoft Dynamics AX 2009 and earlier versions. Use the setTmp table method to make a non-temporary table temporary rather than creating a copy of the table, and then making it temporary. The two versions of the table can quickly become out of sync.
TableContents	Leave this property set to Not Specified for most tables. Set to Default Data for customer-independent data. For example, time intervals and unit conversions. Set to Base Data for customer-dependent data. This data is often from an existing system that has been imported or entered into Microsoft Dynamics AX. For example, customers and vendors. Set to Default+Base Data for data that can be customer-dependent in some countries/regions, but not in others.
SystemTable	Set this property to Yes if you want the table to be designated as a system table. For example, the information about the system table is used during export and import when system tables can be filtered out. Don't overuse this feature.
ConfigurationKey	Set the ConfigurationKey property for most tables. This ensures that the table is removed when the key is disabled. Configuration keys allow a system administrator to enable and disable certain parts of an application.
SecurityKey	In Microsoft Dynamics AX 2012, security keys are obsolete and only remain for upgrade purposes.
Visible	Mandatory. If the table is a transactions table, set this property to Yes.
CacheLookup	Set to the EntireTable value for the following tables: Contains static data (for example: some Main, and most Group and Parameter tables). Are frequently accessed by the NotFound cache when it hits select statements. For example: while select. == selects something that doesn't match the cache candidate key. select using relational operators other than == (such as <, > and so on). Has a moderate number of records (hundreds or thousands, but not millions). Is obvious that one select statement to the database outperforms select statements to the database. If a table performs poorly when the cache type is set to EntireTable, it is possible to change the cache setting to FoundAndEmpty or Found for a particular installation. Tables with the cache type set to EntireTable should have a Cluster index. This ensures that the table loads as quickly as possible. When your code attempts to find a record in a cache by using a utility class such as SysGlobalCache or SysGlobalObjectCache, your code might get a return code which indicates the record is not in the cache. You code must then get the record by using a normal query. After the query returns the record, your code should add the record to the cache. If most of the find attempts indicate the needed records are not already in the cache, it might make sense to discontinue caching the table. Remove Found caching from tables that have no unique index.
CreateRecIdIndex	Set this property to Yes only if you actually need an index on the Record ID field. All tables have a Record ID index, but the index is set to Passive when the CreateRecIdIndex property is set to No.
SaveDataPerCompany	Set to Yes for company-specific tables. Set to No if the data is related to cross-companies, installation, a database, the AOT, tracing, or OLAP. For example, SysTraceTable or OLAPServerTable. Note If the SaveDataPerCompany property on a table is set to Yes, the SetCompany property on a form design that uses that table as a data source must also be set to Yes.
TableGroup	Mandatory. Set to Group for tables that contain grouping and categorizing information. If the parent table is also a Group table, you only sometimes establish delete actions for a table that relates to a group table. Deleting records from a group table can sometimes result in an unstable situation. Enable confirm deletion. For more information, see Maintaining Data Integrity. Typical examples of Group tables from the standard application are CustGroup and VendGroup. Set to Main for tables that contain base data. Note Consider using an alias field for all tables that have the TableGroup property set to Main. Alias fields are set by using the AliasFor property on the field. For example, a phone number could be an alias for a customer ID. When the phone number is entered, it is automatically replaced by the customer's ID.
PrimaryIndex	Mandatory property for tables that have a unique index. If there is more than one unique index, this property determines which index Found caching works on.
ClusterIndex	Set the index that the table should be organized by. Leave the index blank if performance tests on realistic data show that clustering does not work better. You can measure performance along the following dimensions: Amount of space that the index consumes on the disk and in memory. Speed of CRUD operations (Create, Read, Update, Delete). For more information, see Clustered Indexes.
TypicalRowCount	Select a value that corresponds to the number of rows that the table will probably have.
IsLookup	Set to Yes if the table consists of only a primary key and one other field.
SingularLabel	Mandatory if you have set the AnalysisVisibility property to Yes.
ModifiedDateTime	Set this property to Yes only if you need the information it provides.
ModifiedTime	Set this property to Yes only if you need the information it provides.
ModifiedBy	Set this property to Yes only if you need the information it provides.
CreatedTransactionId	Enable the CreatedTransactionId property if your table has the TableGroup property set to Transaction. If the TableGroup property is not set to Transaction, set the CreatedTransactionId property only if you need to use information about which transaction created each record in the table.
CreatedBy, CreatedDateTime, and CreatedTransactionID	Read-only properties.

Modified/Created Fields

The Modified/Created fields are available on all tables, but can be disabled so that space is not used for unnecessary information.

Information is also available about transaction changes for tables by doing the following:

• Enable the TransactionLog system on all transaction tables.

• Use the database log.

See also

Best Practices: Tables

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.