Tip: Suppress “Save Changes” prompt when you close a Excel workbook using X++ in Dynamics AX

In Microsoft Dynamics AX, to read data from excel worksheets the focus needs to shift on the active workbook. So when application is closed you’ll still find the process thread being shown up in Task manager and when trying to view the excel file a dialog prompt appears

Prompt when you close workbook in Excel

To suppress the following, try forcing a workbook to close without saving changes. The best way of implementing this via x++ code [ sysExcelWorkbook.saved(true) ]

REASON:If Saved property is set to True, Excel responds as though the workbook has already been saved and no changes have occurred since that last save.

To know more on the way its handled in C#, refer MS link Workbook.Saved Property

Tip: COMVariantType for Real values in Dynamics AX

Case Study: Reading cell content from excel template for COM variant type VT_R4 or VT_R8 is always little tricky.

Observation: Reading real value can be done in following ways
1) num2Str0(_variant.double(), 0);
2) num2str(_variant.double(), 0, numOfDec(_variant.double()), 1, 0);

Here is the output which is generated where the first function value is always a round-off value compared with the second function which returns the exact content with correct scale and precision.

COMVariantType Functions output for Real values.

/* 
Build excel template as following 
     and specify the path @ excel
=======================================
Column     Integer     Real                
=======================================
Rows(1)    123         60.9756097560976
Rows(2)    234         5.69105691056911
=======================================
*/

static void SR_VariantType(Filename excel = @'C:\Projects\Data.xlsx')
{
    int                 rows;
    int                 columns;

    COMVariant          variant;
    SysExcelCells       sysExcelCells;
    SysExcelWorkbook    sysExcelWorkbook;
    SysExcelWorkbooks   sysExcelWorkbooks;
    SysExcelWorksheet   sysExcelWorksheet;
    SysExcelWorksheets  sysExcelWorksheets;
    SysExcelApplication sysExcelApplication;

    str variant2Str(COMVariant _variant)
    {
        str valueStr;
        ;

        switch(_variant.variantType())
        {
            case COMVariantType::VT_EMPTY   :
                valueStr = '';
                break;

            case COMVariantType::VT_BSTR    :

                valueStr = _variant.bStr();
                break;

            case COMVariantType::VT_R4      :
            case COMVariantType::VT_R8      :

                if(_variant.double())
                {
                    valueStr = strFmt("@SYS311964", 
                                      num2Str0(_variant.double(), 0),
                                      num2str(_variant.double(),
                                      0,
                                      numOfDec(_variant.double()), 
                                      1, 
                                      0));
                }
                break;

            default                         :
                throw error(strfmt("@SYS26908", 
                                   _variant.variantType()));
        }

        return valueStr;
    }
    ;

    sysExcelApplication = SysExcelApplication::construct();
    sysExcelWorkbooks   = sysExcelApplication.workbooks();

    try
    {
        sysExcelWorkbooks.open(excel, 
                               false /*Update links*/, 
                               true /*Read only*/);
    }
    catch (Exception::Error)
    {
        throw error(strFmt("@SYS76826", excel));
    }

    sysExcelWorkbook   = sysExcelWorkbooks.item(1);
    sysExcelWorksheets = sysExcelWorkbook.worksheets();

    // Only considering Sheet 1
    sysExcelWorksheet  = sysExcelWorksheets.itemFromNum(1);
    sysExcelCells      = sysExcelWorksheet.cells();

    // Since in first row there will be field names.
    for ( rows = 2; rows <= 3; rows++)
    {
        for (columns = 1; columns <= 2; columns++)
        {
            variant = sysExcelCells.item(rows, columns).value();
            print variant2Str(variant);
            pause;
        }
    }

    // Close Excel
    sysExcelApplication.quit();

    variant             = null;
    sysExcelWorkbooks   = null;
    sysExcelWorkbook    = null;
    sysExcelWorksheet   = null;
    sysExcelCells       = null;
    sysExcelApplication = null;
}

Whenever reading cell content w.r.t real values always try using the function 2 specified above. Happy A(x)celing

Tip: QueryValue function in Dynamics AX

Case Study: Below a sample example is illustrated on how queryValue usage impacts the data retreival in reports based on different customer data.

Note: Enlarge the screen to view the actual content of the blog

Global function queryValue

Please check out for the XPO in the shared location: SharedProject_Tip_QueryValue | haPPy qURERING vALUE in AX

Reading Selected Node in XML – Dynamics AX2012

Case Study: Below a sample job is illustrated to read selected (multiple) nodes from a given XML.

private static void SR_ReadSelectedNode_XML(Filename fileName)
{
    #define.node('INFORMATION//ROWS/ROW')
    XmlDocument xmlDocument;
    XmlNode     xmlInformationNode;
    XmlNodeList xmlInformationsNodeList;
    XmlNodeList xmlChildNodeList;
    int         i;
    int         j;

    fileName                = @'C:\Projects\ReadNode.xml';
    xmlDocument             = xmlDocument::newFile(fileName);
    xmlInformationsNodeList = xmlDocument.documentElement()
                                         .selectNodes(#node);
    
    setPrefix("@SYS98689");

    for ( i = 0; i < xmlInformationsNodeList.length(); i++)
    {
        xmlChildNodeList = xmlInformationsNodeList.item(i)
                                                  .childNodes();
        
        for (j = 0; j < xmlChildNodeList.length() ; j++) 
        {
            xmlInformationNode = xmlChildNodeList.item(j);

            if (xmlInformationNode.baseName() == 'DETAILS')
            {
                info(xmlInformationNode.innerXml());
                break;
            }
        }        
    }
}

Happy dAX(ml)ING

Generating Lookups using SysAttributes Class – Dynamics AX2012

Case Study: In earlier or current release we have different ways of displaying lookups using table lookup, field group lookups, building reference lookup from custom form using FormAutoLookupFactory.

Building custom lookups for displaying AOT elements using SysModelElements/UtilElementsId was usually performed by Query/QueryBuildDataSource instead we can generate same using SysAttribute . Similiar type of functionality is implemented across AX2012 and one such instance is usage of attribute for displaying Workflow queues.



Design Approach: We are building runtime lookup by checking attributes whose values are ‘True’ and adding to the temporary table. This way we would display all the classes which has the following attribute syntax:

[SysClassEnabledAttributes(boolean::true, classStr(SysEnabledDocument))]

Where SysClassEnabledAttributes is a attribute base class which extends SysAttribute which helps us to build the required lookup.

Code Pattern:

/// <summary>
///    Finds the classes that extend the 
///    <c>SysEnabledAttributes</c> class that are queue enabled.
/// </summary>
/// <returns>
///    A <c>SysAttributeTmp</c> table buffer.
/// </returns>
public static SysAttributeTmp  getAllAttributes()
{
    #define.sysAttribute("SysClassEnabledAttributes")
    SysAttributeTmp                    attributes;
    List                               list;
    SysDictClass                       dictClass;
    DictClass                          currentClass;
    ListEnumerator                     listEnumerator;
    boolean                            flag = true;
    SysClassEnabledAttributes          attribute;

    dictClass = new SysDictClass(classNum(AbstractAttributesClass));
    listEnumerator = new ListEnumerator();
    list           = dictClass.extendedBy();
    listEnumerator = list.getEnumerator();

    while (listEnumerator.moveNext())
    {
        currentClass = new DictClass(listEnumerator.current());

        attribute = currentClass.getAttribute(#sysAttribute);
        if (attribute != null)
        {
            flag = attribute.parmFlag();

            if (flag)
            {
                attributes.Name        = dictClass.name();
                attributes.Description = attribute.parmName();
                attributes.insert();
            }
        }
    }

    return attributes;
}

Please check out for the design implementation in the shared location. Happy Attributing AX2012
SharedProject_Lookup_Attributes

Display Web Image Content in Forms using Dynamics AX2012

Case Study: In this demo scenario, we are going to display Web Image Content on forms using Dynamics AX2012. For this demo, I have taken a clue from my exisiting colleague Sreenath Reddy (Thanks Sreenath) to build a web image content.

Image:AX2012

Source Code


The web image content are read using StreamReader class and set into the X++ form. Using the above code snippet you can populate the external image which is required there by not actually requiring to store in local drives.

Note: Any external URL (HTML source code) needs to be read by textBuffer class and according fill the image content)
 
Please give a try to this wonderful application which is built on AX2012 Happy Streaming data using AX2012

Download Source code link: SharedProject_AX_WebImageContent

Generation of CodePage using X++

Case Study: In this demo scenario, we are going to build Windows Code page using X++ class.
For this project, I have build a generation class which basically returns the list of available codePage.

private void buildCodePage()
{
    System.Text.EncodingInfo[]    encodingInfoArray;
    System.Text.EncodingInfo      encodingObject;

    RecordInsertList              recordInsertList =  
                                  new RecordInsertList(tableNum(CodePageTable));
    System.Exception              ex;
    InteropPermission             permission = 
                                  new InteropPermission(InteropKind::ClrInterop);
    ;

    // Clear always the codePage data is available
    delete_from codePage;

    try
    {
        permission.assert();
        encodingInfoArray = System.Text.Encoding::GetEncodings();

        // BP Deviation Documented.
        for (i = 0; i <= 
                    CLRInterop::getAnyTypeForObject(
                    encodingInfoArray.get_Length()) - 1; 
            i++ )
        {
            encodingObject = encodingInfoArray.GetValue(i);

            codePage.CodePage    = System.Convert::ToString(
                                   encodingObject.get_CodePage());
            codePage.DisplayName = System.Convert::ToString(
                                   encodingObject.get_DisplayName());

            recordInsertList.add(codePage);
        }

        ttsbegin;
                recordInsertList.insertDatabase();
        ttsCommit;

        CodeAccessPermission::revertAssert();
    }
    catch (Exception::CLRError)
    {
        ex = ClrInterop::getLastException();

        if (ex != null)
        {
            ex = ex.get_InnerException();
            if (ex != null)
            {
                error(ex.ToString());
            }
        }
    }
}

Once this code is executed the runtime formRun displays the required output as shown below.



AX2012 DLink: SharedProject_CodePage happy AX2012

AX20009 DLink: SharedProject_CodePage happy AX2009