Friday, August 31, 2007

Don't use Check/Synchronize on AX 4.0

I have investigated some odd results I got when running Check/Synchronize on a customers test system. The conclusion was that SqlDictionary held wrong data, but I couldn't figure out why. My fear was that Check/Synchronize screwed up the data.

Today I found an article in the knowledge base describing that Check/Synchronize actually screws up the data in SqlDictionary.

The problem is "solved" in SP2 - by disabling the Check/Synchronize button.

Read more here.

Thursday, August 30, 2007

Find date using year and week as input

Today we needed an algorithm to calculate a date using only a year and a week number as input. As you may know different parts of the world has different rules for what week to consider as the first week of the year, so you have to be very careful when trying to write a generic algorithm.

In stead of being clever and figure this out ourselves, we decided that “AX knows best”. So basically we produce some dates and let AX tell us if they are in the week we are looking for.
The only part where we try to be clever is where we figure out what date to start asking on and not just start in the beginning of the year.

Here is what we came up with:
static void Week2Ddate(Args _args)
Week week = 1;
Yr yr = 2009;

Date testDate = dateStartYr(mkDate(01, 01, yr)) + ((week - 1) * 7);
Int weekdays;

if (weekOfYear(testDate) != week)
if (week > weekOfYear(testDate))
weekdays = 7;
weekdays = -7;

while (weekOfYear(testDate) != week)
testDate = testDate + weekdays;

info (strFmt("Dato: %1", dateStartWk(testDate)));

Any comments with better ideas are welcome. And well, in the end of the day we found that we didn't need the algorithm anyway, but it was fun to think about how to do it.

Wednesday, August 29, 2007

Turn it off...

The configuration key SysDeletedObjects40 ("Keep update objects") is meant to be turned off, but I often see it turned on in live environments.

The point of the configuration key is to mark tables and fields that are no longer used by the new standard application. The marking indicates that the table or field will be completely removed from the next version of AX.
Having the key turned on allows you to eventually upgrade your data when you upgrade from an earlier version. If you have done the upgrade or you don't need to do any upgrade, turn it off.

Turning it off removes the physical data from the datebase. For fields you will get rid of the field from select statements, which can be a great performance improvement. Take for example the CompanyInfo table where AX keeps selecting the bitmap stored in the field DEL_Logo, until you turn off this configuation key. The content of this particular field has been copied to another table during the upgrade and AX uses this other table when it actually needs the company logo image.

If 4.0 is your first AX version, i.e. you are not upgrading from an earlier version, there is absolutely no point in having the configuration key turned on.

See also "Disable Keep update objects (SysDeletedObjects40)" on MSDN.

Monday, August 20, 2007

Import setup of table collections

If you have an application where you want make use of table collections, you'll have to make an extensive effort to analyze which tables to include. During this analysis phase, you probably work with some sort of list of table names in a spreadsheet.

So how do you get from the list to setting up the table collection? Well, you open the AOT and add each and every table name manually to the table collection. I went through this tedious process today with a customer and when I got home I wrote the following small job to automate this task next time.

This is a very simple job, reading the first row of the spreadsheet, adding what is assumed to be table names to a table collection. As you can see, the job is in "proof on concept" state, so you'll have to add all the bells and whistles yourself.

static void ImportTableCollectionsFromExcel(Args _args)

// Import from Excel
SysExcelApplication excelApp;
SysExcelWorkbooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
FileName fileName;
Counter row;

// Create the new treenodes
TreeNodeName tableCollectionName = 'ImportedCollection2';
TreeNode treeNode;
TreeNodePath treeNodePath = #TableCollectionsPath+'\\'+tableCollectionName;
TreeNodeName treeNodeName;


// Create the table collection if it doesn't exist
treeNode = TreeNode::findNode(treeNodePath);

if (!treeNode)
treeNode = TreeNode::findNode(#TableCollectionsPath).AOTadd(tableCollectionName);

excelApp = SysExcelApplication::construct();

fileName = excelApp.getOpenFileName();

workBooks = excelApp.workbooks();;
workSheets = excelApp.worksheets();

workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();

row = 1;
cell = cells.item(1,1);

while (cell.value().bStr() != '')
treeNodeName = cell.value().bStr();

treeNode = TreeNode::findNode(treeNodePath+'\\'+treeNodeName);

if (!treeNode)
if (treeNode::findNode(#TablesPath+'\\'+treeNodeName))
info (treeNodeName);
treeNode = TreeNode::findNode(treeNodePath).AOTadd(treeNodeName);

cell = cells.item(row,1);

treeNode = TreeNode::findNode(treeNodePath);

Tuesday, August 7, 2007

Database normalization basics

This is a very good (short) explanation of database normalization:

Best Practice for AX solutions is to use the third normal form.

Delete an application object from X++

Sometimes you can just not access an element in the AOT without AX crashing in the attempt.

The first thing you can try when that happens is simply to stop the AOS, delete the Application Object Index file (axapd.aoi) and re-start the AOS to rebuild the index file.

If that doesn't work, you could try to delete the element from X++. Here's an example for a job to do that:

static void Job1(Args _args)
TreeNode treeNode;
TreeNode treeNodeTable; ;
treeNode = TreeNode::findNode(#TablesPath);
treeNodeTable = treeNode.AOTfindChild('Table1');

A third option would be to export everything from the layer where you have the problem. Remember to export with ID's. Stop the AOS, delete the the Application Object Data file (ax.aod), re-start the AOS and re-import the exported layer. This third option is of course only applicable for layers you have development access to.