Import through Excel in D365 F&O (X++):

 Code for Importing data  through Excel in D365 F&O:

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

using System.IO;

class ImportCustomerData

{

    public void run()

    {

        ttsbegin;

        this.updateDailyAttendance();

        ttscommit;

    }

    void updateDailyAttendance()

    {

        System.IO.Stream                      stream;

        ExcelSpreadsheetName                  sheeet;

        FileUploadBuild                       fileUpload;

        DialogGroup                           dlgUploadGroup;

        FileUploadBuild                       fileUploadBuild;

        FormBuildControl                      formBuildControl;

        CustTable                             CustTable, insertCustTable;

        COMVariantType                        type;

        Dialog                                dialog = new Dialog("Customers Import");

        dlgUploadGroup          = dialog.addGroup("@SYS54759");

        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());

        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted('.xlsx');

        str COMVariant2Str(COMVariant _cv)

        {

            switch (_cv.variantType())

            {

                case COMVariantType::VT_BSTR:

                    return _cv.bStr();

                case COMVariantType::VT_EMPTY:

                    return '';

                default:

                    throw error(strfmt("@SYS26908", _cv.variantType()));

            }

        }

 

        if (dialog.run() && dialog.closedOk())

        {

            FileUpload fileUploadControl                      = dialog.formRun().control(dialog.formRun().controlId('Upload'));

            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())

            {

                stream = fileUploadResult.openResult();

                using (ExcelPackage Package = new ExcelPackage(stream))

                {

                    int                         rowCount, i,columncount,j;

                    Package.Load(stream);

                    ExcelWorksheet              worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);

                    OfficeOpenXml.ExcelRange    range       = worksheet.Cells;

                    rowCount                                = (worksheet.Dimension.End.Row) - (worksheet.Dimension.Start.Row)  + 1;

                    columncount                             = (worksheet.Dimension.End.Column);

 

                    for (i = 2; i<= rowCount; i++)

                    {

                        CustAccount               custId;

                        TransDate                 WorkingDate;

                        custId                    = (range.get_Item(i, 1).value);

                        //WorkingDate               = str2Date((range.get_Item(i, 2).value),123);

                        select forupdate CustTable

                            where CustTable.AccountNum         ==  custId;

                            //&& CustTable.WorkingDate       ==  WorkingDate;

                        if(CustTable)     //if record already exists update it

                        {

                            CustTable.selectForUpdate(true);

                            CustTable.Currency                         = any2Str(range.get_Item(i, 3).value);

                            CustTable.CustGroup                        = any2Str(range.get_Item(i, 4).value);

                            //CustTable.InvoiceAccount                   = any2Real(range.get_Item(i, 5).value);

                            ttsbegin;

                            CustTable.update();

                            ttscommit;

                        }

                        Else      //insert the new record

                        {

                            insertCustTable.AccountNum                         = (range.get_Item(i, 1).value);

                            //insertCustTable.WorkingDate               = str2Date((range.get_Item(i, 2).value),123);

                            insertCustTable.Currency                         = any2Str(range.get_Item(i, 3).value);

                            insertCustTable.CustGroup                       = any2Str(range.get_Item(i, 4).value);

                            //insertCustTable.OT                                = any2Real(range.get_Item(i, 5).value);

                            insertCustTable.insert();

                        }

                    }

                }

            }

            else

            {

                error("Exception error");

            }

        }

    }

    public static void main (Args args)

    {

        ImportCustomerData              ImportCustomerData;

        ImportCustomerData              = new ImportCustomerData ();

        ImportCustomerData.run();

    }

}

Process of importing data by data project using JSON file through post man tool:

 

Process of importing data by data project using JSON file through post man tool:

Data files: 

Crate 3 files  as

a. JSON-XML.xslt

b. Categorydata : Specify data for entity fields 

<data>{

  "Document": {

    "TBWEcoResCategoryEntity": [

      {

        "CategoryName":"TestRet4", 

        "CategoryCode":"testRet4-12", 

        "ParentCategoryName":"TestRet3", 

        "CategoryHierarchyName":"TBW_BVFOCUS",

        "LegalEntity":"1000",

        "Level":5

      }

          ]

  }

}</data>

c. CategoryMapping : Entity fields mapping

<?xml version="1.0" encoding="UTF-8"?>

<Document>

   <TBWEcoResCategoryEntity>

      <CategoryName></CategoryName>

      <CategoryCode></CategoryCode>

      <ParentCategoryName></ParentCategoryName>

      <CategoryHierarchyName></CategoryHierarchyName>

      <LegalEntity></LegalEntity>

      <Level></Level>

   </TBWEcoResCategoryEntity>

</Document>

Setting configurations in D365 - FinOps:

1. Go to path: system administration > Data management > Import 

2. Create new data project with entity (Category) and file format will be JSON as below 

 

3. Upload the mapping file (CategoryMapping) which is created in Data files section c.Category mapping.

4. Click on view map then check the mapping details for fields and then go to transformation tab. Here we need to upload the JSON-XM.xslt file which was created previously in DataFiles section C. JSON-XML.xslt

 

5. And then click ApplyTransform button and close the form.

6. And move to data project form action pane Manage tab > manage recurring data jobs

 

7. Here in this form give the name and ID field will generate automatically.

Application id needs to give from post man tool client id data

Enabled field as Yes.

 

Click on set process recurrence button and set recurrence as 1 min

And enable is recurring job field.

8. Manage message button is for checking the status and application ids which is send from post man tool.

 

Message id is generated from post man tool and status field shows the queued or processed or failed. For checking staging data click on view execution details.

Setting post man tool configuration as 

 

Resource: dev box url

1. Generate bearer token  by using AddGetAccessToken

 

2. Create collection for product hierarchy set the below properties as 

Entity value: category (Data entity label)

Activity id: Data project id (which is created in section setting configuration in D365-FinOps point 7).

 

3. Attach the categoryData file which is created in section Datafiles b. in body tab of post man tool

 

4. Now click send button then id will create in post man tool this same id will reflects in data project manage ques showing status as queued.

5. After that status will changed as processing, if data imported successfully status as processed if any errors status as failed.

Open in Microsoft office -> Open in Excel customizations in d365:

Open in Microsoft office -> Open in Excel customizations in d365:

Best example : 

Inventory management > Journal entries > Item > Movement form > Lines > Open in excel > Inventory movement journal header and lines

Here in this form main data source is InventJournalTable and join data source is InventJournalTrans

If your creating entity with InventJournalTable then its name come automatically in open in excel lookup

But if your creating entity other than root datasource like inventjournal trans  table then for showing lookup in that form we need to do some changes

1.       Form will extended with OfficeIGeneratedWorkbookCustomExporter

Ex : public final class InventJournalMovement extends FormRun

                                         implements OfficeIGeneratedWorkbookCustomExporter

2.       Here in class declaration we need to declase one variable as

const str CustomExportLinesToExcelOptionId = 'LinesCustom';

3.       Need to add new method in  that form methods(This is for shwing our entity name in open in excel lookup)

Ex:

public void customizeMenuOptions(OfficeMenuOptions _menuOptions)

    {

        TableName entityName = tableStr(InventInventoryMovementJournalEntryEntity);

        List exportOptions = _menuOptions.customMenuItems();

        var exportOption = OfficeGeneratedExportMenuItem::construct(entityName, CustomExportLinesToExcelOptionId);

        exportOption.setDisplayNameWithDataEntity();

        exportOptions.addEnd(exportOption);

    }

4.       Exporting and importing need to add one more form method

Ex :

public ExportToExcelDataEntityContext getDataEntityContext(OfficeGeneratedExportMenuItem _menuItem)

    {

        ExportToExcelDataEntityContext context = null;

        if (_menuItem.id() == CustomExportLinesToExcelOptionId)

        {

            TableName entityName = tableStr(InventInventoryMovementJournalEntryEntity);

            IdentifierName fieldGroupName = tableFieldGroupStr(InventInventoryMovementJournalEntryEntity, AutoReport);

            context = ExportToExcelDataEntityContext::construct(entityName, fieldGroupName);

            ExportToExcelDataEntityInfo entity = context.findEntityByName(entityName);

            if (entity)

            {

                ExportToExcelFilterTreeBuilder filterBuilder = new ExportToExcelFilterTreeBuilder(entityName);

                var filter = filterBuilder.and(

                    filterBuilder.companyFilter(),

                    filterBuilder.areEqual(fieldStr(InventInventoryMovementJournalEntryEntity, JournalNumber), InventJournalTable.JournalId));

                entity.filter(filter);

            }

        }

        return context;

    }

5.       If publish button is not enabled check the entiry auto lookup field group fields data

Auto lookup filed group fileds are same as key fields (Index)

Getting email parameters and email settings while export

Getting email parameters and email settings while export :


If print medium type is email when you are exporting data it will store data in to PrintJobSettings field which is container data type in PrintMgmtSettings table.
getting the data from below field you need to use  pass PrintJobSettings field value as a parameter for  srsPrintDestinationSettings.unpack(this.PrintJobSettings) class

SRSPrintDestinationSettings  class having all the email type settings


public void postLoad()
    {
        super();

        SRSPrintDestinationSettings srsPrintDestinationSettings = new SRSPrintDestinationSettings();
        srsPrintDestinationSettings.unpack(this.PrintJobSettings);
        if (srsPrintDestinationSettings.printMediumType() == SRSPrintMediumType::Email)
        {
            this.skipDataSourceValidateField(true);
            this.skipDataSourceValidateWrite(true);

            this.PrintMediumType     = SRSPrintMediumType::Email;
            //this.PrintMediumType   = srsPrintDestinationSettings.printMediumType();
            this.PrinterName         = srsPrintDestinationSettings.printerName();
            this.PrinterPageSettings = srsPrintDestinationSettings.printerPageSettings();
            this.landscape           = srsPrintDestinationSettings.landscape();
            this.Caption             = srsPrintDestinationSettings.caption();
            this.PrinterStatus       = srsPrintDestinationSettings.printerStatus();
            this.PrinterType         = srsPrintDestinationSettings.printerType();
            this.PrinterWhere        = srsPrintDestinationSettings.printerWhere();
            this.PrinterComment      = srsPrintDestinationSettings.printerComment();
            this.PrintAllPages       = srsPrintDestinationSettings.printAllPages();
            this.FromPage            = srsPrintDestinationSettings.fromPage();
            this.ToPage              = srsPrintDestinationSettings.toPage();
            //this.NumberOfCopies    = srsPrintDestinationSettings.numberOfCopies();
            this.EmailTo             = srsPrintDestinationSettings.emailTo();
            this.EmailCC             = srsPrintDestinationSettings.emailCc();
            this.EmailSubject        = srsPrintDestinationSettings.emailSubject();
            this.EmailAttachmentFileFormat      = srsPrintDestinationSettings.emailAttachmentFileFormat();
            this.EmailAttachmentImageFileFormat = srsPrintDestinationSettings.emailAttachmentImageFileFormat();
            this.FileName            = srsPrintDestinationSettings.fileName();
            this.FileFormat          = srsPrintDestinationSettings.fileFormat();
            this.ImageFileFormat     = srsPrintDestinationSettings.imageFileFormat();
            this.OverwriteFile       = srsPrintDestinationSettings.overwriteFile();
            //this.OrintToArchive      = srsPrintDestinationSettings.//conPeek(con,27);
            this.OverridePageSettings = srsPrintDestinationSettings.overridePageSettings();
            this.OverridePrintContractSettings = srsPrintDestinationSettings.overridePrintContractSettings();
            //this.OverwriteFileIsSet = srsPrintDestinationSettings.//conPeek(con,30);
            this.Collate             = srsPrintDestinationSettings.collate();
            this.Orientation         = srsPrintDestinationSettings.orientation();
            this.PrintOnBothSides    = srsPrintDestinationSettings.printOnBothSides();
            //this.SendToPrinterAsPdf  = srsPrintDestinationSettings.//conPeek(con,34);
           
            //container con1,con   = this.PrintJobSettings;
            //this.Obj1 = conPeek(con,1);//'fbhEmailBodyCustom';
            //this.Obj2 = conPeek(con,2);//srsPrintDestinationSettings.fbhEmailBody();
            //this.Obj3 = conPeek(con,3);
            //srsPrintDestinationSettings.printMedium().unpack(srsPrintDestinationSettings.printMedium());
            //con1 = conNull();
            //con1 = conPeek(con,4);
            //this.Obj41 = conPeek(con1,1);
            //this.Obj42 = conPeek(con1,2);
            //this.Obj43 = conPeek(con1,3);
            //this.Obj44 = conPeek(con1,4);
         
            PrintMgmtDocInstance docInstance;

            select firstonly docInstance
                where docInstance.RecId == this.ParentId;

            this.ReferencedRecId    = docInstance.ReferencedRecId;
            this.ReferencedTableId  = docInstance.ReferencedTableId;
            this.NodeType           = docInstance.NodeType;
            this.DocumentType       = docInstance.DocumentType;
            this.DocumentPriorityId = docInstance.PriorityId;

            if (this.ReportFormat)
            {
                PrintMgmtReportFormat reportFormat;
                select firstonly reportFormat
                    where reportFormat.RecId == this.ReportFormat;
                if (reportFormat.RecId)
                {
                    this.PRFCountryRegionId = reportFormat.CountryRegionId;
                    this.PRFDescription     = reportFormat.Description;
                    this.PRFDocumentType    = reportFormat.DocumentType;
                    this.PRFName            = reportFormat.Name;
                    this.PRFSystem          = reportFormat.System;
                }
            }
        }
       
    }

Skip validataion methods in D365 in DMF

Skip validataion methods in D365 in DMF: 



for skipping table validation methods when import and export the entity we need to write in to below methods. : persistEntity()

public void persistEntity(DataEntityRuntimeContext _entityCtx)
    {
        this.skipDataSourceValidateField(true);
        this.skipDataSourceValidateWrite(true);
        
        this.skipDataSourceValidateField(fieldNum(FBHPrintMgmtSettingsEntity, PrintSettingDescription), true);
        this.skipDataSourceValidateField(fieldNum(PrintMgmtSettings, Description), true);
        
        super(_entityCtx);
    }

here you can skip the table vaidate write and validate field methods

Getting container value data while import and export in D365

Getting container value data while import and export in d365: 

I have 1 field in my table which is container data type. I need to import/export that field data  through data entity.

Example:
Table :PrintMgmtSettings
Field : QueryPacked , EDT :  PackedQueryRun (container data type)
Here in this field data will store in binary code , we need to retrieve that binary code while import and export.

Created:
data entity : FBHPrintMgmtSettingsEntity
Data source : PrintMgmtSettings
Added all fields from data source to entity

Staging table for data entity : FBHPrintMgmtSettingsStaging
Added one extra field : 
Field : QueryPackedFileName
EDT: Filename

Added one method in staging table :

    public static container getFieldsToBeConvertedToFile()
    {
        return [  ['QueryPacked', 'QueryPacked', 'QueryPackedFileName', true]  ];

    }

Here QueryPacked is our original table field and QueryPackedFileName added one extra field in staging table. It will store the container QueryPacked  field data in one file for each record.

Now add one method in entity : postTargetProcess
this method will update the querypacked fild data from staging.


 public static void postTargetProcess(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
    {
        FBHPrintMgmtSettingsStaging   staging;
        PrintMgmtSettings             printMgmtSettings;

        printMgmtSettings.skipAosValidation(true);
        printMgmtSettings.skipDataMethods(true);

        while select QueryPacked,PrintJobSettings, ParentId,PrintSettingPriorityId, RecId from staging
            where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup &&
                staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId
        {
            select firstonly forupdate QueryPacked, RecId from printMgmtSettings
                where printMgmtSettings.ParentId == staging.ParentId
                && printMgmtSettings.PriorityId == staging.PrintSettingPriorityId;
            if (printMgmtSettings)
            {
                printMgmtSettings.QueryPacked = staging.QueryPacked;
                ttsbegin;
                printMgmtSettings.doupdate();
                ttscommit;
            }
        }
    }



when you exporting entity and download the package you will have one extra file in that package folder with name : Resource
here in this Resource folder for each record we have one file with querypacked field data.