Wednesday, February 27, 2013

Parse out a node from an XML in AX 2012

Parsing out nodes from an XML is fairly easy in AX if you know what you're doing. I'm going to show how I do a basic XML parse.

For this example, the AX system can receive an XML that can either be for a quotation or sales order. There are multiple items in this request. We will need to cycle through the lines, do a process of some type (I will not share this as it wouldn't help anyone out), and finally, send that same XML back to the previous system indicating that a node changed. This node changed can be processed, an adjusted quantity, or whatever you really want.

I have a sample XML as well as the code which will:
  1. grab a node in the XML root and get the value
  2. cycle through the items sent in the XML
  3. grab a quantity from the attributes in the item nodes
  4. change a value in the item nodes
Hope this helps.

public static server XML daxXMLTest(str _custOrderXmlStr)
   XmlDocument custOrderXml;
   XmlElement  xmlRoot;
   XmlElement  xmlRecord;
   str         orderType;
   XmlNode     xmlNode;
   XmlNodeList xmlRecordList;
   RecId       lineRecId;
   int         i;

   custOrderXml = new XmlDocument();
   xmlRoot = custOrderXml.documentElement().getNamedElement('Items');
   xmlRecordList = xmlRoot.childNodes();

   // Grab a value 
   xmlNode = custOrderXml.documentElement().selectSingleNode('OrderType');
   orderType = xmlNode.text();

      // Cycle through the items in the incoming XML
      for (i = 0; i < xmlRecordList.length(); i++)
         // Grab the item in the XML that corresponds to the iteration number
         xmlRecord = xmlRecordList.item(i);

         // Parse out local variables
         lineRecId = str2int64(xmlRecord.getAttribute('RecId'));

         // Do the magic

         // Change value in the XML that was sent to AX so we can return it with new values
         // I'm setting the example 'newValue' to true just out of simplicity
         newValue = true;
         xmlRecord.setAttribute('NodeToChange', newValue);

return custOrderXml.toString();

Sunday, February 24, 2013

AX For Retail POS Error on startup: 'Retail POS cannot connect to a database. Try again later or see your system administrator'

In AX for Retail R2 (or earlier versions), upon clicking on a POS terminal with the intent of opening a POS session, you are slapped in the face by the lovely error 'Retail POS cannot connect to a database. Try again later or see your system administrator.' (see Figure 1 below)

Figure 1

This error (obviously since its stated as such) is because the system tries to open a POS session but cannot access the POS store's database. It then advises you to either try again or see the system administrator. The waiting game is because this could be because of a network issue and the terminal cannot connect to the DB. A DB connection is required for a store to be brought up as the one terminal one DB topology is no longer supported with AX for Retail after the 2009 release.

First off, since this message could be due to a number of reasons, check the Event Viewer's 'Application' Windows Log. It will save time and contains good stuff. As an example, if its the first time a user is attempting to get into a POS terminal and they got this error, the windows log may show: "Login failed for user 'CONTOSO\user1'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]"

Solution to this error
If a user group of 'POS Users' is assigned to that DB and the user is not assigned to that group in the domain, they won't be able to get access. If you do add that user to the appropriate group in the domain, the user will need to log off their OS and get back in before they see the changes or else they will say 'It's still broken'.

Here are some steps the system administrator will do to resolve the issue if its not the above. Check that:
  • The user has access to that database in SQL.
    • See above example
  • the POS configuration file is pointing to the correct DB
    • Right clicking on the POS icon->'Open File Location' will take you to the config file if you have privileges.
    • Then check the contents of the POS.EXE.CONFIG file.
      • The <AXRetailPOS> node will have the DB server in the StoreDatabaseConnectionString attribute as 'Data Source=[THEDBSERVER];Initial Catalog=[THESTOREDBNAME]...'. Make sure these are correct.
  • the Store DB server is up and running.
  • the Store DB's SQL service is running and not stopped or paused
  • the POS terminal machine can access the store DB
    • Ping the POS store's DB server from the terminal with this issue
      • If not able to, make sure the firewall isn't stopping the program from accessing the DB.

Thursday, February 21, 2013

AX MAXDOP settings for SQL performance

What is MAXDOP?
MAXDOP stands for MAXimum Degree Of Parallelism. It's the upper limit on the number of CPUs the SQL Server instance will use for a parallel query. Great. What is a 'parallel query'? Basically, SQL will use multiple processors to speed up the execution of queries. See below for the official MSDN definition.

Parallel Query Processing

SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently.
During query optimization, SQL Server looks for queries or index operations that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.
After exchange operators are inserted, the result is a parallel-query execution plan. A parallel-query execution plan can use more than one thread. A serial execution plan, used by a nonparallel query, uses only one thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of threads that are being used. The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.
The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:
  • The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode. (msdn 3)
Where can I find this setting?
There are two ways:
  1. With a UI, under Server Properties, click the Advanced group on the left hand vertical options menu. The last column will be 'Parallelism' and it is the third option.
  2. With SQL script:
    EXEC sp_configure 'show advanced options', 1
    EXEC sp_configure 'max degree of parallelism'
What should the MAXDOP value be for my system?
This is an interesting question that is highly debated at really nerdy happy hours. The first answer always given is the generic 'Well it depends on your situation'. This is very true even though it is a frustration answer for people to hear. Its more satisfying hearing a number 'commonly' used.

By default, SQL Server sets this value to 0 which means it is dynamic as there are no limits to how many CPUs SQL can spread out a query over (e.g all available processors on the SQL box).

For most large multi-user ERP systems, 1 is pretty common. Some people would get pissed about this value setting since it disables all query parallelism. Others would say that this good though as it eliminates the CXPACKET waits (a thread is waiting on a parallel thread to complete) associated with parallelism. Again the rebuttal is that CSPACKET waits are completely normal and expected behavior on a multiprocessor system and changing this value is not a solution as something else in the system isn't scaling properly. I'm not getting into this deeper but if its your thing, here is a good article I found: SQLBlog: Tuning ‘cost threshold for parallelism’ from the Plan Cache

According to Top Tips for Maximizing the Performance & Scalability of Dynamics AX 2009 systems on SQL Server 2008: "The AX perf team recommends that MAXDOP be set to 1 (again, this is the most common recommended setting for most large multi-user EPR systems). The reason is for this is for performance consistency. It is the most critical goal that all users 'see' the same basic resource use on a consistent basis. This ensures that all users share the resources in an equitable manner.

Since customer to customer setups can be so radically different, if the resources, know how, and time are available, it couldn't hurt to play with this setting and see if there are performance gains setting this to something like 2, 4, 1/2, etc. The trick is knowing what to measure and doing benchmarks thoroughly to know what to measure against. If this can't be done, no use in changing these values.

Parallism is more widely optimized in high performance single user (or low number of users) scenarios such as Data warehouses and/or bulk loading. Regardless of the setting, if you don't know what you're doing, don't set this value. 

How do I change the MAXDOP value?

You can change this value through sp_config tool, SSMS, or T-SQL. You can also change it from the SQL server properties
Figure 1 -  The Max Degree of Parallelism setting in SQL Server properties
  1. SQLCat - Top Tips for Maximizing the Performance & Scalability of Dynamics AX 2009 systems on SQL Server 2008 
  2. MSDN - Parallel Query Processing
  3. MSDN - Server Configuration Options (SQL Server)
  4. MSSSQLTips - What MAXDOP setting should be used for SQL Server

Tuesday, February 19, 2013

AX Issue: 'Method 'Start' in COM object of class '_TillLayoutDesigner' returned error code 0x80131904 () which means: Login failed for user 'DOMAIN\USERNAME'

When attempting to access the POS layout designer in AX 2012 for Retail, depending on how permissions are setup, you may get the error 'Method 'Start' in COM object of class '_TillLayoutDesigner' returned error code 0x80131904 (<unknown>) which means: Login failed for user 'DOMAIN\USERNAME'' (Figure 1 below)

Figure 1 

This could be the result of:
  • DLLs not being installed
  • the user getting this error does not have permissions to use the DLLs. It  This should be part of the install process but if new users are added, its entirely possible their users don't have specifically granted access. 
  • The user getting this error does not have the required database permissions for the ActiveX controller to get to the required tables
As a first step, make sure the appropriate DLLs are in place and installed on the client machine where the user is getting this error (if appropriate). The files need to be on ever client where the POS designer is going to be used.

As a second step, check that the user has access to the DLL. Go to the till layout designer DLL (probably C:\Program Files (x86)\Microsoft Dynamics AX\60\client\Bin\TillLayoutDesigner.dll), right click and go to properties, click on the security tab, and make sure the user attempting to reach the DLL's has permissions to get here.

As a third step, we need to look at the database security that the ActiveX control is using. AX for Retail has a concept called 'Toolbox users'. These users will utilize features in Retail Headquarters (AX) that use a Microsoft ActiveX control that requires direct access to the AX DB. Below are the actions that use these controls. The highlighted ones are the only ones we are interested for this error.
  • Till layouts
  • Form layouts
  • Button grids
  • Item images
  • Item bar codes
  • Language-specific item descriptions
  • Retail POS operations
  • Sales tax overrides
  • Payment types
To remedy this, do the following for each user of Microsoft Dynamics AX that should have access to these features in SQL Server Management Studio: 
  1. Create a new SQL Server user for the Microsoft Dynamics AX database by using Windows authentication and the user’s Windows logon ID.
  2. On the Securables page, grant the user Delete, Insert, Select, and Update permissions to the following tables: 
  3. Grant the user Select permission to the following tables  

Friday, February 15, 2013

Insufficient Memory to Run Script - MaxBufferSize

Note: This is an old post I never got around to posting. It was extremely helpful to me a bunch of other people I shared it with in the AX community back in 2008 - 2010 so hopefully it still is.

In previous versions of AX (AX 2009, AX 4.0, AX 3.0, etc), there was an issue where a file (mainly XML) coming into AX through a custom interface would be too large for AX to handle. It would spit out the error 'Insufficient Memory to Run Script' or something very similar to that.

When I'd hear this from clients, it usually was not at go live or even a month or two after. It was usually 3 or months and due to an unusually large order coming into the system that exceed our max testing limit for them. Since I was usually involved with other clients and this was more of an after hours support thing, I wrote a job to provide to clients to gather the info for me in the registry so I didn't have to log in to check. Unfortunately, it was very common for people to think they changed the settings when doing phone troubleshooting which didn't help anybody out so this solved that issue.

This solution was pretty solid. If you have problems, put a comment in the section below. I can help. While its been a while, I'm pretty familiar with this code since I wrote it and didn't rip it from other people like some people do.

Here's what I had from that old post:

TROUBLESHOOT: I've encountered an issue where I've updated the server and client registry settings as well as the individual .axc files to handle a larger variable but we were still hitting an issue.  When we would click on the grid of a table with the large XML string, it would immediately give us the Insufficient memory to run script error even without processing it.  Restarting the AOS didn't help either. This same process had been done to another environment without a problem so why just this one environment?

The way I solved it was to rebuild the .axc file using the client config with the new registry settings.  This cleared it right up with no problems. I'm guessing it is some kind of bug or something because the settings between the two environments were exact.

To test the max buffer size, do the following

// DAX Dude - Feb 25, 2011 
static void daxMaxBufferXMLVariableLoadTest(Args _args)
   // Job variables
   #define.FILEEXT_XML ('.xml')
   str xmlLocation = "C:\\Users\\daxdude\\Desktop\\issues\\02-25 failure\\";
   str fileName = "TEST";
   str file = xmlLocation + fileName + #FILEEXT_XML;
   XML xml; 
   Boolean loadedFile;
   int64 fileSizeInBytes;

   // Registry Retrieval
   #define.AOSRegistryPath(@'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dynamics Server\5.0')

   InteropPermission interopPermission = new InteropPermission(InteropKind::ClrInterop);
   System.String contents;
   str axAOSName = "01"; 
   str axConfigName = "Debug";
   str regValue;
   str axRegLocation;
   str axRegKeyName = "maxbuffersize";
   str axRegAXName = "application";
   str axEnvironment;

   // XML test objects
   System.IO.StringWriter stringWriter;
   System.Xml.XmlTextWriter textWriter;
   System.Xml.XmlDocument xmlDocument = new System.Xml.XmlDocument();

   // !!!NOTE - Cannot use base AX's XmlDocument class. Must use a System.Xml.XmlDocument type
   //XMLDocument xmlDoc = new XMLDocument();

      // Check the current registry value

      info ('------ CHECK FOR MAX BUFFER SIZE ON AOS ------');

      axRegLocation = #AOSregistryPath + '\\' + axAOSName + '\\' + axConfigName;

      contents = Microsoft.Win32.Registry::GetValue(axRegLocation, axRegAXName, null);
      axEnvironment = System.Convert::ToString(contents);

      if (axEnvironment)
         contents = Microsoft.Win32.Registry::GetValue(axRegLocation, axRegKeyName, null);
            regValue = System.Convert::ToString(contents);
            contents = Microsoft.Win32.Registry::GetValue(axRegLocation, axRegAXName, null);
            info (strFmt("%1 is set to %2 MB", axEnvironment, regValue));
            warning (strFmt('%1 does not have the registry value %2', axEnvironment, axRegKeyName));
            info ('Max Buffer Size (default) - 4 MB variable limit');
            Warning ('NOTE: Make sure to change the .axc file and client configuration. This only measures the AOS setting');
         error (strFmt("Could not find AX registry location: %1", axRegLocation));


      info ('');
      info ('------ XML SIZE TEST-------------------------');

      // Play with loading the XML
      if (WinAPI::fileExists(file))
         fileSizeInBytes = WinAPI::fileSize(file);

         info (file);
         info (strFmt("File Size - %1 KB/%2 MB", fileSizeInBytes/1000, fileSizeInBytes/1000000));

         loadedFile = true;
         stringWriter = new System.IO.StringWriter();
         textWriter = new System.Xml.XmlTextWriter(stringWriter);

         // !!!NOTE - Cannot use base AX's XmlDocument class. Must use a System.Xml.XmlDocument type

         xml = stringWriter.ToString();

         if (xml)        
            info ('!! Variable loaded !!');
         throw error ('Test XML not found');
      error ('No fun here');   

AX Issue: Cannot edit a record in Sales Orders (SalesTable). An update conflict occured due to another user process deleting the record or changing one or fields in the record

Occasionally you can see the error 'Cannot edit a record in Sales Orders (SalesTable). An update conflict occurred due to another user process deleting the record or changing one or fields in the record' (See Figure 1 below). The SalesTable table may not be the actual table name you are seeing but it was for me. This table can be any table in AX. I'm just going to use the example I dealt with.

Figure 1 -The error of interest

There are a lot of  solutions out there that people post about but no one really talks about why this is occurring; they only provide the solution. I'm not a fan of just providing answers as that answer may not be correct or may not apply to everyone. Also, I'm making an effort to add to the community with new content instead of barfing out copy and pasted things from other sites.

The reason why this is occurring is due to a conflict error on the order from a field list selection. When a record is selected forUpdate, the system will store the RecVersion number on that record in the buffer (with Optimistic Concurrency Control (OCC) table property set to 'Yes'). Each time the table is updated, the field RecVersion is updated with a random number that is saved by kernel. When the .update() is called, the system will check if the RecVersion on the table in the DB is equal to the RecVersion in the buffer which was stored at the time the select forUpdate. If these numbers are different, the system indicates that the record has already been changed since it was last selected and throws the error. I used this post as a spring board: Dynamics AX Czech Support Team - Conflict error when list of fields is used in select during update transaction I did my own testing using the code I'm providing at the bottom.

There are three ways to resolve this issue (see case 1 below for issue occuring) :
  1. Add RecVersion field into the selection (see case 3 below)
    1. ex/ select firstonly salesId, custAccount, RecVersion from salesTable; 
  2. Use forupdate keyword in the select statement (see case 2 below)
    1. ex/ select firstonly forupdate salesId, custAccount from salesTable; 
  3. Call [TableName].reread() before the update. (see case 4 below)
    1. ex/  salesTable.reread();
I ran into this issue with the Find method on the SalesTable so I couldn't use options 1 or 2. Option 3 worked for me.

Here is some proof of concept for people if you want to learn more about this:

static void daxTestRecordLock(Args _args)
    daxTestScenarioRecords daxTestScenarioRecords;
    int scenarioToRun = 1;

    switch (scenarioToRun)
        case 1 : 
            info ("SCENARIO 1 - This will FAIL due to the 'selectForUpdate' being its own line");
            select firstOnly RecId, Parameter6, Parameter7 from daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.Parameter8 = 'Second Update';
        case 2 :
            info ("SCENARIO 2 - This will PASS due to the 'forUpdate' in the select statement");
            select firstOnly forUpdate RecId, Parameter6, Parameter7 from daxTestScenarioRecords
               where daxTestScenarioRecords.recId == 5637153185;
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.Parameter8 = 'Second Update';
        case 3 : 
            info ("SCENARIO 3 - This will PASS due to the recVersion being selected even though 'selectForUpdate' was its own line");
            select firstOnly RecId, Parameter6, Parameter7, recVersion from daxTestScenarioRecords
               where daxTestScenarioRecords.recId == 5637153185;           
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.Parameter8 = 'Second Update';
        case 4 : 
            info ("SCENARIO 4 - This will PASS due to the reread() before the second update");
            select firstOnly RecId, Parameter6, Parameter7 from daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.Parameter8 = 'Second Update';
        case 5 : 
            info ("SCENARIO 5 - This will PASS due to not selecting field list");
            select firstOnly * from daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;    

            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.Parameter8 = 'Second Update';
        case 6 : 
            info ("SCENARIO 6 - This will PASS due to not selecting field list");
            select firstOnly daxTestScenarioRecords
                where daxTestScenarioRecords.recId == 5637153185;     
            daxTestScenarioRecords.Parameter7 = 'First Update';
            daxTestScenarioRecords.Parameter8 = 'Second Update';

Figure 2 - Proof of concept output from the code above 

Wednesday, February 13, 2013

AX SSRS Error: The request failed with HTTP status 401: Unauthorized.

In AX, when certain users attempt to access an SSRS report in an environment, they are getting the error 'The request failed with HTTP status 401: Unauthorized' (Figure 1 below). This was occurring for all reports for this user. Other users were able to access the report perfectly fine. The user was able to run everything perfectly the day before. The answer was Troubleshooting step 5 below: Log out of Windows instance and log back in. I'll provide the long troubleshooting steps below in case someone else's issue was different.

Figure 1 - Error message of interest

This issue is a permissions issue somewhere. At this point, we need to determine if its an issue with the user accessing the report. You can do the below troubleshooting steps. This was the order I did it in but you don't have to do it in this order.
  1. SSRS server (server permissions)
  2. SSRS reports (SSRS permission restrictions)
  3. AX security issue (probably not but good to check)
  4. An issue with all users or just them
  5. An issue with local cache or something screwy with current windows session
Troubleshoot 1:
To help the users identify this issue, I usually ask them to do the following on the server where they are receiving this error:
  1. Click on the Start menu in Windows
  2. Type 'cmd' in the 'Search programs and files' search line in the start menu
  3. In the command prompt, type 'ping CDBRS-MAX01'
  4. Take a screenshot of the command responses and send back the results.
    1. The results should come back with the standard ping response of 'Reply from XX.XX.X.XXX: bytes=32...''.
If #4 got a weird response or an error message, the user doesn't have access to the server. Make sure they can ping the server and have them run it again.

Troubleshoot 2:
Having established they can access the report server, have the user access the report through the SSRS server. Have them do the following in the server where the AX client is erroring and if necessary, the SSRS report server itself:
  1. Go to internet explorer and access the SSRS report server (commonly 'http://[SSRSSERVERNAMEHERE]/reports'
  2. Once here, go to 'Dynamics AX' folder
  3. click on the report that is erroring
  4. Run the report and see if it runs. Just make sure required fields are populated.
If the report runs for them, SSRS security should be good. If it fails, make sure they're setup in the proper groups to run the reports and try again.

Troubleshoot 3:
Have the user run the report from a server other than the one where the user was encountering the error. I logged into the AOS on their machine under my name and did the hold shift+right click on the .axc file, enter their credentials so we were running AX as that user in my server instance, and try running the report.

Troubleshoot 4: 
Have other users attempt to run that report as well as others. Determine if its an issue with just that user. If no one is able to run the report or others, the reports may need to be deployed or the SSRS server settings need to be checked.

Troubleshoot 5:
This actually resolved the issue. Something went wrong with the user's Windows server session. I wasn't exactly sure what the issue was but having the user log out and log back in to the server resolved.

In this test, do the following:
  1. go to the person's terminal and watch them try to run the report
  2. hold shift+right click on the .axc file they were using to run the report and run as a different user. Use your own user
  3. In AX under your user in their terminal session, try to run the troubled report. 
  4. If it passes (it did in my instance), hold shift+right click on the .axc file and run as a different user, only this time use the person's credentials. For us, the user was able to run the report this way but not when just clicking on the .axc in the normal fashion
Your issue might be different but this was what it was for us so I decided to share.  Hope this helps!

Tuesday, February 12, 2013

Loop over XPO files in a file location using X++ in AX

The following is code that will loop over all files in a location that are .xpo files. You can change the 'xpo' below to 'txt' or whatever,. It should work for all of it.

static void daxLoopOverFilesInFolder(Args _args)
    FilePath    filePath = 'C:\\Users\\daxdude\\Desktop\\XPO location\\';
    filePath    fileFilter = '\\*.xpo'; // Only want to look at XPO files
    FileName    fileName;    
    int         fileHandle;

    [fileHandle, fileName] = WinAPI::findFirstFile(filePath + fileFilter);
        info(strFmt("%1%2", filePath, fileName));
        // Get next file
        fileName = winAPI::findNextFile(fileHandle);

How to change Outlook 2013's default color scheme

This post will be about how to change Outlook 2013's default color 'theme' from 'all white all the time' (aka how to save yourself from clawing out your corneas). I am not a fan of the default view as I think it hinders visibility. I know my sentiments are shown in the general community as well.  

Note: I would like to preface this post by saying that I do recommend upgrading to Office 2013 and I am enjoying the added features. I do not get compensation for recommendations and referrals. Please don't think that even though I'd like to purchase a Surface. I'm not a fan boy and just really like it.

While there is no way to change the color scheme in Outlook 2013 like you could in previous versions of Windows for the OS, you do have the option to retain your sanity through a small tweak. This 'tweak' (aka new configuration option) will help alleviate this pain, but not fix what I consider to be a big issue.

There are two ways to 'customize' the look of the application: 'Office Background' and 'Office Theme'. I use the quotes in customize as its not making it unique in any way and is pretty mild in the changes that are actually made.
  1. With 'Office Background', it makes small changes to the top of the application... hooray...  See this well done post for more details: GroovyPost - How to Change the Office 2013 Background Theme.
  2. With 'Office Theme', it sightly changes the hue of the top and left hand side of the application. There are three exciting themes: 'White', 'Light Grey', and 'Dark Grey'. While sitting over my five dollar footlong subway sandwich (choice of the weight-conscious consultants), I decided these were about as exciting as their names teased.
Since the Office Background is not that exciting and the 'white' theme (See Figure 2 below) drove me nuts, this blog will just show you how to change this. In Outlook 2013, do the following:
  1. Click on 'File' in the upper left hand corner of the screen and select 'Options'
    • You will see a screen that looks like Figure 1 below
  2. Under 'Personalize your copy of Microsoft Office', you will see the 'Office Theme' option.
  3. Change this and set it to something other than white for god sake. I chose 'Dark Grey' to get it as far away from the default as possible. See Figures 2, 3 and 4 for an example of the options provided. You may need to adjust your monitor to see the difference...
According to HowTo-Outlook - 30 Short questions and tips for Outlook 2013, the white theme grows on you after a month or two. I won't doubt what they say as its a good blog but I can't live with it long enough to verify that statement. I can't believe the consumer preview only had 'white' as the theme. That is a testament to what beta testing consumer feedback can provide.

Figure 1 - The 'Office Theme' setting

Figure 2 - The 'White' Office Theme

Figure 3 - The 'Light Grey' Office Theme

Figure 4 - The 'Dark Grey' Office Theme

Friday, February 8, 2013

Error in AX 2012 for Retail POS: 'Cannot select a record in Catch weight setup (PdsCatchWeightItem)'


In the POS for AX 2012 for Retail, we were seeing the error "Cannot select a record in Catch weight setup (PdsCatchWeightItem). The SQL database has issued an error. Cannot execute the required database operation. The SQL database has issued an error. Cannot execute a data definition language command on (). The SQL database has issued an error." (Figure 1 below). This error can also be something like 'LedgerJournalTrans'

Figure 1 - Error from the POS


Those familiar with AX will recognize this error as a table sync error (except usually in AX, not the POS). There are no issues in AX though. Even more curious, we weren't even using catch weights for items. The table is actually synced correctly in AX but it seems that there is something in the RTS or AOS that makes the POS not happy...

While this may not be the solution to all scenarios where this is encountered, the solution to this can be a few things:
  1. sync the table in the first error (PdsCatchWeightItem in this example)
  2. restart the retail transaction service (RTS)
  3. If still needed: restart the AOS where you are seeing this issue. 
Not entirely sure what the root problem was but the above will fix the issue. Hope this helps out!

Thursday, February 7, 2013

AX 2009 security profiler tool

I know this post is outdated but then again there are still people out there running AX 3.0. I still think its worth throwing out there so here it is.

Security is always a pain point in ERP applications. Its one area that greys the line between functional and technical. AX security in versions 2009 and before pretty much required the configuring users to be somewhat technical and know their way around the AOT fairly well. In AX 2012, security has changed to role-based and is significantly different. I'd say it still helps quite a bit to be technical in this new security model but its not as necessary as the previous versions

Anyways, for AX versions 2009 and before, the hardest part was finding out the name of the AX objects and tying them to a security key for access permissions. This could be very time consuming. There is a person over at Microsoft AX Support who wrote an AX utility (Security Profiler Project 2009) to help speed this process along and make it easier for functional people to configure security. She's always been awesome to work with and it's a very useful tool. I've used it before and liked it a lot but find that I can do it on my own faster (but I've also logged over 1200 hours of security configuration and support work). For most people, this tool will be a fantastic and all previous clients I've had use it love it.

Some things to note is that this utility only records menu items that are not calling classes or menu items within classes. These issues shouldn't be too crazy for someone using this application as a starting point or not getting too granular with restricting access.

Note for this tool: The existing AX class we hook into gets used every time a form or report is opened in AX so the tool will slow performance down – so don’t load this in a production environment.

As a reminder for AX security, always start with full privileges and then take away. There are some issues if starting with nothing in a group and adding to it.

Sunday, February 3, 2013

Remove marked for settlement open transactions through X++ code

Due to the interesting way that AX settles marked transactions (see previous post:, it can be required to unmark all currently marked open transactions before marking a new set of transactions and doing an auto-settlement process. If this un-marking of transactions did not occur, certain credits or debits could be settled against unintended transactions because of a true lacking of 3-way matching functionality. Yes we can mod the system to do this (and I have...) but this post has nothing to do with that. But I digress...

In order to un-mark transactions that were previously marked via X++, use the code below:

CustVendOpenTransManager manager;
Common                   originator;

// Set the originator code from a record. 
originator = element.args().record(); 

manager = CustVendOpenTransManager::construct(originator); 

// If there are marked transactions, clear them
if (manager.getSpecTransCount() > 0)

Obviously the code above shows some flexibility using the common and loading the framework from different forms. If you just want to clear out a customer's marked transactions in a quick way, you can use the code below and simple replace the customer number with the one you are interested in clearing out. Easy enough.

static void unmarkAllTransByCustomer(Args _args)
    CustTable                custTable = CustTable::find('C00023567');
    CustVendOpenTransManager manager = CustVendOpenTransManager::construct(custTable);
    if (manager.getSpecTransCount() > 0)

Saturday, February 2, 2013

'Next-Gen RMS' aka Retail SMB unveiled by Microsoft

UPDATE 2/14/2014: Microsoft is releasing 'Retail Essentials'. While it is not replacing RMS, it is truly the next-Gen of a POS due to the large enhancements to the retail experience. It is AX as headquarters and the enterprise POS (ePOS) and mobile POS (mPOS) with a simplified AX retail architecture. The future is bright for the SMB market. I will publish several posts about this in detail in the near future. I have seen it and it is the future!

Exciting news in the Dynamics Retail realm. Microsoft is going to finally release an RMS and AX 'light' solution. The below is some information released about it from a few different locations (not sure how many are rumors but here is a collection of info):
  • Designed to give small- and medium-sized businesses (SMBs) the advantages of Microsoft Dynamics AX, but without the unnecessary features. 1
  • The new RMS product is going under the working title of "next-gen RMS," will be released near the end of this year according to Michael Griffiths.1 
  • The upgraded RMS product is also being referred to as "Retail SMB.1
  • Microsoft will continue to build on RMS as they are committed to their SMB retail customers and partners.1
  • Microsoft Dynamics AX for Retail, a.k.a., Microsoft Dynamics for Retail, which provides the building blocks of the new SMB retail offering.  3
  • "The vision of omnichannel is showing that all these channels are powered by AX, and now are powered by one commerce engine, the commerce runtime," explained Melissa Stern, senior industry analyst for retail, Microsoft Dynamics. 3  Note: Heavy RTS dependence?
    • 'Line-busting' will be a focus. This a mobile POS which can be used to dampen the effects of things like excessive holiday lines that exceed normal 'peak' capacities.
    • "When we talk about mobility - whether it's phone, or its slate, or a kiosk somewhere in the store, or a thin-web client -- (There are) lots of different platforms, lots of different form factors, the retail server is one way to provide that experience without having to write all the individual one-off solutions."  ~Jeff Blucher, Microsoft program manager
    • Consistency throughout all the channels; one set of rules used consistently throughout all the channels
    • Back end will dictate pricing, discounts and tax calculations like AX for Retail does today
  • Microsoft is intending to release the new version of RMS in 2013. 4
  • New product will effectively supersede both Dynamics RMS 2.0 and POS 2009 4
  • Will be built on a flexible and advanced business software platform shared with Dynamics AX 4 
  • Customers current on the Service Plan will be able to upgrade to the next generation RMS for no additional license costs. 4
  • The following features will be focused on in this new version:
    • Store management control;
    • Role-based, touch optimized, customizable POS user-interface;
    • Merchandising enhancements for loyalty, pricing, and promotion;
    • Replenishment, assortment management;
    • Alerts and monitoring for hardware, user-interface profiles, and data flows;
    • Multi-channel capabilities:
    • Catalog management, cross channel workflows,
    • Integrated sales channel management,
    • Social commerce;
    • Services (payments, commerce, sites);
    • Microsoft stack integration for Visual Studio, Office, and SharePoint;
    • Localization for individual countries.
  • 'Unnecessary features' like HR and CRM will not be in the AX light version but can be added to the solution if needed 5 Note: This makes me think it will be same code base as full version of AX
In the Microsoft Dynamics Retail realm, there are several tiers of retail software that a business could choose depending on their size and business needs: POS 2009, RMS 2.0, and AX for Retail. These point of sale solutions will allow tendering at the point of sale as well as integrations to the following Dynamic ERP solutions: Nav, GP and AX. To note, AX is the flagship ERP in the Dynamics product family with the most functionality and flexibility.

POS 2009 is good for very small businesses with only one or two stores. Usually outside consultants are not needed to implement this and the back end ERP is usually something like QuickBooks. RMS is good for small/medium businesses with a few brick and mortar locations. Usually this requires a consultant but not complex enough to keep one busy year round. Mainly just support during things like inventory counts and connection issues to HQ. AX for Retail usually fits in with the rest of the business models and requires a team of consultants to deploy but is robust with the AX back end and very scalable. Just to note, its primarily a cash and carry style POS but in AX 2012, you can create AX Sales Orders from within AX with it so the sky can really be the limit.

My thoughts:
RMS fits in with the majority of companies but there was a big issue with integrations with AX. While there are a few solutions out there to integrate these two, there was never an 'official' one. From what I've seen in a variety of industries, a company rarely lines up software-size wise between ERP and POS needs. It's pretty common that  a company is too small to use AX for Retail but is too big for GP/NAV/QuickBooks. The common consensus was to leverage AX in a limited capacity but alas we never saw anything. AX can fit with almost any business because of its readily customizable features and out of the box (OOTB) functionality. All business models have their quirks and AX is the best in the Microsoft ERP family to accommodate these when they are fairly large.

This new approach from Microsoft looks promising but I'm really interested to figure out what AX 'light' means. Is it going to use the same AX code base and program but be restricted to just certain functionality? Indication from what I've read so far is yes. There has always been 'rumors' that this would happen as it seemed like a natural progression so its good to see it actually coming to fruition. This is fantastic because it will give Microsoft the niche market of proving a single solution that runs RMS but needs very advanced, integrated functionality like HR or production.

As an important take away, make sure your customers are current on their service plan so they will be able to upgrade to the next generation RMS for no additional license costs. 4

  1. Microsoft Unveils Dynamics AX-based “Next-Gen RMS” at Retail Realm in Vegas
  2. - Microsoft Unveils Dynamics AX-based 'Next-Gen RMS' at Retail Realm in Vegas 
  3. - Understanding Microsoft's Next Gen RMS Begins with a Closer Look at Dynamics AX for Retail’s POS, Mobile, Omnichannel Capabilities  
  4. Retail Hero: RMS "New Version" 
  5. Mohamed Aamer - The Identity of Microsofts 'Next-Gen RMS' Will Be Deeper Than Its Dynamics AX Origins