Address/Contact Information: Export data using View in AX 2012

Hello Everyone,

It’s been a while since I have blogged and this time I will start on newer version named Dynamics AX2012. Before get into the act, I would like to take this opportunity to wish all a “Very Happy and Prosperous New Year 2014”

AX2012 has implemented lot of changes in GAB and the way they function. This would ideally mean we should know the exact relation between tables to export relevant data. In order to validate the customer addresses/contact information it would be a huge task. So the question remains what is the simplest way to export Customer/Vendor address/contact information the same way as in Dynamics AX forms.

Note: Although we have tools like DIXF/AIF for exporting data.

Exporting Customer Address: The form data which is shown in Customer master is got from a view. Similar approach could be followed to export the data.

Under AOT -> Views and find Party postal address view (DirPartyPostalAddressView). If you try to browse through the data you shall find data corresponding to following fields which is similar to customer address section except to which customer it belong to.



In DirPartyPostalAddressView click on Auto-report the following will be visible



In order to get the Customer Id, Customer Name, Purpose(method: locationRoles) and Primary add the following code in the view and drag into Auto-Report and Synchronize it as shown in the image.



On viewing the Auto-report in the view the following result will be as below



Note: Since we are finding customer Id based on it Party from CustTable we might have accounts which are blank, so it could be others account as well like Vendors or warehouse address for which you need to handle via code.

You can now export to excel easily. In case similar functionality is needed to get Contact information for customer/vendor, please follow the similar approach on DirPartyContactInfoView and get the required data ASAP.

Hope this blog post is useful. Happing addressing 🙂 in AX 2012.

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 🙂

Dynamics AX2012: Create Custom Service using X++

Case Study: In this demo scenario, we create a custom service to find the inventory onhand physical item quantity from AX2012.

To expose any Custom Service, method should be ‘public’ Access Specifiers & should be defined by an attribute named ‘[SysEntryPointAttribute(true)]’.



For this project, I have a created a sample InventoryOnHand service which basically returns the number of qty for a given Item.

class InventOnhandService
{
}

[SysEntryPointAttribute(true)]
public InventQty itemOnHandPhysical(ItemId  _itemId = '')
{
    SalesLine       salesLine;
    InventOnhand    inventOnHand;
    InventMovement  movement;
    InventQty       qty = 0;

    select firstOnly salesLine
        where salesLine.ItemId == _itemId;

    if (salesLine)
    {
        movement     = InventMovement::construct(salesLine);
        inventOnhand = InventOnhand::newPhysicalUpdate(movement, 
                                                       movement.inventdim());

        qty          = inventOnHand.availPhysical();
    }

    return qty;
}

Next step is to create a ‘Service’ & map the custom class with newly created service. By which you could exposed all public methods which are in the scope of current class. Please proceed further by Creating a New Service Group & Add it to Service Node Reference. Please mark the ‘Auto Deploy’ property to ‘Yes’ on the Service group (properties).

Right click on the created Service group and click ‘Deploy Service Group’. This would deploy all the relavent artifacts to the Inbound port under System Administration module.


In order to test your custom Service application, simply start VS Command prompt (or) traverse through Start -> AllPrograms -> Microsoft Visual Studio 2010 -> Visual Studio Tools -> Visual Studio Command Prompt

Enter wcftestclient, space and then paste the WSDL URI (which you can find on the deployed Inbound port)

When the WCF test client starts double click on your method ‘itemOnHandPhysical'(in my case) and specify the value under request by passing the _itemId (i.e. 1290 in my case) the invoke function will return the appropriate response of the available physical quantity of an item from the AX2012.

Download Link: InventoryOnHandService happy servicing AX2012 🙂

My Blog Review in 2011

The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.

Here’s an excerpt:

The concert hall at the Syndey Opera House holds 2,700 people. This blog was viewed about 14,000 times in 2011. If it were a concert at Sydney Opera House, it would take about 5 sold-out performances for that many people to see it.

Click here to see the complete report.