Print Customer and Item specific labels in Dynamics 365 FO

This is an example how to print customer and item specific labels in Dynamics 365 for Finance and Operations. The labels shall be printed directly within the sales order form at the sales line grid.

Configuration Table

Create a new table containing the following five fields. The table will hold the configuration which report and design to use for which combination of customer and item

Field Purpose
CustAccount Reference to the Customer
ItemId Reference to an Item
ReportName Name of the SSRS report to use
ReportDesign Name of the report design
IsDefault NoYes

Create Report Classes

Create a new class called ItemLabelContract, which will be the data contract class for the report. It contains the number of labels to be printed, the SalesId and the LineNum to reference the calling SalesLine.

[DataContractAttribute]
class ItemLabelContract
{
NumberOf numberOf;
SalesId salesId;
LineNum lineNum;

    [DataMemberAttribute]
public NumberOf parmNumberOf(NumberOf _numberOfLabels = numberOf)
{
numberOf = _numberOfLabels;
return numberOf;
}

    [DataMemberAttribute]
public SalesId parmSalesId(SalesId _salesId = SalesId)
{
SalesId = _salesId;
return SalesId;
}

    [DataMemberAttribute]
public LineNum parmLinNum(LineNum _lineNum = LineNum)
{
LineNum = _lineNum;
return LineNum;
}

}

Create a new controller class called ItemLabelContract. This class will be called from the menu item in the sales order form. It takes the SalesLine as parameter and decides which report and design to use.

class ItemLabelController extends SrsReportRunController
{
public static void main(Args _args)
{
ItemLabelSetup setup;
SalesLine salesLine = _args.record();

ItemLabelController ctrl = new ERPItemLabelController();
ctrl.parmArgs(_args);

        select firstonly setup where
setup.CustAccount == SalesLine.salesTable().CustAccount &&
setup.ItemId == SalesLine.ItemId;

if(setup.RecId == 0)
{
select firstonly setup where
setup.CustAccount == SalesLine.salesTable().CustAccount &&
setup.ItemId == „“;
}

        if(setup.RecId == 0)
{
select firstonly setup where setup.ItemId == SalesLine.ItemId &&
setup.CustAccount == „“;
}

        if(setup.RecId == 0)
{
select firstonly setup where setup.IsDefault == NoYes::Yes;
}

if(setup.RecId == 0)
{
error(„No report selected“);
}
else
{
str reportName = strFmt(„%1.%2″,
setup.ReportName,
setup.ReportDesign);

            ctrl.parmReportName(reportName);
ctrl.startOperation();
}

    }

    protected void prePromptModifyContract()
{
ItemLabelContract contract =
this.parmReportContract().parmRdpContract() as ItemLabelContract;

SalesLine salesLine = this.parmArgs().record();

        contract.parmSalesId(SalesLine.SalesId);
contract.parmLinNum(SalesLine.LineNum);
contract.parmNumberOf(SalesLine.SalesQty);

        super();
}

}

Create a temporary (InMemory) table called ItemLabelTmp for the report data set including the following five columns:

Field Purpose
ItemId Item Id
ItemName Name
ItemBarcode Barcode value
BarcodeString Encoded barcode string
VendName Name of the vendor, here “Contoso”

Create the report data provide class. Overwrite the prePromptModify method and populate the contract with the number of labels to print taken from the SalesQty, the SalesId and LineNum.

[SRSReportParameterAttribute(classStr(ItemLabelContract))]
class ItemLabelDP extends SrsReportDataProviderBase
{
ItemLabelTmp        itemLabelTmp;

    [SrsReportDataSetAttribute(‚ItemLabelTmp‘)]
public ItemLabelTmp getItemLabelTmp()
{
select * from itemLabelTmp;
return itemLabelTmp;
}

    public void processReport()
{
ItemLabelContract contract = this.parmDataContract()
as ItemLabelContract;
SalesLine salesLine;

        select firstonly SalesLine where
SalesLine.SalesId == contract.parmSalesId() &&
SalesLine.LineNum == contract.parmLinNum();

        BarCodeString bcstring = „“;
InventItemBarcode itemBarcode = InventItemBarcode::findItemId(
SalesLine.ItemId,false,false);

if(ItemBarCode.RecId > 0)
{
Barcode barcode = Barcode::construct(BarcodeSetup::find(
itemBarcode.barcodeSetupId).barcodeType);
barcode.string(true,ItemBarCode.itemBarCode,
BarcodeContentType::Item);
Barcode.encode();
bcstring = Barcode.barcodeStr();
}

        for(int i = 0; i < contract.parmNumberOf(); i++)
{
itemLabelTmp.clear();
itemLabelTmp.ItemId = SalesLine.ItemId;
itemLabelTmp.ItemName = SalesLine.itemName();
itemLabelTmp.ItemBarCode = SalesLine.BarCode;
itemLabelTmp.VendName = „Contoso“;
itemLabelTmp.BarCodeString = bcstring;
itemLabelTmp.insert();
}

    }

}

Create a report with multiple designs

Create a new report and add the report data provider class as source. Create at least two designs. In this example I’ve created two designs, a small and and  large label.

Small Item Label Design

Large Item Label Design

Create Menu Items and Forms

Create a new form using a the Simple List pattern to manipulate the configuration table form. Create a new display menu item for the form and add it e.g. to the accounts receivable module.

Configuration form in Dynamics 365 FO

Create a form extension for the SalesTable form. Create an output menu item form the ItemLabelController class and add it to SalesTable extension e.g. Main > TabPageDetails > DetailsTab > LineView > LineViewTab > LineViewLines > LinesActionPaneStrip > LineOverviewActionTab . Make sure to set the SalesLine as Datasource for the Menu Item.

Menu Item in SalesTable form

Test the labels

Create new sales orders and test the different configurations and labels. Here is a Youtube example.

Youtube Link for Dynamics 365 Finance and Operations Demo

Setup multiple developer VMs for Version Control with Team Services in Dynamics 365 for Finance and Operations

Here is a walkthrough how to connect two Dynamics 365 Finance and Operations developer VMs with VSTS.

Configure Azure AD and setup Visual Studio Team Services

Before you start, you have to add your developers to Azure Active Directory. If you have Azure AD Connect make sure the accounts have been synced to the Cloud. In my case I added two additional users to my Azure AD.

Configure Developer Accounts in Azure AD

Next logon to your Azure Portal using your Organization Account. Create a new service and search for “Team Services”. You should find Visual Studio Team Services (preview).

Create Visual Studio Team Services project in Azure Portal

Create a new instance of VSTS. The basic version of VSTS for 5 users is free. Make sure to use Team Foundation Server as Version Control system. You may choose any Methodology you like, but II ‘d recommend to go for CMMI compatible one.

Create Visual Studio Team Services project in Azure Portal

After the deployment succeeded, logon to your Visual Studio Team Services account, using  the URL https://<ACCOUNTNAME&gt;.visualstudio.com . There you see a new project. Open the project and add your developer users by clicking the (+) icon on the upper left “Members” pane. If everything is configured correctly, you should be able to add your AD users. In my example developer one and developer two.

Add developer accounts to Dynamics 365 FO project

Configure Developer VMs

If you are using the VHD Images from Microsoft, the first thing you should do is to rename the computer. If you don’t rename the VMs you will get errors when mapping the source control on multiple machines. In my case I rename the VMs to “devbox1” and “devbox2”. No domain is needed. Details here.

Rename Dynamics 365 FO developer VM

Configure first Developer Box

After the VM reboots, open Visual Studio 2015 in Admin mode. Depending on your licensing you may need to provide additional credentials to use your subscription. Don’t get confused if this may be your Microsoft ID (aka. Live ID) while you need your Organization Account to access VSTS. Zwinkerndes Smiley  From the menu bar select > Team > Manage Connections. Provide the URL for your VSTS account.

Connect to Visual Studio Team Services

After you have connected to VSTS select the project to work on. Next, from the Team Explorer open the Source Control explorer. Map the root folder to a folder on your developer VM.

Map Source Control Folder in Visual Studio

Afterwards use the source control explorer to create two new folders. One for Visual Studio Projects and one for metadata. This is where the D365 source code artifacts will be stored. Check in you pending changes. This will sync the folders to VSTS.

Map Dynamics 365 FO metadata folder

Now, in the Source Control Explorer open the dropdown Workspace and edit your workspace. Map the metadata folder to C:\AOSService\PackagesLocalDirectory.

Map Dynamics 365 FO metadata folder

From the menu bar > Dynamics 365 > Model Management > Create a new model. Give it a name and complete the wizard. This will ask you to create a new Dynamics X++ project.

Create new Dynamics 365 FO project

In the solution explorer, right click on the project and add to source control.

Check in to Source Control

Add a new element to the project, for example add a new String Extended Datatype called DMOCustomerName. In the solution explorer, right click the project and select build. After a few seconds you should see the console output “Build completed”. Check in all your pending changes.

Next, from the Team Explorer open the Source Control Explorer. You should see the model structure in the tree view. Right click on the metadata folder and select “Add items to folder”. Navigate to your model folder and there to the Descriptor folder. Add the Model Descriptor XML file. Unfortunately you have to do this manually, otherwise the second developer can sync the Folders and Extended Datatypes etc. but will not see the model in the AOT.

Add Dynamics 365 FO Model Descriptor File to Source Control

You can also inspect your code in VSTS

Dynamics 365 FO X++ Source Code

Configure second Developer Box

Make sure that the second VM is properly renamed. Open Visual Studio in Admin mode and connect to VSTS. Logon with the account of the second developer. Select the Dynamics 365 project and again in the Source Control Explorer map the metadata folder to C:\AOSService\ PackagesLocalDirectory. Checkout the latest version of the metadata folder.

Get Latest Version from metadata folder

This will create the model folder in the packages directory.

Model folder created in PackagesLocalDirectory

In Visual Studio open the Dynamics 365 Application Explorer. If the AOT is in classic mode, right click and switch to model view. Scroll down and you we will see the synchronized model and the its software artifacts.

Model in Dynamics 365 FO Application Explorer

Integrate SharePoint Online with Dynamics AX Legacy Application

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

image

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.

image

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

image

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.

image

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

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

Create a Query

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

Document Query

 

Use the AIF Document Wizard to generate the service classes

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

AIF Wizard

Fine tune the generated code

  1. Go to the private projects and open the newly generated project
  2. Compile the project
  3. If the cache() methods don’t compile delete them
  4. Review the open tasks and provide meaningful labels
    Fine tune AIF Code

Configure the Batch Jobs

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

AIF Batch Jobs

 

Configure the File System Adapter

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

AIF Outbound Port with File System Adapter

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.

AIF Queue Manager

Setup und Configure Office 365 Project Web App

Here you can find my video on how to setup Project Web App, link with Project Desktop client and how to setup a SharePoint subwebsite with a synchronized task list

pwa

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

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

CustTransView with Computed Column

Visualize the firms contribution to total revenue in Power BI

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.

Power BI Years Table

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

image

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.

Diagram settings 

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

image