Read excel through X++ in D365FO
Sample code to read an excel file, all it need file stream as a parameter. public boolean importFromExcelfiles(System.IO.Stream _stream) { OfficeOpenXml.ExcelWorksheet worksheet; OfficeOpenXml.ExcelRange cells; OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(_stream); int totalRows,totalCells,rowCounter,cellCounter; if(package) { worksheet = package.get_Workbook().get_Worksheets().get_Item(1); cells = worksheet.Cells; totalRows = worksheet.Dimension.End.Row ; totalCells = worksheet.Dimension.End.Column; for (rowCounter = 2; rowCounter<= totalRows; rowCounter++) { for (cellCounter=1; cellCounter<=totalCells; cellCounter++) { //Do something with the values info(cells.get_Item(rowCounter, cellCounter).value); } } } return true; } This method can be called like this; FileUploadTemporaryStorageResult fileUploadResult; fileUploadResult = FileUploadControl.getFileUploadResult(); public void importFromFile() { if(fileUploadResult) { using(System.IO.Stream stream = fileUploadResult.openResult()) { Filename filename; if(stream) { try { filename = fileUploadResult.getFileName(); if(this.importFromExcelfiles(stream)) { info(strFmt(“File %1 has been imported successfully”, filename)); } } catch(Exception::Error) { warning(“File import failure”); } } } } }
D365FO – Dynamics 365 Tutorials Help You Get up and Running Fast
http://www.erpsoftwareblog.com/2017/06/dynamics-365-tutorials-help-you-get-up-and-running-fast/
D365FO – Method wrapping and chain of command
Another most awaiting Extensibility feature (Chain of Command) is going to come with one of the next platform updates. This enables you to call/use protected members and methods without making them hookable and using pre/post event handlers. More details are here https://blogs.msdn.microsoft.com/mfp/2017/07/04/extensible-x-chain-of-command/ and also here https://roadmap.dynamics.com/?i=296a1c89-ce4e-e711-80c0-00155d2433a1#.WVw1llbSw-M.linkedin Stay tuned on above road map site to find more on this feature.
D365O – How to get current worker through X++
The current user or worker could be retrieved through following code since AX 2012 DirPersonUser::currentWorker() However, In Dynamics 365 for operation this has refactored to; HcmWorkerLookup::currentWorker()
AX 2012 : Remove an XML node through X++
Following code snippet may help you to remove an XML node from an XML message string. private str removeXMLTag(str xml) { XmlDocument doc = new XmlDocument(); XmlElement nodeScript; XmlNode parentNode, childNode; XmlNodeList xmlScriptList, parentNodeList, childNodeList; int i,j; doc.loadXml(xml); // Get the root element and its child nodes nodeScript = doc.getNamedElement(“SalesOrder”); xmlScriptList = nodeScript.childNodes(); for(i=0; i < xmlScriptList.length(); i++) { parentNode = xmlScriptList.item(i); childNodeList = parentNode.childNodes(); for (j=0; j < childNodeList.length(); j++) { childNode = childNodeList.item(j); if (childNode.selectSingleNode(“Notes”)) { parentNode.removeChild(childNode); } } } return doc.outerXml(); } This will remove DocuRef node along with its child nodes. Original XML message <?xml version=”1.0″ encoding=”utf-8″?> <SalesOrder xmlns = “http://schemas.microsoft.com/dynamics/2008/01/documents/SalesOrder”> <DocuRef class = “entity”> <Notes>Business Name</Notes> </DocuRef> </SalesOrder> XML message after node being removed <?xml version=”1.0″ encoding=”utf-8″?> <SalesOrder xmlns = “http://schemas.microsoft.com/dynamics/2008/01/documents/SalesOrder”> </SalesOrder>
AX 2012: Reading files from directory through X++
Following code can be used to read files from the specified path. With this example, I am reading a CSV files from a directory. Class declaration class readFilesFromDirectory extends RunBase { FilePath filePath; Filename filename; CommaTextIo fileIO; Set fileSet; CommaIo commaIo; str currentFile; container lineCon; #File #AviFiles } main method Public static void main(Args _args) { readFilesFromDirectory readFiles = readFilesFromDirectory::construct(); readFiles.readFilesFromDirtecory(); } Construct Public static readFilesFromDirectory construct() { return new readFilesFromDirectory(); } readFileFromDirectory method private void readFilesFromDirtecory() { SetEnumerator sE; int row = 1; filePath = @”C:TempFilesToRead”; this.getFiles(); sE = fileSet.getEnumerator(); while (sE.moveNext()) // loop through all files { filename = sE.current(); this.openFile(); while(this.readLine()) // loop through all lines from current file { this.processRow(row, lineCon); row++; } } } getFiles method private void getFiles() { System.String[] files; System.Collections.IEnumerator enumerator; str file; try { fileSet = new Set(Types::Container); files = System.IO.Directory::GetFiles(filePath, ‘*.csv’); enumerator = files.GetEnumerator(); while (enumerator.MoveNext()) { file = enumerator.get_Current(); fileSet.add([file]); } } catch (Exception::Internal) { this.processCLRErrorException(); } catch (Exception::CLRError) { this.processCLRErrorException(); } } openFile method private void openFile() { FileIOPermission fileIOPermission; fileIOPermission = new FileIOPermission(filename, #io_read); fileIOPermission.assert(); commaIo = new CommaIo(filename, #io_read); if (commaIo && commaIo.status() == IO_Status::Ok) { commaIo.inRecordDelimiter(#delimiterCRLF); } else { throw error(strFmt(“Error when opening file %1”, filename)); } } readLine method private boolean readLine() { boolean readLine; if (commaIo && commaIo.status() == IO_Status::Ok) { lineCon = commaIo.read(); readLine = lineCon != conNull(); } else { lineCon = conNull(); readLine = false; } return readLine; } processRow method private void processRow(int _rowIdx, container _row) { //conpeek(_row, 1); //conpeek(_row, 2); //conpeek(_row, 3); //conpeek(_row, 4); //conpeek(_row, 5); //conpeek(_row, 6); //conpeek(_row, 7); //conpeek(_row, 8); //conpeek(_row, 9); //conpeek(_row, 10); } processCLRErrorException method private void processCLRErrorException( boolean _throw = true ) { str strError; CLRObject exc; CLRObject innerExc; CLRObject clrExcMessage; exc = CLRInterop::getLastException(); if (exc) { clrExcMessage = exc.get_Message(); innerExc = exc.get_InnerException(); while (innerExc != null) { clrExcMessage = innerExc.get_Message(); innerExc = innerExc.get_InnerException(); } strError = CLRInterop::getAnyTypeForObject(clrExcMessage); error(strError); if (_throw) { throw error(“Update has been cancelled”); } } }
AX 2012: Create sales order with delivery address from an XML file
Code snippet to create a sales order from an XML file. private void createSOFromFile() { AxdSalesOrder salesOrder; AifEntityKey key; Filename fileName; RecId salesTableRecId; SalesTable salesTable; XmlDocument xmlDoc; salesOrder = new AxdSalesOrder(); fileName = @”C:TempSalesOrder.XML”; xmlDoc = XmlDocument::newFile(fileName); key = salesOrder.create(xmlDoc.xml(), new AifEndPointActionPolicyInfo(), new AifConstraintList()); salesTableRecId = key.parmRecId(); info(strFmt(“Sales order created: %1”, SalesTable::findRecId(salesTableRecId).SalesId)); } Sample XML file <?xml version=”1.0″ encoding=”UTF-8″?> <SalesOrder xmlns:n=”http://schemas.microsoft.com/dynamics/2008/01/documents/SalesOrder”> <SalesTable class=”entity”> <CustAccount>C000002</CustAccount> <InventLocationId>EMU</InventLocationId> <CustomerRef>Cash on Delivery (COD)</CustomerRef> <PurchOrderFormNum>4669286216,1021</PurchOrderFormNum> <ReceiptDateRequested>2011-11-11</ReceiptDateRequested> <DeliveryName>team03qa@gmail.com</DeliveryName> <TableDlvAddr class=”entity”> <City>southbank</City> <CountryRegionId>AUS</CountryRegionId> <LocationName>delivery address</LocationName> <State>VIC</State> <Street>13075 MANCHESTER RD STE</Street> <ZipCode>3006</ZipCode> </TableDlvAddr> <SalesLine class=”entity”> <ItemId>CANO</ItemId> <SalesQty>20</SalesQty> <SalesPrice>82.25</SalesPrice> <SalesUnit>ltr</SalesUnit> <LineAmount>1</LineAmount> <LineNum>8966003144</LineNum> </SalesLine> <SalesLine class=”entity”> <ItemId>CANO</ItemId> <SalesPrice>9.9</SalesPrice> <SalesQty>1</SalesQty> <SalesUnit>ltr</SalesUnit> <LineAmount>9.9</LineAmount> <LineNum>8966003208</LineNum> </SalesLine> </SalesTable> </SalesOrder> How does it work AxdSalesOrder class uses AifEntity classes to create sales order, these classes internally map the datasources of an AOT query AxdSalesOrder with the element tags in XML with attribute class = “entity”. For example; for delivery address, the XML node name is TableDlvAddr which is the datasource name in AxdSalesOrder query. Similarly, if you want to add notes or attach documents with sales order you can add DocuRefHeader node in XML file.
AX 2012: Reading XML Nodes under specific tag through X++
XML to read X++ Code For above XML structure, I will read all nodes exist within each InstructorAPIModel node. The getElementsByTagName() method of XmlDocument class can be used which returns XmlNodeList which can then be iterated to get node’s values. XmlDocument doc = new XmlDocument(); XmlNodeList apiModelList; XmlNodeListIterator iterator; XmlElement apiModel; XmlElement firstName; XmlElement lastName; doc.loadXml(_xmlMsg); apiModelList = doc.getElementsByTagName(‘InstructorApiModel’); iterator= new XmlNodeListIterator(apiModelList); while (iterator.moreValues()) { apiModel = iterator.value(); firstName = apiModel.getNamedElement(‘FirstName’); if (firstName) { info(strFmt(“First Name: %1”, firstName.text())); } lastName = apiModel.getNamedElement(‘LastName’); if (lastName) { info(strFmt(“Last Name: %1”, lastName.text())); } iterator.nextValue(); }
AX 2012: Reading XML Nodes through X++
XML to read X++ Code XmlDocument doc; XmlNodeList xmlScriptList; XmlNodeList xmlResponseList; XmlNodeList xmlTemplateList; XmlElement nodeScript; XmlElement nodeResponse; XmlElement nodeTemplate; XMLParseError xmlError; str xmlMsg; int i, j; ; // Create the XML Document doc = new XmlDocument(); doc.loadXml(xmlMsg); // Verify XML Document Structure xmlError = doc.parseError(); if(xmlError && xmlError.errorCode() != 0) { throw error(strFmt(“XML Error: %1”, xmlError.reason())); } // Get the root element and its child nodes nodeScript = doc.getNamedElement(“ns2:return”); xmlScriptList = nodeScript.childNodes(); for(i=0; i < xmlScriptList.length(); i++) { nodeResponse = xmlScriptList.item(i); xmlResponseList = nodeResponse.childNodes(); for (j=0; j < xmlResponseList.length(); j++) { nodeTemplate = xmlResponseList.item(j); xmlTemplateList = nodeTemplate.childNodes(); if (nodeTemplate.selectSingleNode(‘id’)) { info(strFmt(“ID: %1”, nodeTemplate.getNamedElement(“id”).text())); } if (nodeTemplate.selectSingleNode(‘messageTemplateName’)) { info(strFmt(“messageTemplateName: %1”, nodeTemplate.getNamedElement(“messageTemplateName”).text())); } } }
AX 2012: Attach documents or creates notes through X++
Came across a requirement to attach documents and create notes for a sales order after reading files from directories. Documents can be attached to a sales order manually from the following button. Code snippet to create notes or attach documents DocuRef docuRef; DocuActionArchive docuArchive; SalesTable salesTable = SalesTable::find(“SO00001”); Filename fileName = @”CTempSalesOrder.docx”; ttsbegin; // Code to create notes docuRef.TypeId = ‘Note’; docuRef.Name = “Sales order notes”; docuRef.Notes = “Checked Postcode: 2000, Select resident type”; docuRef.Restriction = DocuRestriction::External; docuRef.RefTableId = tableNum(SalesTable); docuRef.RefRecId = salesTable.RecId; docuRef.RefCompanyId = curext(); docuRef.insert(); //Code to attach file docuArchive = new DocuActionArchive(); docuArchive.add(docuRef, fileName); ttscommit; DocuRef Table has field RefTable and RefRecId which could be referenced by any table in AX and with any record respectively. Do set RefCompanyId field in DocuRef table otherwise records will not create.