Tables, Fields, and Indexes

Why tables are the starting point

Every screen, every report, every integration in D365 Finance & Operations reads or writes a table. Before you build forms, entities, or classes, you need tables.

Extended Data Types (EDTs)

Before you add a field to a table, you should define an Extended Data Type (EDT). EDTs are reusable type definitions that ensure consistency across the entire application.

What an EDT gives you

Benefit	Without EDT	With EDT
Label	Set on every field individually	Set once, inherited everywhere
Size / precision	Defined per field	Defined once, consistent
Display format	Manual per form control	Automatic from EDT
Relations	Must create manually	EDT can carry a table reference
Find usages	Search field by field	Find every field using this EDT

Creating an EDT

In Visual Studio: right-click your project → Add New Item → Data Types → Extended Data Type.

// EDT: AxionCustomerId
// Extends: str (String)
// Label: Customer ID
// Size: 20
// String size: 20
// Help text: Unique customer identifier assigned by Axion Dynamics

💡 Scenario: Vik's EDT strategy at Axion Dynamics

Vik Kapoor is building a custom module for Axion Dynamics’ quality inspection system. PM Fatima asks: “We need an Inspection ID on five different tables — inspections, results, photos, defects, and sign-offs.”

Instead of creating a string(15) field on each table with manually matching labels, Vik creates one EDT called AxionInspectionId — string, size 15, label “Inspection ID”. Every table that needs this field uses the same EDT. If Fatima later says “make it 20 characters,” Vik changes the EDT once and all five tables update.

Creating a new table

Table properties that matter for the exam

Property	What It Controls	Common Values
TableGroup	Categorises the table for form data sources	Transaction, WorksheetHeader, WorksheetLine, Group, Main, Parameter, Miscellaneous
CreatedBy / ModifiedBy	Automatically tracks who created/modified a row	Yes / No
CreatedDateTime / ModifiedDateTime	Automatically timestamps row changes	Yes / No
PrimaryIndex	The clustered index used for primary lookups	Usually the surrogate key or natural key
ClusterIndex	Which index is physically clustered	Often same as PrimaryIndex
CacheLookup	How aggressively the runtime caches reads	None, NotInTTS, Found, FoundAndEmpty, EntireTable
SaveDataPerCompany	Whether data is filtered by legal entity (DataAreaId)	Yes (most tables) / No (shared tables)

💡 Exam tip: SaveDataPerCompany

When SaveDataPerCompany = Yes, D365 automatically filters every query by the user’s current legal entity. You never see another company’s data unless you explicitly cross company boundaries with crossCompany in X++.

Exam trap: If a question asks why a custom table shows all companies’ data, check whether SaveDataPerCompany was accidentally set to No.

Table creation in X++ (code-first pattern)

While most table creation happens visually in Visual Studio, understanding the underlying structure helps for exam scenarios:

// Table: AxionInspectionTable
// Fields:
//   InspectionId    (EDT: AxionInspectionId, Mandatory: Yes)
//   Description     (EDT: Description, Mandatory: No)
//   InspectorWorker (EDT: HcmWorkerRecId, Mandatory: Yes)
//   InspectionDate  (EDT: TransDate, Mandatory: Yes)
//   Status          (Enum: AxionInspectionStatus)

// The find method — standard pattern on every table
public static AxionInspectionTable find(
    AxionInspectionId _inspectionId,
    boolean _forUpdate = false)
{
    AxionInspectionTable table;

    if (_inspectionId)
    {
        table.selectForUpdate(_forUpdate);

        select firstonly table
            where table.InspectionId == _inspectionId;
    }

    return table;
}

Question

What is the standard naming convention for a static lookup method on a table?

Click or press Enter to reveal answer

Answer

The static find() method. It takes the natural key as a parameter, an optional _forUpdate boolean, and returns a single record. Example: AxionInspectionTable::find('INS-001'). Nearly every table in D365 F&O follows this pattern.

Click to flip back

Field groups

Field groups organise fields that appear together on forms. Instead of dragging individual fields onto a form, you add a field group — and if the group changes, every form using it updates automatically.

Standard field groups

Field Group	Purpose	When It Appears
AutoSummary	Fields shown in FactBoxes and preview panes	Hovering over a record, FactBox area
AutoLookup	Fields shown in lookup dropdowns	When a user clicks a lookup control
AutoReport	Default fields for auto-generated reports	Quick reports, basic exports
AutoIdentification	Fields that identify a record in logs	Change tracking, alerts

💡 Scenario: Nikhil's lookup problem

Junior dev Nikhil at Axion adds a new Inspection Type table. Users complain: “When I look up an inspection type, I only see the ID — I need the description too.”

Vik shows him: “Add Description to the AutoLookup field group on the table. The form control’s lookup dropdown automatically shows all fields in that group.” Nikhil adds it, rebuilds, and the dropdown now shows both ID and description — no form changes needed.

Table relations (foreign keys)

Relations define how tables connect. In the AOT, a relation specifies:

• Related table — which table this one points to
• Field mapping — which field(s) match between the two tables
• Cardinality — how many records on each side (not enforced at DB level, but documents intent)

Relation example

// On AxionInspectionLine table:
// Relation: AxionInspectionTable
//   AxionInspectionLine.InspectionId == AxionInspectionTable.InspectionId
//   Cardinality: ZeroMore (lines) to ExactlyOne (header)
//   RelatedTableCardinality: ExactlyOne

Delete actions

When a parent record is deleted, what happens to the children?

Delete actions control referential integrity when parent records are removed

Delete Action	Behaviour	When to Use
Cascade	Deletes all child records automatically	Lines that have no meaning without the header (e.g., order lines when deleting an order)
Restricted	Blocks deletion if child records exist	When children must be reviewed before parent deletion (e.g., customer with open invoices)
Cascade + Restricted	Restricted if children exist, cascade if forced	Rare — used for conditional cleanup
None	No action — child records become orphaned	When orphans are acceptable or handled by business logic

Question

What is the difference between Cascade and Restricted delete actions?

Click or press Enter to reveal answer

Answer

Cascade: automatically deletes all child records when the parent is deleted. Restricted: blocks the parent deletion entirely if any child records exist. Restricted is safer — it forces the user to deal with children first.

Click to flip back

💡 Exam tip: delete actions are NOT SQL foreign keys

D365 F&O delete actions look like SQL cascade/restrict rules, but they are enforced by the X++ runtime, not by SQL Server foreign key constraints. The database does not have FK constraints — the application layer handles integrity.

This means: if you bypass the application (direct SQL) you bypass delete actions. The exam may test this distinction.

Indexes

Indexes make queries fast. Without them, SQL Server scans every row (table scan). With the right index, it jumps directly to matching rows.

Clustered vs non-clustered

One clustered index per table — choose wisely based on your most common query pattern

Feature	Clustered Index	Non-Clustered Index
Physical order	Physically sorts the table data on disk	Separate structure that points to table rows
How many per table	Exactly one (or none)	Up to 999
Speed for range queries	Fastest — data is already in order	Good — but requires a bookmark lookup to get remaining columns
Speed for inserts	Can cause page splits if data isn't sequential	Minimal impact on insert order
Set via	Table property: ClusterIndex	Index property: AllowDuplicates = No/Yes
Typical use	Primary key, RecId, or most common sort order	Fields used in WHERE clauses, lookups, joins

Creating an index in the AOT

// Index: InspectionIdx on AxionInspectionTable
// Fields: InspectionId
// AllowDuplicates: No (unique index)
// This index is set as both PrimaryIndex and ClusterIndex on the table

Full Text Indexes

Full Text Indexes enable LIKE and contains searches on large text fields. They are rarely tested on MB-500 but good to know:

• Created on string or memo fields
• Use SQL Server Full-Text Search under the hood
• Set FullTextIndex = Yes on the index property

Question

How many clustered indexes can a table have?

Click or press Enter to reveal answer

Answer

Exactly one (or none). The clustered index determines the physical sort order of data on disk. It's set via the ClusterIndex property on the table. Choose the index that matches your most common query pattern — typically the primary key.

Click to flip back

Extending existing tables

You never modify Microsoft’s tables directly. Instead, create a table extension:

1. In Application Explorer, find the table (e.g., CustTable)
2. Right-click → Create extension
3. A new object appears in your model: CustTable.MyModel
4. Add fields, field groups, relations, indexes, or methods

What you CAN do in a table extension

• ✅ Add new fields
• ✅ Add new field groups
• ✅ Add new relations
• ✅ Add new indexes
• ✅ Add new methods
• ✅ Modify certain properties on existing fields (e.g., label on extended fields)

What you CANNOT do

• ❌ Remove existing fields
• ❌ Change existing field types
• ❌ Remove existing indexes
• ❌ Change the table’s SaveDataPerCompany property
• ❌ Modify existing methods (use event handlers instead — covered in Module 10)

💡 Scenario: Vik extends CustTable

Axion needs a “Preferred Inspector” field on customer records. Vik creates an extension of CustTable in his model, adds a new field AxionPreferredInspectorWorker (EDT: HcmWorkerRecId), and adds it to the AutoSummary field group.

He prefixes the field with Axion to avoid naming collisions with other ISV solutions. This is a best practice — always prefix extension elements with your solution’s namespace.

Question

Why should you prefix custom fields added via table extensions?

Click or press Enter to reveal answer

Answer

To avoid naming collisions with other ISV solutions or future Microsoft updates. If Axion Dynamics adds a field called 'PreferredInspector' and another ISV adds the same name, the build fails. Prefixing with 'Axion' (e.g., AxionPreferredInspector) prevents this.

Click to flip back

CacheLookup strategies

Table caching improves read performance by reducing SQL round-trips.

CacheLookup	Behaviour	Best For
None	No caching — every read hits the database	Transaction tables with frequent changes
NotInTTS	Cache reads outside transactions; bypass cache inside ttsbegin/ttscommit	Tables read often but modified in transactions
Found	Cache successful lookups only	Reference tables where most lookups find a record
FoundAndEmpty	Cache both hits and misses	Reference tables where “not found” is common
EntireTable	Load the entire table into memory	Small parameter/setup tables (< ~128 KB)

💡 Exam tip: EntireTable caching

EntireTable caching loads all rows into the AOS memory. It’s fast but dangerous on large tables — it wastes memory and the cache invalidates whenever any row changes.

Exam rule of thumb: Only use EntireTable on tables with fewer than ~100 rows that rarely change (e.g., parameter tables, number sequences setup). Never on transaction tables.

Question

When should you use EntireTable caching?

Click or press Enter to reveal answer

Answer

Only on small, rarely-changing tables like parameter tables or setup tables (< ~100 rows). The entire table is loaded into AOS memory. If the table is large or changes frequently, it wastes memory and causes constant cache invalidation.

Click to flip back

Putting it all together: table design checklist

When Vik builds a new table at Axion, he follows this checklist:

1. Define EDTs first — reusable types for every field
2. Set table properties — TableGroup, SaveDataPerCompany, CacheLookup, CreatedDateTime/ModifiedDateTime
3. Add fields — using EDTs, set Mandatory where needed
4. Create field groups — AutoSummary, AutoLookup, AutoReport at minimum
5. Define relations — link to parent/reference tables
6. Set delete actions — Cascade for dependent children, Restricted for protected references
7. Create indexes — unique index on natural key, set as PrimaryIndex and ClusterIndex
8. Write the find() method — static lookup by natural key
9. Add to extension — if modifying an existing table, use extension model

Knowledge Check

Vik creates a new AxionInspectionLine table that stores individual inspection measurements. Each line belongs to exactly one AxionInspectionTable header. If an inspection is deleted, lines are meaningless and should be removed. Which delete action should Vik configure on the relation?

ANone — let business logic handle cleanupBRestricted — block deletion if lines existCCascade — automatically delete lines when the header is deletedDCascade + Restricted — restrict first, then cascade if forced

Check Answer

Knowledge Check

Nikhil is extending the standard PurchTable to add a custom field. He creates PurchTable.AxionModel extension and adds a new field called 'InspectionRequired' (NoYesId EDT). What else should he do to ensure the field appears in lookup dropdowns and FactBoxes?

ASet the field's Visible property to YesBAdd the field to the AutoLookup and AutoSummary field groups in the extensionCCreate a new form extension and drag the field onto the formDWrite an event handler on the form's init method to add the field at runtime

Check Answer

Knowledge Check

Axion's AxionInspectionSetup table has 12 rows and is read on every inspection form load but never modified during normal operations. Users report slow form loads. Which CacheLookup setting would best improve performance?

ANone — disable caching to always get fresh dataBNotInTTS — cache only outside transactionsCFound — cache successful lookupsDEntireTable — load all 12 rows into AOS memory

Check Answer

Knowledge Check

A developer creates a new field on CustTable called 'PreferredInspector' without any prefix. Another ISV on the same environment also adds a field called 'PreferredInspector' to CustTable. What happens?

ABoth fields appear side by side on the form — no conflictBThe second field silently overwrites the firstCThe build fails with a duplicate field name errorDThe system automatically renames one field

Check Answer

---

Next up: Views, Queries, and Maps — build reusable data retrieval layers using AOT views, queries, and maps.