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
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.
/*
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
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;
}
}
}
}
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.
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.
You must be logged in to post a comment.