SubQueries on Dynamics AX/365 FO for outer Join

Views and computed columns are sometimes really useful to get data from Dynamics with performance and efficiency. 

For the developer, this is also something harder to work with views rather than X++ code because SQL works with a relational and non dynamic execution.

Let's say we have a view on customer table, implemented with a good performance and you have a change request asking to add a new field with a "1-0,N" cardinality relation between the root data source (CustTable) and the data to retrieve (so, for one customer I can retrieve 1 or more addresses from this type, but maybe I can't find one, so this join is an Outer Join query).

An X++ implementation of this requirement will be easy to do, using a virtual field. But for performance, it could be helpful to use SQL through the view using a computed column.

Basics of computed columns in D365 F&O for memory :

Computed columns are SQL executed by Microsoft SQL Server. The related method of a computed column is not running the calculation but it writes the SQL Code during the synchronization to create the SQL Statement use of this field in the query. That's why a computed column method is returning a primitive type (int, str etc...) and is a static method (no context available for this execution during synchronization).

Imagine in our example, you can't implement an inner join in your existing query because you don't know if you could have a value for your new field and also you can't implement an outer join to avoid duplicating lines on the root datasource. If you can't define a unique key to retrieve the data, a "Select TOP 1" subquery should be a means to reach your goal.

You can see a sample of the syntax below to retrieve for instance a particular address of a customer, selected on a specific criteria (here this is the name of the address). If no address is found, the value will be simply empty without any Outer Join inconvenience.

/// <summary>

    /// Get the Country of one NAMED Address of fetched Sales order

    /// </summary>

    public static server str getFirstMyNameAddressRefRecid()

    {

 

        //Target :  select TOP 1 logisticsPostalAddress.RecId

        //    from logisticsPostalAddress

        //    inner join DirPartyLocation on DirPartyLocation.LOCATION = logisticsPostalAddress.Location

        //    inner join CustTable on CustTable.Party = DirPartyLocation.PARTY

        //    inner join DIRPARTYLOCATIONROLE on DIRPARTYLOCATIONROLE.PARTYLOCATION = DirPartyLocation.Recid

        //    inner join LogisticsLocationRole on LogisticsLocationRole.Recid = DIRPARTYLOCATIONROLE.LOCATIONROLE AND LogisticsLocationRole.NAME = 'MyName'

        //    where salesTable.CUSTACCOUNT = view.CustAccount and salesTable.DATAAREAID = view.dataAreaId

 

        SysDictTable logisticsPostalAddress = new SysDictTable(tableNum(logisticsPostalAddress));

        SysDictTable DirPartyLocation = new SysDictTable(tableNum(DirPartyLocation));

        SysDictTable custTable = new SysDictTable(tableNum(CustTable));

        SysDictTable DirPartyLocationRole = new SysDictTable(tableNum(DirPartyLocationRole));

        SysDictTable LogisticsLocationRole = new SysDictTable(tableNum(LogisticsLocationRole));

 

        DictView MyDirPartyMyNameAddress = new DictView(tableNum(MYDirPartyMyNameAddress));

 

        str s = strFmt('SELECT TOP 1 %2.%1 FROM %2 '+           //LOGISTICSLOCATION

                            'INNER JOIN %5 ON %2.%3 = %5.%4 '+  //DirPartyLocation

                            'INNER JOIN %8 ON %8.%7 = %5.%6 '+  // CustTable

                            'INNER JOIN %12 ON %12.%13 = %5.%18 '+  //DIRPARTYLOCATIONROLE

                            'INNER JOIN %15 ON %15.%16 = %12.%19 AND %15.%17 = \'MyName\' '+  //LogisticsLocationRole

                            'WHERE %8.%9 = %10 AND %8.%20 = %11',            //Criteria

        logisticsPostalAddress.fieldName(fieldNum(logisticsPostalAddress, RecId), DbBackend::Sql), // 1

        logisticsPostalAddress.name(DbBackend::Sql), // 2

        logisticsPostalAddress.fieldName(fieldNum(logisticsPostalAddress, Location), DbBackend::Sql), //3

        DirPartyLocation.fieldName(fieldNum(DirPartyLocation, Location), DbBackend::Sql), // 4

        DirPartyLocation.name(DbBackend::Sql), // 5

        DirPartyLocation.fieldName(fieldNum(DirPartyLocation, Party), DbBackend::Sql), //6

        custTable.fieldName(fieldNum(custTable, Party), DbBackend::Sql), // 7

        custTable.name(DbBackend::Sql), // 8

        custTable.fieldName(fieldNum(custTable, AccountNum), DbBackend::Sql), //9

        MyDirPartyMyNameAddress.computedColumnString(tableStr(SalesTable), fieldStr(SalesTable, CustAccount), FieldNameGenerationMode::WhereClause), //10

        MyDirPartyMyNameAddress.computedColumnString(tableStr(SalesTable), fieldStr(SalesTable, DataAreaId), FieldNameGenerationMode::WhereClause), //11

        DirPartyLocationRole.name(DbBackend::Sql), // 12

        DirPartyLocationRole.fieldName(fieldNum(DirPartyLocationRole, PartyLocation), DbBackend::Sql), //13

        DirPartyLocationRole.fieldName(fieldNum(DirPartyLocationRole, RecId), DbBackend::Sql), //14

        LogisticsLocationRole.name(DbBackend::Sql), // 15

        LogisticsLocationRole.fieldName(fieldNum(LogisticsLocationRole, Recid), DbBackend::Sql), //16

        LogisticsLocationRole.fieldName(fieldNum(LogisticsLocationRole, Name), DbBackend::Sql), //17

        DirPartyLocation.fieldName(fieldNum(DirPartyLocation, RecId), DbBackend::Sql), // 18

        DirPartyLocationRole.fieldName(fieldNum(DirPartyLocationRole, LocationRole), DbBackend::Sql), //19

        custTable.fieldName(fieldNum(custTable, DataAreaId), DbBackend::Sql)); //20

 

        return strFmt('ISNULL((%1), \'\')', s);

    }

Advice for implementation and maintenance :Write your SQL Statement in comment and on every lines of your code (number of your variables).

Don't forget also the dataareaid managed normally by the system won't be here added automatically. So you need to manage it.

Tool for Technical Data Model Diagram on Dynamics 365 F&O

One interesting add-on is available for Dynamics 365 F&O development on Visual Studio MarketPlace :

Its name is CIELLOS from the Ciellos company. It's free and really useful for some development tips and even more for its data model export tool.

With this add-on, you could actually export an ERD diagram of data model for several tables, with fields, keys and table name, something really missing on the standard platform. 

The previous release of Dynamics AX has got this kind of tools on the IDE (Docs MS).

For any Data Model visualization or reverse engineering requirements, it could be your best friend.

Other productivity features are available such as 

- Parm method auto creation

- find method/exist method pattern

- Label creation

- Create CoC

- Reports

New Feature to aggregate data on the new grid component

From 10.0.9 release of Dynamics 365 FO, there are some new features really interesting for users to setup data based on grid componant.

- (Preview) Grouping in grids : allows users to group data within grids. This empowers users to analyze data and calculate subtotals per group. Grouping can be saved as part of the saved view definition when the saved views feature is enabled.

- (Preview) New grid control : faster rendering, smoother scrolling, and less jumping when navigating in the grid. The new grid also allows drag-and-drop column reordering, entry of mathematical expressions in numeric cells, the ability to calculate totals at the bottom of numeric columns, and the ability to get ahead of the system when editing or creating data.

- (Preview) Saved views : enables saved views, which is a significant enhancement to the personalization subsystem. This feature allows users to have multiple named sets of personalizations per page. Views can also be published to security roles and legal entities.

Let's see the result :

Grouping

Total

Final result

Grouping :https://docs.microsoft.com/en-us/dynamics365-release-plan/2020wave1/finance-operations-crossapp-capabilities/grouping-subtotals-grids

Remark : This feature should be enable after the new grid componant feature (dependancy).

The save view :

Save your current view (filtering, grouping, order of columns) by clicking on the Standard view button. Save your view with a name.

With the new grid componant, you could by drag and drop, change the order of column easily. When your changes are done, a star appears on your view name.

You can also publish your view by clicking on the three dots on the popup of the view name

As you can see, a lot of option are available on the publishing context setup (company, roles)

Administrators could retrieve the view from this feature on System administration module > Setup > Personalization

List of batch to follow on Dynamics 365 FO

Here is a very good article on Batch List to manage for Dynamics 365 Finance & Operation

https://kurthatlevik.com/2020/01/20/batch-jobs-take-control-of-the-executions/

MetadataSearch vs NotePad++ for Dynamics 365 FO technical search

 I'm very fan of the famous and old tool NotePad++. It is a powerful tool, simple and free with a lot of feature. I'm sure there is better, but what is really interesting for me on NotePad++ is :

Portable edition available for using it on server without any issue

Plugin a portable also

I can use Regex on NotePad++.

I usually search standard code to implement custom code on Dynamics 365FO, to be sure to not reimplement something, take the right pattern or jsut to see new pattern of code from Microsoft and align technical design of them (hate to start from scratch something).

So 2 way to achieve search of code on D365FO solution :

Metadatasearch : Feature available on Visual Studio from the Dynamics 365 button

Here is the link of Microsoft and nothing miore to say about it (maybe, it could be interesting to have more sample of search...) 

https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-tools/metadata-search-visual-studio

Pro's : 

- Integrated

- you can type object your search

Con's :

- Slow

- You can't save your result

- Too dynamics > When you search something, result are searching. So if you don't know exactly what you are searching (sort of pattern or multiple search), not really easy.

NotePad++ :

By searching in a directory, filtering on XML files, we can achieve the same goal as Metadatasearch and increase it by the regex use

For instance, if you miss how to implement a particular Event Handler with XppPrePostArgs parameter :

If you should to find in the code something with OR criteria : Suing the "|" for work item, comment in the code

For name of object search, you can use Regex directly in the application Explorer !

How to synchronize manually database and model on Dynamics 365 for Operations

Hi everyone,

Today, another tips to help you synchonize manually some objets between models and your database (Table, Type, Views, Entities ...)

Visual Studio uses actually a command to do this job on each process of synchronization from IDE (on Visual Project contextual menu for example or in the Dynamics 365 Menu).

To use this command ourself, we need to know it !
Type C:\AosService\PackagesLocalDirectory\Bin\SyncEngine.exe /? in a command prompt and you could see command options

Usage: 

SyncEngine -option=[value]
-metadatabinaries   Specify the metadata provider path
-binfolder   Specify the location of the bin folder
-metadatafolder   Specify the location of the metadata
-connect   Specify the business database connection string
-syncmode   Specify the sync mode. Supported mode: {fullall | initialschema | fullsecurity | fullids | fulltablesandviews | partiallist | drop | drop,partiallist | partialsecurity | partiallist,partialsecurity}
-synclist   Specify list of tables or views that need be synced
-droplist   Specify list of tables or views that need be dropped
-rolelist   Specify list of roles that need be synced
-roleExtensionlist   Specify list of role extensions that need be synced
-dutylist   Specify list of duties that need be synced
-dutyExtensionlist   Specify list of dutie extensions that need be synced
-privilegelist   Specify list of privilegs that need be synced
-policylist   Specify list of security policies that need be synced
-droproles   Specify list of security roles that need be dropped
-droproleextensions   Specify list of security role extensions that need be dropped
-dropduties   Specify list of security duties that need be dropped
-dropdutyextensions   Specify list of security duty extensions that need be dropped
-dropprivileges   Specify list of security privileges that need be dropped
-droppolicies   Specify list of security policies that need be dropped
-midisplaylist   Specify list of display menu items that was changed for the table permission synchronization
-miactionlist   Specify list of action menu items that was changed for the table permission synchronization
-mioutputlist   Specify list of output menu items that was changed for the table permission synchronization
-formlist   Specify list of forms that was changed for the table permission synchronization
-reportlist   Specify list of reports that was changed for the table permission synchronization
-sqltimeout   Specify Sql Command timeout in minutes
-verbosity   Specify logging verbosity level. Supported level: {Normal | Diagnostic}
-ignoreIndexList   Specify list of table with index that need to be ignored during Sync. The Expected format : {TableA.IndexA, TableB.IndexB}

Some examples of the command :
Usual command for a full compil :

C:\AosService\PackagesLocalDirectory\bin\syncengine.exe -syncmode="fullall" -metadatabinaries="C:\AosService\PackagesLocalDirectory" -connect="Data Source=localhost;Initial Catalog=AxDataBase;Integrated Security=True;Enlist=True;Application Name=AXVSSDK" -verbosity="Minimal"

Command for a partial compil of object's list (here just the CustCustomerEntity)

C:\AosService\PackagesLocalDirectory\Bin\SyncEngine.exe -syncmode="partiallist" -metadatabinaries=C:\AosService\PackagesLocalDirectory -connect="Data Source=AxServer.database.windows.net;User ID=admin@AxServer.database.windows.net;Password=xxxxxxxxx;Initial Catalog=DataBaseName;Integrated Security=false;Enlist=True;Application Name=SyncEngine" -verbosity="Diagnostic" -synclist="CustCustomerEntity"

Duplicates ID on two models in Dynamics 365 for operations

Hi everyone,

Today, I would like to speak about Ids issue during environment update with Microsoft KBs.
I don't know if this solution is a best practice, but it works, and that's all I wanted at this time.

Update process contains two main steps : Preparation and installation.
During installation process, the system could notice you of a duplicate Id issue because a package (a custom!) has the same Id as a new one, present in the update.

In this case, you could : Export all code of your package, uninstall it, install the new standard package with the famous reserved Id, then create a new custom package for your customization and import your code in.
It's a bummer like the "Dude" will say, isn't it ? :)

So the shortest way could be : Stop IIS, edit descriptor of your custom package, change the ID, save and start IIS. Just to be safe, you could open Visual and compil all your application.

Launch a new installation of your previous preparation update, and Duplicate Ids issue will in theory vanish.

Happy Dynamics update!