The Infolog

I got asked to find some missing labels in a report and when I counted them the report hit well over 300 labels. Rather than comparing by hand I built the below class to find the label Ids and then compare them. It allowed me to learn using Regular Expressions:

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using System.Drawing.ColorTranslator;
using System.IO.File;
class SRSLabelChecker
{

XmlDocument xmlDoc;
OfficeOpenXml.ExcelRange cells;
int currentRow;

public static void main (Args _args)
{
SRSLabelChecker labelChecker;

labelChecker = new SRSLabelChecker();
labelChecker.run();

}

public void run()
{

OfficeOpenXml.ExcelPackage excelPackage;
OfficeOpenXml.ExcelWorksheets worksheets;
OfficeOpenXml.ExcelWorksheet labelWorksheet;
OfficeOpenXml.ExcelRange cell;
MemoryStream stream;

//Create Excel document
stream = new MemoryStream();
excelPackage = new ExcelPackage(stream);
worksheets = excelPackage.get_Workbook().get_Worksheets();
labelWorksheet = worksheets.Add(“Labels”);
cells = labelWorksheet.get_Cells();
currentRow = 1;

// set header values

cell = cells.get_Item(currentRow, 1);
cell.get_Style().get_Font().set_Bold(true);
//cell.get_Style().get_Font().color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Grey); //not needed return
cell.set_Value(“Label ID”);

cell = cells.get_Item(currentRow, 2);
cell.get_Style().get_Font().set_Bold(true);
cell.set_Value(“English value”);

cell = cells.get_Item(currentRow, 3);
cell.get_Style().get_Font().set_Bold(true);
cell.set_Value(“German translation”);

this.getXML();

this.processFieldLabels();
this.processReportDesign();

//Finally save and publish the XML file to the user

excelPackage.Save();

file::SendFileToUser(stream, ‘MissingLabels.xlsx’);
}

private void getXML()
{
xmlDoc = XmlDocument::newFile(‘C:\\Temp\\report.xml’);
}

private void processFieldLabels()
{
XmlNodeList nodelList;
xMLNodeListIterator iterator;
XmlElement node;
XmlElement label;

nodelList = xmlDoc.getElementsByTagName(‘AxReportDataSetField’);

iterator = new XmlNodeListIterator(nodelList);

while (iterator.moreValues())
{
LabelId labelId;
str labelTxtDE, labelTxtUS;
XmlElement xmlEle;
node = iterator.value();

xmlEle = node.getNamedElement(‘Caption’);

if(xmlEle)
{
LabelId = xmlEle.text();
labelTxtDE = SysLabel::labelId2String2(LabelId,”de”);
labelTxtUS = SysLabel::labelId2String2(LabelId,”EN-US”);

if((LabelId == labelTxtDE) || (labelTxtDE == labelTxtUS))
{
OfficeOpenXml.ExcelRange cell;
currentRow++;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(LabelId);

cell = cells.get_Item(currentRow, 2);
cell.set_Value(labelTxtUS);
}
}
iterator.nextValue();
}

}

private void processReportDesign()
{

XmlNodeList nodelList;
xMLNodeListIterator iterator;
XmlElement node;
XmlElement label;

nodelList = xmlDoc.getElementsByTagName(‘Designs’);

iterator = new XmlNodeListIterator(nodelList);

while (iterator.moreValues())
{

XmlElement xmlEle;
node = iterator.value();

xmlEle = node.getNamedElement(‘AxReportDesign’);

if(xmlEle)
{
System.String reportString, starttag, endtag,pattern;

reportString = xmlEle.text();
pattern = ‘Labels!(.*?)</’;
starttag = ‘Labels!’;
endtag = ‘</’;
this.matchTags( pattern, starttag, endtag, reportString);

pattern = ‘Labels!(.*?) &amp’;
starttag = ‘Labels!’;
endtag = ‘ &amp’;
this.matchTags( pattern, starttag, endtag, reportString);
}

iterator.nextValue();
}

}

private void matchTags( System.String _pattern,
System.String _starttag,
System.String _endtag,
System.String _reportString)
{

System.Text.RegularExpressions.Match myMatch;

myMatch = System.Text.RegularExpressions.Regex::Match(_reportString, _pattern);

while (myMatch.get_Success())
{
System.String labelId;
str labelTxtDE, labelTxtUS;

labelId = myMatch.get_Value();
labelId = labelId.Substring(_starttag.Length);
labelId = labelId.Substring(0, (labelId.Length – _endtag.Length));

labelTxtDE = SysLabel::labelId2String2(LabelId,”de”);
labelTxtUS = SysLabel::labelId2String2(LabelId,”EN-US”);

if((LabelId == labelTxtDE) || (labelTxtDE == labelTxtUS))
{
OfficeOpenXml.ExcelRange cell;

currentRow++;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(LabelId);

cell = cells.get_Item(currentRow, 2);
cell.set_Value(labelTxtUS);
}

myMatch = myMatch.NextMatch();
}
}

}

In the past we could use the table UtilElements to get a list of AOT objects. In 365 for Operations that is no longer possible as the table contains no data. Microsoft have provided us with a way of accessing the metadata of the AOT though. There are a number of methods under the class Microsoft.Dynamics.Ax.Xpp.MetadataSupport which provide us with a string enumerator for looping through elements. For example ReportNames will give us all the report names in the system. However if we want a lookup on a control we will need to use a table. For this we can create a TMP table which we will populate. I have created the InMemory table with a single field to store the report name. I then have created the below method to populate the table. I loop through the results returned by using Microsoft.Dynamics.Ax.Xpp.MetadataSupport::ReportNames() and insert a record in for each:

 

 

All I need to do now is override the lookup method and create a lookup for it:

 

Form lookups  are nothing new in AX, and the patterns are the same in Dynamics 365. However sometimes we need to use a form extension which does not allow us to add a method directly on the form object. Instead we need to create an event against the form control to change the lookup. This can be a pain if the field is used in multiple locations but we must work with what we have.

To do this find your control and expand out the events:

Select the  ‘OnLookup’ event and copy the event handle method. Next we need to create a class and then paste the text in. Finally we would write our code to perform the lookup as we would normally with the added step of cancelling the super call using the event args:

 

Sometimes we might want to allow the end user to select a field from a table within a form, for example if we are parameterizing the mapping values. To do with we will need the following items:

 

• A field to hold the field ID (EDT FieldID)
• A edit method on the form datasource
• A string control on our form (EDT FieldLabel)
• A lookup method on the form control

 

Firstly we add the field ID to the table (there is no need to make that visible) . That should be easy enough to do

 

 

On to our lookup. We will be using the class SysTableFieldLookup and just like a normal lookup we will create a query, however we only need the datasource in this instance, which should be the table the fields are on. We will need to return our lookup class after the lookup is performed as we will need to access it later to get the selected field. Once done it should look as follows

 

We will need a new global variable on the form of type SysTableFieldLookup

Next we will create the edit method.

 

 

You can see that if the control is set we pull the record from the lookup class and get the field ID for a table field from it.  Now its time to create our form control and point it at the edit method. With that done the last step is to override the lookup method to call ours and populate the global variable:

 

 

With that done you should now get a lookup on your form with the field label and help text

 

As standard if we have a form that uses a datasource that is linked to a date effective table we will only see the current record. In AX we can give the user a new button in the action pane which allow them to change that and show the records they need or want.

To do this we have a class that does all the heavy lifting for us: DateEffectivenessPaneController. To use this class and functionality we need to implement IDateEffectivenessPaneCaller in our form, declare an instance of it as a global variable and create a new instance of it in the form init passing in the form and the datasource along with a few other switches:

 

We also need a public method to make the instance of our class accessible:

 

With that we have the filter button added to our form.

 

In AX 7 we have a whole new UI that is web based and around that we also have a few new controls. With things being new I have tried to get one of the newer controls working. I struggled for documentation but in the end got the MediaViewerControl to display a video in a new AX form.

To do this I added a control to my test form:

Then in the init method I set the URL and the content type of the window via code:

When we run the form we will get a video with the controls needed to play the video:

As I’ve just got my hands on AX 7 I wanted to do a little exercise that allowed me to work with form extensions and also the new event handlers. The task I attempted was to add a small street view thumbnail to the address grid that will show the view of the address. Unfortunately, Bing Streetside was not available in my area so I have used Google’s StreetView as the method to produce the images. To do this you’ll need to have an API key from Google. If you have a quick read of their documentation, you’ll see all we need to do is build a URL and then pass it to our image control.

To develop this mod we will need to make changes to the standard form LogisticsPostalAddressGrid. With AX 7 we now have the option of creating an extension rather than changing the form on our layer and requiring a merge. This allows for easier deployment and while cannot be used in every instance it should be your default option for customisations. To add a new extension right click on the object and select ‘Create extension’:

This will then create and add the extension to the standard object to your project. You can then edit the extension.

We can now add the new control to the form extension and set the properties we need:

With the image created we can now look to see what event we can use. I would like to change the image every time we change the record, in previous versions of AX we would override or edit the active method. Looking at the new events on the form data source we can see that there is a event called “OnActivated”

This will fire after the super of “active” so we will use that. To use these new methods we need to create a new class and an event handler method to write our code within. Once we have our new class created we can just right click on the method and select ‘Copy event handler method’ and this will put in to our clipboard the bits we need to create our new method

With that done we are pretty much back to how things used to be. We can write the code to build the URL and then set the URL for the image:

We can then rebuild and test:

Not the prettiest, but enough to show the principle.

I’ve recently been asked to develop a mod to create a load, load line and a shipment for a SalesLine record from code. I had a quick look and could not see anything on the internet so below is the code I used. Sadly it seems that the shipment table does not have any init methods so the values are assigned individually:

static void Job1(Args _args)
{
    SalesLine                   salesLine;
    SalesTable                  salesTable;
    WHSLoadTable                loadTable;
    WHSLoadLine                 loadLine;
    InventDim                   inventDim;
    InventSite                  inventSite;
    WHSShipmentTable            whsShipmentTable;
    WHSShipmentTable            consolidateShipmentTable;
    LogisticsPostalAddress      dlvAddress;
    boolean                     consolidate;
    TMSSalesTable               tmsSalesTable;
    ;
    
    select firstOnly salesLine
        where salesLine.InventTransId == "LOT006059";
    
    salesTable  = salesLine.salesTable();
    InventDim   = salesLine.inventDim();
    inventSite  = inventDim.inventSite();

    
    ttsBegin;
    
    loadTable.initValue();
    loadTable.initFromItem(salesLine.ItemId);
    loadTable.LoadId        = "MyCustomID4";
    loadTable.LoadDirection = WHSLoadDirection::Outbound;
    loadTable.initFromLoadTemplateId(inventSite.DefaultLoadTemplateId);
    loadTable.LoadPaysFreight           = NoYes::No;
    loadTable.insert();
    
    dlvAddress = salesLine.deliveryAddress();
    
    loadLine.initValue();
    loadLine.LoadId = loadTable.LoadId;
    loadLine.initFromSalesLine(salesLine);
    if(loadLine.validateWrite())
        loadLine.insert();
    
    
    
    if(!dlvAddress.whsAddressFormatValidation())
        throw error("error");
    
    whsShipmentTable.initValue();
    
    consolidate = InventLocation::find(inventDim.InventLocationId).ConsolidateShipAtRTW;
    
    select firstonly forupdate consolidateShipmentTable
        where   consolidateShipmentTable.AccountNum                 == salesLine.CustAccount
            &&  consolidateShipmentTable.DeliveryName               == salesLine.DeliveryName
            &&  consolidateShipmentTable.DeliveryPostalAddress      == dlvAddress.RecId
            &&  consolidateShipmentTable.InventLocationId           == inventDim.InventLocationId
            &&  consolidateShipmentTable.LoadId                     == loadLine.LoadId
            &&  consolidateShipmentTable.ShipmentStatus             < WHSShipmentStatus::Shipped
            &&  (consolidate
            ||  consolidateShipmentTable.OrderNum                   == salesLine.SalesId);
    
    if(consolidateShipmentTable)
    {
        consolidateShipmentTable.OrderNum       = consolidateShipmentTable.OrderNum         != salesLine.SalesId                ? '' : salesLine.SalesId;
        consolidateShipmentTable.CustomerRef    = consolidateShipmentTable.CustomerRef      != salesTable.CustomerRef           ? '' : salesTable.CustomerRef;
        consolidateShipmentTable.CustomerReq    = consolidateShipmentTable.CustomerReq      != salesTable.PurchOrderFormNum     ? '' : salesTable.PurchOrderFormNum;
        consolidateShipmentTable.DlvTermId      = consolidateShipmentTable.DlvTermId        != salesTable.DlvTerm               ? '' : salesTable.DlvTerm;
        consolidateShipmentTable.update();
    }
    else
    {
     
        whsShipmentTable.clear();
        whsShipmentTable.ShipmentId                 = "CustomID4";
        whsShipmentTable.LoadId                     = loadLine.LoadId;
        whsShipmentTable.WorkTransType              = WHSWorkTransType::Sales;
        whsShipmentTable.OrderNum                   = salesLine.SalesId;
        whsShipmentTable.AccountNum                 = salesLine.CustAccount;
        whsShipmentTable.DeliveryName               = salesLine.DeliveryName;
        whsShipmentTable.DeliveryPostalAddress      = dlvAddress.RecId;   
        
        whsShipmentTable.CountryRegionISOCode       = LogisticsAddressCountryRegion::find(dlvAddress.CountryRegionId).isOcode;
        
        whsShipmentTable.Address                    = LogisticsPostalAddress::formatAddress(    dlvAddress.Street,
                                                                                                dlvAddress.ZipCode,
                                                                                                dlvAddress.City,
                                                                                                dlvAddress.CountryRegionId,
                                                                                                dlvAddress.State,
                                                                                                dlvAddress.County);
        whsShipmentTable.DlvTermId                  = salesTable.DlvTerm;
        whsShipmentTable.InventSiteId               = inventDim.InventSiteId;
        whsShipmentTable.InventLocationId           = inventDim.InventLocationId;
        whsShipmentTable.CarrierCode                = tmsSalesTable.CarrierCode;
        whsShipmentTable.CarrierServiceCode         = tmsSalesTable.CarrierServiceCode;
        whsShipmentTable.BrokerCode                 = tmsSalesTable.BrokerCode;
        whsShipmentTable.RouteCode                  = tmsSalesTable.RouteConfigCode;
        whsShipmentTable.ModeCode                   = tmsSalesTable.ModeCode;
        whsShipmentTable.CarrierGroupCode           = tmsSalesTable.CarrierGroupCode;
        whsShipmentTable.LoadDirection              = WHSLoadDirection::Outbound;
        whsShipmentTable.CustomerRef                = salesTable.CustomerRef;
        whsShipmentTable.CustomerReq                = salesTable.PurchOrderFormNum;
        if(whsShipmentTable.validateWrite())
        {
            whsShipmentTable.insert();
            whsShipmentTable.createShipmentNotes(salesTable);
            
            loadLine.ShipmentId = whsShipmentTable.ShipmentId ? whsShipmentTable.ShipmentId : consolidateShipmentTable.ShipmentId;
            if(loadLine.validateWrite())
                loadLine.update();
        }

    }
    
    ttsCommit;
}

As we all know that moving forward we should really be making better use of the SysOperationFramework. Having a little more time over xmas I decided to use the framework in a mod I was doing. The mod called for a new export to excel without using BIS.

There is a Microsoft white paper about using it but after reading it I found that the Art of creation have a blog post which is much easier to follow.

We develop using the framework in a similar way to the way we would develop an SSRS report (SSRS uses the framework in the background).

Firstly we need to create a contract class. This will store all of the user input and will be used by the framework to produce the dialog fields. Just like with a report we need to add the DataContractAttribute and then also all of the global variables that we will later create parm methods for it. A word of warning about this, if you add, remove or change any of these methods you’ll need to run a CIL or possibly even restart the AOS

 

 

Then for each of the variables we will need to create a parm method making sure to add DataMemberAttribute. Please note if you get your return data type correct it will handle all of the help text etc. for you but you can override them with additional attributes.

 

If we would like a query to be shown we need a few more methods in our class, first off a parm method for our query. Note the extra attribute AifQueryTypeAttribute in which we pass the packed query and the name of our query.

 

We also have set and get methods for our query:

 

 

 

With our contract class finished we can now move on to our data service class. We don’t have to do anything special with this class other than making sure that it runs on server and that the mothod you will run has the SysEntryPointAttribute and accepts in your contract class:

 

Add your business logic so your class does what you want and then we can create your menu item. The menu item needs to point to a controller class (in this instance I’ve used the system one but you could create a custom class and extend from the standard class. It also needs to accept the execution mode and finally in to the parameters you put the method you need to run.

 

 

With that done you are ready to test. Kick off a CIL and then you can open your menu item. You should get your dialog up, along with the parameters and the query ready to run, your user selections will be automatically packed and unpacked and you will have a batch tab:

 

 

 

 

 

I’ve recently needed to validate if a string was a colour hex value. There was not a standard method I could find to check (although I am convinced there must be a .net class I can use) but as speed was of the essence I wrote my own: