Useful X++ functions

//Return the value from the xmlNode
static str getXmlNodeValue(XmlNode _node)
{
    System.Text.RegularExpressions.Regex re =
    // BP deviation documented
    new System.Text.RegularExpressions.Regex('^[ \r\n\t]*((.|\n)*?)[ \r\n\t]*$');
    System.Text.RegularExpressions.Match m = re.Match(_node.text());
    str result;
    ;

    result = m.Result('$1');
    return result;
}

/// <summary>
/// Provides a lookup of dimension values.
/// </summary>
/// <param name="_ctrl">The control to provide a lookup for.</param>
/// <param name="_dimensionName">The dimension attribute to filter by.</param>
public static client void lookupDimension(FormStringControl _ctrl, Name _dimensionName)
{
    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(DimensionAttribute),_ctrl);
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource = query.addDataSource(tablenum(DimensionAttribute));

    sysTableLookup.addLookupfield(fieldnum(DimensionAttribute, Name));

    queryBuildDataSource.addRange(fieldnum(DimensionAttribute, Type)).value(queryNotValue(DimensionAttributeType::MainAccount));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}


/// <summary>
/// This method has the complete source file as input and will concatenate the target path with the filename
/// </summary>
/// <param name="_sourceFilename">
///  This is the full filename including the path
/// </param>
/// <param name="_targetPath">
///  the target folder
/// </param>
/// <returns>
///  It will return the target filename.
/// </returns>

static filename getTargetFilename(Filename _sourceFilename, Filepath _targetPath)
{
    Filename    targetFilename;
    ;
    new InteropPermission(InteropKind::ClrInterop).assert();

    targetFilename = System.IO.Path::Combine(_targetPath, System.IO.Path::GetFileName(_sourceFilename));

    return targetFilename;
}


// Generate nodes into container.
static container containerFromXMLNode(XmlNode n)
{
    container retval, containerToInsert;
    Common recordToInsert;
    XmlNamedNodeMap attrs;
    XmlNode typeAttr, element, recordNode, containerNode;
    int elemNo = 1;
    ;
    //validate arguments before using them
    if (n == null)
        return retval;

    // Collect all the elements ...
    element = n.firstChild();
    if (element.name() == 'container')
    {
        element = element.firstChild();
    }
    while (element)
    {
        attrs = element.attributes();
        if (attrs)
        {
            typeAttr = attrs.getNamedItem('type');

            switch (typeAttr.nodeValue())
            {
                case 'int'      :
                    retval = conins(retval, elemNo, str2int(BisTools::getXmlNodeValue(element)));
                    break;

                case 'str'      :
                    retval = conins(retval, elemNo, BisTools::getXmlNodeValue(element));
                    break;

                case 'real'     :
                    retval = conins(retval, elemNo, str2num(BisTools::getXmlNodeValue(element)));
                    break;

                case 'enum':
                    retval = conins(retval, elemNo, str2int(BisTools::getXmlNodeValue(element)));
                    break;

                case 'date'     :
                    retval = conins(retval, elemNo, str2date(BisTools::getXmlNodeValue(element), 321));
                    break;

                case 'record'   :
                    recordNode = element.firstChild();
                    recordToInsert = Global::recordFromXMLNode(recordNode);
                    retval = conins(retval, elemNo, recordToInsert);
                    break;

                case 'container':
                    containerNode = element.firstChild();
                    containerToInsert = BisTools::containerFromXMLNode(containerNode);
                    retval = conins(retval, elemNo, containerToInsert);
                    break;

                case 'blob':
                    containerToInsert = BinData::stringToData(BisTools::getXmlNodeValue(element));
                    retval = conins(retval, elemNo, containerToInsert);
                    break;
            }
        }

        element = element.nextSibling();
        elemNo += 1;
    }

    return retval;
}




//Generate html from Record
static str record2html(Common record)
{
    str html;
    DictTable   dictTable = new dictTable(record.TableId);
    DictField   dictField;
    fieldId     fieldId;


    void addField2html(str fieldName, anyType fieldValue, str htmlCode, str fieldLabel)
    {
        str newValue = strFmt('%1',fieldValue);
        if (newValue == '')
        {
            newValue = '-';
        }
        html += strFmt('<tr><td width=30%>%3%4</td><td width=50%>%3%2</td><td width=20%>%3%1</td></tr>',fieldName, newValue , htmlCode, fieldLabel);
    }
    ;
    html = '<html><b>Record values<table border width=100%>';
    addField2html('Field name','Value','<b>','Label');

    fieldId = dictTable.fieldNext(0);
    while (fieldId)
    {
        dictField = dictTable.fieldObject(fieldId);
        if (dictField.arraySize() == 1 && dictField.baseType() != Types::Container && dictField.baseType() != Types::BLOB)
        {
            addField2html(dictField.name(), record.(fieldId),"", dictField.label());
        }
        fieldId = dictTable.fieldNext(fieldId);
    }


    html+='</table></html>';
    return html;
}




//Generate record to Text
static str record2text(Common record)
{
    str         text;
    DictTable   dictTable = new dictTable(record.TableId);
    DictField   dictField;
    fieldId     fieldId;


    void addField2text(str fieldName, anyType fieldValue, str fieldLabel)
    {
        str newValue = strFmt('%1',fieldValue);
        if (newValue == '')
        {
            newValue = '-';
        }
        text += strFmt('Field \'%1\' = \'%2\', Label: \'%3\'\n',fieldName, newValue, fieldLabel);
    }
    ;
    text = strFmt("@BIS635", tableid2name(record.tableId));
    addField2text('Field name','Value', 'Label');

    fieldId = dictTable.fieldNext(0);
    while (fieldId)
    {
        dictField = dictTable.fieldObject(fieldId);
        if (dictField.arraySize() == 1)
        {
            addField2text(dictField.name(), record.(fieldId), dictField.label());
        }
        fieldId = dictTable.fieldNext(fieldId);
    }


    return text;
}


//Create the table record from xmlnode
// Input is <Record table="name"> <Field:field1> value </Field:field1> ... </Record>
// it's the opposite from record.xml() method
static Common xml2record(XmlNode n, Common c = null)
{
    tableId table;
    fieldId field;
    DictTable dt;
    DictField df;
    XmlNode fieldNode;
    str fieldName;
    Struct content;
    str tableName;
    Types t;

    //validate arguments before using them
    if (n == null)
        return null;
    // If we don't have an incoming buffer, one is created
    if (prmisdefault(c))
    {
        // Create a buffer of the correct type
        tableName = n.name();
       // tableName = n.attributes().getNamedItem('name');

        // Check if table node can be accessed
        if (tableName != '')
        {
            table = tablename2id(tableName);
            dt = new DictTable(table);
            c = dt.makeRecord();
        }
    }
    else
    {
        table = c.TableId;
        dt = new DictTable(table);
    }

    fieldNode = n.firstChild();
    while (fieldNode)
    {
        fieldName = fieldNode.attributes().getNamedItem('name').text();
        field = dt.fieldName2Id(fieldName);
        df = new DictField(table, field);

        t = df.baseType();
        if (t == Types::Enum) //Enums need to be treated as Integers. By defult, Enums are getting treated as a boolean and hence loosing its value.
            t = Types::Integer;

        content = BisTools::valueFromXMLNode (t, fieldNode);

        c.(field) = content.value('value');

        fieldNode = fieldNode.nextSibling();
    }
    return c;
}

//Get next working date from the date .
public static date GetNextWorkDay(date _fromDate)
{
    WorkCalendarDate    workCalendar;
    ;

    select firstOnly TransDate from workCalendar
    order by TransDate ASC
        where workCalendar.TransDate > _fromDate
           && workCalendar.WorkTimeControl == WorkTimeControl::Open;

    return workCalendar.TransDate;
}


/// <summary>
///     Attempts to find executatble for file type specified, and open file with it.
/// </summary>
/// <param name="_filename">
///     File name to show
/// </param>
public static client void showFile(Filename _filename)
{
    str filePath, fileName, fileExt, executable;
    if (System.IO.File::Exists(_filename))
    {
        [filePath, filename, fileExt] = fileNameSplit(_filename);
        executable = WinAPI::findExecutable(_filename);
        if (!executable)
        {
            error(strFmt("Windows were not able to find executable for file %1", _filename));
            return;
        }
        if (fileExt == '.msg')
            WinAPI::shellExecute(strFmt('"%1"', executable),strFmt('/f "%1"', _filename),strFmt('"%1"', filePath)); // outlook requires extra parameter  /f
        else
            WinAPI::shellExecute(strFmt('"%1"', executable),strFmt('"%1"', _filename),strFmt('"%1"', filePath));
    }
    else
    {
        error("File not found");
    }
}





/// <summary>
///     Converts enum value to enum label in language specified
/// </summary>
/// <param name="enumId">
///     Enum Id
/// </param>
/// <param name="value">
///     Enum value
/// </param>
/// <param name="languageId">
///     Language for label
/// </param>
/// <returns>
///     Label of enum value in language specified
/// </returns>
public static str enum2Label(EnumId enumId, int value, LanguageId languageId = SrsReportRunUtil::getCurrentUserLangId())
{
    DictEnum dictEnum;
    int      valueIndex, valueBuf;
    ;
    dictEnum = new DictEnum(enumId);
    if (dictEnum)
    {

        for (valueIndex = 0 ; valueIndex < dictEnum.values(); valueIndex++)
        {
            valueBuf = dictEnum.index2Value(valueIndex);
            if (valueBuf == value)
                return SysLabel::labelId2String(dictEnum.index2LabelId(valueIndex), languageId);
        }
    }
    return '';
}


/// <summary>
///     Converts time value into string with hours and minutes. Mostly used when generating file names
/// </summary>
/// <param name="time">
///     Time as integer
/// </param>
/// <returns>
///     Time converted to string in format HHMM
/// </returns>
public static TempStr time2StrHM(int time)
{
    return num2str( time div 3600       ,2,0,0,0) + num2Str0(time mod 3600 div 60,2,0,0,0);
}

/// <summary>
///     Converts any value to string. This is hardcoded conversion without using culture context
/// </summary>
/// <param name="_value">
///     The value to convert
/// </param>
/// <returns>
///     Converted value as string
/// </returns>
public static str any2String(anytype _value)
{
    switch(typeOf(_value))
    {
        // BP deviation documented
        case Types::Date: return date2str(_value, -1,-1,-1,-1,-1,-1,DateFlags::None);
        case Types::Time: return KrishhUtils::time2StrHM(_value);
        case Types::Int64, Types::Integer: return int2str(_value);
        case Types::Real: return strFmt('%1', _value);
        case Types::Enum: return strFmt('%1', _value);
        case Types::UtcDateTime: return DateTimeUtil::toStr(DateTimeUtil::applyTimeZoneOffset(_value, DateTimeUtil::getUserPreferredTimeZone())); // Hotfix 52
        default: return strFmt('%1', _value);
    }
    return "";
}


/// <summary>
///     Checks full path, and if \ is missing at the end - adds it
/// </summary>
/// <param name="path">
///     Full path to analyse
/// </param>
/// <returns>
///     Full path with \ at the end
/// </returns>
public static str fixPath(str path)
{
    str buf = strLRTrim(path);
    if (!path)
        return "";
    if (subStr(buf, strLen(buf),1) != '\\')
        buf = buf + '\\';
    return buf;
}

/// <summary>
///     Generates temporrary file with extension. Also creates file and saves it.
/// </summary>
/// <param name="filePrefix">
///     File prefix to use
/// </param>
/// <param name="extension">
///     Extension to use
/// </param>
/// <param name="filePath">
///     File path to use
/// </param>
/// <returns>
///     Created temporrary file name
/// </returns>
/// <remarks>
///     Creates empty file with unique name. Safe to use with multiple threads
/// </remarks>
/// <exception cref="Exception::Error">
///     When 10 attempts to create file failed
/// </exception>
/// <exception cref="Exception::Error">
///     When 10 attempts to create file failed
/// </exception>
public static str getTempFile(str filePrefix, str extension, str filePath = '')
{
    #File
    str                  tmpPath;
    str                  fileName;
    System.IO.FileStream fileStream;
    boolean              ok;
    int                  i;

    if (filePath)
        tmpPath = KrishhUtils::fixPath(filePath);
    else
    {
        new InteropPermission(InteropKind::ClrInterop).assert();
        tmpPath = System.IO.Path::GetTempPath();
        CodeAccessPermission::revertAssert();
    }
    while(!ok)
    {
        fileName = tmpPath + (filePrefix?filePrefix:filePrefix + guid2str(newGuid())) + extension; // First attempt direct
        new InteropPermission(InteropKind::ClrInterop).assert();
        while(System.IO.File::Exists(fileName))
        {
            fileName = tmpPath + filePrefix + guid2str(newGuid()) + extension;
        }
        CodeAccessPermission::revertAssert();
        try
        {
            i++;
            if (i>10)
                throw Exception::Error;
            new FileIOPermission(fileName, #IO_WRITE).assert();
            fileStream = System.IO.File::Create(fileName);
            fileStream.Dispose();
            CodeAccessPermission::revertAssert();
            ok = true;
        }
        catch (Exception::Error)
        {
            throw error("@SYS326095");
        }
        catch
        {
            exceptionTextFallThrough();
            CodeAccessPermission::revertAssert();
        }
    }

    return fileName;
}

/// <summary>
///     Returns the number associated with the specified month name.
/// </summary>
/// <param name="_monthName">
/// The name of the month.
/// </param>
/// <returns>
/// The number associated with the month name.
/// </returns>
public static int monthUSName2Num(str _monthName)
{
    int ret;
    str monthName = _monthName;
    #Define.month1('january')
    #Define.jan('jan')
    #Define.month2('february')
    #Define.feb('feb')
    #Define.month3('march')
    #Define.mar('mar')
    #Define.month4('april')
    #Define.apr('apr')
    #Define.month5('may')
    #Define.month6('june')
    #Define.jun('jun')
    #Define.month7('july')
    #Define.jul('jul')
    #Define.month8('august')
    #Define.aug('aug')
    #Define.month9('september')
    #Define.sep('sep')
    #Define.month10('october')
    #Define.oct('oct')
    #Define.month11('november')
    #Define.nov('nov')
    #Define.month12('december')
    #Define.dec('dec');

    monthName = strLwr(_monthName);

    switch(monthName)
    {
        case #month1:
        case #jan:
            ret = 1;
            break;
        case #month2:
        case #feb:
            ret = 2;
            break;
        case  #month3:
        case #mar:
            ret = 3;
            break;
        case #month4:
        case #apr:
            ret = 4;
            break;
        case #month5:
            ret = 5;
            break;
        case #month6:
        case #jun:
            ret = 6;
            break;
        case #month7:
        case #jul:
            ret = 7;
            break;
        case #month8:
        case #aug:
            ret = 8;
            break;
        case #month9:
        case #sep:
            ret = 9;
            break;
        case #month10:
        case #oct:
            ret = 10;
            break;
        case #month11:
        case #nov:
            ret = 11;
            break;
        case #month12:
        case #dec:
            ret = 12;
            break;
        default:
            ret = 0;
            break;
    }
    return ret;
}

Create Non clusterindex in Ax with include keyword.

Create non clustered index with include keyword suggestion given by query plan

 Index with Included Columns
You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
•    They can be data types not allowed as index key columns.
•    They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.


Issue

Whenever a synchronization is done, AX overwrites the indexes which we put through SQL

Resolution

a. Created the index via AX as a table method


public static server void createSqlIndexes()
{

SqlStatementExecutePermission SqlStatementExecutePermission;
str createIndexSql;

Connection connection=new Connection(); Statement statement=connection.createStatement();
createIndexSql=
@"IF EXISTS(select * from sys.indexes where object_id=OBJECT_ID(N'[dbo].[InventDIM]')

AND NAME=N'sonIdx' )
DROP INDEX [sonIdx] on [dbo].[InventDIM] WITH (ONLINE=OFF)
CREATE NONCLUSTERED INDEX [sonIdx] ON [dbo].[INVENTDIM] ([INVENTBATCHID],[WMSLOCATIONID],[INVENTSERIALID],[INVENTSIZEID],[INVENTCOLORID],[INVENTGTDID_RU],[INVENTPROFILEID_RU],[INVENTOWNERID_RU],[INVENTSTYLEID],[LICENSEPLATEID],[INVENTSTATUSID],[DATAAREAID],[PARTITION],[INVENTLOCATIONID],[CONFIGID],[INVENTSITEID])
INCLUDE ([INVENTDIMID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)";


SqlStatementExecutePermission=new SqlStatementExecutePermission(createIndexSql);
SqlStatementExecutePermission.assert();
statement.executeUpdate(createIndexSql);
CodeAccessPermission::revertAssert();
}

Execute this code in the application class dbsynchronize method at the end of the method after standard dbsynch done. so that we will have an sql index.

Read xml file with namespace

static void krishh_samplexmlread(Args _args)
{
     XmlNode root;
     XmlNode book;
     XmlNodeList books;
     XmlNamespaceManager nsmgr;

     XmlDocument doc = new XmlDocument();
      int i=1;
      doc.Load(@"C:\Temp\book.xml");

      //Create an XmlNamespaceManager for resolving namespaces.
      nsmgr = new XmlNamespaceManager(doc.NameTable());
      nsmgr.AddNamespace("bk", "urn:samples");

      //Select the book node with the matching attribute value.


      root = doc.DocumentElement();

      books = root.selectNodes("descendant::book", nsmgr);
      for (i = 0; i < books.length(); i++)
      {
           book = books.item(i);
              info(book.selectSingleNode("bk:first-name").text());
              info(book.selectSingleNode("bk:last-name").text());


      }

}

//<?xml version="1.0"?>
//
//<!-- A fragment of a book store inventory database -->
//
//-<bookstore xmlns:bk="urn:samples">
//
//
//-<book bk:ISBN="1-861001-57-8" publicationdate="1997" genre="novel">
//
//<title>Pride And Prejudice</title>
//
//
//-<author>
//
//<first-name>Jane</first-name>
//
//<last-name>Austen</last-name>
//
//</author>
//
//<price>24.95</price>
//
//</book>
//
//
//-<book bk:ISBN="1-861002-30-1" publicationdate="1992" genre="novel">
//
//<title>The Handmaid's Tale</title>
//
//
//-<author>
//
//<first-name>Margaret</first-name>
//
//<last-name>Atwood</last-name>
//
//</author>
//
//<price>29.95</price>
//
//</book>
//
//
//-<book bk:ISBN="1-861001-57-6" publicationdate="1991" genre="novel">
//
//<title>Emma</title>
//
//
//-<author>
//
//<first-name>Jane</first-name>
//
//<last-name>Austen</last-name>
//
//</author>
//
//<price>19.95</price>
//
//</book>


//-<book bk:ISBN="1-861001-45-3" publicationdate="1982" genre="novel">
//
//<title>Sense and Sensibility</title>
//
//
//-<author>
//
//<first-name>Jane</first-name>
//
//<last-name>Austen</last-name>
//
//</author>
//
//<price>19.95</price>
//
//</book>
//
//</bookstore>

Data Partition in AX2012 R2

AX2012 R2

AX2012 R2 release extends the concept of sharing data and processes across legal entities to streamline business processes and simplify the management of 'master data'.

AX2012 R2 release can support 36 country versions in one single installation of Dynamics AX2012, international companies that are represented in many countries to increase IT and operational efficiency. You can support all divisions in one installation, and it is the precondition to share master data and efficiently manage 'master data'.

Data partitioning

Data Partitioning can provide efficiency gains, because installation and system data is still shared.And data partitioning makes it possible to divide the application data into partitions of organizations.You will be able to share application data and processes between organizations within a partition, but not across .

Please see the below figure to understand the datapartitioning.

Please see the  below installation types for AX2012R2-

Entities	A single installation	A single installation of partitioning	Several installations
Application data and processes	Shared between legal entities	Shared between legal entities within a partition. Isolated between legal entities in different partitions	Shared between legal entities in the same installation. Isolated between installations
Master Data Management	Global master data shared between legal entities	Global master data shared between legal entities within a partition	Global master data shared between legal entities within the installation. Isolated between installations.
Meta data and adjustments	Applies to all entities	Applies to all entities	Unique for each installation
IT infrastructure	Single	Single	More
Database	Single	Single	More

A data partition helps sharing the Ax install base but not the data.

There can be companies with the same name in multiple partitions.

E.g Every partition will have the default company ‘DAT’.

- Intercompany doesn’t work across partitions

Mircosoft Recommends: Implementation choice must be made carefully as the companies between two partitions cannot be merged and Intercompany features cannot be used. The only option is to use Data migration tool kit for data export import between partitions and AIF for inter company operations.

Technical:-

A new partition table is introduced with a Key and the Recid field

- There is a new field called ‘PartitionRecid’ in everytable and as the dataareaid was by default applied to all contexts(Forms
queries) the partition key will also be added.

Select * from custTable where data areaid = ‘Dat’ and Partion key = ‘Initial’

- No cross company query on partitions are allowed like the cross company query

- You can know the current partition through ‘getcurrentpartitionrecid()’ similar to ‘curext()’

- Batch servers work across partitions

- AIF works across partitions

New table property like “SaveDataPerPartition” has been introduced as like “SaveDataPerCompany”. 

So for AIF, Batch tables – Save data per partition is set to “No”,because those are shared across partitions.

so get ready folks for AX2012 R2.