Computed columns in Views – Dynamics AX 2012
Computed columns have been using in SQL server since many versions but this feature was available Dynamics AX since version 2012. Follow steps described here to learn how to create view and walthrough the complete example to understand how can we add computed columns in AX 2012 Views. More examples can be found from here; http://daxmusings.codecrib.com/2011/10/computed-view-columns-in-ax-2012.html Well, here is an example which illustrates how can we add computed columns in a view with different logic. Computed column; Returning Field in View public static server str compAmount() { #define.CompView(SWProjForecastCost) #define.CompDS(ProjForecastCost) #define.CostPrice(CostPrice) return SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #CostPrice)); } Computed column; Converting UTC Date to Date in View public static server str compDate() { #define.CompView(SWProjForecastCost) #define.CompDS(ProjForecastCost) #define.ActualDateTime(ActualDateTime) str sDateTime; sDateTime = SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #ActualDateTime)); return SysComputedColumn::fromUtcDateToDate(sDateTime); } Computed column; Returning Enum Value in View public static server str compGeneralTransType() { return SysComputedColumn::returnLiteral(Transaction::ProjectInvoice); } Computed column; Multiplying two coulmns and returning resultant from View private static server str compTotalCostPrice() { #define.CompView(SWProjForecastCost) #define.CompDS(ProjForecastCost) #define.QtyCol(Qty) #define.PriceCol(CostPrice) return SysComputedColumn::multiply( SysComputedColumn::returnField( tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #PriceCol) ), SysComputedColumn::returnField( tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #QtyCol) ) ); } Computed column; Case Statement in View public static server str TransType() { #define.CompDS(ProjForecastCost) #define.CompView(SWProjForecastCost) str ret; str ModelId = SysComputedColumn::returnField(identifierStr(SWProjForecastCost), identifierStr(ProjForecastCost), identifierStr(ModelId)); ret = “case ” + modelId + ” when ‘Sales’ then ‘Forecast Sales’ ” + ” when ‘Orders’ then ‘Forecast Orders’ ” + ” when ‘Latest’ then ‘Forecast Latest’ ” + ” end”; return ret; } Case Statement for this view looks like in SQL server as below; CASE T2.MODELID WHEN ‘Sales’ THEN ‘Forecast Sales’ WHEN ‘Orders’ THEN ‘Forecast Orders’ WHEN ‘Latest’ THEN ‘Forecast Latest’ END Till next post Happy Daxing 🙂
Retrieving/Picking files from Directory through X++
Following piece of code to pick all files from a directory. static void readFromDirectory(Args _args) { #Evat_NL #File // variable declration to retrieve files from folder Filename baseFolder; Filename pdfFilename; Filename foundBaseFileName; Filename foundFileName; container mainFolder, subFolder, fileContainer; boolean filesFoundMainFolder = true; boolean filesFoundSubFolder = true; int apiResult; int startTime, endTime, fileCounter; RecordInsertList recordList = null; int setCurrentFolder (Filename _filename = ”) { ; return WinAPI::setCurrentDirectory(_filename); } ; baseFolder = @”C:Testinvoices”; // Reading all pdf invoices from a directory apiResult = setCurrentFolder(SysTreeNode::duplicatePathDelimiters(baseFolder)); mainFolder = WinAPI::findFirstFile(“*.pdf*”); foundBaseFileName = conpeek(mainFolder, 2); startTime = WinAPI::getTickCount(); while (filesFoundMainFolder) { if (foundBaseFileName != #currentFolder && foundBaseFileName != #upFolder && foundBaseFileName != ”) { // Concatenating each file with base folder to get full path // For example; C:TestinvoicesInvoice1.pdf pdfFilename = baseFolder + foundBaseFileName; fileCounter++; } apiResult = setCurrentFolder(SysTreeNode::duplicatePathDelimiters(baseFolder)); foundBaseFileName = WinAPI::findNextFile(conpeek(mainFolder, 1)); filesFoundMainFolder = foundBaseFileName ? true : false; } endTime = WinAPI::getTickCount(); info(strFmt(‘It took %1 minutes to read %2 pdf files’, ((endTime – startTime)/1000)/60, fileCounter)); }
Tool to copy security permissions in AX
Copying security roles, groups, user options and associating worker against user The role based security works very well in AX 2012 and security development tool has also made the life of AX system administrator very charming. It was a nightmare to define security for each user in earlier versions of MS Dynamics AX. However, there is one area which security development tool does not cover and is often required in many occassions. That is “To Copy user roles from one user to another”. Let’s say we have created/imported a new user in AX and System Administrator requires to assign same role(s), existing users have, to this new added user. This requirement might come due to identical nature of work or responsibilities shared among different users. This tool will help you to copy users roles, users options, user groups, user’s main accounts and user association with worker by creating new worker if not exists. User association with worker is required to perform different business processes in AX, one of them is purchase requisition approval. You can download this tool from CodePlex: https://copysecuritypermissionsax.codeplex.com/ Please feel free to make suggestions and improvements.
Passing parameters to another form and open it in Edit mode
Below code can be used to pass one parameter from one form to another and open that form in Edit mode. FormRun formRun; Args args; str defaultFormOnTable; MenuFunction menuFunction; args = new Args(); args.record(CustTable::find(‘test account’)); menuFunction = new MenuFunction(menuitemDisplayStr(CustTable), MenuItemType::Display); if(menuFunction) { menuFunction.openMode(OpenMode::Edit); formRun = menuFunction.create(args); if(formRun) formRun.run(); } In this example I opened customers form in Edit mode by passing customer account ‘test account’as a parameter.
How to validate Email and URL through X++
With default functionality in AX, Email address and URL can be of any format and these do not get validated as per their respective correct formats. Existing functionality Accounts receivable | Common/Customers | All customers | Customer form | Under Contact information fast tab Someone can put Email address in any format as I did “faisal” which is not a correct format of email address. Same applies for URL under contact information for customers. Customized functionality I created a new class with two static methods to validate these attributes as follows by taking concept from this blog post /// <summary> /// Class to validate Logistics Electronic Address using Regex /// </summary> class LogiscticsElectronicAddressValidator { } /// <summary> /// This method accepts a EMail and validates this using REGEX /// </summary> /// <returns> /// true or false based on Regex match /// </returns> Static Server boolean validateEMail(EMail _eMail) { Boolean xppBool; System.Boolean netBool; Str MatchEmailPattern = @”^(([w-]+.)+[w-]+|([a-zA-Z]{1}|[w-]{2,}))@” + @”((([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9]).([0-1]? [0-9]{1,2}|25[0-5]|2[0-4][0-9]).” + @”([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9]).([0-1]? [0-9]{1,2}|25[0-5]|2[0-4][0-9])){1}|” + @”([w-]+.)+[a-zA-Z]{2,4})$”; System.Text.RegularExpressions.Match myMatch; ; new InteropPermission(InteropKind::ClrInterop).assert(); myMatch = System.Text.RegularExpressions.Regex::Match(_eMail,MatchEmailPattern); netBool = myMatch.get_Success(); xppBool = netBool; CodeAccessPermission::revertAssert(); Return xppBool; } /// <summary> /// This method accepts a URL and validates this using REGEX /// </summary> /// <returns> /// true or false based on Regex match /// </returns> Static Server boolean validateURL(URL _url) { Boolean xppBool; System.Boolean netBool; Str matchURLPattern = “^(https?://)” + “?(([0-9a-z_!~*'().&=+$%-]+: )?[0-9a-z_!~*'().&=+$%-]+@)?” //user@ + @”(([0-9]{1,3}.){3}[0-9]{1,3}” // IP- 199.194.52.184 + “|” // allows either IP or domain + @”([0-9a-z_!~*'()-]+.)*” // tertiary domain(s)- www. + @”([0-9a-z][0-9a-z-]{0,61})?[0-9a-z].” // second level domain + “[a-z]{2,6})” // first level domain- .com or .museum + “(:[0-9]{1,4})?” // port number- :80 + “((/?)|” // a slash isn’t required if there is no file name + “(/[0-9a-z_!~*'().;?:@&=+$,%#-]+)+/?)$”; System.Text.RegularExpressions.Match myMatch; new InteropPermission(InteropKind::ClrInterop).assert(); myMatch = System.Text.RegularExpressions.Regex::Match(_url,matchURLPattern); netBool = myMatch.get_Success(); xppBool = netBool; CodeAccessPermission::revertAssert(); Return xppBool; } To call these methods you need to override two methods (ModifiedField and ValidateField) in LogisticsElectronicAddress table. After implementing this solution you will get error on entering invalid email and URL addresses.
Random number generation through X++
AX has buit-in functionality to generate random numbers. You can use Random class (a kernel class, not visible in AOT) for this purpose. There are two more ways to generate random number in X++ One is using RandomGenerate Class which extends Random class and generates values within specified range; Second is using xGlobal::randomPositiveInt32() method. Example using Random class to generate random numbers (All in CAPITAL letters) public str generateRandomCode() { str pass=”; int length, counter, randInt,randAscii; ; length = 4; for(counter = 1; counter <= length; counter++) { randInt = Random.nextInt(); randAscii = randInt mod 91; if((randAscii >= 48 && randAscii <= 57) || (randAscii >= 65 && randAscii <= 90)) { pass += num2char(randAscii); continue; } else { randAscii = randAscii mod 26 ; randAscii += 65 ; pass += num2char(randAscii); continue; } } return pass; }
Exchange rate cannot be found for exchange rate type default between currenies USD & NZD
I came across this error while making some international payments in AX using EFT (method of payment). “Exchange rate cannot be found for exchange rate type default between currenies USD & NZD” Resolution: Go to General Ledger | Setup | Currency | Currency exchange rates Add new exchange rates for required currencies with start date Simple 🙂
Firewall settings for Microsoft Dynamics AX components [AX 2012 R2]
If you use Windows Firewall to help protect your computers, Microsoft Dynamics AX components require the settings in the following table. For more information about Windows Firewall, see the Windows documentation. Document is available to download.
Configure Visual Studio to connect/open with correct AOS
Visual Studio is essential to create/edit SSRS reports with MS Dynamics AX 2012 and later releases. Here are steps to configure visual studio with correct AOS (in my case it is development AOS). Connect with computer where visual studio is installed Go to location where visual studio file is installed and placed. In my case it is placed at C:Program FilesMicrosoft Visual Studio 10.0Common7IDE Right click and sent to create shortcut to desktop (one can create shortcut anywhere) Right click Visual Studio shortcut (will name something “devenv – shortcut”) and take properties Set target = “C:Program FilesMicrosoft Visual Studio 10.0Common7IDEdevenv.exe” /AxConfig C:FaisalFareedWorkfolderDynamicsAXTestSystem.axc Path of the original visual studio installation file as mentioned in step 2 “C:Program FilesMicrosoft Visual Studio 10.0Common7IDEdevenv.exe” Path of the configuration file where MS Dynamics AX 2012 configuration is stored. This can be UNC file path or local path, depends on your requirements. /AxConfig C:FaisalFareedWorkfolderDynamicsAXTestSystem.axc Double click on Make sure visual studio is connected with correct AOS. This “DynamicsAX_Test” is the configuration name in configuration file used as target in visual studio shortcut’s properties.
Running AX 2012: “An invalid directory structure for Microsoft Dynamics AX was detected”
I came across an issue recently when I tried to run Dynamics AX 2012 from client configuration file on 32-bit operating system. Client configuration file was created on 64-bit operating system and was copied to 32-bit operating system computer to use. As error says it tries to bind directory at path C:Program Files (x86)Microsoft Dynamics AX60\ClientBin which was not exist on 32-bit operating system. Resolution 1 Open client configuration (.axc) file in notepad and change the path at the very end of file. This path should match your directory structure in computer. Source computer (64 bit) configuration will look like this. 64-bit operating system generated configuration file Destination computer (32 bit) configuration should look like this. This is the correct path as per 32 bit computer directory structure. Save configuration file after correcting the paths. It will work smoothly. Resolution 2 http://blogs.msdn.com/b/emeadaxsupport/archive/2011/03/31/running-ax-2009-client-raises-error-quot-an-invalid-directory-structure-for-microsoft-dynamics-ax-was-detected-quot.aspx?CommentPosted=true#commentmessage MS Dynamics AX configuration files register in registry when you import/create new configuration files on Dynamics AX configuration. You can also change bindir, datadir and directory paths from registry too, for this you can find registry keys under Computer | HKEY_CURRENT_USER | Software | Microsoft | Dynamics | 6.0 | Configuration | <Configuration name>