Sunday, April 26, 2009

Lookup methods tutorial + custom list lookup implementation

One of the great features available in Dynamics AX is the support of lookup forms that provide a user-friendly interface for selecting a value for a field from a list, and are highly customizable, allowing the developer great flexibility in meeting user needs.

Recently, I was posed a question of how to present a user with a list of custom-defined values in a lookup form.

The simple (and suggested) approach here is to create a new BaseEnum, containing all the specified values, and add a ComboBox control for this enumeration to the form. If the values are to be displayed conditionally (only a subset is shown each time), you can build a superset of all values in AOT, and use SysFormEnumComboBox class to limit the actual output at runtime.

But, this would not work in my case, because the values in the list were dependent on other data in the form plus the current date. Using a temporary table for this scenario seemed like an overkill (but possible). So i decided to investigate the system class SysLookup for hints on how this can be done with minimum effort. The implementation of this method is provided below.

As part of posting this implementation, I decided to also briefly describe some of the other lookup method options available to an AX developer, turning this post into a small tutorial on lookup methods.


1. The approach used most in the application is the implicit lookup based on table fields. As you can see from the control properties, DataSource and DataField specify which table field the control is based on. In my example, it is based on SalesTable.CustAccount, which uses the ExtendedDataType CustAccount, which has a relation to CustTable.AccountNum. Therefore, any time you add this field to a form, it will automatically provide a lookup button to select one of the customers.

Relations being specified on EDTs is at times confusing to people, who are used to seeing the relations between tables on the tables themselves. Technically speaking, such definitions on EDTs are incorrect. But no worries, AX supports the “correct” scenario out of the box as well. The relation to CustTable could have been specified on the table just as well.

2. 2nd most used approach is to specify the ExtendedDataType on the control directly. This is used to allow the user to specify a value to filter the records on directly from the form, for example (without the need to go to Extended Query form). It is also indirectly used on all RunBase dialogs in the system (when creating a DialogField, we specify the EDT to be used, which is transfered to the ExtendedDataType property on the corresponding control). In the tutorial, it is again the CustAccount EDT that is specified in the properties.

3. The 2 above examples both used metadata to define what lookup is to be displayed. The 3rd most used approach is relying on SysTableLookup class and builds the lookup at runtime. The code is relatively straightforward, and is described in more detail on MSDN. Using this approach, the developer can specify the query to filter the data being displayed in the lookup form. Note, that SysTableLookup only allows to have data from one table in the lookup form (+ display methods on this table). SysMultiTableLookup is an extension I have created a while ago, that adds this and other useful functionality to SysTableLookup class.

4. The remaining 2 standard approaches are rarely used in the application. Lookup based on ExtendedDataType is very similar to approach described under #2, but is executed from code at runtime. This way, you can change the lookup data displayed dynamically, based on some conditions (For example, on Ledger Journal lines, the offset account lookup shows vendors, customers, banks, etc. based on the offset account type specified on the line).

5. BaseEnum values are usually represented as a ComboBox (and, in special cases, CheckBox or RadioButton) control on forms in Dynamics AX. The lookup is provided by the kernel automatically for this type of control. But, sometimes, it is required to show the values of an enumeration in a string control – most common scenario is providing filter capabilities based on enums, where multiple values can be specified at once, similar to extended query form filters. In fact, this approach is actually used on SysQueryForm for enum fields. As you can see from the code, the lookup call is also very simple in this case.

6. This approach does not currently exist in the application, and the goals for its implementation were already described above. The lookup method code in this case looks rather straightforward, here it is:

public void lookup()
Counter yearCount;
List valueList = new List(Types::String);

for (yearCount = 0; yearCount < 5; yearCount++)
valueList.addEnd(strFmt("Year %1", year(SystemDateGet()) - yearCount));

SysLookup::lookupList(this, valueList, "List of years");

As you can see, the data displayed in this lookup depends on current system state (date) and other data (yearCount). It uses the class List to hold the values to be displayed.

You can download the project (Compatible with Axapta 3.0 – Dynamics AX 2009) with the tutorial and custom list lookup implementation by following the link below:

Note: The xpo contains changes to SysLookup class/form (only usr layer has been exported, for your convenience). Be careful when importing those, and don’t import them into the production environment.

Thursday, April 23, 2009

Be careful with join clauses when writing complex queries

Join clause evaluation is dependent on its position in the sql statement. In SQL Management Studio, if you try to specify the conditions incorrectly, you will receive the following error message:


In X++, you will not receive any compilation errors, nor any runtime errors. In complex scenarios with a lot of queries, this might go unnoticed, and will be extremely hard to weed out at a later stage, when data inconsistencies crawl in.

Take, as an example, the following job. At first glance, the 2 methods look exactly the same, and it seems as if they should work just fine. But in reality, the second method will return incorrect results, because inventSum.InventDimId will be treated as a constant (empty string as the default value) and not as a table field used in the same select statement.

QueryBuild classes have a major advantage in this situation, as you cannot easily add join clauses (links) unless adding to the child (joined) queryBuildDataSource.

static void JoinClauseWarningJob(Args _args)

void testCorrectJoin()
InventTable inventTable;
InventSum inventSum;
InventDim inventDim;

select inventTable
where inventTable.ItemId == #ItemId
join inventSum
where inventSum.ItemId == inventTable.ItemId
join inventDim
where inventDim.inventDimId == inventSum.InventDimId;

info(strfmt("Correct join where clause: %1", inventSum.AvailPhysical));

void testIncorrectJoin()
InventTable inventTable;
InventSum inventSum;
InventDim inventDim;

select inventTable
where inventTable.ItemId == #ItemId
join inventDim
where inventDim.inventDimId == inventSum.InventDimId
join inventSum
where inventSum.ItemId == inventTable.ItemId;

info(strfmt("Incorrect join where clause: %1", inventSum.AvailPhysical));
// Actually execute the code

So, the suggestion is simple: Use QueryBuild classes (or AOT queries) whenever possible, and pay attention to the order of tables and join clauses in the select statements that you write.

Wednesday, April 22, 2009

Microsoft FY09Q3 Results

Last time we did quarterly results, it was a doooozy. Immediate layoffs for 1,400 Microsofties and sometime-in-the-next-18-months layoffs for 3,600 more. Of course, the layoffs were offset somewhat by continued crazy hiring for Live Search (should we expect a work of Shakespeare to pop out of there sometime soon, too?).

What kind of questions do you want to be asked during the conference call? Some off of the top of my head:

  • So... how's that, ah, layoff... thingy.. going?
  • Which groups and products are specifically being affected by layoffs?
  • What other cost-cutting measures are in effect?
  • What are the causes in drop of revenue and what are the expectations going forward (will Microsoft give guidance this time)?
  • What bright spots are there in profits?
  • How is gaining share going?
  • The EU seems to be pulling its leg way back for a full-on kick to Microsoft's financial groin. How does the defense against EU charges look to protect shareholder money for additional EU fines?
  • What does a financial geek have to do to get a beer at The Commons?

And you know, speaking of The Commons: I trekked over there today (meh, not the sunniest day) and I have to say it's an impressive space. I walked around admiring the scope of the project, thinking "This is what Windows built. This is what Office built." I then reflected on the irony that it's Mr. Robbie Bach's Entertainment and Devices moving into the new campus with The Commons. Windows and Office funded this extravagant place for the folks who managed to burn through $8,000,000,000USD+ on the Xbox, be shown how it's done right from Nintendo with the Wii, dash the Zune against the juggernaut iPod, and have the iPhone drop-kick WinMobile to Mars.

Microsoft Senior Leadership Team is rewarding something here moving these people into such a great place, but it's not anything that I could make sense of while I wandered the new campus...

I'll update this post later with commentary about the quarterly results. In the meantime, some of my favorite places to track insights and opinions on MSFT quarterly results:

Update: closing the loop here a little bit later than I wanted (sorry, I was bounced off the grid for a while):

Wow: have a plunge in profits and get rewarded by your stock shooting up 10%+ in one day! Sweet! And by "Sweet" I mean none of this makes a lick of sense except to look at an article like Cost-cutting saves Microsoft stock after rough 3Q and realize that the market is supposedly rewarding the stock price and recognizing appreciation for the reduction in overhead and expenditures.

So now, we must have a plan to have constant announcements about reduction of expenditures.

Announcement #1: No more Company Picnic. Ever. Next?

What would be on your short list of things to cut back on? MGX? The Company Meeting? Beer at morale events? Soda? The Company Store? Whole product groups? Your group? Yeah, I don't know how often that last one makes the list. Though I have friends who have sniffed the way the FY10 wind is blowing and are getting the hell out of groups that have spent more time talking about what they are going to do than actually doing anything or - get this - shipping something to actual customers. You know, the type of groups that make Yahoo's Carol Bartz slip in the F-bomb.

I'm surprised to learn from Ms. Fried's Company Picnic article above that The Company Meeting is still on. As much as I love the Company Meeting, it was totally dead and gone to me in my mind. Talk about the most challenging Company Meeting ever. Yes, we'll have Win7 and coming in close Office 14, along with other emerging products. But how in the world to you manage to pull off a great Company Meeting within our current environment? You have to take the big issues head-on, and part of that will be looking at the upcoming MSPoll numbers and actually sharing with Microsofties who they hell were let go as part of the layoff. And why.

The Company Picnic boggled my mind just looking at the logistical nightmare it had turned into. Tell you what: if we reduce the company size back down to something reasonable, we should bring it back. But for now, I'll be happy with my group renting space for a family morale team event at Vasa Park.

CRF: unmoderated comment thread: Microsoft FY09Q3 Results.

Wednesday, April 15, 2009

Spitfire Backfire, Live Labs, and Not My 1,400

Here's a quick post before next Thursday's quarterly results. Given the layoffs in the last quarterly results I know there is some increased anxiety about any additional cost cutting Mr. Ballmer might be ready to unleash, like more staff reductions or, you know, "Hey congratulations on entering RC and all. Now... about that next release and, well, you specifically..."

Mr. Joseph Tartakoff over at paidContent has a post around further reductions: Microsoft Still On Track To Cut Another 3,600 Jobs.

Contrition time for me: last post I shared how amazed I was that everyone in the 1,400 that I knew or that were in my extended network got rehired. Pretty much the feedback I heard here and on Twitter was, "Wow, you're one lousy sample set," because a number of you are either in the non-presently-Microsoftie 1,400 or know plenty of people in that pool.

I wish I could blame it on drinking too much at Spitfire but, (1) it wasn't opened yet, and (2) it will never be opened thanks to the brazenly stupid idea to have it planned and ready to go for what seems like the better part of a year and then three days before it was to open, Microsoft yanks the rug out from under Spitfire and thirsty Microsoftie patrons.

You know, if upfront the Senior Leadership Team had decided that a bar was an interesting idea but didn't make sense for Microsoft I would have just shrugged and scooted over to a Mustard Seed, had I even heard of the idea. But to have this set up for so long with curious expectations building in Redmond and then to have a last minute revelation and cancelling Spitfire really calls into question basic judgment and execution abilities.

So-- what, what's that? Hmm, checking in over at the Bring Spitfire back to Microsoft Facebook group ( it appears Spitfire is on track to open at the end of the month? From Jonathan Sposato:

thx for everyone's support! OK there's GOOD NEWS today; the spitfire has reached an agreement in principal with msft to open on the west commons campus later this month : ) over the last few days, both parties re-engaged and worked creatively to find a way to deliver on a great spitfire experience on campus.

('scuse me while I tweet that.) Great! Allow me to raise a glass. Now I'm vaguely curious to know about all the soap-opera court-intrigue going on behind the scenes here. And I can cancel my idea for a BYOB + Towel Commons protest.

Update: well, not so fast, you twittering fool. Looks like the compromise is pretty extreme, in that drinks will only be served for scheduled special events. Hmm. More by Mr. Todd Bishop here: Microsoft revives Spitfire pub under compromise arrangement.

One last thing: Live Labs recently restructured, with some projects going forward and others getting the axe and the researchers being repurposed into groups like Mobile and Search. I've only had superficial dealings with Live Labs folks and I found them very refreshing compared to other teams in research I had planned with: a sort of practicality that was uncommon. To add a few more words here, we'll end with a special guest writer reflection on the recent Live Labs happenings:

Microsoft (NSDQ: MSFT) is downsizing its high-profile Live Labs group, which was created three years ago to speed up innovation in the company’s online business, has learned. -- Joseph Tartakoff, Microsoft breaks up its Live Labs group, 2009

Microsoft describes it as a "restructuring". About half of the team will be moved to various product groups. The remaining half, still run by former Overture/Yahoo star Gary Flake, will focus on search, data organization and user experience aspects. There's more information in articles by Mary Jo Foley on ZD Net, Ina Fried on CNET, and Gavin Clarke in The Register.

Contrary to recent whispers and tweets, we are not shutting down, disbanding, dismantling, or anything of the sort. In the coming weeks and months we'll bring you updated developer tools, new ways to use Seadragon, and much more. Going forward, we intend to focus on a smaller number of projects relative to what we've done in the past, but invest in them at a much bigger scale. -- What's next for Live Labs, on the Live Labs blog

Mary Jo comments that "Microsoft execs often touted Live Labs as proof that Microsoft is an innovator, not just a follower," and there's no question that the acquisition of Seadragon and merging it with Microsoft Research technologies has a chance to be transformational. Photosynth is an encouraging start, and there's a lot more to build on. A tighter focus and bigger bets could be a good thing here. On the other hand, Live Labs was also supposed to be a company-wide (or at least Windows Live/MSN-wide) innovation center. Gary's 2006 Live Labs Manifesto and coverage at the time like Ina Fried's captures the original vision: bridging the gap between research and the product groups and revitalizing how software is developed. This was always going to be a tough sell, with senior executives like Steven Sinfosky firmly against the idea of a separate innovation group outside of their control. The restructuring seems to throw in the towel on this front. And Microsoft spokeswoman's Stacy Drake McCredy's comments in are somewhat alarming:

Economic conditions are imposing constraints that challenge the original Live Labs model by diminishing the group’s ability to transfer innovations to business groups who’re understandably giving priority to “needs” vs. “opportunities.”

Well yeah, it is understandable. But what this is saying that Microsoft product groups aren't in a situation where they can take advantage of opportunities. It's really hard to see that as a recipe for long-term success.

Administrivia: The Cutting Room Floor is unmoderated and folks have been pleading for me to shut down the red-hot discussions over there. I moderate here - when I can manage - to keep things on track. If the CRF can be the crazy diatribe magnet and that makes moderation here all that more easy: fan-damn-tastic.

CRF: Unmoderated thread for "Spitfire Backfire, Live Labs, and Not My 1,400"