Best Practices for Table Properties

  • Article

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. Error icon

The text value of the label must be unique across all tables (excluding temporary tables), views, and maps, in all languages. Error icon

FormRef

For tables where the FormRef property is set to a display menu item, the display menu item must exist. Error icon

TitleField1, TitleField2

Mandatory unless:

  • The Visible property for the table is set to No.

    –or–

  • The TableGroup property is set to Parameter. Error icon

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.Error icon

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. Warning icon 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. Warning icon

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. Warning icon

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. Error icon

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.