D365 F&O. Sales order creation via composite data entity

Disclaimer

This topic is explanation of my thoughts and feelings about Data Management Framework, composite entities, and usage of composite entities for inbound integrations. All the conclusions were made only on my personal experience. Topic describes my ‘fight’ on a way of covering task requirements. It is much less technical than subjective. It does not contain a walk-through guideline, but it highlights not obvious points to be checked during Data Entities and Composite Data Entities creation.

I kindly ask you not being shy and add your thoughts in comments!

Requirements

You must have knowledge about D365F&O Data Management Framework. Understand Data Entity concept and believe in magic.

Problem

About a month ago, I received an Inbound Integration Task. The aim was to receive information about sales order and sales order lines from external system. External system may send us a document to be attached to Sales Order header for specific orders.

Despite the fact, that we have stable solutions both Sales Order Import and Attachments Import (files/notes/links) via Web Endpoint (custom web-service) in several different projects, the brand-new integration should be done via Data Management Framework (DMF) with composite data entity. Solution should utilize or use as an example a standard “Sales orders with attachments composite V2” (SalesOrderWithDocumentAttachementV2Entity).

Annotation

This passage contains summary of the topic. I made all these conclusions based on my personal experience of solving the task by composite data entity. Some statements from the list are relevant for regular data entity in case of performing a DMF import using staging table.

1. The step 0 is a data pumping from source file to staging table. It is performed via object of the Microsoft.Dynamics.AX.Framework.Tools.DMF.DMFData.DMFEntityContract class which implements IDTSComponentMetaData100 Interface from Microsoft.SqlServer.Dts.Pipeline.Wrapper namespace.
2. The mapping for the import project reflects mapping to a staging table, not to an entity.
3. Generation of “Number Sequence controlled values” (like sales id, inventory transaction id, voucher number, journal number, etc.) must be defined on entity mapping level in case when this value (sales id) is a part of entity primary key.
4. The update is unavailable once primary key filed is mapped to “Auto. Import will always create new record.
5. The default value of a field can be defined on project mapping level (DMF only).
6. The blank values of a field on a staging table can be defined on project mapping level.
7. The automatic generation of GUID on a staging table level is not supported.
8. The SQL API object (implements IDTSComponentMetaData100 Interface) uses a human readable name (composite entity label) for composite entities prefix. During data pumping system creates as many objects as many entities are inside the composite one. Name follows next pattern: [Composite entity lable]_[AOTComponentEntityName]. Composite entity label can contain spaces, but it cannot contain dot (‘.’).
9. Data project mapping should be properly defined in “PackageHeader.xml” for data package import.
10. In case of Composite Entity, the “postTargetProcess” method is called for Root data source only.

Solution

Phase 1: denial

I have started this task with a denial because of the two reasons. The first one is that we have a working web-service for sales order import in another project. The service is stable and quick. It is doing what it should and can be adjusted quickly. The only issue we’ve received for more than two years was expired API-key. I tried several times to convince to utilize this custom web-endpoint instead of creating a brand-new solution with data entities. Unfortunately for me, I was not able to prove my point.

The second reason is that I have played with composite data entities back in 2019. And it was not a positive experience at all. The outbound (export) worked fine. But inbound… Inbound made me crazy. Long story short, I didn’t manage to perform any successful import back then. Because of that, I wouldn't bet on successful implementation of this requirements.

Because of that, the first goal for me was to perform a successful import using the standard “Sales orders with attachments composite V2” entity. I think, I wouldn’t surprise you by saying “I didn’t make it”: export works fine from the first try, but import does not.

I received the problems with:

• Sales Order ID (it would not come from external system, and I had no idea how to use standard number sequence).
• Inventory transactions ID.
• Sales Order attachment.

By that point, we agreed with solution architect, that possibility of sending attachments together with order is in a low priority. Because of that, I switched from SalesOrderWithDocumentAttachementV2Entity to SalesOrderV3Entity.

And, as I know now, it was a great move. It appeared that standard SalesOrderWithDocumentAttachementV2Entity entity anyway cannot import attachments in case when a brand-new order coming (e.g. Sales Order Id is not defined for attachment record).

Phase 2: anger

I will be honest. Anger is not a phase for this task. It is a constant companion. Sometimes only anger kept me working on this task. Because of that, let’s go straight to the next chapter.

Phase 3: googling

After almost 3 hours of different unsuccessful import runs for the SalesOrderV3Entity I started to google. I cannot say that it was problematic to found similar questions. And almost all of them have a verified answer which is a link to the setup topic  Mukesh Hirwani - Dynamics Ax: Dynamics 365 Operations | Composite data entity – Sales order import (Only works with XML) (mukesh-ax.blogspot.com)

But it was extremely hard to me to understand, why do we must perform mapping adjustments?

Corresponding Sales Order data entity has an implementation of the initValue method. This method contains a code to force Sales Order Number initialization by usage of corresponding Number Sequence.

public void initValue()
{
        if (!this.skipNumberSequenceCheck())
        {
            NumberSeqRecordFieldHandler::enableNumberSequenceControlForField(

                this, fieldNum(SalesOrderHeaderV2Entity, SalesOrderNumber), SalesParameters::numRefSalesId());
        }

        super();
}        

During debug session I saw , that value was assigned. In addition, I can confirm, that call sequence from the D365 – Data Entity Method Call Sequence – AXAtoZ (wordpress.com) topic is correct. The first method to be performed is initValue. Same is relevant for Inventory transactions ID for Sales Order Lines.

But why is it still empty in staging and then it was passed empty to mapEntityToDataSource method? Why does it populated once I used mapping adjustments? How does it work?

Unfortunately, I was already far behind task schedule. Because of that, I decided to left all these questions unanswered and start an actual development.

Phase 4: development

I have compared field list from requirements with field list of the standard entities. I realized that only 10% form the standard field list would be in use. Also, additional post processing will be needed (yes, a both mapEntityDataSource and postTargetProcess methods will be involved).

I made a pros and cons list of extending existing entities and making copies of those. The winer was obvious. I decided to make copies of standard SalesOrderHeaderV2Entity, SalesOrderLineV2Entity and SalesOrderHeaderDocumentAttachmentV2Entity.

I cannot say that it was extremely hard to make copies, to delete unused fields and data sources, to clean-up code. Because of that, an actual development took me about 10 hours. The result was 3 regular data entities with corresponding staging tables, security objects, etc. And, of course, a composite data entity.

The composite entity itself contains a root Sales Order Header entity with two children: Sales Line entity and Sales Order Header Attachment entity. The label of the composite entity and each of its components starts with project prefix. The entity purpose was separeated from prefix by dot. For example, Sales Order Header entity had next label “[prefix]. Webshop Sales Order”. It was made to simplify search during DMF setups. It appeared, that this approach complicated my life a lot.

Project compiled without issues. Data base synchronization ended successfully. All my data entities appeared in DMF entity list. It’s time to check the result!

Phase 5: denial (again)

Let’s agree, that I will share only important (from my point of view) screenshots and code parts. I kindly ask you to read both topics from phase 3 and have access to D365F&O UI and development environment. Following this advice will simplify understanding of my actions.

As usual, I have started with testing an export of my composite entity. Somehow, it didn’t work. Unfortunately, I was so mad that I forgot to save the error I have received.

“Keep calm and try with export of components separately” I said to myself. Each entity worked well. I decided to google problem. Several times I saw the comment, that there is a limitation on a length of a component’s name. The longest one was an attachment entity. To check if it was a right track, I have simply deleted the attachment entity form the composite. Export worked!

I changed attachment entity name to a shorter version. After this adjustment, a composite entity worked. I exported a single sales order (by adding a filter).

However, the result was not the same to the standard entity. The export file contained information about attachments, but file itself was missing. In other words, the expected result should be a data package, but I got a single file. Due to low priority of the attachments import I decided to solve this problem later.

At that point, I thought, that I was ready to perform the import. I created an import project based on the exported data. I modified mapping. I prepared a data to be imported. However, execution failed with DMF013 error. I didn’t analyze the error deeply at the beginning. On of the reasons was the documentation (Data management error descriptions and known limitations - Finance & Operations | Dynamics 365 | Microsoft Learn). List of errors starts with DMF021.

Phase 6: magic outside of Hogwarts

I decided to perform testing of the entity components separately.

First things first: the base for the import is a Sales Order header. Because of that, I started with Sales Order creation. I exported one random sales order. I modified the result by removing sales order number (Sales Order Id). I created an import project based on this file. Import failed. But it failed on moving data to target with “Sales id cannot be blank” error. But why? I have an exact copy of the standard entity code. I have marked “Sales Order Number” as Auto in mapping. What did I miss?

I have started debugging. I saw, that initValue assigned a next Sales Id to the field, but on mapEntityDataSource sales order number is blank. Staging table does not have a value too!

Probably, the good idea was to stop and think. Unfortunately, I lost the patience. I performed import repeatedly, without any changes. I performed import via standard entity. It worked. I performed import via custom entity. It didn’t work. I was trying to find the difference, through debug. And it was a mistake. I have been missing one important thing: Standard staging table had a Sales Order Number value, and my staging did not.

Once I spotted this difference, I started to compare staging tables. And I found the reason! The EDT of the SalesOrderNumber field. The standard staging table has a SalesId. My regenerated by standard function staging table had a SalesIdBase.

This fact has closed a gap in my knowledge. It appeared that prior doing anything with Data Entity, DMF performing “data pumping”, e.g. inbound file is converted into staging table outside the Dynamics core. Outside for me means that no X++ is involved. In addition, I don’t know how it exactly works, but “data pumping” from file to staging can resolve Dynamics number sequence dependency.

Once I have changed the EDT and performed build with data base synchronization, my entity started to work!

At that point, I was able to make next conclusions.

1. The initValue method has no big effect in case of DMF import (physical fields). Staging table is a main source of the data even though it does not have a value for a field (I will explore, that this statement is not 100% accurate later)
2. There is a magic inside the “black box”. Correct EDT on staging table is responsible for automatic value generation. However, I didn't try to investigate the mechanism

With these discoveries, I moved forward to the Sales Order Line entity. This time I started with comparing staging tables for two fields: InventoryLotId and LineCreationSequenceNumber. Lucky me, regeneration of the staging table didn’t change EDT on those fields. Because of that, I just changed mappings for this fields to Auto. I performed an import for newly created sales order. It works from first try! Even post processing on postTargetProcess.

Saying that “I was happy” doesn't describe the level of my satisfaction. But then cruel reality stroke back. Requirements said that line number might not come. Because of that I have tried to import lines to another sales order, but with empty Line Number field.

It ended up with staging to target error related to Line Number. I checked staging table. Both lines (yes, I was testing on 2 lines only) had a 0 value for the line number field. However, now I am experienced enough to “make advance setups”. I marked “Ignore blank values” hoping, that insertEntityDataSource will work as per expectations. To be honest, expectations was not so big. Code contains initialization of the line number in case when it was not set on mapEntityToDataSource

public boolean insertEntityDataSource(DataEntityRuntimeContext entityCtx, DataEntityDataSourceRuntimeContext dataSourceCtx)
{
        switch (_dataSourceCtx.name())
        {

            case dataEntityDataSourceStr(PIBWebshopSalesOrderLineEntity, SalesLine):
                SalesLine salesLine = _dataSourceCtx.getBuffer();
                salesLine = this.setProjectFields(salesLine);
                salesLine.LineNum = salesLine.LineNum ? salesLine.LineNum : SalesLine::lastLineNum(this.SalesOrderNumber) + 1; 

                this.initializeDefaultInventDimForDirectDelivery(salesLine);

...        

I managed to perform import of the sales lines! That brought me the hope in a successful implementation of the task.

At that point, I was able to make adjust my summary list with:

1. It is possible to ignore empty values from staging by mapping (but not for the primary key)

I decided to check the attachments entity before coming back to the composite one. As you can remember, export from my entity did not prepare a package and did not export attached file. Lucky me, I had a task to import attachments (files mainly) from legacy Ax2012 to D365F&O back in 2019 (during an upgrade project). That time we managed to utilize standard attachments entity. However, it was not so easy to prepare all packages. Because of that, it was decided to implement “straight forward” file pumping from intermediate BLOB-storage. But I saved the guideline for utilizing an attachments entity.

Following the guidance from past, I prepared standard attachments export project. I performed an export. I got an expected result.

I did same actions for my custom entity (which was a 1 to 1 copy of standard one). The export file was identical, but standard export prepared a data package with manifest, mapping file and folder with attachments.

I started to compare entities but didn’t find any differences. Then I investigated export result (files) deeply. I found a strange field called AttachmentFileName. I went to the project mappings. I saw this field in the mappings of the standard entity, but not for my entity.

I checked standard entity from AOT. This field does not exist. I have tried to add it from the mapping form, but it doesn’t allow me. I compared staging tables and found, that standard one has an extra field.

I have added this field to my staging table. Apparently, it helped to achieve the result. Entity has started to work both directions. However, I faced a magic again.

I could not find any reference (cross-reference from code) to the field name. I could not find any mentioning of this field. I don’t understand how it works. All I know that once I have added this field data project automatically set flag “generate package” to Yes.

I played with this entity trying to understand how to import Files and Notes properly. I was morally prepared for illogical behavior due to previous steps. Because of that I was almost calm and nothing was able to impress me.

The positive output was an extension of my knowledge:

1. The DMF mapping is done for the Staging Table, not for the entity.
2. Staging population is a step 0.
3. Staging population is done outside X++ code flow.
4. We must send a primary key value or use option “Auto”.
5. GUID cannot be autogenerated.

By that time all components of composite entity were ready. I still have had “bad feelings” coming to composite entity tests, but there was no longer an option to step back from this implementation. I had come too far to just give up.

Phase 7: the light at the end of the tunnel

I performed an export project for composite entity once again. It was my “base” for further import attempts. I created a brand-new import project from the data package I received. I modified mapping for each component. Unfortunately, I once again received DMF013 error.

I decided to perform an impot via SalesOrderWithDocumentAttachementV2Entity. I created an import project based on previously exported data. I modified file the way that it should create an order with two lines. Also, I added a file to be imported as a Sales Order Header attachment. I was not surprised when the result of the import didn’t meet expectations.

SalesOrderWithDocumentAttachementV2Entity has created a new order with all lines I had expected to see. But attachment was not imported. I performed several more imports. I tried to attach Free Text (Notes), URL and a File. Nothing happened.

However, it was not so useless. I realized that I could modify mapping (Auto creation of the Sales Order ID for example) in the PackageHeader.xml. Most probably, it is not a discovery for you, but I didn’t work with data packages much. Because of that, it was a step forward for me.

I decided to move forward. I should make custom entity work rather than fixing standard entity attachments import.

I compared my composite entity with standard one. I didn’t find any major differences that might be crucial for the execution. The only one option left - return to debugging.

After several runs, I figured out that error comes from IDTSComponentMetaData100 inheritor. It failed on passing the Name state (variable) value. I saw that the value is a [Human Readable Composite Entity Name]_[AOTComponentEntitytName]. For example, a Sales Order Header entity of the SalesOrderWithDocumentAttachementV2Entity will have next name “Sales orders with attachments composite V2_SalesOrderHeader”.

I checked documentation of the IDTSComponentMetaData100. I didn’t find any restrictions for the Name value. I tried to make names of my entities shorter. Didn’t work. I tried to remove spaces in Composite Entity label. It did not help too. I tried to remove components from entity. Still negative result. Once again during implementation, I was one step away from saying “It is impossible”. However, it was the end of the day. I turned my laptop off and went home.

At home I realized that my composite entity label contains a “dot” (literally a '.' sign) in it. I added a “prefix” and a “dot” separator to simplify search of the entities during export project creation. It was only a guess, but next day first thing I did was removing '.' from entity label. It appeared that '.' is forbidden. Probably, due to some further conversions of that Name.

Finally, I managed to create a proper data package and perfrom a successful import! However, the result still didn't match requirements.

The first issue was that post processing (postTargetProcess) for the Sales Order Lines entity didn’t work. As you can remember, it worked fine during import via my custom entity separately.

I had to use debug again. I set a breakpoint to postTargetProcess of the Sales Line . It was not hit. However, I was able to see an Infolog message, that post processing was triggered by the system. But it was performed only for Sales Order Header entity. I got a theory, that post processing is called only for root entity. I created the empty postTargetProcess (super() call only) for Sales Order Header entity to prove it. I put breakpoint here and started debugging once again. That time system hit the breakpoint. I didn’t have enough strength to continue investigation. So, I simply copy-pasted postTargetProcess from Sales Order Lines entity. It solved the post processing problem.

The second issue was that entity didn’t import attachments. Lucky me, the last point didn’t require debugging and didn’t take much time. Attachment entity is connected to Sales Order Header entity by the Sales Order Id (same as the Sales Line Entity). I checked properties of the Sales Order field both attachment and lines entities. The attachment entity had a mandatory “Yes” for Sales Order Number, but Sales Order Lines had a property “No”. I changed value of the mandatory property to “No:”. That small adjustment solved my problem. Import of the attachments for a new sales order started to work.

I finally had a working composite entity which covers all requirements. But it didn’t bring me a satisfaction. To be honest, this task only increased my confidence in statement “do not use DMF for any import that more complex than update of one field”.

Phase 7 extended my knowledge with next statements,

1. The SQL API object (implements IDTSComponentMetaData100 Interface) uses a human readable name (composite entity label) as a “prefix” for its components. Seems that during data pumping system creates as many IDTSComponentMetaData100 objects as many entities the composite one consist of. Name follows next pattern: [Composite entity lable]_[AOTComponentEntityName]. Composite entity label can contain spaces, but it cannot contain dot ‘.’ and, probably, some other characters.
2. Data project mapping can be defined in PackageHeader.xml to simplify import via data package.
3. In case of Composite Entity, the postTargetProcess method is called for root data source only.

Summary

I believe that a valuable technical summary can be found under annotation passage. There I would like to place my private opinion on Data Management Framework and Data Entity.

Data Management Framework (DMF) should be a first choice for next types of data integration:

• Outbound (export) file based (non limited) or oData (size limits) integration.
• Simple inbound (import) file based (I don’t have an experience of oData) integration, like update status fields without complex logic. An as-is (data pumping) integration with legacy system (except header-line documents).
• Complex inbound integrations can be covered with DMF. However, the best approach is doing those through "shadow" tables. E.g. Data Entity is built for custom queue (“shadow”) table, which later can be processed into target table (tables). However, such approach requires much more effort, mainly for the not directly related to the integration parts.

P.S.

It appeared that external system was not able to prepare a proper data package with attachment export. Not for the composite entity, not for the Attachments stand alone import.

The best run we had was an Sales Order creation with "Note" type attachments (free-text). As a result, the decision to utilize a custom Web endpoint was made.