MSDyn365FO. How to Import CSV file using Electronic Reporting. Part 2 – Format.

In this blog post we will create new Format. It represents document schema and is used to parse it. Go to Organization administration > Workspaces > Electronic reporting, select Data Model created in the previous post and create new configuration:

CSVImportFormat

In the format designer Add root –> File:

FormatRoot

Add sequence and set delimiter to New line – Windows (CR LF). It will tell ER that file has lines split by CR LF. It is possible to select CR for Mac or LF for Linux or specify a custom delimiter.

FormatRootSequence

Add new sequence. This sequence will represent lines. Set Multiplicity to “One many” to say that at least one line is required.

FormatRootLine

Add another sequence. It will represent individual lines. Set delimiter to ‘,’, to split fields by comma. Use another delimiter, if required.

FormatRootRecord

Add 3 fields:

FormatFeilds

In the end you should have format like this:

Format.png

In the next post we will map format to model and test it!

 

 

MSDyn365FO. How to Import CSV file using Electronic Reporting. Part 1 – Data Model.

In this post series I will show how to use Electronic Reporting (ER) to import a CSV file. This tool allows us to create new import process without a line of X++ code. It can be maintained by end users without developer’s help and does not require deployments, because can be easily transferred by XML export and import between environments.

I used documentation available, but it does not have enough details, so here I will try to explain the process step by step. We will start from a Data Model creation and go thought all the stages below:

er-overview-import.png

For simplicity, I’m going to use a custom table that has 3 fields: String, Real and Int.

TestImportTable.png

Data Model is an abstraction over destination\source tables and could be used by multiple different formats. In our case, it will be similar to destination table because it’s quite simple. To create it, go to Organization administration > Workspaces > Electronic reporting.

CSVImportModel

In the designer create model root node:

ModelRoot

Add Records List:

ModelLines.png

Now add 3 fields, one for each in a source file:

ModelFIelds

In the end you should get this structure:

DataModel

Change model status from Draft to Completed, it is required for next step.

In the next blog post we will create new Format.

MSDyn365FO. Add postTargetProcess method to a Data Entity via extension.

Quite a while ago I wrote a blog post about postTargetProcess method that could be added to a data entity for post processing. Previously, you could add it only to a newly created entity, but now you can extend any entity using CoC.

This method is a bit special because it is not declared on the parent object, but is called via reflection by DMFEntityBase class. Full list of method that work in the same way:

  • defaultCTQuery
  • copyCustomStagingToTarget
  • postGetStagingData
  • preTargetProcessSetBased
  • postTargetProcess

DMFEntityBase uses tableHasStaticMethod() to check if a method is defined and DictDataEntity.callStatic() to call it. In the latest PUs Microsoft added support of CoC methods to Dict* API and reflection methods, so now it is possible to use CoC method in this scenario as well.

Extension is quite simple and straightforward, all you need to do is to declare static method with respective signature and do not call next because this method is not defined on the parent object:

[ExtensionOf(tableStr(MyDataEntity))]
final public class MyDataEntity_Extension
{
    public static void postTargetProcess(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
    {
        //do stuff
    }

}

MSDyn365FO. AX 2012 data upgrade with virtual companies.

In Dynamics 365 for Finance and Operations virtual companies are deprecated and could not be upgraded according to the documentation that just state this fact without proposing a solution.

If we cannot upgrade, then we need to get rid of them. Here are the high level steps how you can de-virtualize a table. Let’s say that we have virtual company V with two companies: A and B. V has only one table collection with one table for simplicity, let’s call it Table1.

  1. Delete a data from Table1 that belongs to the companies A and B. When you create a virtual company, you may already have some data in the tables you want to share. That data may be orphaned, so we want to delete it to avoid duplicates. Can be done via simple T-SQL script:
    DELETE FROM Table1
    WHERE DATAAREAID = 'A' or DATAAREAID = ‘B’
    
  2. Go to System administration > Setup > Virtual company accounts and delete the virtual company.
  3. Restart AX client.
  4. Insert data from the virtual company to de-virtualized companies via X++ job:
    static void deVirtualizeTables(Args _args)
    {
        DataAreaId          virtualDataAreaId = 'V';
        container           oldCompaniesCon = ['A', 'B'];
    
        VirtualDataAreaList virtualDataAreaList;
    
        void deVirtualizeTable(TableId _tableId)
        {
            int             i;
            DataAreaId      dataAreaId;
            SysDictTable    dictTable = new SysDictTable(_tableId);
            Common          buffer = dictTable.makeRecord();
            Common          newBuffer;
    
            while select crossCompany buffer
                where buffer.dataAreaId == virtualDataAreaId
            {
                for (i = 1; i <= conLen(oldCompaniesCon); i++)
                {
                    dataAreaId = conPeek(oldCompaniesCon, i);
                    changeCompany (dataAreaId)
                    {
                        newBuffer = null;
                        newBuffer = dictTable.makeRecord();
    
                        buf2buf(buffer, newBuffer);
                        newBuffer.doInsert();
                    }
                }
            }
        }
    
        select firstOnly RecId from virtualDataAreaList;
    
        if (virtualDataAreaList)
        {
            throw error('Delete a virtual company first!');
        }
    
        ttsBegin;
    
        deVirtualizeTable(tableNum(Table1));
    
        ttsCommit;
    
    }
    
  5. Delete data from the virtual company. It cannot be done from X++ because you cannot use changeCompany with a company that does not exist. T-SQL:
    DELETE FROM Table1
    WEHERE DATAAREAID = 'V'
    

Now we are ready to run standard data upgrade procedure!

MSDyn365FO. Code to build cross reference data without a full compile.

After 8.1 there is no need to do code upgrade and build standard code supplied by Microsoft or at least we’ve been told so. However, it’s impossible to refresh cross references without building model with Visual Studio tools and we need this because now hotfixes and monthly updates are cumulative and include binaries and X++ code as well, so cross reference data on dev VMs becomes outdated quite quickly.

While MS is working on actual solution, I dug a bit into xppc that compiles X++ code and build cross references and here you are, this code can be used to build cross references for a module without compile, that is way faster!

using Microsoft.Dynamics.AX.Framework.Xlnt.XReference;
using Microsoft.Dynamics.AX.Metadata.XppCompiler;
using System;

class XRefBuilder
{
    static void Main(string[] args)
    {
        try
        {
            string moduleName = "ApplicationCommon";
            string metaDataPath = @"K:\AosService\PackagesLocalDirectory";

            ICrossReferenceProvider xRefProvider = CrossReferenceProviderFactory.CreateSqlCrossReferenceBatchProvider(".", "DYNAMICSXREFDB", moduleName, true, true, new DiagnosticsHandler());
            xRefProvider.StartBatch();
            new MetadataXRefSweeper( metaDataPath, moduleName, xRefProvider, DateTime.MinValue, new DiagnosticsHandler()).Run();
            xRefProvider.CommitBatch();
        }
        catch (AggregateException ae)
        {
            ae.Handle(ex => {
                    Console.WriteLine(ex.InnerException != null ? ex.InnerException.Message : ex.Message);
                return true;
            });

        }
    }
}

Note catch section, it was a big surprise for me, but standard code has lots of compile issues, for example, KanbanMultiDelete action menu item has EnumParameter property populated but EnumTypeParameter is not. In 2012 days it was not possible to do this, but now you can literally type anything into EnumParameter without specifying enum and save it. Cross reference builder would try to find that enum and throw an exception saying that cannot find an enum with empty name. That’s why I have that catch section and that’s what xppc does, just skip all these errors and probably log them somewhere.

Another gotcha here is OutOfMemoryException that you can get with ApplicationSuite model, so don’t forget to handle it as well.

 

UPDATED:

MetadataXRefSweeper build cross references only for objects like EDT or Tables, but does not cover source code. To build cross references for source code you have to actually compile it 😦

 

 

MSDyn365FO. How-to send PDF document to a printer from X++

SendPDFToPrinter.png

In AX 2012 it could be done with 2 lines of X++ code:

#WinAPI
WinApi::shellExecute(fileName,'', fileFolder, #ShellExePrint);

Now it’s not that easy. Files are in Azure BLOB storage instead of a folder, printers are in a local network that is not accessible from the cloud and WinApi is deprecated. To print standard reports Document Routing Agent should be installed. We need it to send PDF directly from the system as well.

Let’s say we want to print PDF file saved in document attachments (DocuRef). First, we need to check if a printer selected by a user is active and get printer details:

SrsReportPrinterContract activePrinterContract = SrsReportRunUtil::getActivePrinter(printerName);
if (!activePrinterContract.parmPrinterName())
{
    return;
}

printerName value should come from somewhere, for example a field on a form. In this case we can use SrsReportRunUtil::lookupPrinters() method to add a lookup with all available printers:

public static void lookupPrinters(FormStringControl _ctrl)
{
    SrsReportRunUtil::lookupPrinters(_ctrl);
}

Then we need to create print destination settings:

SRSPrintDestinationSettings srsPrintDestinationSettings = new SRSPrintDestinationSettings();
srsPrintDestinationSettings.printMediumType(SRSPrintMediumType::Printer);
srsPrintDestinationSettings.fileFormat(SRSReportFileFormat::PDF);
srsPrintDestinationSettings.printerName(activePrinterContract.parmPrinterName());
srsPrintDestinationSettings.printerWhere(activePrinterContract.parmPrinterPath());
srsPrintDestinationSettings.numberOfCopies(1);
srsPrintDestinationSettings.collate(false);
srsPrintDestinationSettings.printOnBothSides(SRSReportDuplexPrintingSetting::None);
srsPrintDestinationSettings.printAllPages(true);
srsPrintDestinationSettings.fromPage(0);
srsPrintDestinationSettings.toPage(0);

srsPrintDestinationSettings.printerWhere() is important bit here. This parameter accepts path to a printer. It’s possible to install multiple DRA on different servers and path for DRA installed on a print server could be different to path for DRA installed on any other server, so watch for this.

To send document we need to read file into a memory stream:

container fileCon = DocumentManagement::getAttachmentAsContainer(_docuRef);
var stream = Binary::constructFromContainer(fileCon).getMemoryStream();

And create new DocumentContract:

DocumentContract documentContract = DocumentContractFactory::Instance.Create(DocumentContractType::Pdf);

documentContract.Name = _docuRef.Name;
documentContract.Contents =  stream.ToArray();
documentContract.TargetType = TargetType::Printer;
documentContract.Settings = srsPrintDestinationSettings.printerPageSettings();
documentContract.ActivityID = newGuid();

If you send multiple documents ActivityID should be initialized for each document. Don’t forget to add a reference to Microsoft.Dynamics.AX.Framework.DocumentContract:

using Microsoft.Dynamics.AX.Framework.DocumentContract;

And finally send the contract to DRA:

SrsReportRunPrinter::sendDocumentContractToDocumentRouter(documentContract);

Whole method:

public static void sendToPrinter(DocuRef _docuRef, str _printerName)
{
    SrsReportPrinterContract activePrinterContract = SrsReportRunUtil::getActivePrinter(_printerName);
    if (!activePrinterContract.parmPrinterName())
    {
        return;
    }

    SRSPrintDestinationSettings srsPrintDestinationSettings = new SRSPrintDestinationSettings();
    srsPrintDestinationSettings.printMediumType(SRSPrintMediumType::Printer);
    srsPrintDestinationSettings.fileFormat(SRSReportFileFormat::PDF);
    srsPrintDestinationSettings.printerName(activePrinterContract.parmPrinterName());
    srsPrintDestinationSettings.printerWhere(activePrinterContract.parmPrinterPath());
    srsPrintDestinationSettings.numberOfCopies(1);
    srsPrintDestinationSettings.collate(false);
    srsPrintDestinationSettings.printOnBothSides(SRSReportDuplexPrintingSetting::None);
    srsPrintDestinationSettings.printAllPages(true);
    srsPrintDestinationSettings.fromPage(0);
    srsPrintDestinationSettings.toPage(0);

    container fileCon = DocumentManagement::getAttachmentAsContainer(_docuRef);
    if (fileCon)
    {
        var stream = Binary::constructFromContainer(fileCon).getMemoryStream();
        if (stream)
        {
            DocumentContract documentContract = DocumentContractFactory::Instance.Create(DocumentContractType::Pdf);
            documentContract.Name = _docuRef.Name;
            documentContract.Contents =  stream.ToArray();
            documentContract.TargetType = TargetType::Printer;
            documentContract.Settings = srsPrintDestinationSettings.printerPageSettings();
            documentContract.ActivityID = newGuid();
SrsReportRunPrinter::sendDocumentContractToDocumentRouter(documentContract);
        }
    }
}

If you want to check documents printed or see if there are any in a queue you can go to Common -> Inquiries -> Document routing status

DRA status