In Microsoft Dynamics 365 Finance & Operations (F&O), tables are the foundation of the system’s data structure.
They decide how business data is saved, connected, and protected in the application.
If a table is not designed properly, it can slow down performance, create data issues, and cause problems during upgrades.
In this article, we’ll discuss the key best practices that every X++ developer should follow when building or modifying tables.
Use the Right Table Type
Choosing the correct table type is very important to make sure your data works as expected in Dynamics 365 F&O.
| Table Type | Use When |
|---|---|
| Regular | For main or transactional data |
| InMemory | For temporary data that doesn’t need to be saved in the database |
| TempDB | For temporary data that can be joined or queried using SQL |
| Miscellaneous | For special cases like parameter or framework tables |
💡 Tip:
Use TempDB when you need to run queries or joins on temporary data — it’s backed by SQL and faster for large data sets.
Use Proper Table Relations
Defining table relations keeps your data consistent and helps with lookups and forms.
Instead of writing code for validation, always set up table relations in the AOT (Application Object Tree).
Types of Table Relations in D365 F&O
- Normal Relation
- Links fields directly between two tables.
- Example:
Table1.Field = Table2.Field - Use Case: One-to-one or one-to-many relationships.
- Field Fixed Relation
- Filters data in the main table using a fixed value (like an enum).
- Example:
Table.Field = <EnumValue> - Use Case: When showing specific records, e.g., players where
PlayerType == Footballer.
- Related Field Fixed Relation
- Filters data in the related (foreign) table using a fixed value or enum.
- Example:
<EnumValue> = Table.Field - Use Case: When showing related records with specific conditions.
- Foreign Key Relation
- Connects a foreign key field to another table’s primary key.
- Ensures data integrity and enables features like lookups, IntelliSense, and cascade delete.
Optimize Indexes
Indexes help improve query speed — but too many indexes can slow down inserts and updates.
Create indexes based on how your table is used.
| Index Type | Purpose | Unique |
|---|---|---|
| Primary Index | Uniquely identifies each record | Yes |
| Cluster Index | Defines how records are physically stored in SQL | No |
| Replacement Key | Shows friendly values in lookups | N/A |
| Alternate Key | Adds an extra unique identifier | Yes |
Apply Field Groups for Reuse
Field Groups help organize related fields that can be reused across forms, data entities, and reports.
They save time and maintain consistency.
Common Field Groups:
- AutoReport – fields used in reports
- Overview – summary fields for list pages
- Details – full record fields for detailed forms
💡 Tip:
Create clear and meaningful field groups early — this speeds up form design and keeps the UI consistent.
Use Table Methods for Business Logic
Table methods store business logic directly inside the table, making your code cleaner and reusable.
Common Methods:
initValue()→ set default field valuesvalidateWrite()→ check data before savemodifiedField()→ run logic when a field changesinsert(),update(),delete()→ handle data operations
Set the Right Caching Strategy
Caching helps improve performance by reducing database reads.
But use it wisely depending on how your data changes.
| Cache Type | When to Use |
|---|---|
| None | For data that changes frequently — always read from the database |
| EntireTable | Loads all data once and reuses it until updated or deleted |
| Found | Caches only records that exist — good for static or master data |
| FoundAndEmpty | Caches both found and not-found records — avoids repeated lookups |
| NotInTTS | Keeps cache only within a transaction (ttsBegin / ttsCommit) |