Friday, February 13, 2015

Select Count Distinct

The Select statement doesn't have a way to let you do a count of distinct records.

Today I found this neat way to do it anyway, in the method \Data Dictionary\Tables\LedgerJournalTable\Methods\numOfVouchers:
sql = 'select count(distinct %1) from %2 where %3 = %4 and %5 = %6 and %7 = %8';

sql = strFmt(sql,
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Voucher)),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, JournalNum)),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, DataAreaId)),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Partition)),

Thursday, February 12, 2015

Build a valid file name

I could not find anything in AX building a true valid file name. That is, just the name of the file itself, not the path.

So I came up with this:
public static Filename buildValidFilename(str  _filename)

    Filename  validFilename;
    str       invalidFileNameChars;

    new InteropPermission(InteropKind::ClrInterop).assert();

    // Remove the characters that .NET defines as invalid
    invalidFileNameChars = new System.String(System.IO.Path::GetInvalidFileNameChars());
    validFilename = System.Text.RegularExpressions.Regex::Replace(_filename, #LeftSquareBracket + invalidFileNameChars + #RightSquareBracket, #emptyString);

    // Remove the characters that .NET doesn't remove
    invalidFileNameChars =  @'"\\/:*?\<>|' + "'";
    validFilename = System.Text.RegularExpressions.Regex::Replace(validFilename, #LeftSquareBracket + invalidFileNameChars + #RightSquareBracket, #emptyString);


    return validFilename;

Tuesday, February 10, 2015

Best Practice check for multiple models in the same layer

The Team Foundation Server integration in AX doesn't play very well with multiple models in the same layer. Without getting into too much detail, you for one thing risk having unexpected elements deleted from the AOT when you synchronize.

I'm on a project where we work with a few different models in the same layer. We have dependencies to these models, developed elsewhere. We have one specific model for my project, which also is the model where we merge all conflicts between models. If we have something in an element, like a class or table, where sub elements belongs to another model, we move these sub elements into our model. So in these cases our model contains the sum of changes to the entire root element.

In order to not forget moving the sub elements, I have developed an extra Best Practice check to check if an element belongs to more than one model, and raise an error in that case. To resolve the error I have to move all sub elements into a single model.

Here is the code for the Best Practice check (it is what it is...):
public void checkMultipleModelsInSameLayer()
    TreeNode            treeNode = sysBPCheck.treeNode();
    SysModelElement     sysModelElement;
    SysModelElement     sysModelElementRoot;
    UtilElements        utilElement;
    SysModelElementData sysModelElementData;
    SysModel            sysModel;
    SysModelManifest    sysModelManifest;
    Set                 modelNameSet;
    SetEnumerator       modelNameSetEnumerator;
    str                 modelNamesString;

    if (versioncontrol.ideIntegration())
        if (versioncontrol.parmSysVersionControlSystem() &&
            !(versioncontrol.parmSysVersionControlSystem() is SysVersionControlSystemMorphX)) //MorphX VCS is not file based.
            if (treeNode.treeNodeType().isUtilElement() && !SysTreeNode::hasSource(treeNode))
                modelNameSet = new Set(Types::String);
                utilElement = treeNode.utilElement();

                // Only work with root objects
                if (utilElement.parentId != 0)

                // Find the root model element
                select firstonly RecId from sysModelElementRoot
                    where sysModelElementRoot.Name          == utilElement.Name
                       && sysModelElementRoot.ElementType   == utilElement.RecordType
                       && SysModelElementRoot.ParentId      == 0;

                // Analyse all children of the root, in the same layer
                while select tableId from sysModelElement group by sysModelManifest.Name
                    where sysModelElement.RootModelElement == sysModelElementRoot.RecId
                join tableId from  sysModelElementData
                    where sysModelElementData.ModelElement == sysModelElement.RecId
                join tableId from sysModel
                    where sysModel.RecId   == SysModelElementData.ModelId
                       && sysModel.Layer   == currentAOLayer()
                join Name from sysModelManifest
                    where sysModelManifest.Model == SysModel.RecId

                if (modelNameSet.elements() > 1)
                    modelNameSetEnumerator = modelNameSet.getEnumerator();
                    while (modelNameSetEnumerator.moveNext())
                        if (modelNamesString)
                            modelNamesString += ', ';

                        modelNamesString += modelNameSetEnumerator.current();

                    sysBPCheck.addError(0, 0, 0, strFmt('Object associated with several models in the same layer. Models: %1', modelNamesString));
This method as added to relevant classes that extend SysBPCheckBase.

Tuesday, January 27, 2015

Create QR codes from AX 2012 R3 CU8

It seems like the Brazilian authorities are very modern in their approach to how you report to and communicate with them. I have found several neat things in AX from the Brazil specific features.

This time I stumbled over an assembly called QRCode and I thought I should look into what that was used for. It turns out you can create QR codes from AX pretty easy. Here is an example:
static void JobCreateQRCode(Args _args)
    Image           image;
    container       imageContainer;
    str             url;
    EFDocQRCode_BR  qrCode;  
    // The url to create the QR code. Could also for example be an e-mail address
    url = '';
    // Create an instance of the QR code class
    qrCode = new EFDocQRCode_BR();
    // Generate a QR code for the URL and save the result to a container
    imageContainer = qrCode.generateQRCode(url);
    // Use AX's good old Image class to load the image from the container
    // and save it as a file
    image = new Image();
    image.saveImage("F:\QrCode.jpg", ImageSaveType::JPG);
You can now open the file and see the resulting QR code.

It links to Which leads me to the thing I really don't understand about QR codes. For security reasons we are always told not to click an URL that doesn't seem familiar. But scanning QR codes having absolute no idea about where they lead to is perfectly alright. Go figure.

Monday, January 5, 2015

Entering a multi line control in a grid

If you add a multi line FormStringControl to a grid, and set it up to just show one line in order to keep the normal layout of the grid, you will find that the control behaves differently depending on how you enter it; by keyboard or mouse.

If you enter the control by keyboard, it behaves as expected setting the cursor on the first line of the text. But, if you enter the control using the mouse it starts out by clearing the field and making it blank. Only when you click the field again or use up and down arrows it will show the text.

Today I spent a couple of hours trying to figure out how to fix it, and all I could come up with was the following odd solution. So, I run some code when I release the mouse button after moving the cursor into the field. This helps, but doesn't quite fix the problem. To fix it entirely I have to call that exact same code again after a short wait.

So here is my code. Executed once, and then again after a short wait for 100 ms.
public int mouseUp(int _x, int _y, int _button, boolean _Ctrl, boolean _Shift)
    int ret;

    ret = super(_x, _y, _button, _Ctrl, _Shift);


    // The same code needs to be called again after a short wait in order to make
    // the control show the right line and move the cursor to that line
    this.setTimeOut('setLineFocus', 100);

    return ret;

private void setLineFocus()
If I remove any part of this code, the fix won't work. I don't have any good explanations about this behavior, but it works so I'm happy leaving it at that.

Thursday, January 1, 2015

Testing JSON from AX 2012

I wanted to see how JSON could be consumed from AX, and found this great article from Jonathan.

Then I needed something to test with and found the site

And so, here are two examples.

The first example just returns your IP address:
static void JSONTestIP(Args _args)
    RetailWebRequest    request; 
    RetailWebResponse   response; 
    str                 rawResponse; 
    Map                 responseData; 
    str                 responseValue;
    RetailCommonWebAPI webApi = RetailCommonWebAPI::construct(); 
    request = RetailWebRequest::newUrl(""); 
    response = webApi.getResponse(request); 
    rawResponse = response.parmData(); 
    responseData = RetailCommonWebAPI::getMapFromJsonString(rawResponse); 
    responseValue = responseData.lookup("ip");
    info(strFmt("Element name: %1", responseValue));

The next example returns key/value pairs. In this example first name of a person is found based on the persons last name:
static void JSONTestKeyValue(Args _args)
    RetailWebRequest    request; 
    RetailWebResponse   response; 
    str                 rawResponse; 
    Map                 responseData; 
    str                 responseValue;
    RetailCommonWebAPI webApi = RetailCommonWebAPI::construct(); 
    request = RetailWebRequest::newUrl(""); 
    response = webApi.getResponse(request); 
    rawResponse = response.parmData(); 
    responseData = RetailCommonWebAPI::getMapFromJsonString(rawResponse); 
    responseValue = responseData.lookup("Kent");
    info(strFmt("Element name: %1", responseValue));

Thursday, December 18, 2014

Hide the Export button from the report viewer

I did a little investigation today into how you can work with the report viewer toolbar.

One practical example is to hide the "Export" button.

First add a reference to: Microsoft.ReportViewer.WinForms

Next, add this code to be called somewhere from the report viewer form:

What I really would like to do, was to add some buttons to the toolbar. But I didn't get that far today.