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::backendTableName(tableNum(LedgerJournalTrans)),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, JournalNum)),
             sqlSystem.sqlLiteral(this.JournalNum),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, DataAreaId)),
             sqlSystem.sqlLiteral(ledgerJournalTrans.DataAreaId),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Partition)),
             getcurrentpartitionrecid());
...

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)
{
    #xppTexts
    #define.LeftSquareBracket('[')
    #define.RightSquareBracket(']')

    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);

    CodeAccessPermission::revertAssert();

    return validFilename;
}

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)
                {
                    return;
                }

                // 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
                {
                    modelNameSet.add(sysModelManifest.Name);
                }

                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.