D365 F&SCM leveraging table extension framework

Introduction

In most of the implementation projects where I have participated, there is always requirements to extend standard tables where is required to add multiple fields.

Some of the key tables that support the main business processes get loaded with custom fields to support specific business processes.

When the required number of fields to be added to a particular table is big, we can run in some issues.

First, we can get warnings when we add more than ten fields in a particular table extension.

This could be overcome by creating multiple extensions, despite is not the best option.

We can run as well with some SQL restrictions when a table exceeds certain row size depending on the number of fields added and the nature of the field types.

From a point of view of database normalization, it would be better to create a related table to include the new fields that will be related with a foreign key to the standard table.

Despite the creation of this separated table introduces other challenges as records in the new table would need to be handled together with the records in the standard table when we talk about CRUD operations.

The good news is that Microsoft has thought about this and has provided a table extension framework that will help in the implementation of the CRUD operations for the new related table in relation to the standard table.

This framework has been around since AX2012 and continues available in D365F&SCM.

In this post we will review how to leverage the table extension framework to create our custom related table so we can get the advantages for CRUD operations reducing the implementation of custom logic to maintain the records in our custom table using the SysExtensionSerializerMap logic provided by the standard.

There are many samples of this in the standard like the tables below

Create new custom table

Create the new table and add a foreign key relation with the parent table.

In our example we will create a new table to add fields related with purchase order header (PurchTable)?

On the relations node, Right-Click > New > Foreign Key Relation

Setup the relation properties as below:

Cardinality: ZeroOne, indicates that we can have one or none records in the new table related with records in the core table.

Related Table: in our example the related table is the core table PurchTable

Index: select the RecId index of the related table.

On Delete: Cascade, this will ensure that when a record is deleted in the core table, it will be deleted as well in the new custom table.

The value on this property has to be cascade for the relation type Composition

Related Table Cardinality: ExactlyOne, this indicates that for each record in the new table we will have one and only one related record in the core table.

Relationship Type: Composition, this means that the child record cannot meaningfully exist without being related to a specific parent record.?

After setting the properties, a new field will be automatically added to our new table

The field, will have an EDT of RefRecId and will be used to make reference to the RecId of the parent core table.

Set table mapping with SysExtensionSerializerMap

Create a new Map and select SysExtensionSerializerMap as the Map property.

And configure the table mapping connections as below, selecting the Map field to property as the New RefRecId field added.

Adding this mapping will help on the implementation of the CRUD operations for the new child table based on operations in the core table as logic is implemented in the map for this purpose.

We can check the code in this methods to understand better what happens.

As an example, if we check the insertAfterBaseTable method, we can see how it will take care of inserting the record in the child table.

Some of the key methods are the post methods in SysExtensionSerializerMap that ensure insert/update of child record and initialization of relation field from parent core table.

Table index

Create an index and set the properties as below

In table properties set this new index as Primary key

After this, we can proceed to add fields, field groups and set all remaining properties for the table following best practices.

Ensure post methods are used in insert or update of parent core table

Most of core tables that have the Insert or Update methods implemented will have a call to the post method of the map.

If not, we would need to add code to call the post methods in the parent core table.

In our case PurchTable has them on insert and Update as below calling them after super

Parent table should have as well a PackedExtensions field related with a mapping.

PurchTable has it, if the table does not have it, would need to be added.

This field will be a container of type SysExtensionSerializerPackedContainer

Methods in parent table

Pack method to store child table

Method to retrieve the child record

Methods in child table

We will add a set of methods in the child table as below

get method to retrieve base parent table & find method by parent table RecId

Insert & update methods need to be modified in child table to only apply if we have parent table RecId.

Add DS to form

Once the table is set, we will add DS to a form extension.

In my case I will add it to PurchTable.

The DS needs to be added joined to the parent core table with a Link Type of OuterJoin

Outer join form data source link type will return all parent records and matched child records. It will return all rows in the parent table.

After adding the DS, we need to extend the parent table form DS adding code to insert if does not exist in child

Enjoy!