最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

如果“排序字段”为空,则按另一个字段排序 / If Sort-Field is empty sort by another field

网络文摘 William 118浏览 0评论
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
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址