Primary key index not showing up in AX 2012 table

There was an issue where I was training a couple resources for the client on how to create table and a form in AX so they can import data into AX, view/modify the data in a simple form, then export the data to Excel for reporting. Simple enough. A 30 minute mod that turns into a 4 hour AX training exercise because its a 'teaching tool' exercise.

After creating everything in a training session, we hit the an error 'Cannot load [AXTable]. Tables must have a valid replacement key or a primary index other than RecID for use in the Office Add-ins' when trying to add the table datasource in Excel.

Figure 1 - The error in Excel

It sucks that all the stuff from previous versions of AX were followed but the error above was hit.  We had a unique key and everything. it just wasn't assigned on the table properties as primary. The primary key wasn't showing up in the primary key drop down like it did in previous versions. Just typing the name of the id in there if the idx doesn't show up in the drop down does not mean it will work. The name will show up in the properties but then, when clicked on, the name disappears. This is because it doesn't actually exist there. Don't be fooled. The Surrogate index won't work for this either btw. The primary key I planned to use contained a unique combination of three fields

I'll cut to the chase. There are new features for AX 2012 leveraging features in SQL that AX used to not be able to use (primarily alternate keys). Here are the criteria that need to be done to get the index to show up in the properties of the AX table so this error can go away:

• The index needs to be unique ('AllowDuplicates' property set to No) (Figure 2)
• The index needs to have 'AlternateKey' property set to Yes (Figure 2)
• The primary index of interest can only have one field in it
• NOTE: This sucks in the scenario I have where we have three fields which would usually be the primary index. A primary field should technically only be one field anyways. This is just forcing the scenario. So create two unique indexes if you need to export to Excel using the add-in: one that is the primary and one that contains the unique combinations of the other fields. Both Alternate Keys values should be set to true.
• The field on the table used in the primary key needs to be mandatory (Figure 3)
• The field on the table used in the primary key cannot be editable (Figure 3)
• Setup a number sequence or value creation logic for this value (complexity just went up)

Figure 2 - The index properties on the table (note: do not edit in the USR layer like in this picture...)

Figure 3 - The field properties in AX for the field used in the primary key

Hope the above helps some people. It's not a huge deal but was a source of frustration when it should not have been an issue...  Please make sure to chose your primary key carefully as it can be extremely tricky to change this in a production environment.
 

Hotel toiletries and status ranking

So, let me preface by saying this post is pretty much worthless. there is nothing technical in it whatsoever. I doubt I will get any hits on it at all. If I do, I hope everyone who reads this appreciates that I wrote it with the expectation no one will truly read it.

Ever since I was young, for some strange hoarding reason, I've collected hotel toiletries. It sounds stupid when categorizing a collection of things that are used on a (hopefully) daily or bi-daily basis in a term that sound like an object you crap in...  Toiletries are necessities in a zombie apocolypse: shampoo, conditioner, face soap, body soap, hand soap, liquid soap, Q-Tips, mouthwash, etc. I'll happily sell you them at a premium. I'll also trade for gold and silver.

These are a huge draw for me and I have stock piled so many I could keep a small army clean for a small war. Some would smell like Aveda sandalwood and others as Peter Thomas Roth Grapefruit with exfoliating beads. An unlucky few would smell like cheap mountain rain from an cheap hotel branded product line.

Anyways, I always size up a hotel by the toiletries. I've stayed at places that span from $80 - $800 a night in Hiltons, Marriotts, Four Seasons, etc and one of the most constant comparisons to quality is the toiletries. Maybe I'll create a blog post where I try to detail out what places have what. Probably not but whatever.  Just wanted to write about it because I can and this is my domain.

Missing tool bar and close X on task manager (taskmgr)

So I was on a very late night call and immediately passed out afterwards right in my chair.  Unfortunately that is not unusual when you are hitting 5 hours a night of sleep everyday on a crazy schedule.  All consultants know the situation of when and how this happens.  This post isn't about that though so moving on...

When I woke up, my task manager window did not have a border around it (see Figure 1 below). I think I must've double clicked on the edge of the task manager on accident but I really don't remember. All I know is I woke up and it didn't look right.  I didn't know how to close out of it and do the stuff that I needed to.

This is an easy fix but thought I would post about it on here.  If you double click on the border somewhere (see highlighted area in Figure 1), it will show the border again (Figure 2).

Easy enought but I didn't know about it with all of the stuff that I know so I thought it was blog worthy.  Enjoy!

Figure 1 - Task Manager with no border and highlighted area to double click

Figure 2 - Task Manager with the borders

Windows 7 and Autorun.inf. Won't work from USB

I'm writing this post to let everyone know that the autorun.inf file will not automatically trigger from a USB drive in Windows 7.  I wrote a post (What is autorun.inf file) talking about the autorun.inf file because this can work for CDs/DVDs and other applications but WILL NOT work for auto starting a program from a USB drive. 

This used to be possible from a USB when I originally wrote the stuff in my files but since Microsoft removed it, its not anymore. I used it for a personal program I had on my USB so I didn't care about getting a virus from the USB.

As a rule of thumb, I always suggested people disable the autorun on their PCs to avoid viruses.  The reason for this is that if it is enabled and someone puts a USB in your computer, your computer can get a virus. It's insane how many people have viruses on their computer and don't even know it.  It's bad practice for people to put updates on a USB and just go computer to computer installing it that way for a few reasons.  First and foremost, its incredibly inefficient for deploying updates.  The second is the virus aspect.

Before you pass judgement on Microsoft for disabling this for USBs, look at the following:
If you insert a USB into your computer with autorun enabled and the autorun.inf pointing to an infected program, your computer now has it. Congrats! The autorun.inf viruses can really suck and wreck havok on things. Seriously bad news. Now in Windows 7, the virus will only transfer to your computer if you manually open the drive. This is great since you now have a chance to scan the drive and clean all viruses before opening it.

All about 'Autorun.inf'

What is the 'Autorun.inf' file?
The file extension .inf stands for information file. The file is a simple text-based configuration file that tells the operating system (OS) how to deal open the presentation and treat the contents of the media (CD, DVD, USB thumb drive, etc). It tells the operating system which executable to start, which icon to use, and which additional menu commands to make available.
It defines the following:

• The process or application that will automatically run when a disk is inserted
• Optionally, one can define the process or application that will run for specific Operating environments.
• The icon that will represent your application's CD or DVD when the drive is viewed with My Computer or Explorer.
• Menu commands displayed when the user right-clicks the CD-ROM icon from My Computer or Explorer.

What does it contain?
There are 5 parts to this file: [autorun], [Content], [ExclusiveContentPaths], [IgnoreContentPaths], and [DeviceInstall]

Sections:

1. [AutoRun]
1. This section is required for the file to be valid
2. There can be one of these sections for the different operating systems (e.g. [autorun], [autorun.mips], [autorun.alpha], etc).  This is important so the appropriate executables run that are compatible with the computer the media is entered in.
3. PURPOSE: contains the default AutoRun commands
4. VALID KEYS:
• Action
• Specifies text to be ussed in the AutoPlay dialog to represent the program specified in the open or shellexecute keys.
• VALUES: Text or '@[filepath\]filename, -resourceID 
• Open
• Specifies the path, file name and optional parameters to the application that AutoRun launches when a user inserts a disc in the drive
• It is the CreateProcess function that is called by AutoRun.
• VALUES: '[exepath\]exefile [param1 [param2 ...]]'
• Icon
• Name of a file resource containing an icon.  This icon will replace the icon of the drive when it is looked at from
• VALUES: 'iconfilename[,index]'
• EXAMPLE:
• icon=MyProg.exe,1
• Label
• This will be the text label of the drive
• VALUES: Text
• EXAMPLE:
• label=My Drive Label
• shellexecute
• Like the open command but this uses the machine's association information for the file type.
• VALUES: '[filepath\]filename [param1 [param2 ...]]'
• EXAMPLES:
• shellexecute="Readme.txt"
• shellexecute=[filepath\]filename[param1, [param2]...]
• UseAutoPlay
• Use AutoPlay rather than AutoRun
• VALUES: boolean
• EXAMPLE:
• UseAutoPlay=1
• shell\verb\command
• Adds a custom command to the drive's shortcut menu.
• Verb is a string with no embedded spaces.
• Verb is also the text that will appear in the shortcut menu unless specifically altered to some other text
• VALUES: '[exepath\]exefile [param1 [param2 ...]'
• EXAMPLE:
• shell\readit\command=notepad abc\readme.txt
  shell\readit=Read &Me
• shell\verb
• Optionally specify the text displayed in the shortcut menu for the verb above.
• Use an ampersand (&) to select a hotkey for the menu.
• VALUES: Menu Text
• EXAMPLE:
• shell\verb\command=Filename.exe
  shell\verb=MenuText
• shell
• Defines the menu command referred to by shell\verb as the default command in the shortcut menu.
• The default command is the command executed when the drive icon is double-clicked.
• If missing, the default menu item will be "AutoPlay", which launches the application specified by the open entry.
• VALUES: Text
• EXAMPLE:
• shell=verb
2. [Content]
1. This section is not required
2. PURPOSE: Allows authors to communicate the type and intent of content to AutoPlay without AutoPlay having to examine the media
3. VALID KEYS:
• MusicFiles
• Values: boolean
• PictureFiles
• Values: boolean
• VideoFiles
• Values: boolean
4. The key values will determine if the handlers associated with that contect type are displayed
5. EXAMPLES:
• [Content]
  MusicFiles=Y
  PictureFiles=0
  VideoFiles=false
3. [ExclusiveContentPaths]
1. This section is not required
2. PURPOSE: Limits AutoPlay's content search to only those folders listed, and their subfolders. The folder names are always taken as absolute paths (a path from the root directory of the media) whether or not a leading slash is used.
3. EXAMPLE:
• [ExclusiveContentPaths]
  \music
  \music\more music
  music2
4. [IgnoreContentPaths]
1. This section is not required
2. IgnoreContentPaths takes precedence over ExeclusiveContectPaths so if a path given in a [IgnoreContentPaths] section is a subfolder of a path given in an [ExclusiveContentPaths] section it is still ignored.
3. PURPOSE:AutoPlay's content search system will not scan the folders listed, nor their subfolders.
4. EXAMPLE:
• [IgnoreContentPaths]
  \music
  \music\more music
  music2
5. [DeviceInstall]
1. Used to indicate where driver files may be located
2. Not used with AutoRun or AutoPlay and is only referred to during a driver installation phase
3. Multiple key entries are allowed.
4. PURPOSE: used to indicate where driver files may be located. 
5. VALID KEYS:
1. DrivePath
1. Values: text (a directory path)
6. EXAMPLE:
• [DeviceInstall]
  DriverPath=drivers\video
  DriverPath=drivers\audio

Simple example of an autorun.inf

[autorun]
open=PStart.exe
action=Run PStart Program
icon=PStart.exe
label=My Portable PC

Complex example of an autorun.inf

[autorun]
open=filename.exe /argument1
icon=\foldername\filename.dll,5
[autorun.mips]
open=filenam2.exe
icon=filename.ico
[autorun.alpha]
open=filenam3.exe
icon=filename.ico
[autorun.ppc]
open=filenam4.exe
icon=filename.ico
shell\install = &Install
shell\install\command = setup.exe
shell\uninstall = &UnInstall
shell\uninstall\command = Uninstall.exe
shell\readme = &Read Me
shell\readme\command = notepad readme.txt
shell\help = &Help
shell\help\command = helpfilename.hlp

For more information see these associated blog posts:

How to view all Embedded Resources in AX 2012

Are you trying to create a form, report, etc and want to find an image for it that exists in base?  There are a lot that exist within AX that are currently used (in the form ribbon bars for example).  The trick is finding the perfect image for your application.

While it may be fun tireless searching through hundreds of forms looking at all the images then finding the resource number (ImageLocation = EmbeddedResource), there is a much faster way.  There is a form that contains all of these images as well as the number that correlates to them.

To get to this form, go into the AOT (Ctrl+D), expand out the forms node, type (or move scroll bar) to the 'SysImageResources' form. Open this form (Right click -> Open or Ctrl+O).  When I did this for the first time, it opened underneath the properties window as a right docking window.  To get this to be a movable window, right click on the header of the embedded window and select 'No Docking'.  This will allow you to move the window around. There used to be a report that had it all on there but it doesn't look like its there anymore.  Oh well.

This embedded resource number can be used on the 'NormalImage' property of the object (like a form button).  If you use it, make sure you set the 'Big' property of the button to 'Yes'.

Assign a date to DateTime in AX 2012

I was working with a client where we were seeing an issue with a data import and the table OMHierarchyRelationship's ValidFrom and ValidTo date fields were importing wrong for the Organizational hierarchies in AX2012.

We wanted to set the ValidFrom to the date of 1/1/2005 (not derived from an AX function) and the ValidTo as the maximum date.  The maximum date seemed to be set to the valid of 'Never' but that was actually a max date of 12/31/2154.  Really, this is stupid because the world will 'end' on December 21, 2012.   That is 142 wasted daysYEARS of real estate my Microsoft friends... 

There is often a 'developer' issue of remembering how to set dates not using an AX function.  Unless you do it all the time, its always a subject that takes a few minutes to remember how to do things with. Don't lie... You know its true...  If you're coming from AX 4.0, welcome to the UTC shock.

AX 4.0 always stored the data type 'Date' behind the scenes as a DateTime but just hacked off the time part on the AX end. There was a time EDT which was stored in seconds. Not too bad as long as you don't mind dividing by 60 all the time... There were some huge improvements in AX 2009 and 2012 wiht the UTCDateTime functionality. The biggest is the ability to see the date and time in AX as it is in SQL as well as be able to dynamically display timezone changes to the users based on where they are located.

Anyways... There can be issues with the code needed to do paragraph two above.  Here is how to do it:
omHierarchyRelationship.ValidFrom  = DateTimeUtil::newDateTime(1\1\2005, 0);
omHierarchyRelationship.ValidTo    = DateTimeUtil::maxValue();

The maxValue() function will put the dateTime as the max date of 2154.  The newDateTime() function will allow you to set the field for what you specify if you use MM\DD\YYYY.  Make sure the slashes are '\' and not '/'.  It MUST be 1\1\2005 and not be 1/1/2005; note the back vs forward slashes.  There do not need to be single colon's around the variable.

I hope this helps!! Please put a comment below if it doesn't.  I'll make sure that it does.