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(PurchTable, ERPCarrierConfirmed), _carrierId);
    }

    return purchLine.ERPCarrierConfirmed;
}

public ERPCarrierId parmERPCarrierRequested(ERPCarrierId _carrierId = ”)
{
    if (!prmisDefault(_carrierId))
    {
        this.setField(fieldNum(PurchTable, 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.

Preserve data when AIF service call fails

Dynamics AX AIF is great for all kind of interfaces and integration with 3rd party applications. By default AIF has an built-in transaction mechanism that prevents your system to become inconsistent if something goes wrong during processing.

Here is an example of a simple method which writes message data and the current transaction level into a table

[SysEntryPointAttribute]
public void call(Description _message)
{
    ERPServiceTable serviceTable;

    serviceTable.Message = _message;
    serviceTable.Info = strFmt("%1", appl.ttsLevel());
    serviceTable.insert();
}

Here is the service call from a C# console application

var client = new ERPService.ERPServiceClient();
client.ClientCredentials.Windows.ClientCredential.Domain = "YOUR_DOMAIN";
client.ClientCredentials.Windows.ClientCredential.UserName = "YOUR_USER";
client.ClientCredentials.Windows.ClientCredential.Password = "YOUR_PASSWORD";

var context = new ERPService.CallContext();
context.MessageId = Guid.NewGuid().ToString();
client.call(context, "Service Call");

When you call the service the result looks like this: Notice, the transaction level is already 1 although there is no ttsbegin in code.

AIF service call

But what happens when something goes wrong during processing the service call (e.g. posting a journal fails). Here is as simple method which can be used to simulate such a situation:

[SysEntryPointAttribute]
public void callWithError(Description _message,boolean _error)
{
    this.call(_message);
    if(_error)
    {
        throw error("Exception!");
    }
}

When you call the service method with the error flag set to FALSE, the message is written to the table and stays there.

context.MessageId = Guid.NewGuid().ToString();
client.callWithError(context, "Service Call without error", false);

However if you call the method with the error flag set to TRUE a rollback is triggered and your data is gone!

try
{
        context.MessageId = Guid.NewGuid().ToString();
        client.callWithError(context, "Service Call 2 with error", true);
}
catch
{
        Console.WriteLine("The service failed as expected!");
}

The table contains only 2 entries for both successful service calls. The data from the third service call is gone.

Service call with error not saved

However, if you want to keep the initial data for a post mortem analysis, this behavior is a problem. One way to work around this rollback is to reduce the transaction level back to 0 by calling an additional ttscommit. This will ensure no rollback will delete the transmitted data. Finally, raise the ttslevel back to 1 for further processing.

[SysEntryPointAttribute]
public void callWithError(Description _message,boolean _error = false)
{
    this.call(_message);

    ttsCommit;
    ttsBegin;

   
    if(_error)
    {
        throw error("Exception!");
    }
}

When you call the service method with the error flag set to true, the method still fails. But the initial transmitted data is preserved and can be used for analysis or manual processing.

Service call with error preserverd

BTW.

A more relaxed way to address this issue is to separate interfaces from business logic. For example provide two methods, one to transmit the data and another to process the data after it was successfully transmitted. If a second method is not an option (for what reason ever) write a batch class which processes the data. However, if you require immediate processing and a second call is not feasible this workaround may help.

Create a Power BI Dashboard for Dynamics AX 2012 Sales

This is an update to the previous published articles on Data Visualization, OData Feeds, Power Map, Power Pivot in Office 2013 and Power Pivot in Office 2010. It shows how to use Power BI for Desktop to create a Sales Dashboard for Dynamics AX 2012 (R2).

Power BI Dashboard

Get Data

Start Power BI for Desktop and start with an empty report. From the ribbon on top click “Get Data”, choose SQ Server and provide your server and database. In this example I’m using a single server installation. However, in a production environment you might need to provide <SERVERNAME> \ <INSTANCENAME> , <PORT> e.g. SRVSQL\PROD,2303.

Get data into Power BI

In the next step you have to provide credentials. In my case I’m allowed to access the server with my domain account. In a production environment it is recommended to create a separate Login which is only used for BI Purpose. Don’t get confused if you get a warning that your SQL does not support encryption. If the connection was established successfully, the data wizard presents you a list of tables. Select the following tables:

  • CustInvoiceJour
  • CustInvoiceTrans
  • CustTable
  • InventTable
  • LogisticsPostalAddress

Select tables for Power BI

Click Load, and choose “Import” to load the data in Power BI for Desktop.

Transform Data

In Power BI for Desktop, at the Ribbon click “Edit Queries”. This will open the query editor. We don’t need all columns for this Demo. For each table click the “Choose Columns” button and the select only the following columns:

Choose columns for Power BI

CustInvoiceJour CustInvoiceTrans CustTable
  • InvoiceAccount
  • InvoiceDate
  • InvoiceId
  • NumberSequenceGroup
  • SalesId
  • InvoicePostalAddress
  • DataAreaId
  • InvoiceId
  • InvoiceDate
  • NumberSequenceGroup
  • ItemId
  • LineAmountMST
  • SumLineDiscMST
  • DataAreaId
  • AccountNum
  • CustGroup
  • DataAreaId
InventTable LogisticsPostalAddress
  • ItemId
  • ItemType
  • DataAreaId
  • Address
  • CountryRegionId
  • ZipCode
  • City
  • RecId

Click “Close & Apply” to finish this task.

Choose columns for Power BI

 

Create Relations

In Power BI for Desktop switch to the Data View (with the table symbol on the left pane). It is required to create primary keys and foreign keys before linking the tables. From the list of tables (on the right) select the CustTable and at the Ribbon click “New Column”. Type the following definition:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & “-” & CUSTTABLE[DATAAREAID]

Create Foreign Keys in Power BI

This will create a new column with a customer account which is unique for all company accounts. Repeat this step for the following tables and columns:

InventTable:

PK_Item = INVENTTABLE[ITEMID] & “-” & INVENTTABLE[DATAAREAID]

CustTable:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & “-” & CUSTTABLE[DATAAREAID]

CustInvoiceJour:

FK_Cust = CUSTINVOICEJOUR[INVOICEACCOUNT] & “-” & CUSTINVOICEJOUR[DATAAREAID]

PK_Invoice = CUSTINVOICEJOUR[INVOICEID] & “-” & CUSTINVOICEJOUR[SALESID] & “-” & CUSTINVOICEJOUR[NUMBERSEQUENCEGROUP] & “-” & CUSTINVOICEJOUR[DATAAREAID]

CustInvoiceTrans:

FK_Item = CUSTINVOICETRANS[ITEMID] & “-” & CUSTINVOICETRANS[DATAAREAID]

FK_Invoice = CUSTINVOICETRANS[INVOICEID] & “-” & CUSTINVOICETRANS[SALESID] & “-” & CUSTINVOICETRANS[NUMBERSEQUENCEGROUP] & “-” & CUSTINVOICETRANS[DATAAREAID]

Save, and open the relations by clicking on the relations item in the navigation pane on the left. You can drag&drop columns from one table to another table to create relations. Link the following columns:

  • CustInvoiceTrans,FK_Item > InventTable.PK_Item
  • CustInvoiceTrans.FK_Invoice > CustInvoiceJour.PK_Invoice
  • CustInvoiceJour.FK_Cust > CustTable.PK_Cust
  • CustInvoiceJour.InvoicePostalAddress > LogisticsPostalAddress.RecId

Your data model should look like this:

Create relations in Power BI data model

Name the ItemType

In the data view, select the InventTable. From the ribbon create a new column and name it “TypeName”. Add the following code to translate the Enum based ItemType Integer Value to a meaningful name.

TypeName = IF(INVENTTABLE[ITEMTYPE] = 0; “Item”; IF(INVENTTABLE[ITEMTYPE] = 2; “Service”; “Not an Item”))

The InventTable should look like this:

Name item type column

Create a Discount measure

Next we will create a measure which calculates the given discounts as the percentage of the total price. Open the data view using the second button on the left navigation pane. Select the CustInvoiceTrans. From the ribbon, click “New Measure” button in the “Modelling” tab. Provide the following code:

M_DiscPerc = sum(CUSTINVOICETRANS[SUMLINEDISCMST]) * 100 / ( sum(CUSTINVOICETRANS[LINEAMOUNTMST]) + sum(CUSTINVOICETRANS[SUMLINEDISCMST]))

For example:

Qty = 1 € , Unit Price = 1000 €     –> Price = 1000 €
Discount = 100 €                    –> Price = 900
Discount Percentage 3 %             –> Price = 873 €

CustInvoiceTrans.LineAmountMST = 873
CustInvoiceTrans.Discount = 100
CustInvoiceTrans.LinePercent = 3.0
CustInvoiceTrans.SumLineDiscMST = 127

M_DiscPerc = 127 * 100 / (873 + 127) = 12,7

Visualize

Switch to the empty report view using the first button on the left navigation pane. From the Visualization toolbox click the “Card”. This will place an empty card on the report. Drag&Drop the LineAmountMst from the CustInvoiceTrans on the empty card. It should look like this:

Power BI card chart

Next, place a map from the toolbox on the report. Drag&Drop the fields CountryRegionId, City and ZipCode from the LogisticsPostalAddress table on the Location. Drag&Drop the LineAmountMST from the CustInvoiceTrans on the Values Field. The map should look like this:

Power BI map chart

Add a new Gauge to the report and use the Measure M_DiscPerc as value. You cannot set a hardcoded Min. and Max. value in the data properties. Switch to the Format view using the pencil icon.  In the group Gauge Axis, set the Min. Value 0, the target value to 3 and Max. Value 100. Depending on your data, the gauge may look like this:

Power BI gauge control

Next a donut chart to visualize the revenue per item type. Drag&drop the LineAmountMST from the CustInvoiceJour on the value property of the donut chart and drag the TypeName from the InventTable.

Power BI donut chart

Place a column chart on the report to visualize the revenue per customer group. Place the LineAmountMST in the Value field. Use the CustGroup from the CustTable as Axis. Change the sort order to LineAmountMST by using the […] Dropdown Menu in the upper right corner of the chart.

Power BI column chart

Finally, add a line chart on the report to visualize the revenue per year. Place the LineAmountMST from the CustInvoiceTrans on the charts value field and put the InvoiceDate from the CustInvoiceTrans on the Axis field.

Power BI line chart

Fine-tuning

Give each chart a meaningful name. Change the size for the text to fit your report style. Switch to the data view. Change the column names into something more meaningful for an end user e.g. LineAmountMST to Amount. Change the columns formats e.g. Currency for LineAmountMST, date format for the InvoiceDate.

Lookup Financial Dimension in AX 2012

Financial Dimensions have changed in AX 2012. In contrast to earlier versions of Dynamics AX, financial dimensions now are somehow similar to inventory dimensions where a certain combination of dimension values are stored as dimension set. I faced a requirement that an external application (SharePoint based DMS) sends a certain combination of financial dimensions and AX has to assign this combination to a certain record. Here is a (simplified) code how to check the existence of such a dimension.

// Example:
// Business Unit 005 Electronics
// Department 025 IT
// ItemGroup Services

//Step 1: Find Dimension Attributes

DimensionAttribute  attributeBU = DimensionAttribute::findByLocalizedName("BusinessUnit");
DimensionAttribute  attributeDP = DimensionAttribute::findByLocalizedName("Department");
DimensionAttribute  attributeIG = DimensionAttribute::findByLocalizedName("ItemGroup");

//Step 2: Find Dimension Attributes Values

DimensionAttributeValue valueBU = DimensionAttributeValue::findByDimensionAttributeAndValue(attributeBU,"005");
DimensionAttributeValue valueDP = DimensionAttributeValue::findByDimensionAttributeAndValue(attributeDP,"025");
DimensionAttributeValue valueIG = DimensionAttributeValue::findByDimensionAttributeAndValue(attributeIG,"Services");

//Step 3: Create an Array of Hash Keys from the Attribute Values 

DimensionSHA1Hash hashKey;
DimensionAttributeValueSet valueSet;

HashKey dimAttrHashKeyArray[];
dimAttrHashKeyArray[1] = valueBU.HashKey;
dimAttrHashKeyArray[2] = valueDP.HashKey;
dimAttrHashKeyArray[2] = valueIG.HashKey;

hashKey = DimensionAttributeValueSetStorage::getHashFromArray(dimAttrHashKeyArray,3);

//Step 4: Lookup Hash Key

valueSet = DimensionAttributeValueSet::findByHash(hashKey);

if(valueSet.RecId != 0)
{
    info("Dimension found!");
}

Publish Dynamics Ax documents to SharePoint

A typical requirement is to publish documents like Invoice, Purchase Order, etc. to an DMS system like SharePoint. However, in Dynamics AX 2012 and lower there is no built-in functionality to publish these documents to SharePoint. But this requirement can easily be achieved without coding.

Prerequisite

  1. Install (at least) SharePoint 2013 Foundation incl. SP1. Foundation 2013 SP1 includes all the filters required to process PDF files and a basic search center. By default the configuration wizard creates a new team website collection.
  2. Make sure the WebClient window service is installed and running. If not, you may have to install the User Interface and Desktop Experience Feature on your server.
    WebClient Windows Service

Document Library and Content Type

In SharePoint  open the Settings image and open the Site Settings. From the User and Permissions group, open “People and Groups”. Select the Group “Owners”, Click “New” and add the Dynamics AX AOS Service account.

Provide AOS user access to SharePoint document library

In SharePoint create a new Document Library. On the team website open Settings image (right, on top) > Add an app > Name the document library “Invoice”.

Go to the newly created document library. Open the Library ribbon, and edit library with SharePoint Designer. SharePoint Designer is not installed by default, and you may have to download and install it first.

Open with SharePoint Designer

After SharePoint Designer was installed, click again on “Edit Library” with SharePoint Designer. Within SharePoint designer, select Content Types and create a new Content Type. Call it “Invoice” and make sure to select the parent content type form “Document Content Types” and parent content type “Document”. Save your modifications.

New content type for Invoice

In the navigation, select Lists and Libraries and open the Invoices library. Make sure the option “Allow management of content types” is enabled. At the “Content Types” group at the bottom, open “Add” and select your newly created Invoice content type. This will add the content type to the document library.

Add Invoice content type to library's managed content types

Select the Invoice content type at the content types group. At the ribbon select “Set as default”. Now every new document uploaded to the library will be an Invoice type. Save your modifications and go back go SharePoint.

Set invoice as default content type

At the Invoices document library in SharePoint, open the Library Ribbon and choose “Open in Explorer”. This will open a new instance of windows explorer, showing the content of the document library.

Open Library with Windows Explorer

Click in the address bar and copy the address.

image

 

Post and Publish Sales Invoice

Open Dynamics AX 2012 and go to “Accounts Receivable” > Periodic > Sales Update > Invoice. Change the Quantity to Packing Slip. Make sure the options “Posting”, “Late Selection” and “Post Invoice” are enabled.

Post Sales Invoice

Open “Select” and make sure the selection is configured to identify all Delivered sales orders. Click OK and save the selection.

Select only delivered sales orders

Open Printer Setup > Invoice. Select File as Target and choose File Format PDF. Open the File Name Dialog and provide the Document Library Address as Target. Repeat this step for the Invoice (Copy).

Print to SharePoint

Back at the Posting Invoice Dialog, open Batch processing (at the bottom). Enable batch processing and modify the recurrence to “No End” and repeat all 5 minutes.

Batch processing

 

Test

In Dynamics AX create one or more new sales order and post the packing slip. Wait until the Post Invoice batch job runs. The sales orders should be invoiced. Go to SharePoint and open the Invoices document library. There you should see the uploaded PDFs.

Dynamics Ax Invoice Document Library

Open the SharePoint Central Administration and go to Application > Manage service application > Search Service Application.

SharPoint Search Service

Select Content Sources and start the crawl manually. This will force SharePoint to index the PDF files in the Invoices document library. By default the crawler indices the documents on SharePoint periodically without manual interaction.

Manually start crawl

Open SharePoint Team Website. In the Search Box, type the Name of Customer. SharePoint will present a list of found documents related to the customer.

Find Dynamics AX documents on SharePoint

 

(Optional) Confirm and Publish Purchase Orders to SharePoint

Create a new document library with an purchase order content type as default:

Go to SharePoint, open the settings image menu and choose to add an app. Select document library and name it Purchases. Navigate to the new document library, and from the Library Ribbon, select “Open with SharePoint Designer”. In SharePoint designer create a new content type, based on a document content type, and name it Purchase. In SharePoint Designer navigate to the libraries, select the newly created Purchases library and make sure the option “Allow Management of Content Types” is enabled. Below, at the content types group add the Purchase content type you’ve created. Finally select the content type, and from the ribbon in SharePoint designer select “Set as Default”.

In Dynamics AX go to the Procurement and Sourcing module > Settings > Procurement and Sourcing Parameters > and activate Change Management. This option is required to run the purchase order approval workflow.

Activate change management

Next go to Procurement and Sourcing > Settings > Procurement and Sourcing Workflows. Create a new workflow based on the Purchase Order Workflow template. In the Workflow Configuration, add the Approve Purchase Order task to the workflow, between start and end.

Configure Purchase Order Workflow

Provide meaningful texts for the Workflow Submission instruction. Double click on the Approve Purchase Order taks to open the details. There assign Step 1 to a user or a user group. For testing purpose, you may assign the approval to yourself. Save and activate the workflow configuration.

Configure Purchase Order Workflow

Create a new purchase order. The purchase order starts in the approval state Draft. Submit it to the workflow for approval. This will change the state to “In Review”. Go back to the Purchase Order List Page and refresh the data. It may take a few minutes, depending on your workflow batch jobs configuration, until the purchase order is assigned to you for approval. When the purchase order is assigned for approval, approve the purchase and wait a few minutes until the purchase order has reached the state approved.

Purchase Order Approval State

Go to Procurement and Sourcing > Periodic > Purchase orders > Confirmation. Make Sure the Options Posting, Late Selection and Print purchase orders are active. From the Select button on the right, make sure only Approved and Open purchase orders will be selected.

Confirm Purchase Order

Open the Printer Setup > Purchase Order > Choose print to file as PDF and provide the path to the Purchases document library on your SharePoint. Repeat this for Printer Setup > Purchase Order (copy).

Print to SharePoint

Finally, open the Batch dialog  using the button on the lower right. Make sure to activate batch processing and set the recurrence to no end and recurring pattern to 10 minutes.

Batch processing

After a few minutes the purchase order state has change from Approved to Confirmed, and you should see the generated PDFs on SharePoint. These documents will be included within the next search crawl and can be found using the search box at your SharePoint website.

Purchase order confirmation

(Optional) Enhance Search Experience

Open SharePoint Central Administration and click the group Application Management. Go to “Create Site Collection” . Make sure your are using the same Web Application as the Team Website. Call the new site collection “Search” and provide an URL including like http://YOUR_SERVER/site/search. Make sure to select the “Basic Search Center” template from the Enterprise template group. Click OK, this will create a new basic search center.

Create Basic Search Center

Open the URL you have provided in a web browser. You should see an almost empty web page with a search box.

SharePoint Basic Search Center

In SharePoint Central Administration go to “Application Management” > “Manage Service Applications” > “Search Service Application”. Provide the Search Center URL at the property “Global Search Center URL”. This will instruct SharePoint to redirect all search to the Search Center.

Global Search Center URL

Open the Search Center URL in a browser and start a search e.g. for a Customer. This will open the result page. On the top right open the settings  image and click on “Edit page”. This will switch the page to edit mode. On the left side, click the drop down of the refiner web part and click “Edit Web Part”. This will open the Edit Dialog on the right.

Customize Search Center Refiner

Click on “Choose Refiners” and make sure SPContentType, File Type and Last Modified Time are selected. Remove all the other refiners from the select. Click OK in the dialog, Apply in the Web Part Configuration Dialog on the right and Stop Edition in the ribbon on the top left.

SharePoint Search Refiner

When you use the search center the next time, the result page will allow you to refine by Content Type like Invoice, Purchase Order, etc., File Type like PDF, Word, etc., and Date.

Dynamics AX Document Search

Ax 2012 Workflow Exception: Token StartElement in state Epilog would result in an invalid XML document.

At work we developed a quite simple Sales Order Approval workflow for a customer running Dynamics Ax 2012 R2. The workflow artifacts compiled without error and the workflow was configured. However, every time a sales order was submitted to the workflow we were facing an error:

Ax 2012 Workflow Error

A detailed look at the artifacts in the AOT reviled the problem. The Workflow Document class referred to a Query.

Workflow Document Class

The query had a SalesTable Datasource, but the SalesLine was not added as Sub Datasource within the SalesTable. Therefor the system could not generate a valid XML document.

Query with 2 data sources

We moved the SalesLine Datasource to the Sub Datasources of the SalesTable. Finally we compiled the project in X++ and IL and restarted the AOS.

Corrected workflow query

Finally the workflow completed successfully

Dynamics Ax 2012 Workflow completed

How to create a Word App for Address Lookup in Dynamics Ax

Office Apps provide additional functionality within an Office document like Excel and Word. This is an example how to create an App for Word 2013 which can be used to lookup an Address of a customer in Dynamics Ax.

Dynamics Ax Word App

 

Architecture

Word App Architecture

  1. Dynamics Ax will provide a web service which takes the (partial) name of a customer as input parameter, and returns a list of matching customers and their addresses.
  2. However, apps prefer a more lightweight form of communication.Therefore another web service will provide a simple JSON interface.
  3. Finally the Word App will be used to select a partial text within the document eg.”Contoso”, send it to AX and present the result of matching Customer / Addresses.

Dynamics Ax Service

Create a new data contract class and call it ERPCustomer. These will be the result objects for a customer lookup.

[DataContractAttribute]
class ERPCustomer
{
    CustName    custName;
    LogisticsAddressing address;
}

[DataMemberAttribute]
public LogisticsAddressing parmAddress(LogisticsAddressing _address = 
                                                              address)
{
    address = _address;

    return address;
}

[DataMemberAttribute]
public CustName parmCustName(CustName _custName = custName)
{
    custName = _custName;

    return custName;
}

Create a second class for the lookup logic and call it ERPAppHelper and add this static method:

public static List lookup(Name _name)
{
    CustTable custTable;
    DirPartyTable dirParty;
    List customers = new List(Types::Class);
    ERPCustomer customer;

    _name = strFmt("*%1*",_name);

    while select dirParty
        exists join custTable
        where dirParty.Name like _name && dirParty.RecId == custTable.Party
    {
        customer = new ERPCustomer();
        customer.parmAddress(dirParty.primaryAddress());
        customer.parmCustName(dirParty.Name);

        customers.addEnd(customer);
    }

    return customers;
}

Create a third class for the Lookup Service and call it ERPAppService

[SysEntryPointAttribute(true),
AifCollectionTypeAttribute(‘return’, Types::Class, classStr(ERPCustomer))]
public List lookup(Name _name)
{
    return ERPAppHelper::lookup(_name);
}

Create a new Service in the AOT, assign the ERPAppService class to the service and add the lookup method. Next create new privilege or use an existing one. Add the service lookup method to the privileges’ entry points. Right click on the service, from the context menu choose “Register”

Dynamics Ax App Service

Finally, to to System Administration > Setup > Services and AIF > Inbound Ports and create a new service. Use net.tcp port and add the lookup method as service operation.

Dynamics Ax App Service AIF

[Optional] Test the Dynamics Ax Service

Open Visual Studio, create a new Console Project and call it TestAifService. Add the Service reference (URI from the Screenshot above) to your project and call it ERPAppService. Call the lookup method and print the results. Use code below, and replace USR, PW, DMN with a valid User, Password, Domain combination in your environment.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TestAifService.ERPAppService;

namespace TestAifService
{
    class Program
    {
        static void Main(string[] args)
        {
            var cred = new System.Net.NetworkCredential("USR","PW","DMN");
            var client = new ERPAppServiceClient();
            client.ClientCredentials.Windows.ClientCredential = cred;

            var context = new CallContext();
            context.MessageId = Guid.NewGuid().ToString();

            var result = client.lookup(context, "Contoso");
            foreach (var customer in result)
            {
                Console.WriteLine(customer.parmCustName);
                Console.WriteLine(customer.parmAddress);
            }
            Console.ReadKey();
        }
    }
}

If you are using the Contoso Demo Data, the result should look like this:

Test Dynamics Ax AIF Service

JSON Frontend Service

In order to host the JSON Frontend Service, you need to install the Application Server Role and HTTP Activation. You will also need the Webserver role installed, including Application Development with .NET and ASP.NET.

In Visual Studio 2013 create a new Website Project and choose a new WCF Service. Use .NET Version 4.5 and select the file system as host. Select a C:\inetpub\wwwroot\appservice as target directory. Name it ERPAppService. This will create a WCF service project in Visual Studio including a working Hello World Service. Add the service reference to the Dynamics Ax net.tcp service to your project and call it DynamicsAx. Your Visual Studio Project should look like this:

Add Dynamics Ax AIF Service as Service Reference

Next, modify the IService1.cs Interface:

namespace ERPAppService
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        [WebInvoke(Method="GET",ResponseFormat=WebMessageFormat.Json,
            BodyStyle=WebMessageBodyStyle.Wrapped,
            UriTemplate="lookup/{name}")]
        ERPAppService.DynamicsAx.ERPCustomer[] lookup(string name);
    }
}

Go to the Service1.svc.cs object and implement the lookup method. Replace USR, PW and DOMAIN with a valid user to access Dynamics Ax.

namespace ERPAppService
{
    public class Service1 : IService1
    {

        public ERPAppService.DynamicsAx.ERPCustomer[] lookup(string name)
        {
            var cred = new System.Net.NetworkCredential("USR","PW","DOMAIN");
            var client = new DynamicsAx.ERPAppServiceClient();
            client.ClientCredentials.Windows.ClientCredential = cred;

            var context = new DynamicsAx.CallContext();
            context.MessageId = Guid.NewGuid().ToString();

            return client.lookup(context, name);
        }
    }
}

Open the web.config and go to the bindings element and add the webHttpBinding element. Within the webHttpBinding element add a binding element and set the security element to mode=Transport. This is required to host the service with SSL.

<bindings>
  <netTcpBinding>
    <binding name="NetTcpBinding_ERPAppService" />
  </netTcpBinding>
  <webHttpBinding>
    <!– SSL !!!  –>
    <binding>
      <security mode="Transport"></security>
    </binding>
  </webHttpBinding>
</bindings>

 

Save, Compile and publish the service to the local directory. In the IIS Manager, right click on the Default Website and from the context menu create a new application and name it AppService. Select as directory the directory where your service is deployed.

Host JSON App Service

Go to IIS Manager, select the server running IIS and from the feature pane open certificates. Create a new, self-signed SSL certificate. Next go to the Default Web Site, from the right pane open the Binding Dialog. Select the HTTPS:443 Binding and assign the newly created SSL certificate.

SSL Certificate for JSON App Service

Go to the AppService application and from the feature pane open SSL Settings. Set the “Requires SSL” active.

[Optional] Test the JSON Service

Open a web browser and navigate to the service address. In this example the server name is SRV-Demo within the Domain Demo.Local. Therefore the serivce URL is https://srv-demo.demo.local/AppService/Service1.svc. The result should look like this.

Test JSON App Service

Next call the service lookup method with parameter “Contoso”. The call should look like this https://srv-demo.demo.local/AppService/Service1.svc/lookup/Contoso. The result should like this:

Test JSON App Service

Develop a Word App

In order to create Office Apps, you need at least the professional edition. Community Edition and Express edition do not include the Office / SharePoint App Development projects:

Create new App Project in Visual Studio

Create a new Office App Project. In the wizard choose to develop a side pane app for Word. This will create a new project which already contains an app that can be used to process the selected text within word. The Home.js contains the getDataFromSelection function, which will be used to call the JSON Service.

JSON Call from Word App

Modify the code in the getDataFromSelection() method. Change the URL of the JSON Service to the URL of your service. Use HTTPS and the server name for which the SSL certificate was created. The following code will send the selected text to the JSON service, parse the result display the result as list of links within the word app. By clicking on the link, the text will be pasted into word.

function getDataFromSelection() {
       

Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
    function (result) {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
            var lookupUrl = https://srv-demo.demo.local/AppService/
                            Service1.svc/lookup/
+ result.value;

            $.getJSON(lookupUrl, function (data) {
                var result = data.lookupResult;
                $.each(result, function (key, val) {
                    var custname = val.parmCustName;
                    var address = val.parmAddress;
                    address = address.replace(/\n/g, ",");

                    $(‘#result_list’).append("<h3>" + custname + "</h3>");
                    $(‘#result_list’).append("<a onclick=
           \"Office.context.document.setSelectedDataAsync(‘"+address+"’)\">"
                                              + address + "</a>");

                     });
                 });

             } else {
                app.showNotification(‘Fehler:’, result.error.message);
             }
        }
    );
}

 

Run and test your app Smiley