Dynamics AX | ErpCoder

Integrate SharePoint Online with Dynamics AX Legacy Application

5. February 2018 Leave a comment

SharePoint Online is a wide spread solution for collaboration. Actual versions of Dynamics AX / 365 provide great integration capabilities and for all other purpose there exists the SharePoint Online Client API. However, legacy applications may not support the required libraries or .NET framework versions to use the SharePoint Online Client API. One way to overcome this issue is to utilize the REST API and communicate via HTTP. Here you can find a step by step guide how to provide an external application access to SharePoint Online via an App: http://www.ktskumar.com/2017/01/access-sharepoint-online-using-postman/

Register an App in SharePoint Online

The first thing you need to do is register an App in SharePoint Online. Open the registration site in a browser: https://YOUR_SITE.sharePoint.com/_layouts/15/appregnew.aspx . In my case I’m using a sub site, therefore my URL would look like this: https://YOUR_SITE.sharepoint.com/sites/development/_layouts/15/appregnew.aspx .Use the form to generate a client ID and a secret code. Provide any title you like. Set the domain to localhost and redirection URL to https://localhost

Set Permissions for the App

Open the following URL in a Browser: https://YOUR_SITE.sharepoint.com/_layouts/15/AppInv.aspx . Use the Client ID to lookup the created App. In the free text form add the required permissions e.g. access a list. You can find the syntax here: https://www.sumitagrawal.io/sharepoint-add-in-permission-xml-cheatsheet/ If you need access to more than one list, you have to repeat this step. In my case I only need access to one list. The XML Permission Code looks like this:

<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web/list" Right="Read" /> </AppPermissionRequests>

Click the Create button, this will forward you to a form where you can choose which list can be accessed by the app.

SharePoint Online Tenant ID

To find the Tenant ID open the following URL in your Browser https://YOUR_SITE.sharepoint.com/_layouts/15/appprincipals.aspx Since I’m using a sub site my URL looks like this https://YOUR_SITE.sharepoint.com/sites/development/_layouts/15/appprincipals.aspx . There you can find the Tenant ID

Create a Token

To interact with SharePoint Online you need to generate a token.This is done by sending an HTTP POST requiest with client ID, secret, and desired resource to SharePoint Online. Here is the Code in X++ written in Ax 2009.

str clientID = “<YOUR_CLIENT_APP_ID>”;

str secret = “<YOUR_SECRET>”;

str tenant = “<YOUR_TENANT>”;

str ctx = “00000003-0000-0ff1-ce00-000000000000”; //i.e. SharePoint

str sp = “<YOUR_SITE>.sharepoint.com”;

str listName = “<NAME_OF_YOUR_LIST>”; // e.g. Customers

str listUrl = “https://YOUR_SITE.sharepoint.com/_api/web/lists/GetByTitle(‘”+listName+”‘)/items; “

str url = “https://accounts.accesscontrol.windows.net/”

+tenant+”/tokens/OAuth/2″;

System.Net.WebClient cl = new System.Net.WebClient();

System.Net.WebHeaderCollection headers = new System.Net.WebHeaderCollection();

System.Collections.Specialized.NameValueCollection body = new System.Collections.Specialized.NameValueCollection();

System.Byte[] response;

System.Text.Encoding enc = System.Text.Encoding::get_UTF8();

System.String responseText;

str xppResponse;

int indexOf;

int start;

int totalLength;

int length;

str token;

;

headers.Add(“Content-Type”,”application/x-www-form-urlencoded”);

body.Add(“grant_type”,”client_credentials”);

body.Add(“client_id”,clientID+”@”+tenant);

body.Add(“client_secret”,secret);

body.Add(“resource”,ctx+”/”+sp+”@”+tenant);

cl.set_Headers(headers);

response = cl.UploadValues(url,”POST”,body);

responseText = enc.GetString(response);

// cut token out of response text

indexOf = responseText.IndexOf(“access_token”);

start = indexOf + 15;

totalLength = responseText.get_Length();

length = totalLength – start – 2;

xppResponse = responseText;

token = responseText.Substring(start,length);

Query List Elements

The token has a valid timestamp measured in Unix Ticks. So it might be good idea to store the token in a parameter table and only request a new one if it is or will expire soon. However, in this example I go on and query the Customer List.

// .. Code from above

cl = new System.Net.WebClient();

headers = new System.Net.WebHeaderCollection();

headers.Add(“Accept”,”application/atom+xml”);

headers.Add(“Authorization”,”Bearer “+token);

cl.set_Headers(headers);

body.Clear();

url = listUrl;

responseText = cl.DownloadString(listUrl);

xppResponse = responseText;

info(xppResponse);

In this case the response type is an XML. You may parse the XML and only view e.g. the Title Tag or any other fields your are interested in. Here is the Infolog of the complete XML response from the SharePoint Online List.

Filed under Development, Dynamics AX, Office Tagged with SharePoint; Office 365; Dynamics AX; REST

Walkthrough: Configure XML/File Data Export via AIF in AX 2012

31. January 2018 2 Comments

This is an example how to export the LedgerJournalTable + LedgerJournalTrans records as XML via AIF

Create a Query

Create a new query and name it AxdLedgerJournal.
Place the LedgerJournalTable as main Datasource
Set the Fields Property Dynamic to Yes
Add the LedgerJournalTrans as child data source
Set the Fields property Dynamic of the LedgerJournalTrans data source to Yes
Set the Relation property of the LedgerJournalTrans data source to Yes
Set the Fetch Mode property of the LedgerJournalTrans data source to 1:n
Set the Join Mode property of the LedgerJournalTrans data source to Outer Join

Use the AIF Document Wizard to generate the service classes

In the menu bar go to Tools > Application Integration Framework > Create document service
Provide the AxdLedgerJournal query as source query for the document service
Select the options to generate the Read and Find methods
Select the option to generate the AxBC classes
Let the wizard generate the software artifacts for you

Fine tune the generated code

Go to the private projects and open the newly generated project
Compile the project
If the cache() methods don’t compile delete them
Review the open tasks and provide meaningful labels

Configure the Batch Jobs

In AX go to System Administration > Inquires > Batch Jobs > Batch Jobs
Create a new Batch Job and go to “View Task”
Add four task and choose the following classes
AifGatewayReceiveService
AifGatewaySendService
AifInboundProcessingService
AifOutboundProcessingService
Close the Task window and set the Recurrence of the Batch Job to run e.g. all 5 minutes

Configure the File System Adapter

In AX go to System Administration > Setup > Services and Application Integration Famework > Outbound Ports
Create a new Outbound Port
Select the FileSystemAdapter as Adapter
Provide a target location where to place the files e.g. a share with write permissions for the AOS account
Open the Service Operation Dialog and add the LedgerJournalService.read method
Activate the Port

Add a send method

On the LedgerJournalTable add the following method

server void sendElectronically(XMLDocPurpose _xmlDocPurpose,
                               AifSendMode _aifSendMode = AifSendMode::Async)
{
    AxdSendContext      axdSendContext = AxdSendContext::construct();
    AifEntityKey        aifEntityKey   = AifEntityKey::construct();
    Map                 keyData;
    AifConstraint       aifConstraint = new AifConstraint() ;
    AifConstraintList   aifConstraintList = new AifConstraintList();
    ;
    keyData = SysDictTable::getKeyData(this);

    aifEntityKey.parmTableId(this.TableId);
    aifEntityKey.parmRecId(this.RecId);
    aifEntityKey.parmKeyDataMap(keyData);

    axdSendContext.parmXMLDocPurpose(_xmlDocPurpose);
    axdSendContext.parmSecurity(false);

    aifConstraint.parmType(AifConstraintType::NoConstraint);
    aifConstraint.parmId(this.JournalNum);
    aifConstraintList.addConstraint(aifConstraint);

    AifSendService::submitDefault(
        classNum(LedgerJournalService),
        aifEntityKey,
        aifConstraintList,
        _aifSendMode,
        axdSendContext.pack());
}

Test the export

Create a job and use the following code to test the export

static void JobTestAifExport(Args _args)
{
    LedgerJournalTable journal;
    ;
    journal = ledgerJournalTable::find("00001");
    journal.sendElectronically(XMLDocPurpose::Original);
}

In AX got to System Administration > Periodic > Services and Application Integration > Queue Manager. You will find a record for the LedgerJournal export. The Batch Job will first create an XML Document and in a second step perform the export to the file system.

Filed under AIF, Development, Dynamics AX Tagged with AIF; Filesystem; XML Export

Last Day Of Month and Year as View Computed Column in AX 2012

3. October 2017 1 Comment

View Computed Columns in Dynamics Ax 2012 support the use of TSQL functions to calculate additional view columns. Here is an example how to calculate the Last Day of Month and Last Day of Year based on the TransDate at the CustTransView. First, Extend the SysComputedColumnDatePart Base Enum and add two additional values Month and Year. Extend the getDateDiffFormatSelector() method at the SysComputedColumn class.

private static server str
getDateDiffFormatSelector(SysComputedColumnDatePart _dateDiffFormat)
{
    switch(_dateDiffFormat)
    {
        case SysComputedColumnDatePart::Day :
            return ‘d’;
        case SysComputedColumnDatePart::Second :
            return ‘s’;
        case SysComputedColumnDatePart::Month:
            return ‘m’;
        case SysComputedColumnDatePart::Year :
            return ‘yy’;
    }

    return ‘d’;
}

Last Day Of Month

Add a new static method to the view and call it lastDayOfMonth.

private static server str lastDayOfMonth()
{
    DictView    ctView = new DictView(tableNum(‘CustTransView’));
    str sTransDate = ctView.computedColumnString(‘CustTrans’,’TransDate’);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(‘-1’,
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff(
    ‘0’,sTransDate,SysComputedColumnDatePart::Month)+ ‘+1′,’0’,
    SysComputedColumnDatePart::Month),
    SysComputedColumnDatePart::Day),
    ‘DateTime’);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayofMonth() method as ViewMethod

Last Day of Year

Add a new static method to the view and call it lastDayOfYear

private static server str lastDayOfYear()
{
    DictView    ctView = new DictView(tableNum(‘CustTransView’));
    str sTransDate = ctView.computedColumnString(‘CustTrans’,’TransDate’);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff
    (‘0’,sTransDate,SysComputedColumnDatePart::Year) + ‘+1’,
    ‘-1’,
    SysComputedColumnDatePart::Year),
    ‘DateTime’);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayOfYear() method as ViewMethod

The View

Dynamics AX 2012 translates both fields in TSQL Code. The view looks like this

SELECT
ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION, 1010 AS RECID,
CAST(CAST(DATEADD(yy, DATEDIFF(yy, 0, TRANSDATE) + 1, – 1) AS DateTime)
AS DATETIME) AS LASTDAYOFYEAR,
CAST(CAST(DATEADD(d, – 1, DATEADD(m, DATEDIFF(m, 0, TRANSDATE) + 1, 0)) AS DateTime) AS DATETIME) AS LASTDAYOFMONTH
FROM
dbo.CUSTTRANS AS T1
GROUP BY ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION

The result in Dynamics AX looks like this

Filed under Development, Dynamics AX Tagged with Dynamics AX 2012; TSQL; View

Visualize the firms contribution to total revenue in Power BI

4. September 2017 Leave a comment

We recently discussed at work how to display the contribution of multiple firms to the total revenue in Power BI, in a way that the firms can be filtered but the total revenue stays constant. We came up with a very simple no-code solution.

Data from Dynamics AX 2012 R3

All the required data can be fetched from the CustInvoiceJour table in Dynamics AX. There are three datasets in the Power BI data model. First, the invoices including the InvoiceAmountMST, InvoiceYear and DataAreaId. Second the sum of InvoiceAmountMST grouped by Year and DataAreaId. Finally, a table that contains the years. Here are the SQL statements to load the data.

select
InvoiceId, InvoiceAmountMST, InvoiceDate, DataAreaId,
year(InvoiceDate) as InvoiceYear
from CustInvoiceJour

Total Revenue:

select
sum(InvoiceAmountMST) as InvoiceAmountTotal,
year(InvoiceDate) as InvoiceYear, DATAAREAID
from CustInvoiceJour
group by year(InvoiceDate),DATAAREAID
order by DATAAREAID

In Power BI create a new dataset and enter the years manually, like 2010, 2011, 2012, etc.

In Power BI go to the data model an connect the Invoices and Total Revenue with the dataset that contains the years

Line and stacked Column

Add a new Line and stacked Column chart to the Power BI Report. Set the field Year from the Year table as shared axis. From the CustInvoiceJour set the DataAreaId as Colum Series and the InvoiceAmountMST as Column values. Set the InvoiceAmountTotal from the Totals table as line values. In the format tab disable the Y-Axis property Show Secondary.

Your diagram should look like this. Filtering the companies does not effect the total revenue line.

Filed under BI, Dynamics AX, PowerBI Tagged with PowerBI; Dynamics AX 2012

Integrate an USB Scale with Dynamics AX

29. May 2017 Leave a comment

I was recently playing with an Dymo USB scale and how to connect it to a Dynamics AX 2012 instance on a virtualized HyperV installation. It turned out that connecting these two is not so hard at all. The Dymo scale was immediately recognized by my PC.

To access the USB scale, you need to know the Vendor ID and Product ID. This can be found in the Windows device manager. In my case the Vendor ID is 0x0922 and the Product ID is 0x8003

Access the USB Scale via C#

You need the Human Interface Device library, which is also available as source code. Download the Visual Studio project, open the .sln Solution from the SRC folder and build the library: https://github.com/mikeobrien/HidLibrary

Some code is needed to access the scale. Fortunately, there is a code snipped available here: http://r.lagserv.net/scalereader.htm .

Create a Visual Studio C# DLL Project.
Copy the code for the USBScale class from the website.
Add the previous built HIDLibrary to your projects references.
Add two int properties to the class, for Vendor ID and Product ID.

public class USBScale
{
public int VendorId { get; set; }
public int ProductId { get; set; }

Change the following line in the GetDevices() method at the USBScale class

public HidDevice[] GetDevices()
{
//return HidDevices.Enumerate(0x0922, 0x8004).Cast().ToArray();
return HidDevices.Enumerate(VendorId, ProductId).Cast<HidDevice>().ToArray();

}

For convenience, add a new method GetGramm()

public decimal GetGramm()
{
    decimal? lb = -1;
    decimal? gr = -1;
    decimal? oz = -1;
    bool? stable = false;

    GetWeight(out lb, out gr, out oz, out stable);

    if (gr.HasValue)
        return gr.Value;
    else
    return -1;
}

Build the DLL library

Forward the USB device to HyperV

There are different ways to pass an USB device to a virtual machine. However, I was using a tool called USB Redirect which is available as trial version for testing. It has two components. The server which manages and shares the USB devices is installed on the physical machine.

The client is installed on the VM and can access the USB device at the physical machine.

Integrate with Dynamics AX

Finally, the last step to integrate with Dynamics AX is easy. Copy the HID Library DLL and the USBScale DLL to the client bin folder. Create a form with a Scale button. At the clicked() method create a new instance of the USBScale class, provide your product and vendor ID and call GetGramm().

<YOUR_NS>.UsbScale scale = new <YOUR_NS>.UsbScale();
scale.set_VendorId(<YOUR_VENDOR_ID);
scale.set_ProductId(<YOUR_PRODUCT_ID);
real value;

value = scale.GetGramm();

info(strfmt(“%1 g”,value));

Filed under Development, Dynamics AX Tagged with Dynamics AX; USB; Scale

Performance Optimization by using Included Columns and Field Selects

25. April 2017 1 Comment

Since version 2012, Dynamics AX supports included columns in indices although SQL Server supports it for quite a long time. Here are some examples how and why it is good practice to use included columns in an index. I’m using Dynamics AX 2012 R3 Cu12 on Windows Server 2016 and SQL Server 2016 with Contoso Demo data for this example

Cluster Index.

The cluster index can be defined using multiple fields and is used to defined the order of records stored in the table. Even more important is the fact, that if a table has a clustered index all the data is stored in the table, i.e. the cluster index IS the table!

Take a look at the space allocated by the indices. About 219 MB are used to store actual data and 167 MB are used to store index information

The following SQL Statement reveals the size in detail

SELECT
ind.name,
SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM
sys.indexes ind
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
INNER JOIN
sys.dm_db_partition_stats AS s ON s.[object_id] = ind.[object_id]
AND s.[index_id] = ind.[index_id]
WHERE
t.name = ‘INVENTTRANS’
GROUP BY ind.name
order by IndexSizeKB desc

The table data is stored in the TransOriginIdx

name	IndexSizeKB
I_177TRANSORIGINIDX	226992 ~ 221 MB
I_177OPENITEMIDX	63720
I_177STATUSITEMIDX	34312
I_177ITEMIDX	24872
I_177RECID	23416
I_177DIMIDIDX	22192

Index Usage with Field Select

Here is an example of a select statement with field select on the InventTrans table

while select ItemId,DatePhysical
from inventTrans
where
InventTrans.ItemId == ‘0001’ &&
inventTrans.DatePhysical >= str2Date(‘1.1.2011’,123)

{ .. }

The trace parser reveals the actual SQL Statement sent to the database

What happens is what you would expect, SQL uses the ItemIdx for this query

Only 5 logical reads where necessary

Select Non-Index fields

When the query selects fields which are not part of the index, SQL server has to perform a lookup in the Cluster Index for each record identified by the ItemIdx to get all the other fields. For example the Voucher and Qty are not part of the ItemIdx.

213 logical reads were necessary to fetch the data

This can get even worse, when performing the lookup becomes to expensive. This can happen when the query returns a larger number of records. For example, when querying for another ItemId. In this example SQL server does not use the ItemIdx anymore, but performs a search in the clustered index instead. The ItemIdx became completely useless for this query.

SQL server required 1345 logical reads to fetch the data!

Included Columns

Since version 2012 Dynamics AX supports the definition of Included Columns for indices. These columns are not used to sort the index. These are just fields which are stored within the index to avoid costly lookups in the clustered index. In Dynamics AX you just add columns to the index and set the property IncludedColumn to Yes.

You can find the included columns in SQL server when viewing the properties of the index

When the statement from above is executed again, SQL server can use the included columns from the index and does not perform costly lookups in the clustered index.

Only 6 logical reads are required to fetch the data. This is a huge optimization compared to the 1345 reads without included columns.

Filed under Admin, Development, Dynamics AX, Performance Tagged with Index, SQL Server

Add a calculated field to an AIF Document

24. March 2017 Leave a comment

AIF is great for application integration and providing external applications required data. Document exports can be generated very easily by providing a query to the AIF document wizard. However, if you want to provide not only table fields but calculated values (e.g. display methods) in an AIF document some work is required. This is an example how to add the CustInvoiceJour.contributionMargin() method to the SalesSalesInvoiceService.

Parameter methods

Add the following methods to the AxCustInvoiceJour class. (You may duplicate the parm* and set* method from an existing field e.g. InvoiceAmount and change the name and parameter.)

public AmountMst parmContributionMargin(AmountMst _margin = 0)
{
;
return custInvoiceJour.contributionMargin();
}

protected void setContributionMargin()
{
;
return;
}

Add a macro in the class declaration of the SaleSalesInvoice_CustInvoiceJour class

class SalesSalesInvoice_CustInvoiceJour extends AfStronglyTypedDataContainer
{
#define.XMLDocPurpose(‘XMLDocPurpose’)
#define.Weight(‘Weight’)
#define.Volume(‘Volume’)
// lot more here ..

#define.ContributionMargin(‘ContributionMargin’)

}

Add the following methods to the SaleSalesInvoice_CustInvoiceJour class

public boolean existsContributionMargin()
{
return this.exists(#ContributionMargin);
}

public AmountMST parmContributionMargin(AmountMST_value = 0)
{
;
return this.get_Attribute(#ContributionMargin);
}

Refresh the schema definitions

Open the AIFDocumentSchemaTable in the table browser and delete the record for the DocumentName SalesInvoice.

In the AOT navigate to the SalesSalesInvoiceService. From the context menu choose to register the service. This will populate the AIFDocumentSchemaTable with the new XML schema including the new field.

Don’t’ forget to activate the new field in your endpoint. Navigate to Basic > Setup > Application Integration Framework > Endpoints > Action Policies > Data Policies > Data Policies and active the new field.

Now you’re ready to test your work. In this example navigate to Accounts Receivable > Inquiries > Journals > Invoice and click on send electronically. Depending on your AIF configuration, check the output. Your new field should be there. Here is the XML from AIF Queue Manager processing an AIF message to a File System Adapter:

Filed under AIF, Development, Dynamics AX Tagged with AIF

SQL Server 2016 SP1 and Dynamics AX 2012 R3

7. February 2017 7 Comments

Here are some ideas on SQL Server 2016 SP1 and Dynamics AX 2012 R3

Enterprise Features in Standard Edition since Service Pack 1

There was a major change in Service Pack 1 for SQL Server 2016. While most cool features were Enterprise-Edition-Only for a very long time, many features like Column Store Index and Compression are now available for Standard Edition too. Have a detailed look at this Blog. SQL 2016 also introduces new features like the Query Store and Power BI Integration with Reporting Services

Reporting Services

SQL Server 2016 Reporting Services require Dynamics AX R3 CU12 and an additional KB3184496 hotfix. Otherwise the installation will fail. The typical AX user won’t see the difference between SSRS 2016 and older versions. However, there are some features that might be interesting for us AX folks too, namely Power BI Integration.

Right now (January 2017) Power BI Integration is not so useful. You can place your Power BI files at the SSRS, which is actually only a better alternative to place the .PBIX file on a file share. However, it is said SSRS will be able not only to store but also to render Power BI files On Premises. This might be interesting for customers who are not willing to use Power BI in the cloud.

Right now in SSRS 2016 SP1 you can pin SSRS reports to your Power BI (Online) dashboard. This means, you can integrate your SSRS reports in Power BI. This might not sound very useful for Dynamics AX users. Why should I pin an invoice to a Power BI dashboard? But if a customer is already using SSRS for reporting, this might be a good option to start with Power BI and reuse the existing reports. Some Dynamics AX reports with OLAP data source can also be pinned to the Dashboard.

There is a Power BI Button in the SSRS report portal

This will pin your report to one of your Power BI (Online) dashboards

Query Store

This is a very useful feature. All of us are familiar with performance problems reported by some users. The problem is to identify and reproduce the query which performed badly and find the reason. Query Store can be used to store information about such problem-queries, like the SQL statement executed, the used execution plan, etc. In SQL Server Management Studio you can view reports based on execution time, logical and physical write/reads, memory usage, etc.Query Store therefore is a very useful feature in SQL 2016 to identify performance issues.

Column Store Index

Column Store Indices were introduced in SQL Server 2012 too speed up aggregation queries (e.g. sum). However, CSI hat a lot of limitations and was an Enterprise Edition features till 2016 (non SP). In SQL 2016 SP1 we can now use CSI in combination with Dynamics AX at our customers who have licensed Standard Edition of SQL Server.

In contrast to traditional Row Store Indices where records stored in 8 KB pages (e.g. CustInvoiceJour records), CSI store column values (e.g. LineAmountMST) together in 8 KB pages. Therefore aggregation functions can perform faster because less pages have to be read.

Here is an example:

select CustGroup, year(InvoiceDate) as YR, sum(LineAmountMST) as Amount
from CustInvoiceJour
group by CustGroup, year(InvoiceDate)

When executing this query against a Dynamics AX Contoso Demo database, 2158 logical reads were required.

Next, create a non-clustered Column Store Index on the fields CustGroup, InvoiceDate and InvoiceAmountMST which are used in the query

The same query now utilizes the Column Store Index to fetch and aggregate the data. The IO statistics show that less reads were required to get the result. The query performs faster than with the traditional Row-Store index.

Be aware that Dynamics AX removes the Column Store Index from the database when you synchronize the data dictionary. This might not be such an issues in a production environment. When you deploy a new application version from Test to Live, make sure to recreate all lost CSI.

Stretch Database

With stretch database you can migrate cold data (aka. existing but hardly not used) from your on premises expensive high performance storage to the cloud. This means you can split the data in large table and move old records in SQL azure. The application doesn’t recognize this split. Only if you query cold data, it will take longer to fetch the result. This sounds good. however there are some very crucial show stoppers.

You can’t UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
You can’t INSERT rows into a Stretch-enabled table on a linked server.

So right now, this feature is not useful for Dynamics AX on premises installation

Filed under Admin, Dynamics AX, PowerBI Tagged with SQL Server 2016

Extend the PurchRFQTable2Line Framework (AX 2012)

15. November 2016 Leave a comment

This is the my third post regarding the “Table 2 Line Update” mechanism in Dynamics AX. This shows you how to extend the request for quotations framework to update changes made to the header to the lines. The other post can be found here:

Example: Add a preferred shipping carrier

Use the existing extended data type TMSCarrierCode to add a new field called PreferredCarrier to the

PurchRFQCaseTable,
PurchRFQCaseLine
and the map PurchRFQTableMap

At the PurchRFQTableMap, add the new field PreferredCarrier to the HeaderToLineUpdate field group. Define a mapping for the PurchRFQCaseTable PreferredCarrier field. At the AxPurchRFQCaseTable class add the following parm and set methods

At the AxPurchRFQCaseLine class add the following parm and set methods

protected void setPreferredCarrier()
{
    if (this.isMethodExecuted(funcName(),
        fieldNum(PurchRFQCaseLine,PreferredCarrier)))
       {
           return;
       }

    this.setAxPurchRFQCaseTableFields();
    if (this.isAxPurchRFQCaseTableFieldsSet() ||
        this.axPurchRFQCaseTable().isFieldModified(
        fieldNum(PurchRFQCaseTable, PreferredCarrier)))
    {
        this.parmPreferredCarrier(this.axPurchRFQCaseTable()
                                    .parmPreferredCarrier());
    }
}

public Name parmPreferredCarrier(TMSCarrierCode _PreferredCarrier = ”)
{
    if(!prmisDefault(_PreferredCarrier))
    {
        this.setField(fieldNum(PurchRFQCaseLine, PreferredCarrier),
                                                 _PreferredCarrier);
    }
    return purchRFQCaseLine.PreferredCarrier;
}

At the AxPurchRFQCaseLine.setTableFields() method add the call of the setPreferredCarrier method

protected void setTableFields()
{
    // <GIN> #ISOCountryRegionCodes
    useMapPolicy = false;
    // </GIN>    super();
    this.setLineNum();    this.setLineNumber();
    // … lot of set* calls here
    useMapPolicy = true;

    //ERP
    this.setPreferredCarrier();
}

public FieldLabel lineUpdateDescription()
{
    switch(fieldExt2Id(this.fieldId()))
    {
        case fieldNum(PurchRFQTableMap, DefaultDimension):
        return "@SYS14926";
        case fieldNum(PurchRFQTableMap, InventLocationId):
        return "@SYS108782";
        case fieldNum(PurchRFQTableMap, DeliveryDate):
        return fieldId2pname(tableNum(PurchRFQCaseLine),
                              fieldNum(PurchRFQCaseLine, DeliveryDate));
        case fieldNum(PurchRFQTableMap, ExpiryDateTime):
        return fieldId2pname(tableNum(PurchRFQCaseLine),
                             fieldNum(PurchRFQCaseLine, ExpiryDateTime));
        case fieldNum(PurchRFQTableMap, TaxGroup):
        return fieldId2pname(tableNum(PurchRFQLine),
                             fieldNum(PurchRFQLine, TaxGroup));
        case fieldNum(PurchRFQTableMap, LanguageId):
        return fieldId2pname(tableNum(PurchRFQCaseLine),
                             fieldNum(PurchRFQCaseLine, Name));
        // ERP preferred carrier
        case fieldNum(PurchRFQTableMap, PreferredCarrier):
        return fieldId2pname(tableNum(PurchRFQCaseLine),
                             fieldNum(PurchRFQCaseLine,PreferredCarrier));
    }
    throw error(strFmt("@SYS19306",funcName()));
}

In the PurchRFQCaseTable2LineUpdate class, extend the getFieldIdFromMappedTable() method to support the new PreferredCarrier field

FieldId getFieldIdFromMappedTable(FieldId _mapFieldId)
{
    switch(_mapFieldId)
    {
        case fieldNum(PurchRFQTableMap, DefaultDimension) :
        return fieldNum(PurchRFQCaseTable, DefaultDimension);
        case fieldNum(PurchRFQTableMap, InventLocationId) :
        return fieldNum(PurchRFQCaseTable, InventLocationId);
        case fieldNum(PurchRFQTableMap, InventSiteId)     :
        return fieldNum(PurchRFQCaseTable, InventSiteId);
        case fieldNum(PurchRFQTableMap, DeliveryDate)     :
        return fieldNum(PurchRFQCaseTable, DeliveryDate);
        case fieldNum(PurchRFQTableMap, ExpiryDateTime)   :
        return fieldNum(PurchRFQCaseTable, ExpiryDateTime);
        case fieldNum(PurchRFQTableMap, LanguageId)       :
        return fieldNum(PurchRFQCaseTable, LanguageId);
        // ERP
        case fieldNum(PurchRFQTableMap, PreferredCarrier) :
        return fieldNum(PurchRFQCaseTable, PreferredCarrier);
    }
    return 0;
}

Go to Procurment and Sourcing module > Setup > Procurement and Sourcing Parameters > Request for Quotation and open the Update request for quotation lines. You should see the parameter dialog including the new Carrier field. Set the Update method to Prompt.

Filed under Development, Dynamics AX Tagged with AX 2012; Framework

Extend PurchTable2Line Framework (AX 2012)

18. July 2016 4 Comments

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!

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

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

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.

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.

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.

Filed under Dynamics AX Tagged with X++

← Older posts

Newer posts →