Thursday, December 30, 2010

How to read records from different company accounts with one select statement

A Dynamics Ax installation can hold information for different company accounts, all in one database. If you wanna share information between different company accounts, you can use Table Collections and Virtual Companies. Maybe you can use the InterCompany module of Ax, in order to automate the information exchange between different company accounts in your setup.

But even if the data is not shared and you don't use InterCompany, you can retrieve data from different company accounts with a simple select statement. In Ax 2009, you can use the crossCompany keyword for this.
All you have to do is add this keyword to your select statement.
Code sample 1: Select records from all company accounts in the database.

static void CrossCompanyTest(Args _args)
{  CustTable CustTable;

while select crossCompany CustTable
order by AccountNum asc
info(strfmt('%3 - %1 %2',CustTable.AccountNum,CustTable.Name,CustTable.dataAreaId));
In code sample 2, we limit the company accounts we want to read from.
For this, we can add a container after the crossCompany keyword, that limits the company accounts. The container holds the different wanted company identifiers.

static void CrossCompanyTest(Args _args)
{  CustTable CustTable;
container companyAccountcontainer = ['ca1','ca2'];
while select crossCompany : companyAccountcontainer CustTable
order by AccountNum asc
info(strfmt('%3 - %1 %2',CustTable.AccountNum,CustTable.Name,CustTable.dataAreaId));

Note that you cannot use the crossCompany keyword with data modification commands like insert_recordset or delete_from. But with a little workaround and the help of the changeCompany statement, you can still use the crossCompany keyword. Together, they can perform the data updates. More of that later on...

Happy New Year!

Hi there,

A Happy New Year to all my blog readers!
May 2011 bring you, your family and your friends good health and prosperity. I hope all your wishes may come true in this new year.
Let's make it a year to remember!

Lots of new Ax stuff coming up, with the Technical Conference just ahead of us and a new release of Dynamics Ax planned later in 2011. You can expect more blog posts as well over here, so stay tuned!

Sunday, December 26, 2010

How to read group membership information from Active Directory

Last post I talked about reading from Active Directory. This post takes it a little further. We gonna read the user list for a specific group from a given domain.

With this group membership list, you can for example

  • add new users to Dynamics Ax according to an AD group (called 'Ax Users' fe)

  • add users from the Administrators group (AD) to the 'Admin' group in Dynamics Ax

Plenty of applications, for which you can use following job as a base. Again we'll use CLR Interop with the System.DirectoryServices namespace.

static void ReadMoreFromAD(Args _args)

{ System.DirectoryServices.DirectorySearcher DirectorySearcher;

System.DirectoryServices.SearchScope SearchScope;

System.DirectoryServices.DirectoryEntry DirectoryEntry;

System.DirectoryServices.SearchResultCollection SearchResultCollection;

System.DirectoryServices.SearchResult SearchResult;

System.DirectoryServices.PropertyCollection PropertyCollection;

System.DirectoryServices.PropertyValueCollection PropertyValueCollection;

str networkDomain="yourdomainnamehere";

str prefix = 'LDAP://';

int totalCount;

int counter;

str groupName="Administrators";

str groupCrit;

int usercount;

int ucount;

str userinfo;




DirectoryEntry = new System.DirectoryServices.DirectoryEntry(prefix + networkDomain);

SearchScope =CLRInterop::parseClrEnum('System.DirectoryServices.SearchScope', 'Subtree');

DirectorySearcher = new System.DirectoryServices.DirectorySearcher(DirectoryEntry);


groupCrit = strfmt('(samaccountname=%1)', groupName) ;

DirectorySearcher.set_Filter(strfmt('(&(objectClass=group)%1)', groupCrit));

SearchResultCollection = DirectorySearcher.FindAll();

totalCount = SearchResultCollection.get_Count();

for (counter=0; counter < totalcount; counter++)
SearchResult = SearchResultCollection.get_Item(counter);
DirectoryEntry = SearchResult.GetDirectoryEntry();
if (DirectoryEntry)
PropertyCollection = DirectoryEntry.get_Properties();
if (PropertyCollection)
PropertyValueCollection = propertyCollection.get_Item('member');
usercount = PropertyValueCollection.get_Count();

for (ucount=0; ucount < usercount; ucount++)
userinfo = PropertyValueCollection.get_Item(ucount);


} catch (Exception::CLRError)
error("Error reading AD");

Thursday, December 23, 2010

How to read in Active Directory

Dynamics Ax relies on Active Directory for user authentication. And thanx to CLR Interop, you too can use Active Directory and all it's objects and properties from within Ax. You can use AD for what it is designed for: a central storage location for application data.

But how does one get to read information from the AD? In following code snippet, I'll show you how to collect a list of all users from a specific domain, with some basic information about those users.
For this, we'll use the System.DirectoryServices namespace, an easy way of getting access to Active Directory from managed code.

In order for your code to work, don't forget to edit the networkDomain variable!

static void ReadFromAD(Args _args)
System.DirectoryServices.DirectorySearcher DirectorySearcher;
System.DirectoryServices.SearchScope SearchScope;
System.DirectoryServices.DirectoryEntry DirectoryEntry;

System.DirectoryServices.SearchResultCollection SearchResultCollection;
System.DirectoryServices.SearchResult SearchResult;

System.DirectoryServices.PropertyCollection PropertyCollection;
System.DirectoryServices.PropertyValueCollection PropertyValueCollection;

str networkDomain="";
str prefix = 'LDAP://';

int totalCount;
int counter;

str mysamaccountname;
str myusername;

DirectoryEntry = new System.DirectoryServices.DirectoryEntry(prefix + networkDomain);
SearchScope = CLRInterop::parseClrEnum('System.DirectoryServices.SearchScope', 'Subtree');

DirectorySearcher = new System.DirectoryServices.DirectorySearcher(DirectoryEntry);

SearchResultCollection = DirectorySearcher.FindAll();

totalCount = SearchResultCollection.get_Count();
for (counter=0; counter < totalcount; counter++)
SearchResult = SearchResultCollection.get_Item(counter);
DirectoryEntry = SearchResult.GetDirectoryEntry();

if (DirectoryEntry)
PropertyCollection = DirectoryEntry.get_Properties();

if (PropertyCollection)
PropertyValueCollection = PropertyCollection.get_Item('samaccountname');

PropertyValueCollection = PropertyCollection.get_Item('name');

info(strfmt('%1 - %2',mysamaccountname,myusername));

catch (Exception::CLRError)
error("Error reading AD");


Wednesday, November 3, 2010

Ax Trivia - The human factor

Anno 2010, computer code is still written by humans. And though by default you want to take the human factor out of it (think of the sporadic human error), there always stays a bit human-like element in the code, even with all the best practices checking going on.

Thank God those coders have a sense of humor as well, as you can see in this post at GotDAX.

But there are also typos or simple spelling errors in the code. Examples? (From Ax 2009 SP1).

In the Shop Floor module, there exists a class JmgAbcensPreRegistre. It should be absence.
In class LedgerYearAccountDiskBel, there is a variable called transactionsWritenToFile. Written is the correct syntax.
In report SalesPackingSlip, there is a reportcontrol called invoiceLable.

Nitpicking, that's what it is. Some of these errors go years back, which proves renaming isn't always the easiest option. If it ain't broke, don't fix it.

Another trace of humans involved in coding, is the comments you find in the code. They range from type 'obvious' to 'funny'. Again, some examples:

Class LedgerAccrualTrans_Calendar
// so it won't loop endlessly!

Or class LedgerGeneralJournalService\find, which contains some tips from the programmer:

// Performance Gain:
// Another implementation could get all the daily journals that meet the criteria in one statement
// this.findList(_queryCriteria, ledgerGeneralJournal). Then, get another set with all the blocked
// daily journals in one statement. Then filter the result set set in memory. This implementation
// would be faster because we would not make so many database hits.

The same class, method read

// We need to verfiy a few properties before returning the journal.


// We are not passing the user group to this message by design because we do not want to
// leak that information. At this point in the development cycle, I can not create a new label.

Class SysExpression\buildXmlDocument

// ax did not like passing the static method call to the clr
// so we load the values into local ax types first

Class SysExpression\buildXPathDocument

// Prime the pump

(had to look that one up: to do something in order to make something succeed, especially to spend money)

Class SysLabelFind\doFormEditControl

tmpTreeNode = tmpTreeNode.AOTparent(); // so walk up the tree

And if you ain't got the time to complete the job, remind yourself (or fellow coder) to finish the job later:
Class SysLabelFind\doFormGroupControl

// TO DO find label of datagroup

I'm pretty sure there are a lot more geeky comments hidden in the code, so if you know any, share them in the comments!

Tuesday, October 19, 2010

How to link an Ax user id to an employee

In different areas in Ax, it’s not so much the user id that is needed, but the employee information. In order not to constantly re-identify yourself in the system, you can link both.

How can a user id be linked to an employee?
Make sure you have created both an employee and a user. Now you are ready to associate them with one another. Go to the Administration - User form and select the desired user. Now press the button User relations. Ax will automatically create a new record, if no link already exists.

Go to the second tab page and under Employee, select the wanted Employee.
You have now successfully linked your Dynamics Ax user to an employee.

Linking a Ax user can either be internal (employee) or external (customer, vendor, business relation).
Same method applies for linking with external contacts.
If desired, you can use a wizard to accomplish the above task as well.

Wednesday, October 13, 2010

How to prevent the error 'Division by zero'

Since the early days of programming, the dreaded 'Division by zero' error exists.

And there is no exception in Dynamics Ax.

So if you're gonna write code like A = B /C, you better make sure that C holds a value.
Of course you can do a check with a simple if-statement (if C then A = B/C), but there is an easier way. You can use minOne, a method that exists in the Global class.


myvalueA = myvalueB / minOne(myvalueC);

Definition of minOne:

If myvalueC holds a value (is not zero), it's value is returned. If it is zero, the value 1 is returned.
So better be safe then sorry and spare your users the error message (and execution stop).

PS: I know it has been very quite on this blog the last couple of months.
As you can see, I'm not dead. Nor am I planning on ending my blog, I've just been very busy with other stuff. Thanx for your patience.

Monday, July 5, 2010

Service Pack or Hotfix Rollup

If you monitor the Ax news on the net, you've probably read that Hotfix Rollup 5 is out.
Pretty fast after Rollup 4, no? Is it me or do these rollups get released more quickly after one another?
And when does Microsoft stop calling it a hotfix rollup, and when does it become a service pack?
The rollup for SP1 is now about 146Mb in size and carries over 350 fixes. (Also note that SP1 was already available end of 2008.)

I have to admit, applying a hotfix rollup does not sound that obtrusive or radical as installing a service pack. You prepare for it, sure. You test it, of course. But you seem to install the rollup faster then a service pack. These rollups don't have the reputation of introducing new problems either.
Maybe that's why...

Edit: Funny note on the side.
From the documentation of this hotfix rollup, the description for hotfix 2130575:

2130575 Assume that you install hotfix 977685. When you click to select the "Select all" check box in the "Create credit note" dialog box, you receive an incorrect error message. However, you do not expect to receive the incorrect error message

When do we expect to receive an incorrect error message? ;-)

Wednesday, June 30, 2010

How to hide/unhide the task pane from code

Small post, with a small job to do: How can we hide or unhide the task pane in Ax from code? (You know, that pane on the right that some love and others hate.)

This small piece of code will do the trick

static void TaskPaneSet(Args _args)
{ boolean visible=true;
new xInfo().taskPane().setVisiblity(visible);

How to translate an enumerated text

You can easily convert an enumerated text to a string value with the enum2str function. Example:


But this will get you the label in the default language. What about other languages?
The following small job will get you the translated label info for all values of a specified enum, with a specified language.

static void Enum2Label(Args _args)
{   DictEnum                dictEnum;

int                     valueIndex;
LanguageId              languageId='de';

int                     enumId=enumNum(SalesType);
str                     labelId;

dictEnum = new DictEnum(enumId);
if (dictEnum)
for (valueIndex = 0 ; valueIndex < dictEnum.values(); valueIndex++)
labelId = dictEnum.index2LabelId(valueIndex);
info(SysLabel::labelId2String2(labelId, languageId));
Result: Journal Angebot Dauerauftrag Auftrag Zur├╝ckgegebener Auftrag Abrufauftrag Artikelbedarf

Similar code as above is used in the SRS classes in Ax. They make it possible for humans to understand and interpret the different enum values, as they merely are integer values in the database and as such for third party applications.

How to create complex SQL statements in Ax

I posted a reply to a forum discussion the other day, but somehow the post never showed up in the discussion. So I'm dedicating this blog post to the issue.

Ax comes with it's own set of supported SQL commands. You can do your basic select statements, sorting, grouping, counting etc
But there are things with SQL you cannot do in Ax. Like this

select SUM ( A * B) from myTable

Of course, you can create a while select statement, performing the sum record by record. But this comes with a performance penalty.

There is an alternative. Ax allows you to connect to the database, writing your own SQL statements.
We need to setup a new database connection, assign permissions and perform our own custom SQL statement.

server static void TestSumSelect()

{   Connection      connection;
Statement       statement;

str             sql;
ResultSet       resultSet;

SqlStatementExecutePermission perm;

connection = new Connection();
sql = strfmt( "SELECT SUM(QtyOrdered * SalesPrice) FROM SALESLINE where SalesId='xyz'");

perm = new SqlStatementExecutePermission(sql);
statement = connection.createStatement();
resultSet = statement.executeQuery(sql); 



In order for this to work, the code must be executed on the server. (Encapsulate this in a class of your own.)
You can supply additional parameters (a salesid for example).

Tuesday, May 25, 2010

How to create a column based report

Like with my previous post about rotating text on a report (find out here how to place text vertically on a report), there are several ways on how to accomplish this. And what is it that we are trying to accomplish? A report with a true column setup. So first column 1 is filled, top to bottom, then column 2. And then over to the next page.

So like this:

And not like this:

For this, we need 2 some lesser used report methods, gotoYmm100 and mm100Left. More about these later.

Like earlier, we use an inventory report, with InventTable as our datasource, for demonstration purposes.

Create your basic report settings (add the datasource and setup some ranges, create a report design and add some fields in the body section). Now for the extras we need:

public class ReportRun extends ObjectRun
int curcolumn;

int mytopofpage;
int mybottomofpage;

We need a variable that holds the current column position.
And 2 variables, indicating the top and bottom of the page.

For the init method of our report:

public void init()
{ ;



We need a method to change columns, switching between column 1 and 2.
We call this method in the init method of the report, to make sure we start printing on each page at the same, correct top position.

void ChangeColumn()
} else if (curcolumn==2)

In the switching columns method, we shift from one column to the other.
In case we go to column 2, we reset our cursor print position back to the top of the page. For this, we use the gotoYmm100 method. This method allows us to set the vertical position where the next report section is printed.
Then we change the left margin of our section, in order for it to appear on the right side of the page.
When we go to column 1, we set the newPage command in order to start a new page in our report. After that, we set the print position with gotoYmm100 as well, to make sure we start on the same level for both column 1 and 2. Also the left margin is reset to the left.

One thing left, the body of our report, with the executesection.

public void executeSection()
{ ;
if(element.mm100Left() < mybottomofpage) element.ChangeColumn(); super(); }

In this part, we check where we are now with our cursor on the page. If we are near the end, we need to switch columns. We use the mm100Left method, but there is also currentYmm100.

Our result will look like this:

So item numbers counting up in column one, then column two.

Now if you look at your print preview in Ax, the output may look garbled. And if you use the built-in PDF generator, dito. But if you print the report to printer or use a third party PDF generator, all looks OK.

Monday, May 24, 2010

How to place text vertically (rotated) on your report

With a standard Ax installation and with the standard available report control properties, it's not possible to place text vertically (rotated 90 degrees) on a report. (or am I wrong? Let me know in the comments! I'll rename this post to 'An alternative methode to place text vertically on a report' ...)

Now you can have an add-on for Ax that does this for you. Or you may use a separate report generator all together. Or you can use the following solution, using tools available in standard Ax, but with some help of Microsoft technology.

What we gonna do is create a graphics object in memory, write some text, rotate it and then use it as a source for an image control in Ax. Sounds complicated? Maybe. But it's really not that difficult.

Let's assume you have a report in Ax, based on table InventTable. Your report is showing some item information, and we want the itemid as barcode on it as well.

I've put all the code together in one display method. Not best practice, but easier for me now.
Here goes:

Display container ShowBarcode()
System.Drawing.Bitmap   BarcodeBitmap;
System.Drawing.Graphics BarcodeGraphics;

int     dx=200;
int     dy=200;

str     barcodetxt;
Image   BarcodeImage;

System.Drawing.Pen                TxtPen;
System.Drawing.Brush              TxtBrush;

System.Drawing.Brush              DrawBrush;

System.Drawing.StringFormat       StringFormat;
System.Drawing.StringAlignment    StringAlignment;

System.Drawing.Font BarcodeFont = new System.Drawing.Font('BC C128 Narrow',36,System.Drawing.FontStyle::Regular);

Int64             BarcodeBitmapPtr;
BarcodeCode128    MyBarcode = BarcodeCode128::construct();

BarcodeImage = new Image();

BarcodeBitmap = new System.Drawing.Bitmap(dx,dy);
BarcodeGraphics = System.Drawing.Graphics::FromImage(BarcodeBitmap);


// clear canvas
DrawBrush = System.Drawing.Brushes::get_White();

// set barcode text
TxtBrush = System.Drawing.Brushes::get_Black();
TxtPen = new System.Drawing.Pen(TxtBrush);

// set text alignment
StringFormat = new System.Drawing.StringFormat();
StringAlignment = System.Drawing.StringAlignment::Center;

// init rotation

// draw text
BarcodeGraphics.DrawString(barcodetxt, BarcodeFont,TxtBrush, any2int(dx/2) , any2int(dy/2),StringFormat);

// transfer image to Ax

return BarcodeImage.getData();

Now make sure you have a report control, type bitmap. Set the newly created display method in the properties.

Tip: Watch out with the ResizeBitmap property. When an incorrect image ratio is used, they will reduce the readibility by scanners.
If we run our report, the result will look like this (based on demo data company):

If we would put the text in plain font, we would get something like this:
(leaving out the barcode encoding and using font Arial)

Like I said: If you know a better/easier way to rotate some text in reports, let me know in the comments!

Sunday, May 16, 2010

Bill Gates invests in cloud technology

If you are following the active dicsussion wether cloud technology will see its breakthrough in 2010, you will be interested to know that Microsoft founder Bill Gates is also investing in cloud technology. Only with a different twist.
The former chairman of Microsoft is funding research in order to actually create clouds up in the air, made up of sea water. The goal is to cool our planet, also known as geoengineering. You can read more about it over here.

Saturday, May 15, 2010

How to use barcodes in your Ax report - Part 2/2

In part 2 of the barcode posts (read part 1 here), we'll have a look at another barcode type.
We gonna see how to use barcodes of type Code 128 in your report. This is a high density barcode type, with checksum digit, supporting alphanumeric symbols. Very commonly used.

We gonna use the same example as in the previous post, an inventory report, with InventTable as the datasource.

We'll start off with our class declaration.
In this, we'll create an object for class BarcodeCode128. This class is used for encoding the barcode, checking/calculating check digit.

public class ReportRun extends ObjectRun
   BarcodeCode128 MyBarcode;
In the init method of our report, we instantiate the class.

public void init()
{  ;
   MyBarcode= BarcodeCode128::construct();


As opposed to Code 39 (see previous post), we now have to do some actual encoding. But no worries, the barcode class does this for us.
We'll create our display method in which we'll do the encoding:

Display BarcodeStr ShowBarcode()

   return MyBarCode.barcodeStr();
Remember to set the report control properties with the appropriate font, something like this:

And this is what your result looks like when you call the report.

As you can see from the other classes in the AOT named Barcode*, Ax supports barcodes of type EAN, Interleaved 2 of 5, UPC, ...

Tip: This website, a site by Russ Adams, has some real good info on barcodes.

Friday, May 14, 2010

How to use barcodes in your Ax report - Part 1/2

Barcodes have been around for a long time. And despite newer technologies, they remain popular. Deservedly!
You can use barcodes in production environments, for coding your invoices, in retail, POS, ...

The programmers of Ax have made it easy for you to use them. Fonts and classes have been provided in Ax to get you up and running with barcodes quickly. In this post I'll show you how you can use barcodes in your report.

This is post 1 of 2. In this post, we'll cover the very basics. And for that, we'll use barcodes of type Code 39. Why Code 39? Because they are commonly used, read by almost every scanner and very easy to implement.

Code 39 supports alphanumeric characters, and some special characters like - . $ / + % SPACE.
Unlike with most other type of barcodes, no real encoding is needed (no check digit either). All you really have to do is set the right font. This means you can even use this barcode type easily in for example Word!
Altough Ax supports encoding of Code 39, we are not going to use it now, for reasons of simplicity. (Encoding will be dealt with in part 2 of this post.)

Let's assume we have a report in Ax, showing all our inventory items. This reports uses InventTable as a base.
We would like the barcode to consist of the ItemId. For that purpose, we create a display method, like this:

Display str ShowBarcode()
return "*"+strupr(InventTable.ItemId)+"*";

The asterisk (*) is the start and stop character in Code 39, that's why we add it.
We add this display method to the body of our report.
Next up, we set the appropriate font for our report control.

Make sure you set the width of the report control big enough! (Make it center aligned as well.)

And that's all there is to it. Have a look at the result, as seen in our print preview:

Note that the readible text below the barcode is printed with a separate report control. It's not included with the actual barcode.

The main drawback with Code 39 is the length of the barcode. They use up quit some space on your reports, where as the available space may be limited on some occasions (small labels).

Look out for part 2 of this post, with encoding of Code 128, a more dense code.

How to let the user browse for a folder

When it comes to programming in Ax, I have to confess I'm lazy.
I reuse existing code as much as possible. I will store portions of code in static methods in a global class, so I can reuse them as the need arises.
Basically, I don't believe in re-inventing the wheel. When something has been done and it has been done good, why try duplicating it?

That's why I would like to draw your attention to some lesser known code in Ax, the browseforFolderDialog method from the WinAPI class. It's easy, flexible and powerful. (That's what I look for in good quality code.)
The code will provide the user the opportunity to supply a folder name from the file system, a common request in software.


client public static str browseForFolderDialog(
[str _description,
str _selectedPath,
boolean _showNewFolderButton])

So you can give a description to your dialog. Set a default path. And give the user the possibility to create a new folder.


str filelocation;

Of course, you can validate the user input (did the user press cancel?) with


This one line of code sure beats writing your custom dialog or a complete form with the necessary controls.

How to retrieve the location of various Ax and Windows folders


For retrieving Ax client folder information, we can use the xInfo class, with the directory method.
Here are some examples.

The bin directory of the Ax client


The Axapta client temporary folder

The include folder of the share directory


A different one, the application log directory

C:\Users\Public\Microsoft\Dynamics Ax\Log\


But there is more. Sometimes, you might have a need for a temporary file. Your Windows client can help you with this.
How to get the location of the Windows temporary folder (a personal, user specific folder, located in your profile)


Or if you like shortcuts: System.IO.Path::GetTempPath()



And you can get a temporary file name as well, so no need to create a routine that generates a random id:

(you only need to supply the folder name, and some prefix)
This will give you something like this


Not enough folder information for you? No problem. You can retrieve all your Windows folder information with the WinApi::getFolderPath method. All you need to do is to supply the right parameter, coming from the WinAPI macro.

What about the temporary folder of Internet Explorer:



The desktop of the current user:



Other macro names you can use as a parameter: CSIDL_FAVORITES (favorites), CSIDL_WINDOWS (Windows directory, alternative for WinApi::getWindowsDirectory()), CSIDL_FONTS (fonts directory of Windows), ...

Thursday, April 29, 2010

How to remove leading characters, like zeros, in a string

Ax comes with lots of functions to operate on strings. Like substr, strfind, strscan, strlen, strIns, strLTrim, ...
But it looks like there is always room for more...

This post is about the trimming of strings. Unfortunately, strLTrim (and strRTrim) aren't that flexible. You can only remove leading (or trailing) blanks with these functions, no other characters.

Say you have to remove the leading zeros in a string (an amount you get in string format, a VAT num, bank account number, ...), you are out of luck. You could write your own little function to perform this task, or... use CLRInterop.

You can fall back to the use of TrimStart from System.String. With TrimStart, you can remove all leading occurrences of a set of characters specified in the parameters.


System.String myString2Trim="0000123-456-000-789";
System.String trimChars="0";

With this, you can remove any leading characters you wish. (Think of spaces, dots, ...)
So if you want your own version of strLTrim from Ax:

System.String myString2Trim=" 123-456-000-789";
System.String trimChars=" ";

As a complementary function, there is also TrimEnd to remove any trailing occurrences of some specified characters.

(Note: Maybe you use a shortcut like int2str(str2int("001001")) to remove leading zeros from a string. That's OK, as long as you realise this has it's limitations.)

Friday, April 23, 2010

Alternative address - The value 999 is not found in the map


When a user tried to set an alternative address on a purchase order, we got following error
The value xyz is not found in the map

After some debugging, the root of the problem was located in the form AddressSelect, in the setFocusRecord method. The value that could not be found was actually a tableid.

What happened: We have some modifications on site, where we setup the delivery address of purchase orders, based on the user who creates the order.
Now this form AddressSelect is used all over Ax, and tries to customize it's behaviour based on the origin of the current address setup. This is where things went wrong.

Let's look at the code:

At first, it's checked if a specific value exists as a key in a map. That's a good thing.
But later on in the same method, a lookup in the map is performed without a check. This will cause an error if the key does not exist. What happened in our situation.
By rearranging the code in this form, we quickly got rid of the error.
Lesson learned: Looking up a non-existent key in a map will cause an error in Ax. So if you are not sure that the value exists, first do a check and act accordingly.

(Version MS Dynamics Ax 2009 SP1 - HFR3)

Monday, April 19, 2010

A different view on ERP software

I've been reading some posts about ERP software in general over at the 360° blog, a blog from Eric Kimberling of the Panorama consulting group.

They turn the spotlight over at MS Dynamics (in general, not Ax specific). With some strange, expected and not so expected conclusions.

Like this one (about MS Dynamics):
  • Leads all ERP vendors in the product’s level of employee satisfaction
  • But a bit further, we read as a tradeoff:

  • Below average executive and management satisfaction.

So if I understand this correctly, the normal users are delighted with their Dynamics solution, but their bosses not so.

There are some more contradictions in this article.
As a pro, we can read following statement for Dynamics ERP:
  • Highest predictability (or least variance) of actual ERP implementation costs of all vendors.

A good thing. The customer gets what was offered to him, moneywise.
But again, further on we read
  • Highest variance and unpredictability of actual implementation duration

As implementation takes longer then expected, costs will rise. Where does that leave us with 'Time is money'?

Microsoft scores well in another post on this blog, in the 2010 ERP Vendor Analysis report.

From this post:
  • Microsoft Dynamics delivers the fastest payback and ROI of all the major ERP vendors, followed by Infor and Epicor.

But then again, some statements that make you think. Like this one:
  • Tier I solutions (SAP, Oracle EBS, Microsoft Dynamics) are much more likely to require customization than Tier II and Tier III counterparts.

Strange. You'd expect the bigger systems to be less subject to customizing by code, as more parameterization is possible in general.

Maybe there is no budget with Tier III solutions to customize them, or maybe not even the possibility?

What's your 2p?

How to perform a lookup by code

Custom lookups are a recurring topic in Axapta discussions and blog posts. The lookup functionality in Ax is versatile, flexible and has changed over the different versions.

This post is about programming your lookup without the need of defining a separate lookup form, as this functionality isn't that well documented in Ax. We'll do this by using class SysTableLookup.

Let's look at a full code example first.

public void lookup()
SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(CustTable),this,true);

Query query;
QueryBuildDataSource queryBuildDataSource;


query = new Query();

queryBuildDataSource = query.addDataSource(tablenum(CustTable));




You can use the code above to override the lookup method of a control in a form.

Now let's have a look at the different parts in the code.

First, we create the table lookup by instantiating the sysTableLookup class.

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(CustTable),this,true);

The form control from which the code is called is included (this).

We define which fields to include in our lookup form.
The second parameter is set to true, if that's the field you wish to return from your lookup.
If you accidentally set it multiple times to true, the field where it's last set will be the lookup field, so your lookup may not work correctly.


Now we create the actual query, setting sorting fields and ranges as desired.

query = new Query();

queryBuildDataSource = query.addDataSource(tablenum(CustTable));


We pass along the parameters to our sysTableLookup...


And we're ready to go


I tend to use this functionality, when using edit methods instead of display methods in a form (where the return EDT is for example Name, and not a specific extended data type).

How to run code in the security context of another user

If you are reading this, you're probably an Ax administrator in your company. Or you have full control over your Ax system.
But that's not the case with all the users in your Ax environment (thank God for that).
Security limits normal users in their actions, sometimes so much they cannot get the job done. Sometimes exceptions are needed, in order to let a "normal" user perform some actions. Or vice versa, as an administrator you want to run code with "lower" security rights for testing. Or you need different security rights when running batch operations.

Ax is equiped with a function to allow code to run as if it's run by another user, it's called RunAs. (We're not talking about the RunAs from Windows, which allows you to run complete programs with different security rights.)

We are running Ax with our normal user account, only temporary impersonating another user's security.

Primary condition: The code is started on the server.
Seconday condition: The called function is a static one.

Take following method of a class we have created as example.

server static void MyMethod(UserId _UserId)
   RunAsPermission perm;
   perm = new RunAsPermission(_UserId);

   RunAs(_UserId, classnum(YourClassName), "YourMethodName");


Now we can call that piece of code from for example a job, like this:


That's all there is to it. By calling MyMethod, the runAs is activated and the defined class and method are activated with different rights.

If necessary, you can pass on additional parameters with the call to RunAs. You can include a container in your arguments. Like this:

RunAs(_UserId, classnum(YourClassName), "YourMethodName", [param1,param2]);
Be careful with what you program, as you can give any normal user administrator rights like this. Sometimes convenient if a user doesn't have specific table access, but sometimes simply dangerous or unwanted.

Wednesday, April 14, 2010

How to use the like operator with string comparisons

If you programmed SQL statements in Ax, you've probably used the like operator in your statement.
Like (!) this

select * from CustTable where CustTable.Name like 'A*'

Something less known about the like operator, is that you can also use it in string comparisons.

static void LikeOperator(Args _args)
{ str test;

test="Dynamics Ax";

if(test like "Dynamics*")

You can use the like operator with your well known wildcards as * and ?
So for example

test like "D?namics*"

As such, it's use is complimentary to the likes of functions as strscan and strfind.

Wednesday, March 31, 2010

How to set the language used on forms and menus

Ax is available in many languages (approx 45 I believe). It comes in Spanish, English, Polish, German, Arabic, ... thereby underlining it's global presence.

In reports, you can use any supported language, weather you bought a license key for it or not.

This policy makes senses, as your company, your customers or vendors may not speak the same language. When you exchange documents (for example an order), you need to understand its contents.

I had a post about how to change the language in a report a while back.

Another thing is the user interface, the client you do your every day work in. You can set the language used in forms and menus with the user options. You can find them under Administration - Users - User Options.

In this setting, you are limited to the languages you bought a license for.

Now you can set the language used in the user interface by code as well. And strangely enough, this way you are not limited to the set of licensed languages (this applies to MS Dynamics Ax 2009 SP1).

You can set the language like this


Play around with it and see what's available!

Tuesday, March 30, 2010

Fun and games with Ax

Every Windows OS version has its games included.

They're fun for the guys who programmed them, they are useful for the users who play them. And vice versa. (Solitaire in Windows for example helps you exercise your mouse control).

Ax comes with some games as well, showcasing some of it's programming techniques.
You'll find them hidden in the AOT under the forms node, just lookup under 'Tutorial'.

An overview:

Horserace (form tutorial_HorseRace)

A simple betting game, showcasing a progress bar and using random numbers.

Memory (form tutorial_Memory)

A very basic memory game, with buttons on a form.

Tetris (form tutorial_Tetris)

An implementation of the arcade hit Tetris. With graphics. And a timeOut implementation. And catching keystrokes with the task method.

During some extra time a couple of years ago, I extended the Tetris game in Ax. I've included some start levels in them. Feel free to download the modified version from my SkyDrive here .

The above games are included in Ax 2009. The older Ax versions had some more built-in fun, like BattleShips,

or Tic Tac Toe.

The people over at Trucos Ax, an Axapta community site in the Spanish language, even have a link up for a Space Invaders game in Ax. Cool! Maybe the gameplay isn't as smooth as you can find in the arcade hall, but this is definitely a solid effort to bring back that old spirit.

Know some more ways to have fun with Ax? Share it in the comments!

How to filter records in a form by code

The standard filter functionality in Ax forms is a neat and powerful feature.
Using this filter functionality in your code is something you'll definitely use at some point in time as a programmer.

Although it's possible to do it in a single line of code, I prefer a 3 step solution. That way it's more flexible.
Let me show you by example. We'll filter the customers records in form CustTable, only showing customers with currency USD.

Step 1: Declare a class variable
In the ClassDeclaration method of the form, define a range.

QueryBuildRange CurrencyQBR;

Step 2: Instantiate the new range.
In the init method on the datasource of the form, you assign the range to a specific field (after the super call).

public void init()

CurrencyQBR = this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency));

Step 3: In the last step, you assign a value to the range.
This is done in the executeQuery method on the same datasource of the form. Before the super call. Like this:

public void executeQuery()
{ ;



You're done! When you open the form, your customer records are filtered, you only get the customers with currencycode USD set up.

Like I said in the intro of this post, this can be done in one line of code as well.
In the init method of the form datasource, after the super call, place this code:


But this way, it's fixed. If you choose the 3 step method, you could for example use a variable in the range value. The way to go would be to place an input field on your form, get the value from it and supply it in the executeQuery method.

For example like this:

public void executeQuery()
{ ;



Just make sure the executeQuery method is executed, thus applying the desired filter (maybe be using a button on your form to activate it).
Of course it's possible to combine multiple querybuildranges.

Thursday, March 25, 2010

How to create a GUID in Ax

In my previous post I talked about the Ax cache file and how it's name is created by using a GUID. Actually GUIDs are used in several places in Ax, like AIF for example.

You can create your own GUID with Ax as well, if you have a need for one. The WinAPI class has a method for that.


static void CreateGUID(Args _args)
{ str myGUID;


This method depends on the kernel function newguid() to create a globally unique identifier.

Note that you can have the GUID with or without the { }.

Wednesday, March 24, 2010

More information about the AUC file

What does AUC stand for?

AUC is short for Application Unicode Cache.

What is the AUC file used for?

The file contains a cache. Objects of the application object tree from Ax are stored locally, so that they don't have to be read from the AOS server over and over again.
So it should speed things up a bit.

Where can you find the AUC file?

This cache file is stored together with the user profile information. And as so it's location varies according to the operating system version used.

You can find it in following folder:
Windows XP and Windows 2003
C:\Documents and Settings\%username%\Local Settings\Application Data

Windows Vista, Windows 7 and Windows 2008

Can you delete the AUC file?

Barack Obama and I agree: Yes you can!
Close your Ax client session, delete the AUC file. The AUC file gets recreated automatically next time you logon to Ax.

How is the naming of the AUC file defined?

In older Ax versions (3 and 4), the filename for the cache file is based on the AOS name, server name etc. In Ax 2009, the name is based on a GUID. The GUID used is the one you can find in the Ax table SysSqmSettings (field GlobalGuid).
As such, watch out with duplicating databases for test purposes. You don't want 2 Ax server instances using the same GUID and as such the same cache file, as this will lead to unexpected results. (Should this happen: Replace the GUID in table SysSqmSettings with an empty one. A new GUID will be created by the AOS at restart.)

Tuesday, March 23, 2010

The Cloud

You've heard about it. You've read about it.
Maybe you've worked with it. Or only had a short test drive with it.

What am I talking about? The cloud.

Or to be more precise: Microsoft's offering for the cloud. What's in?

Over at ZDNet, they do not believe in Microsoft as a serious cloud contender. A very critical voice is heard on Microsoft's cloud offering in this article, titled 'Why Microsoft really, really, hates the cloud'.
Summing it up: In 5 years Microsoft's operating income will drop by 75 %. Because it cannot adapt to the changing IT environment.

My opinion, for what it is worth: A bit over simplified reasoning in this article.

Will we see a move to cloud based applications: Absolutely. Your communications will be without doubt cloud based. Email only being one of them.
Your basic word processing, spreadsheet functions will be cloud based. I can dig that. Image, audio and video management, sure.
But your business critical applications like an ERP system? I don't think so. Partially maybe.
Small companies (-25 employees) will be interested. But anything above that?

I also do not see a reason on why Microsoft cannot adapt and change it's income pattern (that's without doubt needed). But are Cisco or IBM better positioned then Microsoft in this area then?

Microsoft is not postponing the arrival of Cloud Computing as stated in the article at ZDNet. It's just gearing up things and when and where there is money to be made with the Cloud, Microsoft will be around.
Remember the spread sheet. Lotus 1-2-3 was once number one. And the word processor, with WordPerfect. Or the internet browser.
Maybe this time around Microsoft's rival isn't another software company.

Wednesday, March 17, 2010

How to get rid of the report scaling message the lazy way

When a report doesn't fit on a page, depending on it's properties Ax will resize the report. This is a powerful and very useful feature.
Now Ax will inform you that the report has been rescaled (Report is scaled xx percent to fit to page) and this message is generally not well received by users.

Users are annoyed by the message, they get it every time they run the report, they cannot do anything about it, they have to click to close the infolog, ...

Ax has a builtin feature to suppress this scaling message. You can modify the init method of your report, and add something like this:


This is very effective and will do the job.
Only, this requires you to modify every report with these kind of messages.

A nicer way would be if we could switch it off in one place for all reports. Fortunately, this is possible as well.

Go to class SysReportRun, in the Run method, place following code before the call to super:


Now we don't have to modify each and every report and our users are happy.

Note that you can still override the settings in your report. In some reports this is done by default, like SalesInvoice and SalesConfirm.

Monday, March 15, 2010

An unknown error occurred while accessing an unnamed file

When working with the Microsoft Dynamics AX Debugger the other day, we ran into following error:

An unknown error occurred while accessing an unnamed file. So I guess Ax doesn't have a clue on what's going on, right?

The Ax client crashed, the debugger came up with the above error text. It was not possible to close the debugger the proper way, we had to kill the process.

Once we got this error, we always got it with every breakpoint that was set in the code.

We googled the web for a solution or a cause, but came up short of an answer. Badly written X++ code probably caused the crash of the client, which in term caused the debugger to respond with the error message (after all, when there is no client to connect, there's nothing to debug)

The only solution left for us was to restart the AOS service (and rewrite our custom code). After that, everything runned fine again.

How to set security on a temporary table

The security for temporary tables works exactly the same way as for "normal" tables.
Meaning you can set the security in the AOT with the table properties. Like this

Now the appropriate rights are effective for the security groups a user is member of.

But... (There had to be a but, otherways there wasn't a blog post about this subject, right?)
These temporary tables don't show up in the list when assigning rights to security groups.

Strangely enough, there are multiple temporary tables in the AOT with a security key attached to them. For example tables TmpDimTransExtract, TmpInventBalance, TmpInventAge, TmpPackMaterialFeeSum, ...

When a non administrator user has use of them, he or she will get an error like this:
(for example when you run a report based on a temporary table)

Unable to run report xyz due to access restriction in table ABC.
Object 'ReportRun' could not be created

I see 2 ways of solving this issue:

1) Remove the security key in the table properties
(Wether it makes sense to setup a security key to a temporary table in the first place is an interesting discussion.)

2) Change some code, either in the SysDictTable class or the SysSecurity class.
With this modification, you can assign rights for temporary tables to the user groups, just like you can with "normal" tables.

For the SysDictTable class, change the allowSecuritySetup method. Comment out the call to isTmp().

Alternative: For the SysSecurity class, change the expandSecurityKey method.

Make sure the AllowSecuritySetup method for SysDictTable isn't called for temporary tables.

This is a bit of a contradiction in Ax. According to the code, security is not expected to be setup for temporary tables. You cannot assign rights to user groups for them. But in the SYS layer of the AOT, there are a few temporary tables setup with security keys.

(If you've already openend the user group permissions form, you may have to restart the client after the above modifications, as the information in this form is cached.)

Tuesday, March 9, 2010

How to create a new workspace from code

One of the nicer things in Ax is the possibility to start a whole new workspace from scratch, just next to the one you are working on right now.
So when you're in the middle of something in one module in Ax and get a phone call for something completely different, this gives you the option to simply start with a clean sheet, handle the phone call and then go back to where you were with your first business.

For a user, this option can be manually activated by the keyboard combination CTRL +W.

From code, you can access this by using the xInfo class. Like this:

new xInfo().createWorkspaceWindow();

Saturday, March 6, 2010

How to set up a tax exempt text on your sales documents

I don't know about you, but in our company the accountants are more nervous then ever with all the new tax (VAT) regulations that got into effect January 2010.

We needed to update some of our legal stuff regarding VAT calculations that goes onto the sales documents, so I decided to create a (very) short checklist on how to set this up.

Step 1: Create the tax exempt code.

Go to General Ledger - Setup - Tax - Tax exempt code.

Create the necessay exempt codes. Also create any necessary language texts.

Step 2: Assign the exempt codes to tax codes in the tax groups.

Go to General Ledger - Setup - Tax - Tax groups.

Select the desired group and go to the tab page Setup.
Now for the appropriate tax codes, mark the field Tax Exempt and fill in the Tax Exempt Codes.

And that's it.
When the right tax setup is applied on for example a sales invoice, the tax exempt text will be shown in the appropriate language.

One small remark:

Maybe you have a pretty long exempt text set up ("... chapter xyz from law 123 ..."). And you notice that only a part of the text ends up on your sales documents.

Reason: A language text in Ax is bound to EDT LanguageTxtText, a Memo field. But in the code, EDT Description is used for the actual text. This EDT has only 60 characters.

Solution: Modify class TaxSpec\ClassDeclaration.


Description taxExemptDescription;


LanguageTxtText taxExemptDescription;

That will do the trick.

How to get the different parts of a real number

A real basically has 2 parts: the part before and the part behind the decimal separator. The integer part and the decimal part.
It's easy to get both parts separated, by using these functions: trunc and frac.


Truncates a real number by removing any decimal places.


trunc (123.45) equals 123


This function retrieves the decimal part of a real number.


frac (123.45) equals 0.45

Note: The help section for function trunc says that 'numbers are always rounded down to a complete integer'.
Now if you've read my previous post about rounding in Ax, you may find this remark not to be completely true. Rounding to zero is closer to the truth.


trunc ( -9.1 ) equals -9


rounddown (- 9.1 , 1) equals -10
roundzero ( -9.1 , 1) equals -9

On the other hand: rounddown ( -9.1 , -1) equals -9

Now I don't use these functions that often. But when I need them, I always have to go to the back of my head to remember their names.
So I thought I post them up here, as my online help.

How to round numbers

There is a versatile function in Ax to perform rounding operations: round.
This function rounds the first real argument to the nearest multiple of the second real argument. So plenty of possibilities, for example

round ( 1.2 , 1) equals 1
round ( 1.2 , 5) equals 0
round ( 6.4 , 5) equals 5
round ( 7.5 , 5) equals 10
round ( 1.2 , 0.5) equals 1
round ( 1.12 , 0.1) equals 1.1

If you don't want to work with the multiples of the second argument and instead just want to specify a number of decimals places to round, you can use decround.
This functions rounds the first real argument to the number of decimals specified (second argument). So for example

decround (1.2 , 0) equals 1
decround (1.23 , 0) equals 1
decround (1.23 , 1) equals 1.2
decround (1.25 , 1) equals 1.3

But the second argument can be negative as well. Like this:

decround (123, -2) equals 100

Now for rounding with a little twist: roundup.

If you want to round a number up to the next real value, you can use roundup. This function is the same as the ceiling function from other environments.
Same format as the previous round functions, needing 2 arguments.
So for example

roundup ( 1.23 , 0.1) give as result 1.3
roundup ( 123 , 5) equals 125
roundup ( 123.23 , 5) equals 125
roundup ( 123.23 , 1) equals 124

If that ain't enough, more rounding functions: rounddown and roundzero.
Rounddown rounds your real value always down to the multiple of your second argument.
While roundzero, as the function name says, rounds towards zero.
The difference you can see in the next example:

rounddown (-9 , 5) equals -10
roundzero (-9 , 5) equals -5

Saturday, February 27, 2010

Impact Analysis feature for Dynamics AX 2009

Hot from the presses: Microsoft has just released a new tool, available from PartnerSource or CustomerSource, to analyze the impact of a patch on a system.

From the official text:

Impact analysis is the process of understanding a change that is being introduced into a system, determining the consequences of the change on related subsystems, and creating possible mitigation plans to reduce risk. When you install a Microsoft Dynamics AX 2009 hotfix, impact analysis consists of understanding the changes being made to application objects in the SYP and GLP layers, analyzing how these application objects are customized in the environment, and creating test plans to test the system after you install the hotfix.

In theory, this sounds very good. Very anxious to test this out, definitely worth a try. Hope it is as good as it sounds.

With a new hotfix rollup under way (Rollup 4 for Ax 2009 is expected first half of March 2010), this could be very usefull.
We've all been in that kind of situation: Where the only solution left was to install a hotfix, but you didn't know if it didn't create more troubles on the way.
I would have expected a tool like this when Ax 2009 came out, but better late then never.

Friday, February 26, 2010

How to get a label in a different language

The tasks seems easy enough: How the get a specific label in a different language.
(Remember my post, 'How to set the language used on a report')

Now we only need to retrieve the text for one label id, in any given language. Going through the help files of Ax, you come across this class SysLabel. It contains the labelid2String method.



Only... This does not work like that. Didn't work in Ax 4.0, doesn't work in Ax 2009. You always get the label back in the language that's been setup for the current user, no matter which language you specify in the call to this function.

Luckily, there's an easy workaround, tricking Ax: Split up your label id. Like this:


Credits: With thanx to this blog for this simple but yet very effective solution.

Edit: SysDictCoder pointed out a nicer solution in the comments, by using literalstr.


Wednesday, February 24, 2010

How to get the biggest value (retrieve the greater figure)

If you ever needed the greatest value of 2 variables and wrote an if-then-else statement, there is a simpler solution. Ax has a built-in function to determine the maximum value in a range of variables, namely max.

The use: max(anytype var1,anytype var2);

Where var can be an integer, real, ...


static void MyMaxJob(Args _args)
{ int test;
int var1=200,var2=300;

test = max (var1,var2);


Now the documentation suggests this only works with 2 values (or figures), but in practice you can use more values as well. Like this:

static void MyMaxJob(Args _args)
{ int test;

test = max (100,1200,300,400,500);


This sure gives nicer code that those if-then-else statements, not?

Of course, there is a counterpart function as well for finding the smallest (or lesser) values, min.

(Minus 200, or -200, is smaller than 100. So it's not the absolute value that is taken into account!)

Friday, February 19, 2010

Internal error number 174 found in script

Another blog post title for this one could be 'How to crash your AOS server in 1 easy step'. Because that is what we'll be doing in this post!

So be warned and take care if you wanna test the code on this blog. As always, it's your responsibility.
Anyway. We experienced some AOS crashes in our environment and started to investigate the cause. We could trace the crash to some specific code. When executed, we got following error in a dialog box on the client:

Internal error number 174 found in script

And with that, the AOS service died on us.
The application event log of the AOS server reported a 'Faulting application Ax32Serv.Exe' error, with event id 1000.

The code to simulate:

static void CrashAOS(Args _args)
{ SalesLine SalesLine;
SalesId SalesId='xyz';

update_recordset SalesLine
setting LineNum=-SalesLine.LineNum
where SalesLine.SalesId==SalesId;


Looks like Ax didn't like what we tried to do with the update_recordset statement.
We ended up recoding, replacing the update_recordset statement with a select forupdate statement.
(Note: This code didn't give problems in old Ax versions, but it did in Ax 2009.)

Thursday, February 11, 2010

Recordcount and recordsize

In my last post, I talked about tables and how they can grow over time.
Now there are 2 elements that matter:
  • the number of records in a table
  • the size of a record in a table

It's fairly easy to keep track of both them, by using the SysDictTable class.

The number of records in a table? Use method recordCount

static void RecordCount(Args _args)

{ SysDictTable dictTable;

dictTable = new SysDictTable(tablenum(CustTable));

info(strfmt('Table %1 has %2 records',,int2str(dictTable.recordCount())));


The size of a record in a table? Use method recordsize

static void RecordSize(Args _args)

{ SysDictTable dictTable;

dictTable = new SysDictTable(tablenum(CustTable));

info(strfmt('One record of table %1 has this size: %2',,num2str(dictTable.recordSize(),0,0,1,0)));


The method recordsize just iterates over all the table fields, summing up their size.

Now their is a funny thing with method recordcount. This function returns a value of type integer. Now in the old days, record id's of Ax were integer values. But they reached the limitation of that, so decided to uplift the record id's to type int64. But still, the function recordcount is limited to giving back integer values, so your maximum count will be 2 147 483 647 (the value of maxint()).

There's always room for improvement, the need for an upgrade...

It's not Big, it's Large

The database of every Ax installation will grow over time. How fast? Depends on the activity.
Which modules our active? How many transactions are created? Do you have 10 invoices a day, or a 1000 a day. Many different factors influence the data growth.

You can check which tables occupy the most space in your database. Ax has an out-of-the box report to do some follow up. You can find it under Admin - Reports - Size of company accounts.

This report gives detailed on all the database tables, with a record count and an estimated size used.
The outcome may stimulate you to clean up some old data, and allows you to focus on the right direction as well. (Again, Ax has some built-in functionality for that also.)

Also, be on to lookout for not so obvious suspects responsisble for excessive growth in database volume. Like table SysTraceTableSQL. On our installation, it had grown to several gigabytes over the years, claiming way to many disk space.
This system table stores information about trace events. You can purge the old data if you want, after you've done analyzing it. (Be carefull with your timing though, as deleting a table of several gigabyte can stall your database server.)

(Oh and yes, this blog post title was lent from Lyle Lovett.)