Table Properties in D365 F&O

 

Table Properties in D365 F&O

When a new element, such as a table or field, is added to a project, we recommend that you update and modify the element's properties. While many properties can be manipulated, only a few will commonly be used. Table type - Determines if the table is considered a regular database table or a temporary table, such as a TempDB or InMemory table. Name - If used in a form to display in the user interface as a page, the name is what the user will see on the page. Names and labels should use camel case for consistency, but table names will display in capital letters. Label - Will be added from a label file and is used to identify the table in Visual Studio. Primary index - Specifies which field in the table should be used as the primary index and is used for database optimization purposes. Cluster index - Specifies the field by which the table should be organized. This property should never be left blank. Configuration key - Allows a system administrator to enable and disable certain parts of the application and ensures that a table is removed when the key is disabled. Support inheritance - Determines whether the table can become a base table by using inheritance. Setting this property to Yes indicates that this table can be extended, or that it can inherit properties from an existing table. Extends - Used when the Support Inheritance property is set to Yes. The Extends property chooses the base table that will become the parent table to the current table.

The following animation shows the Properties window for the CustTable that is opened from the Application Explorer window.

Field properties are commonly adjusted at the element level instead of within the table. For example, if you added an EDT as a field to a table, you would open the EDT in the element designer to adjust any properties before adding it to the table.

Base enum properties determine if the property should be displayed as a drop-down menu, option buttons, or a slider bar in the finance and operations apps user interface. EDT properties vary depending on the type of EDT, such as string, integer, or date. For a string EDT, for example, you can control how many characters can be entered in the text field. For a date EDT, you can choose if the month and day are listed as one or two numbers and if the year should be listed as two or four numbers. It is best practice to leave properties set to Auto so that the system can adjust the field as necessary in the user interface.

Identify and apply caching mechanisms

Cached data is information that is retrieved from an outside source, such as a website, app, or database that is stored on your computer. Caching data decreases loading times by reducing the number of calls that are made to the database. While caching data can improve performance, it does take up space on your computer, so you should consider clearing your cache periodically.

finance and operations apps cache settings for a table can be set on the table property CacheLookup. Caching tables use either set-based or single-record based caching and there are differences between the types.

• Set-based caching - Caches groups of records all at once. To implement set-based caching, you would need to set the CacheLookup property to EntireTable. This will cache all the records in a table after your first selection. This type of caching should be avoided for large tables because when the cache size reaches 128 KB, the cache moves from memory to disk. A disk search is slower than an in-memory search and can decrease performance.

• Single-record caching - Caches a single record at a time. Two conditions must be met to use single-record caching.

  • The CacheLookup property must be set to NotInTTS, Found, or FoundAndEmpty.

  • The record buffer disableCache method does not return true. Single-record caches are used on the client and the Application Object Server (AOS). If a Select query is run on the client side, the client side browser cache is searched. If no record is found in the browser cache, finance and operations will search the AOS cache for the record. After looking through the client and server caches, the data will be retrieved from the database. You can also run queries on the server. These queries will look in the server cache for the record and then call the database if the record is not found. You can set up the number of records to be maintained in the cache from the System administration > Setup > System > Server configuration page in finance and operations apps.

Below are some important Table Properties:

Property	Value	Comment
Label	Vehicle groups	This is the plural name that appears to the user. VehicleGroupTable is a good label ID for this, as it gives context to others that might want to reuse this label.
Title Field 1	VehicleGroupId	These two fields appear in automatic titles generated when this table is used as a title data source.
Title Field 2	Name
Cache Lookup	Found	This is linked to the table type, and warnings will be generated should an inappropriate cache level be selected. None: no caching is fetched from the DB every time. NotInTTS: Fetched once per transaction. Found: Cached once found, not looked up again. EntireTable: The entire table is loaded into memory. The cache is only invalidated when records are updated or flushed.
Clustered Index	GroupIdx	This index is created as a clustered index. Clustered indexes are useful as they include the entire record in the index, avoiding a bookmark lookup. This makes them efficient, but the key should always increment, such as a sales order ID; otherwise, it will need to reorganize the index when records are inserted. This table is small, so it won't cause a performance issue. It will also sort the table in Vehicle Group order.
Primary Index	GroupIdx	This defines the primary index and is used when creating foreign key joins for this table.
Table Group	Group	This should always be Group for a group table. Please refer to the table of table groups in the Introduction section.
Created By Created Date Time Modified By Modified Date Time	Yes	This creates and maintains the Created by tracking fields and is useful if we want to know who created and changed the record, and when.
Developer Documentation	The ConVMSVehicleGroup table contains definitions of vehicle groups.	This is required for best practice and should contain information that other developers should understand about the table.
FormRef	ConVMSVehicleGroup	This is a reference to the display menu item that references the form that is used to view the data in this table. When you choose View details in the user interface, it is this property that is used to determine which form should be opened. It is fine to fill this in now for speed, but the build will fail unless we have created the form and menu items.