At work we recently discussed a customer requirement regarding sorting of a SalesTable data set in Dynamics Ax. The requirement was to sort by ShippingDateConfirmed. If the order has no confirmation date yet, use the ShippingDateRequested instead.
If exists sort by Shipping Date Confirmed otherwise by Shipping Date Requested
There are several ways to implement this requirement. Depending on the technology you can use SQL code, computed columns in Dynamics Ax 2012+ or a union query in AX 2009.
SQL: Select CASE
The easiest way to achiev the goal is using pure SQL code where you can define a new column within the select statement and use it for sorting. Here is an example:
SELECT
SalesId, SalesName,
ShippingDateRequested, ShippingDateConfirmed,
CASE
WHEN ShippingDateConfirmed = '1900-01-01 00:00:00.000'
THEN ShippingDateRequested
ELSE ShippingDateConfirmed
END
AS ErpSortField
FROM SalesTable
WHERE DataAreaId = 'CEU'
ORDER BY ErpSortField
The result in SQL Server Management Studio for a Dynamics Ax 2009 database looks like this:
SELECT CASE WHEN .. THEN .. ELSE .. END in SQL
You may use such a SQL query as data source for an SSRS report
SSRS Report based on AX 2009 Sales Order
Dynamics 365 F/SCM: Computed Column
Since AX 2012 we can use computed columns in views. One way to address this requirement is to create a column that contains the same CASE – WHEN SQL Statement. To do so create a new view based on the SalesTable. Add a new static method:
Add a computed column to the view and set the method as view method. Build and synchronize.
View with computed column in Dynamics 365 Finance
This will result in the following SQL definition in the AXDB:
Generated SQL view code in AxDB
Use the view as data source in form:
View in Dynamics 365 F/SCM form
Dynamics AX 2009: Union Query
Older versions of Dynamics AX link 2009 computed columns were not supported. One workaround is to use a UNION Query.
First create a new view called ERPSalesTableConfirmed. Set the SalesTable as data source. Add a range based on the ShippingDateConfirmed field and set the range value to != ” (i.e. not empty). Add a view field based on the ShippingDateConfirmed and call it ERPSortField. This view will return all SalesTable records with a confirmed shipping date and a new field with the value in it.
SalesTable with confirmed shipping date
Second, create a new view called ERPSalesTableRequested. Set the SalesTable as data source. Add a range based on the ShippingDateConfirmed and set the range value to = ” (i.e. empty). Add a view field based on the ShippingDateRequested and call it ERPSortField. This view will return all SalesTable records without a confirmed shipping data and use the ShippingDateRequested for the ERPSortField.
SalesTable with requested shipping date
Next, create a query called ERPSalesTableSort. Set the query type to UNION. Add both views as data source. The execution of this query will return all SalesTable records. If the sales order was confirmed, the ERPSortField will contain the ShippingDateConfirmed value, otherwise the ERPSortField will contain the ShippingDateRequested.
UNION query in Dynamics AX 2009
Finally, create a new view called ERPSalesTableSort based on the query with the same name. Use all fields you like to see and the ERPSortField.
Dynamics AX 2009 view based on UNION query
Open the view. The result is a SalesTable dataset that can be sorted on the confirmed shipping date, and if the confirmed date is not present sorted by the requested date.
Sort SalesTable in Dynamics AX 2009 by confirmed or requested shipping date
Azure Functions are simple way to pack and provide business logic as web service without worrying about hosting a web server. Azure Functions can be implemented in different programming languages like C#, JavaScript, PHP, Java, etc. and can be hosted on Linux and Windows with different runtime environments that feed your need.
In the Azure Portal click + Create a resource and search for Function App:
In the next screen choose a subscription and create a resource group (or use an existing one if you like). Provide a useful name and choose code as Publish method. Select .NET Core 3.1 as runtime stack and a region that is near your location:
Click Review + Create to create the Azure Function. It takes a view minutes to provision all the required elements:
Click on Go to Resource. Next to the Functions group click + to create a new function and select In-Portal to edit the function code direct in the browser:
Choose the webhook + API to create a demo function that can be called via HTTP POST.
This will create a function that takes a name as parameter and returns “Hello ” + the parameter name.
You can test the function by using Test tab on the right. The function takes a JSON string with a name parameter and returns a simple string.
Call the function from X++
In the azure portal get the function URL with a function key. Copy the URL with the key:
In Visual Studio create an X++ class with a main method for testing. Use the System.Net.Http.HttpClient class to call the service. The content is a JSON string encoded in UTF-8 with a name parameter and value. In this example the name is Dynamics:
System.Net.Http.HttpClient httpClient = new System.Net.Http.HttpClient();
System.Net.Http.HttpContent content = new System.Net.Http.StringContent(
"{\"name\":\"Dynamics\"}",
System.Text.Encoding::UTF8,
"application/json");
At the moment X++ does not support the await keyword for asynchronouse calls. The workaround is to use the Task.Wait() method. Call the service with your function URL async and get the content of the call:
var task = httpClient.PostAsync("https://<YOUR_FUNCTION_URL>",content);
task.Wait();
System.Net.Http.HttpResponseMessage msg = task.Result;
System.Net.Http.HttpContent ct = msg.Content;
var result = ct.ReadAsStringAsync();
result.Wait();
System.String s = result.Result;
info(s);
Start the class from Visual Studio. The result should look like this:
This is an update to my older post how to extend the SalesTable 2 Line framework. The big difference is that in Dynamics 365 Finance and SCM overlaying is not supported and extensions and delegates need to be used. This post uses the same use case. A sales-notes field from the SalesTable needs to be updated in the SalesLines if it is configured so.
Create a new string datatype and call it ERPSalesNote. Extend the SalesLine and add the ERPSalesNote datatype to the list of fields. Extend the SalesTable and add the ERPSalesNote to the fields. Also add the ERPSalesNote field to the field group HeaderToLineUpdate.
Extend the user interface
Extend the SalesTable form. Place the SalesTable.ERPSalesNote in the Delivery group of the HeaderView tab.
Add the SalesLine.ERPSalesNote field to the delivery group in the LineView tab.
Prepare the update-order-lines dialog
The dialog to choose if and which fields need to be updates at the lines is generated automatically based on the HeaderToLineUpdate field group. There is some code needed to show the fields name in the dialog. This is done by subscribing a custom method to the delegate SalesTable2LineField.lineUpdateDescriptionDelegate
Create a new class that returns a name for the ERPSalesNote field.
Open the Header to Line update dialog by clicking on Accounts receivable > Setup > Accounts receivable parameters > Tab update > update order lines
Extend the framework classes
Create an extension for the AxSalesTable class and create a parm method for the ERPSalesNote field
[ExtensionOf(classStr(AxSalesTable))]
final class AxSalesTable_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
if (!prmisDefault(_salesNote))
{
this.setField(fieldNum(SalesTable, ERPSalesNote), _salesNote);
}
return salesTable.ERPSalesNote;
}
}
Create an extension for the AxSalesLine class. Implement a parm and set method. Use the chain of command pattern to extend the setTableFields method.
[ExtensionOf(classStr(AxSalesLine))]
final class AxSalesLine_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
if (!prmisDefault(_salesNote))
{
this.setField(fieldNum(SalesLine, ERPSalesNote), _salesNote);
}
return salesLine.ERPSalesNote; } protected void setERPSalesNote() { if (this.isMethodExecuted(funcname(), fieldnum(SalesLine, ERPSalesNote))) { return; } this.setAxSalesTableFields(); if (this.isAxSalesTableFieldsSet() || this.axSalesTable().isFieldModified(fieldnum(SalesTable, ERPSalesNote))) { this.parmERPSalesNote(this.axSalesTable().parmERPSalesNote()); }} protected void setTableFields() { next setTableFields(); this.setERPSalesNote(); }
}
Test your implementation
Make sure that the update method in the parameter is set to prompt. Open an existing sales order. Change to Header view and switch to edit mode. Change the notes in the delivery tab and save.
A dialog pops up and asks to update the lines. Click yes.
Check the sales note field in the sales line. The note field at the sales line should be updated with your text from the header.
Recently, a customer using Dynamics AX 2009 implemeted a web service that access a view directly in SQL Server. Therefore they created a new SQL user login and gave the user read permissions on the view.
Read permission on a view
However, when synchronizing the data dictionary in Dynamics AX 2009, the views are droped and recreated and the permission on the object is lost. Therfore the webservice call fails.
One way to address this issue from a SQL perspective is to create a DDL trigger that sets the permissions on the view programmatically. Here is a small SQL script that sets read permissions for the user view_user on the the DIRPARTYVIEW after the view has been created again.
CREATE TRIGGER [VIEW_PERMISSION]
ON DATABASE
FOR CREATE_VIEW
AS
BEGIN
DECLARE @name SYSNAME
SELECT @name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
if @name = 'DIRPARTYVIEW' begin
GRANT SELECT ON [dbo].[DIRPARTYVIEW] TO [view_user]
end
END
GO
ENABLE TRIGGER [VIEW_PERMISSION] ON DATABASE
GO
Power Apps recently got the capability to create some computer vision AI models. One of the IMHO most popular ones is called object detection, which is used to detect (predefined) objects on images. For getting started you may use the free edtion of power apps.
Environment and CDM Entity
AI Builder is bound to a Power Apps environment and the Common Data Model. If you don’t have already created an environment, logon to https://web.powerapps.com and create a new environment.
Create new environment
To use the CDM entities you need a new database. In my case, I created a new one with USD and English as preferred language.
Create new database
It may take a while, and you may need to refersh your browser screen, but the AI Builder (Preview) option will appear on the left menu bar.
Create new Power Apps AI Builder Object Detection model
Next, go to Data > Entites and create a new entity for the type of objects you want to identify. In my case, I’m playing around with Nerf guns, therefore I created a new Nerfgun entity. It requires at least a useful name. Feel free to add more fields.
Select entity from Common Data Model
Provide entity data
In a next step you have to provide information about the different elements that shall be identified. In my cases, which Nerfs guns will be on fotos e.g. Rapidstrike, Slingfire, Cyclonshot, etc.
Entity in Common Data Model
There is an option to edit the entity via Excel. However, in my case the Excel addin is not working, and failing to authenticated 😦 If this happens to you, a workaround is to create a simple Power App and edit the entity via Power App.
Fill CDM entity with data using Power Apps
Create and train AI model
In Power Apps go to AI Builder and create a new model for object detection.
Create new AI model for object detection
Provide a name for the model and select the created entity for detection. From the records in the entity select those records that are relevant for detection.
Select object to detect using AI builder
Next comes the time consuming part, take pictures of your objects in different locations, lighting, quality, day time, etc. upload it to the power apps model. You need at least 15 pictures for each element you want to detect.
Upload images to Power Apps AI Builder
Tag each of the pictures with the corresponding object on the picture.
Tag object on images in Power App AI Builder
After uploading and tagging each picture, train the model. This may take a while and will result in a (not very usefull) quality estimation. Right now I didn’t see recall, precision, AUC, or any other more detailed information. If you are satisfied with the models estimated power, publish it so it can be used in your apps.
Train AI model for object detection
Use AI model in Power Apps
It’s easy to use the trained model in a Power Apps application e.g. on the smart phone. Create a new power app with empty layout. From the menu insert the object detection component.
Create a new PowerApps app with AI Builder
Select your object detection model for the component. Save and publish your app, load it on your phone and test it.
Since version 10 Dynamics 365 for Finance and Operations supports the entity store export to Azure data lake. The main benefits are reduced costs because Azure Cloud storage is cheap and easy access for Business Intelligence tools like PowerBI.
If you are running a local development VM, the data connection tab in system parameters ist deactived by default. However, this can be actived using the SysFlighting table.
The configuration is pretty well documented by Microsoft. I’ve performed all the necessary steps and recorded a video:
Calling a web service with HTTP Basic Authentication is easy in C#. Here I’m using a REST service via HTTP GET which is secured via Basic Authentication.
C# Code
A HTTP GET webservice call using System.Net.WebRequest and System.Net.Webrespose in C#
The product configuration model in Dynamics 365 for Finance and Operations is a great tool dynamically generate production orders including bill of materials (ProdBOM) and routes. In the product configuration model, the user is free to define attributes of different types, and use these attributes for calculations and conditions within the model. However, I had to come up with a solution to write the attribute value from the production configuration wizard to the generated ProdBOM. Here is an example code how this can be done:
Data type, table and form extensions
Create a new string EDT and call it ERPPCStringAttribute
Create table extensions for the BOM and ProdBOM and add a new field based on the ERPPCStringAttribute EDT
Create a form extension for the ProdBOM and add the new field in the grid (Tab > Overview > Grid)
Extend the PCBOMLineDetails form
This is a tricky part. In the form for the BOM line details in the product configuration model, create a section for the attribute that looks the same like all the others. Here is a screenshot from Visual Studio:
You need to create a group, hat contains another group, with a checkbox and group with a string edit field and a radio button. Make sure to name the elements like shown in the screenshot. If you are not sure, compare it with the other existing groups e.g. the SubContractor. You will need to compare the properties of each element with the corresponding properties of an existing element to make it look similar.
Extend PCBOMLineDetails Form UI logic
You cannot overwrite methods on UI elements in form extensions e.g. clicked() . Therefore you have to implement the logic for the UI elements in a separate class. Create a new class ERPPCBomLineDetailsEventHandler and implement the following UI logic for clicked(), modified() and lookup()
Like many forms in Dynamics 365 for Finance and Operations the form has a class-behind that implements the business logic. You need to extend this class in order to deal with the newly created Attribute group. Create a new class ERPPCBomLineDetails_Extension and impelement the following logic:
[ExtensionOf(formStr(PCBOMLineDetails))] final class ERPPCBomLineDetails_Extension { public PCClass component() { return component; }
[ExtensionOf(FormMethodStr(PCBOMLineDetails,loadAllocations))] public void loadAllocations() { PCTemplateAttributeBinding templateAttributeBinding; PCTemplateAttribute fieldReference;
Implement the following classes to extend the product configuration model framework in Dynamics 365 Finance and Operations:
PcAdaptorBOMLine class:
[ExtensionOf(classStr(PcAdaptorBOMLine))] final class ERPPcAdaptorBOMLine_Extension { public ERPPCStringAttribute parmWANPCStringAttribute( ERPPCStringAttribute _stringAttribute = bom.ERPPCStringAttribute) { EcoResTextValue value;
By default the framework does not recognize to create template records for the newly added attribute field. Therefore you have to delete the existing templates and trigger the framework to reinitialize. Be aware, this might harm your existing models!
PCTemplateInitialize class:
[ExtensionOf(classStr(PcTemplateInitialize))] final class ERPPCTemplateInitialize_Extension { public static void main(Args _args) { PCTemplate tableTemplate; PcTemplateInitialize init = PcTemplateInitialize::construct(); delete_from tableTemplate; init.run(); } protected void createTemplatesForBOM() { next createTemplatesForBom(); PCTemplate tableTemplate; select firstonly tableTemplate where tableTemplate.ReferencedTableId == tableNum(BOM); this.createFieldTemplate(tableTemplate,fieldNum(BOM, ERPPCStringAttribute )); } }
Start this class from Class Runner or add a menu item to call it by hand.
Test your Implementation
Create a new product configuration model
Add a new attribute “notes” of type string
Add a BOM line to the model
Open the BOM line details and assign the notes attribute to the string attribute 1
Save the model
Create and approve the model version
Create a new production order
Use the configuration wizard to provide a text value for the notes attribute
Create the production order
Verify that you can see the attribute value in the ProdBOM of your production order
A very common task required by Dynamics 365 Finance and Operations clients is to send a report directly from the report viewer. This can be achieved with a view lines of Code. Here is video on Youtube how it works:
This video explains how you can use the product configuration model in D365 FO to model a heater. The heater can have a length between 0.5 m – 4,5 m, can have a certain color and provide an analog handle, a touch panel or a remote control. Touch panel and remote control is only available for heaters > 1m . A heater contains of heating units (4x 1m), requires connector modules between each 4 heating units and an enclosed. The product configuration model is used to define these requirements and constrains, provide a wizard for production configuration and generates the correct BOM.
