Friday, December 14, 2007

Print report to Excel

I have a client who asked me if it was possible to print a report from AX using Excel as the output media. My first thought was, “Eeeh – no”. But this was something they could do in their old Baan system, so I was really provoked to find a solution.

Actually it turns out that AX has a framework allowing you to integrate your own media as the target for an AX report. This is covered by mainly the ReportOutputUser class hierarchy.

But for some ¤/#&%” reason you can’t really hook this up to the print job settings and get the report engine to use your classes. I hope that will be fixed for 5.0.

In the mean time you’ll have to pull some tricks. In regards to my solution let me just give you this hint; the user should not attempt to make a PDF file with XLS extension of the filename.

Apart from the tricks I had to pull it works really well.

Contact thy:data if you are interested in learning more about the solution.

Record Level Edit Security

I’ve just delivered a solution to a client allowing them to control edit permissions in forms for tables on a record by record base.

The solution is heavily inspired by the existing record level security, hence the title of this post.

The feature is almost totally generic, as it has a hook into the document cursor method on SysSetupFormRun. But to be totally bulletproof (well as bulletproof as things get in the client UI) you must include a single statement in validateWrite and validateDelete on the tables you want covered.

To minimize the negative impact on performance, I interpret the query of the table setup to be covered by this, and if it is a simple query I don’t need to actually run the query to figure out the permissions. If I do need to run the query, I have some code adding everything I need to cover the unique index of the table.

And of course the permissions for each record is cached while the form is open.

Leave a comment if you want more information.

Thursday, November 15, 2007

Control SharePoint with X++ code

I have never really seen an example where you control stuff like lists on a SharePoint site from X++ without involving EP.

I have managed to "Google" my way to a couple of samples where a custom dll acts as the middleman between AX and SharePoint. But since we now have the ClrInterOp stuff in AX 4.0, you should be able to work directly with SharePoint from X++ in my opinion. And true enough you can...

Togehter with a colleague here at thy:data we have made a few scenarios for working with a different lists in SharePoint from AX.

You could for example use such a solution in the cases where a full-blown EP would be out of proportions for the customers requirements.

Here is a little teaser, which you can run on the AX 4.0 SP1 VPC image if you have a copy of that:
static void HelloSharePoint(Args _args)
Microsoft.SharePoint.SPSite spSite; 
Microsoft.SharePoint.SPWeb  spWeb; 
spSite = new Microsoft.SharePoint.SPSite('http://ax401vpc');

spWeb = spSite.OpenWeb();

info (strFmt("Site name: %1", ClrInterOp::getAnyTypeForObject(spWeb.get_Title())));


In AX you need to add a reference to the Microsoft.SharePoint dll.

Saturday, November 3, 2007

Client:Unexpected target(1)

I have never seen this error message from AX before this week when a client of us started getting it quite frequently.

At the client site they got this error in one of the sales form letter reports. But only when there was a note in the document management and only when they used printer, printer archive og e-mail as output.

We tried to change almost every propery on the report, in particlar those related with the output from DocuRef, but nothing helped.

Well after some hours of this we finally found the bug. Another team member had implemented a change from the standard to how the documents notes were printed. In the process he had moved the line to print these in the fetch method. But he moved it to a place before the place where the print job settings are loaded from the sales form letter setup.

So lessons learned: Don't try to print anything in a report if you intend to change the print job settings later.

Friday, October 12, 2007

Print Management in 4.0 SP2

It seems like the print management (4.0 SP2) setup for external documents is totally ignored when you print an already updated document or when you print free text invoices.

That is quite annoying since for example the sales invoice by default is printed in landscape. I can’t recall ever having met a customer that did not print the invoice in portrait.

I'd love to hear from anyone having a quick fix for this bug or if anyone know if MS will fix it in SP3.

Monday, October 8, 2007

Best Practice check on labels in forms and reports

When you run a Best Practice check on forms and reports, it will check labels that end with a punctuation. These are in general not allowed, except in help texts.

There is in SysBPCheck.checkLabelBasics a list of approved exceptions to the rule. This list however, is in EN-US only. If you run BP in EN-US the label “Invoiceno.” is all right, but if you run it in for example Danish, the Danish equivalent to this label, “Fakturanum.” will give you a BP error.

So if you run a BP check in any language apart from EN-US you could end up with a lot of BP errors from SYS elements.

Friday, September 28, 2007

Fun blog post about software estimation

The posting is by the Steve McConnell himself and you can read it here.

If you don't know Steve McConnell I'll strongly suggest that you pick up a copy of his book Code Complete, Second Edition.

Thursday, September 20, 2007

New unit tests listeners

If you create a new unit test listener, you have to add a new element to the SysTestListeners enum to be able to select it from the parameters form.

Please note two facts about this enum element:
  1. The name must be the same as the listener class.
  2. The enum value must be the next consecutive number.

If this is not fullfilled, the framework fails to match the enum with the listener class.

Thursday, September 13, 2007

Rename Primary Key and AIF

Please observe that there is a problem with AIF endpoint contraints (Table AifEndpointConstraint) if you decide to change the primary key for an entity set up here.

The relation between this table and CustTable, VendTable and InventLocation is not modelled in the AOT, so the Rename Primary Key doesn't know that records in this table should be modifed and you will endup not having the Id's updated in this table.

Monday, September 10, 2007

Thursday, September 6, 2007

Debug the HTML from your web applications

Microsoft has a toolbar you can install with your Internet Explorer, that allows you to debug the HMTML generated from your web application.

This is just some of the feautures in the toolbar:

  • Explore and modify the document object model (DOM) of a Web page.
  • Locate and select specific elements on a Web page through a variety of techniques.
  • Selectively disable Internet Explorer settings.
  • View HTML object class names, ID's, and details such as link paths, tab index values, and access keys.
  • Outline tables, table cells, images, or selected tags.
  • Validate HTML, CSS, WAI, and RSS web feed links.
  • Display image dimensions, file sizes, path information, and alternate (ALT) text.
  • Immediately resize the browser window to a new resolution.
  • Selectively clear the browser cache and saved cookies. Choose from all objects or those associated with a given domain.
  • Display a fully featured design ruler to help accurately align and measure objects on your pages.
  • Find the style rules used to set specific style values on an element.
  • View the formatted and syntax colored source of HTML and CSS.

Download it here.

Monday, September 3, 2007

For the schizophrenic

I was asked today how you start AX 4.0 as another user than the user logged into Windows.

As old users of AX 1.0 – 3.0 we are used to just enter another account at the login prompt. But with the integrated login in 4.0 you are not even asked.

Well, the solution lies not in AX but in the operating system. Simply right click the shortcut you start AX with and select “Run as…” and enter the credentials of the user account you want to login with.
From Properties / Advanced you setup the credentials to use in the future when starting AX from the shortcut.

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.