How to sort file data before inserting to target table in DMF in D365 FO

Problem: Many a times we receive requirements where we need to upload a file using DMF with a different order sequence. For example a file has data as below:

client wants that this file should get import with ID column in ascending /descending order.

Solution: To achieve this need to customize data entity as below:

1. Add a field integer field "ImportSequence" to staging table (GSCashStaging table).

2. Add below methods to staging table:

public static container orderByFieldListForImportExport(DMFOperationType _operationType = DMFOperationType::All)

{ container fieldList = conNull();
switch (_operationType)
    {
case DMFOperationType::Import, DMFOperationType::Export:
  fieldList += fieldStr(GSCashStaging, ImportSequence);
     break;

default:
    fieldList += fieldStr(Common, RecId);
    break;
    }

return [fieldList, sortOrder];

 

public static void resolveImportSequence(DMFDefinitionGroupExecution _dmfDefinitionGroup)
{
    GSCashStaging    staging;
    int   importSequence;

    ttsbegin;

    while select forupdate staging
order by staging.ID
  where staging.DefinitionGroup ==_dmfDefinitionGroup.DefinitionGroup     && stagingTable.ExecutionId == _dmfDefinitionGroup.ExecutionId        && stagingTable.TransferStatus == DMFTransferStatus::NotStarted                

{
    importSequence++;
    stagingTable.ImportSequence =importSequence;
}
       
    ttscommit;
} 

3. Add below method to data entity

public static void postGetStagingData(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)

{

GSCashStaging::resolveImportSequence(_dmfDefinitionGroupExecution);

}

4. Rebuild your project and sync it. It will solve the issue.