Pages

Friday, September 7, 2018

D365FO: Import from Excel Template

Noted:  This content is based on Dynamics 365 for Finance and Operations version 8.0 Platform Update 15 



Scenario: 

Sometime consultants don't want to use the Data Management in D365 due to lack of data structure knowledge,  short timeline or many table relations.  They want to import from an Excel to all related tables in one shot.      


Solution: 

Prepare a batch class for importing master data.  I normally use this template and just change the part of create() method for each data.   


using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;

class AeyImportTemplate extends RunBaseBatch
{
    Filename        ItemFileName;
    Filename        filename;
    DialogField     dialogFilename;

    System.IO.Stream    stream;
    FileUploadTemporaryStorageResult fileUploadResult;
    OfficeOpenXml.ExcelRange    range;
    CommaTextStreamIo   commaIo;
    RecordInsertList    rsl;
    boolean             newIteration;
    int                 line,
                        lineimported,lineerror, lineskipped;
    Counter             loop;
    FileUploadBuild     dialogFileUpload;
    FileUpload          fileUpload;
    DialogRunbase       dialog;



    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        fileName
    #endmacro

    void create(int row)
    {
        str                         itemId;
        price                       inventPrice;

        InventTableModule           inventTableModule;

        itemId = range.get_Item(row, 1).Value;
        inventPrice = range.get_Item(row, 2).Value;

        try
        {
            ttsbegin;
            select forUpdate inventTableModule
                where inventTableModule.ItemId  == ItemId
                && inventTableModule.ModuleType == ModuleInventPurchSales::Invent;

            if (!inventTableModule)
            {
                inventTableModule.clear();
            }
            inventTableModule.Price             = inventPrice;
            if (!inventTableModule)
            {
                inventTableModule.insert();
            }
            else
            {
                inventTableModule.update();
            }
            lineimported ++;
            ttscommit;
        }
        catch
        {
            lineerror ++;
            ttsAbort;
        }
    }

    void import()
    {
        Container               con;
        InventTable             inventTable;
        
        ;
        setPrefix("Import master data");
        
        if (this.openFile())
        {
            using (ExcelPackage Package = new ExcelPackage(stream))
            {
                int                         rowCount, i, endRow, startRow;
                Package.Load(stream);
                ExcelWorksheet  worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);
                range       = worksheet.Cells;
                endRow      = worksheet.Dimension.End.Row;
                startRow    = worksheet.Dimension.Start.Row;
                rowCount    = endRow - startRow + 1;

                try
                {
                    for (i = 2; i<= rowCount; i++)
                    {
                        setPrefix(strFmt("Line %1", i));
                        this.create(i);
                    }
                }
                catch
                {
                    throw error("Error - Import template");
                }
            }
        }

        info(strfmt("%1 record(s) imported", lineimported));
        info(strfmt("%1 record(s) fail", lineerror));
    }

    boolean openFile()
    {
        boolean ret = false;

        if (fileUploadResult != null && fileUploadResult.getUploadStatus())
        {
            stream = fileUploadResult.openResult();
            ret = true;
        }
        else
        {
            throw error(strfmt("@SYS62207",filename));
        }

        return ret;
    }

    public Object dialog()
    {
        dialog = super();
        
        DialogGroup dialogGroup = dialog.addGroup('Upload file');

        FormBuildControl formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
        dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), 'FileUpload');
        dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
        dialogFileUpload.fileNameLabel("@SYS308842");
        dialogFileUpload.fileTypesAccepted(".xlsx");
        
        return dialog;
    }

    /// <summary>
    /// Disables the dialog Ok button until the file upload is complete.
    /// </summary>
    /// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
    public void dialogPostRun(DialogRunbase _dialog)
    {
        fileUpload = _dialog.formRun().control(_dialog.formRun().controlId('FileUpload'));
        fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
        this.setDialogOkButtonEnabled(_dialog, false);
    }

    /// <summary>
    /// After the file has been uploaded, the Ok button is enabled.
    /// </summary>
    public void uploadCompleted()
    {
        filename = fileUpload.fileName();

        this.setDialogOkButtonEnabled(dialog, true);
        
        fileUploadResult = fileUpload.getFileUploadResult();

        fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
    }

    /// <summary>
    /// Enables or disables the dialog Ok button.
    /// </summary>
    /// <param name = "_dialog">The <c>Runbase</c> dialog object.</param>
    /// <param name = "_isEnabled">Indicates to enable or disable the Ok button.</param>
    protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
    {
        FormControl okButtonControl = _dialog.formRun().control(_dialog.formRun().controlId('OkButton'));

        if (okButtonControl)
        {
            okButtonControl.enabled(_isEnabled);
        }
    }

    public container pack()
    {
        return [#CurrentVersion,#CurrentList];
    }

    public boolean unpack(container packedClass)
    {
        Version version = runbase::getVersion(packedClass);
        ;
        switch (version)
        {
            case #CurrentVersion:
                [version,#CurrentList] = packedClass;
                break;
            default:
                return false;
        }
        return true;
    }

    public void run()
    {
        this.import();
        super();
    }

    static void main(Args _args)
    {
        AeyImportTemplate   import;
        FormRun formRun;
        Args    args;


        ;
        import = new AeyImportTemplate();
        if (import.prompt())
        {
            import.run();
        }
    }

    public ClassDescription caption()
    {
        return 'Import template';
    }

}

4 comments:

  1. Nice post My sincere thanks for sharing this post Please Continue to share this post.
    Microsoft Dynamics Online Training

    ReplyDelete
  2. Hi,

    Same code, Its not working while running set to batch job.
    Manual mode its working fine.

    Any idea?.

    Thanks,
    Sangeeth

    ReplyDelete
    Replies
    1. Hi Sangeeth!

      Did you find any solution for this problem?

      Delete
  3. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing

    Your info is really amazing with impressive content..Excellent blog with informative concept. Really I feel happy to see this useful blog, Thanks for sharing such a nice blog

    it is really explainable very well and i got more information from your blog.

    ReplyDelete