PowerShell and SQL scripts for Database Refreshing in a Devbox

 Say you created a new database AxDB_TEST2 and restored a test environment backup there.

Now you need to change the databases names so that D365FO would target a restored data.

First, you need to stop D365FO services, for example, with a similar PowerShell script.

function StopD365RelevantService()
{
    $services = "DynamicsAxBatch","Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe","MR2012ProcessService","LCSDiagnosticClientService"
    foreach ($item in $services)
    {
      Set-Service -Name $item -StartupType Disabled  
    }
    Stop-Service -Name Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe -ErrorAction SilentlyContinue
    Stop-Service -Name DynamicsAxBatch -ErrorAction SilentlyContinue
    Stop-Service -Name W3SVC -ErrorAction SilentlyContinue
    Stop-Service -Name MR2012ProcessService -ErrorAction SilentlyContinue
    Stop-Service -Name LCSDiagnosticClientService -ErrorAction SilentlyContinue

    Set-Service -Name W3SVC -StartupType Automatic
}

Then you can use the following SQL script to 'exchange' two databases.

use master
ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE AxDB MODIFY NAME = AxDB_ORIG
ALTER DATABASE AxDB_ORIG SET MULTI_USER

ALTER DATABASE AxDB_TEST2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE AxDB_TEST2 MODIFY NAME = AxDB
ALTER DATABASE AxDB SET MULTI_USER

Once it is done, get the services back to life

function StartD365RelevantService()
{
    #Set-Service -Name "DynamicsAxBatch","Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe","W3SVC","MR2012ProcessService" -StartupType Automatic
    $services = "DynamicsAxBatch","Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe","W3SVC","MR2012ProcessService","LCSDiagnosticClientService"
    foreach ($item in $services)
    {
      Set-Service -Name $item -StartupType Automatic  
    }
    #Set-Service -Name 'DynamicsAxBatch' -StartupType Automatic
    Start-Service -Name Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe -ErrorAction SilentlyContinue
    Start-Service -Name DynamicsAxBatch -ErrorAction SilentlyContinue
    Start-Service -Name W3SVC -ErrorAction SilentlyContinue
    Start-Service -Name MR2012ProcessService -ErrorAction SilentlyContinue
    Stop-Service -Name LCSDiagnosticClientService -ErrorAction SilentlyContinue
}

Multiple enum values selection in forms and tables

Previously I posted three supporting functions to work with multiple enum values selection. Now, let's see how they can be used in real scenarios.

With these functions you can easily expose enum values in selection lists and then save the user selection in tables.

Enum lists in a form

Check first how to show two grids in a form; so that the user could move enum values from one to another.

[Form]
public class SysPolicyTypesOneCompanyActiveOnly extends FormRun
{

    private Map                 policyTypes = wzhTest::createMapForEnum(enumStr(SysPolicyTypeEnum));
    private SysPolicyTypeEnum   type;
 
    private void resetSysPolicyTypeListPanel()
    {
        SysPolicyTypeAvailableGrid.deleteRows(0, SysPolicyTypeAvailableGrid.rows());
        SysPolicyTypeEnabledGrid.deleteRows(0, SysPolicyTypeEnabledGrid.rows());

        var mapEnumerator = policyTypes.getEnumerator();
        while (mapEnumerator.moveNext())
        {
            type        = mapEnumerator.currentKey();
            
            if (SysPolicyTypesOneCompanyActiveOnly::exist(type))
            {
                this.addRowForTypes(SysPolicyTypeEnabledGrid, type);
            }
            else
            {
                this.addRowForTypes(SysPolicyTypeAvailableGrid, type);
            }
        }

        SysPolicyTypeAvailableGrid.row(SysPolicyTypeAvailableGrid.rows() ? 1 : 0);
        SysPolicyTypeEnabledGrid.row(SysPolicyTypeEnabledGrid.rows() ? 1 : 0);
    }

    private int addRowForTypes(FormTableControl _table, SysPolicyTypeEnum _type)
    {
        int i;
        // Insert it into the data set in sorted order.
        for (i = _table.rows(); i >= 1; i--)
        {
            SysPolicyTypeEnum typeIdTmp = _table.cell(1, i).data();
            
            if (strCmp(enum2Str(typeIdTmp), enum2Str(_type)) < 0)
            {
                // We need to insert after the current item.
                break;
            }
        }

        // Insert the new item, i is equal to the index of the item we need to insert after.
        _table.insertRows(i, 1);
        _table.cell(1, i + 1).data(_type);

        return i + 1;
    }

...

}

Multiple enum values in a table

In order to save user's selection of particular Enum values in a table, you can add a string type field there. 

The rest is to convert these selected values from string to a list or a container to present them in a form.

Say, we need to let the user to select particular FiscalPeriodStatus values.

First, we add a new string field FiscalPeriodStatusSelection to our table.

We can show the currently saved selection via a display method

    /// <summary>
    /// Returns Fiscal period statuses string values
    /// </summary>
    /// <param name = "_parm">container</param>
    /// <returns>string values of selected period statuses</returns>
    [SysClientCacheDataMethodAttribute(true)]
    public display LedgerExchAdjFiscalPeriodStatusSelection fiscalPeriodStatusSelectionDisp()
    {
        return wzhTest::enumValuesStr2EnumStrStr(this.FiscalPeriodStatusSelection, enumName2Id(enumStr(FiscalPeriodStatus)));
    }

And updates this field via AnotherClass which treats the user's selection (in a form, for example)

    this.FiscalPeriodStatusSelection = con2Str(AnotherClass.getFiscalPeriodStatusSelectionCont(), wzhTest::ContSeparator);

    /// <summary>
    /// Gets FiscalPeriodStatus selection as a container
    /// </summary>
    /// <returns>container</returns>
    public container getFiscalPeriodStatusSelectionCont()
    {
        container                               cont;
        
        while (...)
        {
            cont += SomeBufferOrList.FiscalPeriodStatus;
        }
                
        return cont;
    }

Supporting functions to work with multiple selection of Enum values

There a couple of custom static methods you can use to facilitate your job with Enum values in tables and forms.

class wzhTest
{
    public const str contSeparator = ';';
    /// <summary>
    /// Populates a map for all enum's values
    /// </summary>
    /// <param name = "_enumName">Enum name</param>
    /// <returns>Map object</returns>
    public static Map createMapForEnum(EnumName _enumName)
    {
        Map             map          = new Map(Types::Enum, Types::String);
        DictEnum        dictEnum = new DictEnum(enumName2Id(_enumName));
        for(int i = 0; i < dictEnum.values(); i++)
        {
            map.insert(dictEnum.index2Value(i), dictEnum.index2Symbol(i));
        }
        return map;
    }
    /// <summary>
    /// Creates a container with enum values from a given string values container
    /// </summary>
    /// <param name = "_cont">string values container</param>
    /// <param name = "_enumType">enum variable for defining its type</param>
    /// <returns>Container with enum value</returns>
    public static container enumValuesCont2EnumStrCont(container _cont, int _enumId)
    {
        container       ret;
        str             s;
        int             idx = 0;
        int             len = conLen(_cont);
        while (idx < len)
        {
            idx += 1;
            s           = conPeek(_cont, idx);
            if(s)
            {
                ret += enum2Symbol(_enumId, conPeek(_cont, idx));
            }
        }
        return ret;
    }

    /// <summary>
    /// Creates a string with enum values string
    /// </summary>
    /// <param name = "_s">string values separated with ; sign</param>
    /// <param name = "_enumType">enum variable for defining its type</param>
    /// <returns>String with enum values separated with ; sign</returns>
    public static str enumValuesStr2EnumStrStr(str _s, int _enumId)
    {
        container c = str2con(_s, wzhTest::contSeparator);
        c = wzhTest::enumValuesCont2EnumStrCont(c, _enumId);
        return con2Str(c, wzhTest::ContSeparator);
    }
}

Let's see how they work with AccessControlledType enum type.

 static public void main(Args _args)
    {
        AccessControlledType enumEDT;
        //AccessControlledType::MenuItemDisplay / 0
        //AccessControlledType::MenuItemOutput / 1
        //AccessControlledType::MenuItemAction /2
        //AccessControlledType::WebUrlItem /3
        //AccessControlledType::WebActionItem /4
        //AccessControlledType::WebManagedContentItem / 5
        //AccessControlledType::Table / 6
        // AccessControlledType::TableField / 7
        EnumId      id      = enumName2Id(enumStr(AccessControlledType));
        str         name    = enumId2Name(id); //enumStr(AccessControlledType)

Basically, they convert a enum values list from string and vice-versa by using standard functions.

Check the output of each static method.

        //createMapForEnum
        Info("Test createMapForEnum");
        Info(strFmt("%1 : %2 values", id, name));
        Map         m = wzhTest::createMapForEnum(name);
        MapIterator mi = new MapIterator(m);
        int i;
        while(mi.more())
        {
            Info(strFmt("%1 : '%2'", i, mi.value()));
            mi.next();
            i++;
        }

        //enumValuesCont2EnumStrCont
        Info("Test enumValuesCont2EnumStrCont");
        setPrefix('');
        container   c1 = [AccessControlledType::WebActionItem, AccessControlledType::MenuItemOutput];
        container   c2 = wzhTest::enumValuesCont2EnumStrCont(c1, id);

        for(i=1;i<=conLen(c2);i++)
        {
            Info(strFmt("%1 => '%2'", conPeek(c1,i), conPeek(c2,i)));
        }

        //enumValuesStr2EnumStrStr
        Info("Test enumValuesStr2EnumStrStr");
        str         s1 = con2Str([AccessControlledType::Table, AccessControlledType::TableField], wzhTest::contSeparator);
        str         s2 = wzhTest::enumValuesStr2EnumStrStr(s1, id);
        Info(strFmt("'%1' => '%2'", s1, s2));

DMF import records for multiple companies

 Say, we need to import Bank reconciliation records from an Excel file for many target companies at once.

The standard DMF approach does not allow to do so via DMF data entities out of the box.

However, we can easily customize Data Entity copyCustomStagingToTarget method to loop through all companies encountered in an imported file lines.

/// <summary>
    /// Performs a custom copy from the staging table to the target environment.
    /// </summary>
    /// <param name = "_dmfDefinitionGroupExecution">The definition group.</param>
    /// <returns>A container of counts of [new records, updated records].</returns>
    /// <remarks>
    /// When doing set-based inserts, the full defaulting logic from LedgerJournalTrans is not
    /// run. In order to get full defaulting, row-by-row processing must be performed. Since
    /// this method is called specifically from the DIXF framework, the entity in
    /// DIXF can be marked as AllowSetBased=False in order to force row-by-row defaulting
    /// and validation. The trade off is a significant degradation in copy performance.
    /// </remarks>
    public static container copyCustomStagingToTarget(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
    {
        CDPBankReconStaging     staging;
        Set                     companySet;
        DMFStagingValidationLog log;


        log.skipDataMethods(true);
        delete_from log
            where 
            log.DefinitionGroupName == _dmfDefinitionGroupExecution.DefinitionGroup && 
            log.ExecutionId         == _dmfDefinitionGroupExecution.ExecutionId;


        update_recordset staging setting TransferStatus = DMFTransferStatus::NotStarted
            where
            staging.TransferStatus  == DMFTransferStatus::Validated &&
                staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup &&
                staging.ExecutionId     == _dmfDefinitionGroupExecution.ExecutionId;

        int64 updatedRecords = 0;
        int64 newRecords = 0;
        // Validating companies
        myBankReconDE_Helper::validateCompany(_dmfDefinitionGroupExecution);
        // Getting the company list to loop through
        companySet = myBankReconDE_Helper::getStagingCompanySet(_dmfDefinitionGroupExecution);

        // the party begins here!
        SetEnumerator se = companySet.getEnumerator();
        while (se.MoveNext())
        {
            SelectableDataArea currCompany = se.current();
            changecompany(currCompany)
            {
                ttsbegin;

                myBankReconDE_Helper::validateTransactionCurrency(currCompany, _dmfDefinitionGroupExecution);
                // do any other necessary logic
                //...
                newRecords += myBankReconDE_Helper::createBankAccountTransactions(currCompany, _dmfDefinitionGroupExecution);
                // making DMF happy
                myBankReconDE_Helper::updateStagingTransferStatusToCompleted(currCompany, _dmfDefinitionGroupExecution);

                ttscommit;
                // Posting
                myBankReconDE_Helper::reconcileAccountStatement(currCompany, _dmfDefinitionGroupExecution);
            }
        }

        return [newRecords, updatedRecords];
    }

Getting company list

/// <summary>
    /// gets a set of all legal entities present in the staging
    /// </summary>
    /// <param name = "_dmfDefinitionGroupExecution"></param>
    /// <returns></returns>
    public static Set getStagingCompanySet(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
    {
        myBankReconStaging staging;
        Set companySet = new Set(Types::String);
        while select myCompany from staging
            group by myCompany
            where
             staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup &&
             staging.ExecutionId     == _dmfDefinitionGroupExecution.ExecutionId &&
            (staging.TransferStatus == DMFTransferStatus::NotStarted || staging.TransferStatus == DMFTransferStatus::Validated)
        {
            companySet.add(staging.myCompany);
        }
        return companySet;
    }

If cloud-hosted deployment is stuck at preparation

 If you see that your deployment process to a cloud-hosted environment hangs up at Preparation step with no failed steps and empty logs; it may be required to rotate the secrets.

Setup Business Event Endpoint with MuleSoft

 

When it comes to setting up a Business Event BE endpoint: D365FO just sends a basic data contract (“payload”) via POST method.

So, in fact we can use Microsoft Power Automate endpoint type to interact with your eventual MuleSoft  HTTP listener.

A simple app (a flow) in MuleSoft Design Center consisting of two steps: HTTP listener and Logger.

This basic BE data contract info to add a new type:

{"TestField":"","BusinessEventId":"","ControlNumber":0,"EventId":"","EventTime":"/Date(-2208988800000)/","MajorVersion":0,"MinorVersion":0}

For you particular case you can use pre-generated scheme for your new Business Event.

After that you can add other steps to your MuleSoft app.

Some ISV/Custom labels not resolved when migrated from AX 2012 to D365FO

 After migration from AX 2012 to D365FO I noticed a strange thing with one ISV module labels.

Some of them are perfectly resolved by their old notation with @ in the reference; but none in a new style.

As suggested by Muthusamy V in this old thread https://community.dynamics.com/365/financeandoperations/f/dynamics-365-for-finance-and-operations-forum/312828/label-issues-in-sdp-deployment?pifragment-109037=2#responses

and explained by one of my colleagues, I simply need to delete these files from K:\AosService\PackagesLocalDirectory\ApplicationSuite\ folder. (All D365FO service must be stopped)

Then it works well.

Where to find Azure Virtual Machine RDP port

Here you go

TF command line to fix the error: Mapping on the working folder is already in use

 Just to document my command line to get rid of this blocking error.

My environment is under Azure DevOps version control and some other user already created a workspace on this computer.

Close Visual Studio (2017 in my case) and open the command line. It can be open via Developer Command Prompt for VS 2017; so you would not need to provide the entire path to tf.exe.

Next, run a tf command to delete the workspace created by the previous user (Name and Surname)

tf workspace /delete DEV365-FO-VM-3;"Name Surname" /collection:https://YOURCOMPANYPROJECT.visualstudio.com

Then stop all locally running D365FO services, like Web Publishing, Batch processing, etc, in order to avoid errors with locked files in your K:\AosService\PackagesLocalDirectory subfolders.

Now you can open Visual Studio, connect to Azure DevOps server, create a new workspace, map your folders for projects and metadata, and get latest.

My first D365FO Build pipeline with Microsoft-hosted agent

This contains some particular details and explanatory images which can be useful while following the basic Microsoft article https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-tools/hosted-build-automation

Thanks Joris for the NuGet packages and other colleagues for their help.

So, my goal is to create a Build pipeline for Version 18, and my deployable package must have a few ISV models, one of which is provided as libraries and source code.

Personal Access Token

First create or update your Personal Access Token and copy-paste it in a secure place (I mean Notepad++, of course). This will be used as a password for uploading Nuget packages to your Artifacts feed later.

Create a feed

Create nuget.config file and place it together with Nuget files as described in the next part.

NuGet packages

Get NuGet packages from LCS shared asset library.

Place these files in a special NuGet folder and create or update packages.config file.

Add them to the source controlled folder. DEV must be mapped too.

Publish packages by using the command line

Now open Windows command prompt to publish these Nuget files to your feed.

Use your Personal Access Token as a password.

Once uploading is done, you can check that the feed contains all packages.

Creating the pipeline

Before importing or creating a pipeline, install Azure DevOps pipeline tools Dynamics 365 Finance and Operations from Marketplace in your Extensions.

I export a pipeline from one organization and then import it to mine.

Then check and update the project name and relevant folder references, change variables and triggers, if needed.

Visual Studio build step

As one of my ISV provided in mix code/binaries mode, I need to reference its non X++-libraries at this step.

/p:ReferencePath A semicolon-separated list of paths that contain any non-X++ binaries that are referenced and required for compilation. You should include the location of the extracted Compiler Tools NuGet package, because it might contain required references.

So, once triggered it builds the solution, creates a deployable deployable package and publishes it.

Published artifacts can be found here.

Catastrophic Failure

 Thirteen years with Axapta and finally got it today!

Maximum Size for Business Events

Business Events help to integrate D365FO with other systems; they are supposed to be specific and small. But how exactly small should they be? What is the maximum size for one message?

If we take a look at the code, we will see that the maximum is driven by the type of Business Event End Point:

Some of these classes specifically set their max. 

For example, BusinessEventsServiceBusAdapter even implements some logic here.

As per Flow and HTTP, it is defined as 1MB minus 4KB for the header.

So, we have two conclusions here:

- we have to check these hard-coded limits directly in the code, as they may be changed in future;

- if you need to send a chunk of information bigger than the maximum size, you'd better revise your solution. For example, instead of sending a file you can send just its Azure Storage locator;

Another option can be developing your own End Point.

Some useful links.

How to develop Business Events

How to use Business Events

Example of using Microsoft Power Automate (Flow) to integrate with D365FO via Business Events  

Switch in View Computed column

 If you need to compare multiple fields in your view while populating a computed column, take into consideration that the standard implementation of method SysComputedColumn::switch uses a map enumerator. 

public static client server str switch(str _controlExpression, Map _comparisonExpressionMap, str _defaultExpression)
    {
        MapEnumerator  mapEnumerator;
        str caseExpression = '';

        caseExpression ='CASE ' + _controlExpression;
        mapEnumerator = _comparisonExpressionMap.getEnumerator();
        while (mapEnumerator.moveNext())
        {
            caseExpression += ' WHEN ' + mapEnumerator.currentKey() + ' THEN ' + mapEnumerator.currentValue();
        }
        caseExpression += ' ELSE ' + _defaultExpression;
        caseExpression += ' END';

        return caseExpression;
    }

It means that your given order will be replaced in final SQL clause by alphabetical one of your keys.

CASE  
	WHEN (T4.PROJID1) != ('') THEN T4.PROJNAME1 
	WHEN (T4.PROJID2) != ('') THEN T4.PROJNAME2 
	WHEN (T4.PROJID3) != ('') THEN T4.PROJNAME3 
	ELSE T4.PROJNAME END

For example, we want to populate field mgcProjInvoiceGLTransView.ParentProjectName for a given project id up to three level up in the project hierarchy. Let's assume that they are prepopulated in mgcProjInvoiceOnAccView view: projId1 is the parent of projId, projId2 is the parent of projId1, and so on.

Once we reference this computed column to the following method, we will get a new order in CASE construction on the SQL side.

 private static str projNameParent()
    {
        str         ret;
        SysComputedColumnBase::switch cannot keep a given order while using Map enumerator; so we put the SQL string as a string constant
        tableName   viewName                    = identifierStr(mgcProjInvoiceGLTransView);
        tableName   tableName                   = identifierStr(mgcProjInvoiceOnAccView);
        str         compareValue                = '';


        Map         comparisonExpressionMap     = SysComputedColumn::comparisionExpressionMap();
        str         fieldNameProjName3          = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName3));
        str         fieldNameProjName2          = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName2));
        str         fieldNameProjName1          = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName1));
        str         fieldNameProjName           = SysComputedColumn::returnField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjName));

        comparisonExpressionMap.insert(
        SysComputedColumn::notEqualExpression(
            SysComputedColumn::comparisonField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjId3)),
            SysComputedColumn::comparisonLiteral(compareValue)),
        fieldNameProjName3);

        comparisonExpressionMap.insert(
        SysComputedColumn::notEqualExpression(
            SysComputedColumn::comparisonField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjId2)),
            SysComputedColumn::comparisonLiteral(compareValue)),
        fieldNameProjName2);

        comparisonExpressionMap.insert(
        SysComputedColumn::notEqualExpression(
            SysComputedColumn::comparisonField(viewName, tableName, fieldStr(mgcProjInvoiceOnAccView, ProjId1)),
            SysComputedColumn::comparisonLiteral(compareValue)),
        fieldNameProjName1);

        ret       = SysComputedColumn::switch(
                                                '',
                                                comparisonExpressionMap,
                                                fieldNameProjName);
        return ret;
    }

Of course, it always returns  the name of the parent on the first level, even though it had its own parent, which is incorrect result. 

Therefore, the easiest way is to replace the aforementioned construction with a simple string.

 private static str projNameParent()
    {
        str         ret;
        ret = "CASE WHEN (T4.PROJID3) != ('') THEN T4.PROJNAME3 WHEN (T4.PROJID2) != ('') THEN T4.PROJNAME2 WHEN (T4.PROJID1) != ('') THEN T4.PROJNAME1 ELSE T4.PROJNAME END";
        return ret;
    }

Corrupted copy of SSRS report

Sometimes after copying an existing SSRS report or even just a part of its design, parser starts encountering strange errors. For example:

Unidentifiable substring 'Value' in expression. the parser reported error message

There must be something wrong in the mechanism responsible for copying text boxes. In most case, it replaces simple function names with predicates like Microsoft.Value etc. In my case it turned out to be even worse: some bug in strings concatenation; no matter what.

The problem here is that you have no hint about the object name, where such a non-conform string was added. You can try your textboxes one by one by checking all their properties with functions inside: visibility, font, border etc, even labels for its placeholder! And it can become a nightmare when your report contains dozens of them.

Fortunately there is a some workaround. You can open XML files for both of the original report and your copy of it in Notepad++ and run Compare over them (Install this Compare plugin).

Then scroll down to the first bad guy: a couple of strings above you will see its name.

Go back to the editor, find this textbox.

Now check its corrupted property function expressions and fix it.