If Sort-Field is empty sort by another field

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
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
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:

private static server str compColShippingDate()
{
  #define.ViewName(MBSSalesTableView)
  #define.DataSourceName("SalesTable")
  #define.FieldConfirmed("ShippingDateConfirmed")
  #define.FieldRequested("ShippingDateRequested")
  str sReturn;
  str sRequested, sConfirmed;
  DictView dv = new DictView(tableNum(#ViewName));

  sRequested = dv.computedColumnString(
                   #DataSourceName,
                   #FieldRequested,
                   FieldNameGenerationMode::FieldList);
  sConfirmed = dv.computedColumnString(
                   #DataSourceName,
                   #FieldConfirmed,
                   FieldNameGenerationMode::FieldList);

  sReturn = "CASE WHEN " 
          + sConfirmed + " = '1900-01-01 00:00:00.000' THEN " 
          + sRequested + " ELSE " + sConfirmed + " END";

  return sReturn;
}

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
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
Generated SQL view code in AxDB

Use the view as data source in form:

View in Dynamics 365 F/SCM 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
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
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
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
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
Sort SalesTable in Dynamics AX 2009 by confirmed or requested shipping date

Extend SalesTable2Line Framework (Dynamics 365 Finance / SCM)

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.

Download the sample source code: https://erpcoder.blog/source-code/

Extend the data model

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.

class ERPSalesTable2LineField
{
[SubscribesTo(classStr(SalesTable2LineField), delegateStr(SalesTable2LineField,lineUpdateDescriptionDelegate))]
public static void lineUpdateDescriptionDelegate(FieldId _fieldId, TableId _tableId, EventHandlerResult _result)
{
  if(_tableId == tableNum(SalesTable) &&
  _fieldId == fieldNum(SalesTable,ERPSalesNote))
  {
    _result.result("Sales Note");
  }
}
}

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.

Call REST Webservice with HTTP Basic Authentication from X++

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#

string url = “http://yourhost.net/service”;
string user = “YourUserName”;
string pass = “YourPassWord”;

byte[] bytes = System.Text.Encoding.UTF8.GetBytes(user+”:”+ pass);
string base64  = System.Convert.ToBase64String(bytes);

WebRequest request = WebRequest.Create(url);
request.Headers.Add(“Authorization”, “Basic “+base64);
WebResponse response = request.GetResponse();
string wsResponse = new StreamReader(response.GetResponseStream())
.ReadToEnd();

Console.WriteLine(wsResponse);
Console.ReadKey();

X++ Code

Since Dynamics 365 FO does not support packages and some syntactic sugar from C# the code is more wordy.

str url = “http://yourhost.net/service”;
str user = “YourUserName”;
str pass = “YourPassword”;

System.Text.Encoding encoding = System.Text.Encoding::UTF8;
System.Byte[] bytes = encoding.GetBytes(user + “:” + pass);
System.String base64 = System.Convert::ToBase64String(bytes);

System.String headerKey = “Authorization”;
System.String headerValue = “Basic ” + base64;
System.Net.WebRequest request = System.Net.WebRequest::Create(url);       
System.Net.WebHeaderCollection headers = request.Headers;
headers.Add(headerKey,headerValue);

System.Net.WebResponse response = request.GetResponse();
System.IO.StreamReader reader = new System.IO.StreamReader(
response.GetResponseStream());
str wsResponse = reader.ReadToEnd();

info(wsResponse);

Extend PurchTable2Line Framework (AX 2012)

This is a follow-up to my initial blog post how to extend the SalesTable2Line Framework from 2011. However, this post is a walkthrough how to update PurchLine fields from the PurchTable header.

Fields and Field Groups

Create an extended datatype called ERPCarrier which extends the Name datatype. Provide a label called Carrier.On the PurchLine create two new fields called ERPCarrierRequested and ERPCarrierConfirmed based on the datatype ERPCarrier. Provide two meaningful labels, Requested Carrier and Confirmed Carrier. Create a field group called ERPCarrier and add both fields to the group.

On the PurchTable add two new fields called ERPCarrierRequested and ERPCarrierConfirmed based on the datatype ERPCarrier. Provide the same labels as on the PurchLine. Create a field group called ERPCarrier and add both fields to the group. Moreover, add both fields to the field group HeaderToLineUpdate!

image

On the PurchTable form, add the PurchTable field group ERPCarrier in the header view in the group delivery.

image

Add the PurchLine field group ERPCarrier in the line view in the tab delivery.

image

Code

On the AxPurchTable class add two parm Methods for the two new fields

public ERPCarrierId parmERPCarrierConfirmed(ERPCarrierId _carrierId = ”)
{
if (!prmisDefault(_carrierId))
{
this.setField(fieldNum(PurchTable, ERPCarrierConfirmed), _carrierId);
}

    return purchTable.ERPCarrierConfirmed;
}

public ERPCarrierId parmERPCarrierRequested(ERPCarrierId _carrierId = ”)
{
if (!prmisDefault(_carrierId))
{
this.setField(fieldNum(PurchTable, ERPCarrierRequested), _carrierId);
}

    return purchTable.ERPCarrierRequested;
}

On the AxPurchLine class add two parm methods for the two new fields

public ERPCarrierId parmERPCarrierConfirmed(ERPCarrierId _carrierId = ”)
{
if (!prmisDefault(_carrierId))
{
this.setField(fieldNum(PurchLine, ERPCarrierConfirmed), _carrierId);
}

    return purchLine.ERPCarrierConfirmed;
}

public ERPCarrierId parmERPCarrierRequested(ERPCarrierId _carrierId = ”)
{
if (!prmisDefault(_carrierId))
{
this.setField(fieldNum(PurchLine, ERPCarrierRequested), _carrierId);
}

    return purchLine.ERPCarrierRequested;
}

Next, on the AxPurchLine class add two set methods

protected void setERPCarrierConfirmed()
{
if (this.isMethodExecuted(funcName(),
fieldNum(PurchLine, ERPCarrierConfirmed)))
{
return;
}

    this.setAxPurchTableFields();

    if (!this.parmERPCarrierConfirmed() &&
this.axPurchTable().parmERPCarrierConfirmed())
{
this.parmERPCarrierConfirmed(
this.axPurchTable().parmERPCarrierConfirmed());
}
}

protected void setERPCarrierRequested()
{
if (this.isMethodExecuted(funcName(),
fieldNum(PurchLine, ERPCarrierRequested)))
{
return;
}

    this.setAxPurchTableFields();

    if (!this.parmERPCarrierRequested() &&
this.axPurchTable().parmERPCarrierRequested())
{
this.parmERPCarrierRequested(
this.axPurchTable().parmERPCarrierRequested());
}
}

On the AxPurchLine class add a new static method which is used to set the new fields.

public static void setTableFields_ERPCarrier(XppPrePostArgs _args)
{
AxPurchLine     thisAxPurchLine = _args.getThis();
thisAxPurchLine.setERPCarrierRequested();
thisAxPurchLine.setERPCarrierConfirmed();
}

On the AxPurchLine class, go to the setTableFields method and expand the event handler. Add a new Post X++ event handler. Provide the AxPurchLine as class for the event handler and the newly created method setTableFields_ERPCarrier as event handler method.

image

On the PurchTable2LineField class, open the getFieldDescription method and scoll down. Add  the following code to handle the two fields.

case fieldNum(PurchTable, ERPCarrierConfirmed):
description =  fieldid2pname(tablenum(PurchLine),
fieldnum(PurchLine, ERPCarrierConfirmed));
break;

case fieldNum(PurchTable, ERPCarrierRequested):
description =  fieldid2pname(tablenum(PurchLine),
fieldnum(PurchLine, ERPCarrierRequested));
break;

Test

Compile your code an build incremental IL. Open the table PurchTable2LineParameters and delete all records. Restart the AOS to make sure no cached version is used. In AX go to Accounts Payable > Settings > Parameter > Tab Updates and click the button “Update order lines”. Set the Update Requested Carrier and Confirmed Carrier to Always.

image

Open a purchase order in AX and edit the purchase header. Provide a requested carrier e.g. UPS and a confirmed carrier e.g. DHL. Save your changes. Check if the values from the header have been copied to the purchase lines.

Multiple Aggregations in X++ Join

At work we’ve recently discussed how to calculate a customers invoice amount for multiple years. One idea was to loop over the customer accounts (while select)  and individually calculate the sum for each year with individual statements (select sum). However, this can be done by a single statement.

CustInvoiceJour     custInvoiceJour;
CustInvoiceTrans    custInvoiceTrans1;
CustInvoiceTrans    custInvoiceTrans2;

while select InvoiceAccount,InvoiceId from custInvoiceJour
group by InvoiceAccount
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1
where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId &&
custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011’) &&
custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011’)
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2
where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId &&
custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012’) &&
custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012’)
{
info( strFmt(“Customer:%1 2011:%2 2012:%3”,
custInvoiceJour.InvoiceAccount,
custInvoiceTrans1.LineAmount,
custInvoiceTrans2.LineAmount));
}
//Customer:DE-001 2011:3.814.145,50 2012:3.842.223,00

This will result in the following output

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join

Add Sound to Infolog

I’ve been asked by a colleague if it is possible to let the Infolog make a noise depending on the log type, e.g. a *beep* for an info, a *pling* for a warning and an evil noise for an error. Fortunately we can use .NET in AX 2009. The System.Media.SystemSound class already provides access to the typical system sounds. I’ve modified the Info class in AX this way:

Exception add(
    Exception _exception,
    str _txt,
    str _helpUrl = ”,
    SysInfoAction _sysInfoAction = null,
    boolean buildprefix = true)
{

    int numOfLines,i;
    int actionClassId;
    container packedAction;
    xSession session;   

    System.Media.SystemSound sound;
    InteropPermission permission;

    ;

// default infolog code here …

    permission = new InteropPermission(Interopkind::ClrInterop);
    permission.assert();
   
switch(_exception)
    {
        case(Exception::Info):
        sound = System.Media.SystemSounds::get_Asterisk();
        break;
        case(Exception::Warning):
        sound = System.Media.SystemSounds::get_Hand();
        break;
        case(Exception::Error):
        sound = System.Media.SystemSounds::get_Exclamation();
        break;
    }    
    if(sound!=null)
        sound.Play();

    CodeAccessPermission::revertAssert();

    return super(_exception, (buildprefix?getprefix():”)+_txt);
}

Visualize DocuRef notes in grid

With Dynamics AX you can attach document references like Word files, Excel sheets, mail, fax and notes to any displayed record. The icon to do so can be found in the forms toolbar. Unfortunately it’s hard to discover if a document is attached to a record. Therefore it’s helpful to display an icon in the overview grid to indicate attached documents.

document references

Create a new Integer extended data type called DocuRefIcon and label it with Documents (@SYS124737). Set the help text to Document Handling (@SYS22623).

Add a display method showDocuRefIcon() to the CustTable. Document references use a RefTableId and RefRecId to reference any record in Dynamics. If there is a document reference for the current CustTable record return resource id 3028 otherwise return id 0.

//BP deviation documented
display DocuRefIcon showDocuRefIcon()
{;
    if(DocuRef::exist(this.dataAreaId,this.TableId,this.RecId))
        return 3028;
 
    return 0;
}

Open the CustTable form and a new icon to the Overview grid. Set width and height properties to 16. Set CustTable as datasource and showDocuRefIcon as data method. Save and open the form. Attach a document reference to a customer and you’ll see a notes icon in the new grid column.

document handling icon in custtable grid