Previous Page: 11 of 16 Next

Oct 19, 2011 - Computed View Columns in AX 2012

One of the new exciting features in AX 2012 which has not received much attention in my opinion, is the possibility of adding computed columns to your views. In case you are unfamiliar with views, views are basically queries that are stored in the database, and the results of which can be accessed as if they were tables themselves. You can check Wikipedia and MSDN.

The basic premise of queries in AX is that the data model matches exactly what is in the AOT. You model a table in the AOT by adding fields, and then you automatically get a table object available in your code with properties representing those fields. Doing computations is impossible, however, since those technically add a virtual column to your SQL result set. This is very apparent if you think about aggregate functions. You can use aggregate functions in AX such as count(), max(), min() etc, but for this to work with the table buffer model, you have to perform the aggregate on a field of the return type you want. It basically re-uses an existing field on your model to be able to present the data back to you.

If you think about views strictly in a SQL sense, aggregates and calculations in virtual columns are a common thing. But they were impossible before AX 2012. When you query a table in AX, your result set consists of your table’s buffer object, with no properties for dynamics fields anywhere. With views however, you are modeling the query up front, so you know exactly what new columns will be added by your calculations. In fact, you have to manually add new columns to the view and then map them to fields from your query, so why not map them to code directly? Well, that’s exactly what we can do! And since those calculated columns are part of the resultset from SQL, you can sort, group, and filter on those computed columns!

Now, a few things to understand. One may think these computed column methods will behave like display methods, on the SQL side. But that is NOT the case. The methods you create for computed columns return a string, which contains the SQL statements that will be added as columns to your view’s query definition. So in that respect, they definitely do not behave like a SQL server-side display method. They are pieces of code that will be executed when AX synchronizes your view to the SQL database, using the output string of the methods and embeds them into the SQL view query. So, we’ll start by adding a new View on the SalesLine table. We’ll call it SalesLineView, and we just add the SalesLine table as a datasource.

So next, let’s try to add a simple computed column that returns the multiplication of the SalesPrice and the SalesQty columns. To start, we need to add a a new method, with modifiers static and server, which returns a str.

static server str NetAmount()
{
    return '';
}

</code> Alright. So now, to build up the string return value for our view’s computed column, we will use the class SysComputedColumn and its static methods. You’ll notice there’s a lot of them, but let’s start with our easy example of multiplying two columns. First, we’ll call SysComputedColumn::multiply which takes two expressions. Expression means this could now in its turn do again multiple calls to SysComputedColumn to create other calculations, etc. If you’ve done any type of calculated columns in Excel for example, you’ll see where this is going. Now, for each of the two expressions we want to multiply, all we want to get is a field from the datasource. For that, SysComputedColumn has a static method “returnField”, which takes the name of the view itself, the name of the datasource, and the name of the field on that datasource. This of course implies it supports multiple datasources, which it does. In any case, according to best practice, we won’t just hardcode the names in here, but use the precompiler *str methods where possible. Unfortunately there is no way of validate the name of the datasource, but we’ll just validate it’s at least a valid identifier. As for the view, tableStr() validates view names as well. The result will look like this (the code lines are long so I tried to put new lines and indentations in to make it more clear):

public static server str NetAmount()
{
    return SysComputedColumn::multiply(
        SysComputedColumn::returnField(
            tableStr(SalesLineView),
            identifierStr(SalesLine),
            fieldStr(SalesLine, SalesPrice)),
        SysComputedColumn::returnField(
            tableStr(SalesLineView),
            identifierStr(SalesLine),
            fieldStr(SalesLine, SalesQty)));
}

</code> Ok, so now that we have the method, let’s add it as a column on the view. Right-click on the view’s “Fields” node and select “Real Computed Column” to indicate this will be a column of type real (since price and qty are real).

On the properties of the new column, change the name to NetAmount (or whatever you want, it does not necessarily have to match the name of the method), and in the “ViewMethod” select the NetAmount method. If you new method doesn’t show up here, make sure it has been saved, and make sure your method is declared static server and returns a str type…

Now, as soon as you save this AX will synchronize your view to the database. So if you open your SQL manager and look at your view there, you should see the following in its design query:

That looks great. If you had problems synchronizing or saving your view, make sure you are not referring to the wrong datasource name, or have some type of type mismatch going on! (you will get the “Synchronize database” dialog which contains the SQL error that was generated… (click on the “Errors” tab to see the error) So remember that if your method compiles, that does not necessarily mean your view can be created without error! So using the *str() (tableStr, fieldStr, identifierStr, etc) precompiler functions will help you with that as much as possible.

Ok, how about something a bit more fancy? As you probably know, enums are stored as integers in the database. Why not “compute” them to the AOT definition name? Let’s use the SalesStatus field for that! I’ll explain what I’m doing, but you’ll have to figure out the details yourself, code example is below.

So, first, we iterate over all the enum values of the SalesStatus enum. to do this, we instantiate the DictEnum class and pass it the enum id of the SalesStatus enum. Next, we iterate the enum’s values (remember AX starts at 1, not 0!), and we fill up a Map class with two strings: as a key, the enum value we want to translate from (the enum’s integer value) and the value we want to translate to (the enum’s name in the AOT, which is the “symbol”). Finally, we call the SysComputedColumn::switch statement. Again we need to specify the name of the view and the name of the datasource. We also pass in our map, which will be used for all the “case” statements in the switch, and finally a default value, for which I return “[unknown value]”.

public static server str SalesStatusName()
{
    DictEnum dictEnum = new DictEnum(enumNum(SalesStatus));
    int enumIdx;
    Map valuesMap = new Map(Types::String, Types::String);

    for (enumIdx = 1; enumIdx <= dictEnum.values(); enumIdx++)
    {
        valuesMap.insert(int2str(dictEnum.index2Value(enumIdx)), SysComputedColumn::returnLiteral(dictEnum.index2Symbol(enumIdx)));
    }

    return SysComputedColumn::switch(
        SysComputedColumn::returnField(tableStr(SalesLineView), identifierStr(SalesLine), fieldStr(SalesLine, SalesStatus)),
        valuesMap,
        SysComputedColumn::returnLiteral('[unknown value]'));
}

</code> The thing to remember here is everything needs to be translated to strings, as you can see… returnLiteral() for string value constants (such as the unknown value, or the symbol name). Once done, we again add that to our Fields definition, this time selecting “String Computed Column”: <div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div>If we go into SQL, this is what our view’s design now looks like: <div class="separator" style="clear: both; text-align: center;"></div> In AX you can use the table browser on the view to check the results (or you can look at the view’s output in SQL). One of the reasons I opted to use “Symbol” (the AX enum name) is because of course there are no labels in SQL. So I’d rather use the developer name for the enum names. Anyway, those are details that have nothing to do with the exercise at hand…

So, this was a good start on the computed columns. There is a lot more to explore on the SysComputedColumn, and there’s always the possibility to add your own methods to this class to generate your own SQL query strings. Since this method is executed when the View is synchronized, it’s not as prone to SQL injections (since it doesn’t deal with user input directly), but depending on how you generate the query str, you can end up with a bad query in your SQL view. So developer beware!

  Read more...

Oct 14, 2011 - Client Access Log and Binary Data - Weekend Reading

Wanted to point out these two great AX 2012 blog posts from late this afternoon.

First on is from the Dynamics AX Performance team, and talks about the client access log in AX 2012. If you want to log what users are doing so you can trace issues, this is your blog post to read over the weekend!

The other post is from Martin Dráb, about using binary data in AX 2012. What I loved about this one is loading the AX image into a WPF BitmapImage. Cool stuff, read here.

Got some great AX 2012 code walkthroughs coming up next week. Get your RSS feed setup or follow me on Twitter!

  Read more...

Oct 14, 2011 - Query Object Datasource Linking

This is based on a question asked on the Microsoft Dynamics AX Community forums, you can find the original post here. I’ve been asked the question of adding datasources to a query object and linking them together a lot. The main issue exists around trying to use the .addLink() method on the datasource and how to avoid AX doing automatic linking.

First off, when I say automatic linking, this is not to be confused with DynaLink, which is what forms use to stay connected (when you select a new record in one form, the other form updates its query in relation to the form you selected in). What we’re talking about here is explicitly giving the relationship between two tables, using Query objects. One thing to remember is that the query object and related objects represent a data structure which is the runtime variant of the modeled queries in the AOT. That also means, rather than creating the query in code completely, you can instantiate the query object based on the modeled query in the AOT by passing it the name of a query in the AOT, like so:

Query query = new Query(queryStr(Cust));

</code>where “Cust” is the name of a query in the AOT. That also means sorting, ranges, etc behave the same on the query object as they do on the modeled query in the AOT. That also means you can use the Query node in the AOT as your guideline.

Now, when linking two datasources, you have the option of either using whatever default relationship AX comes up with (based on the modeled relations on the tables), or you can specify your own relation between the tables. To do this successfully, there is a flag “Relations” on the datasource (both in AOT and the object). Unfortunately, in AX 2009, the AOT property “Relations” has a helptext that reads: “Specify whether database relations used for data source linking are explicitly giving”. This seems to imply setting the property to “yes” means you will be explicitly giving the relation. Unfortunately, it’s the other way around. This label was luckily changed in AX 2012 and it now reads “To use the relations defined in the AOT Data Dictionary, set to Yes; otherwise No.”

So, below is a full example of linking two datasources with an explicit relation, from code. Note this code works in AX 2009 and AX 2012 both (in AX 2012 you wouldn’t necessarily need the semi-colon separating the declaration).

static void QueryExample(Args _args)
{
    Query                   query;
    QueryBuildDatasource    datasource;
    ;

    query = new Query();

    // Add SalesTable main datasource
    datasource = query.addDataSource(tableNum(SalesTable));

    // Add child datasource "SalesLine" to previously created DS
    datasource = datasource.addDataSource(tableNum(SalesLine));

    // Set the join mode
    datasource.joinMode(JoinMode::InnerJoin);

    // Indicate you don't want to use relations automatically
    datasource.relations(false);

    // Add link between parent field and child field
    datasource.addLink(fieldNum(SalesTable, SalesId),
        fieldNum(SalesLine, SalesId));

    info(query.xml());
}

</code>

Note how at the end I put the XML representation of the query in the infolog. I have found this to be helpful when troubleshooting issues with query objects, since you can see the actual definition and result of your code:

  Read more...

Oct 13, 2011 - New Whitepaper: AX 2012 Report Programming Model

This one is important enough to do a quick blog post on it. Microsoft has just released a new whitepaper on the AX 2012 report programming model. An official manual on reporting is still in the works at Microsoft, but this will give everyone who wants to create SQL reports in AX 2012 a head start.

It talks about using temp tables, best practices and bad practices, and goes through a bunch of examples and the associated code for them.

You can find the download here. As usual, this has been added to my list of AX 2012 Developer Resources.

  Read more...

Oct 11, 2011 - Forum: Advanced Display Method Querying Joined Datasources

I thought I’d share this code snippet that I posted on the Dynamics AX Community Forums today. The exact post in question is here. The details of this post apply to Dynamics AX 2009 although I have no reason to suspect the code would not work in AX 2012 as well.

The question was on the InventOnHandItem form. The requirement asks to add a display method on the grid showing values from a table that relates to the ItemID and the inventory dimensions displayed on the form. The trick here is that the InventSum is grouped by InventDim fields. So, your display method will not get an inventdim or inventsum record per se, but a grouped version of those, based on the display settings (the button Dimensions Display which you can find on a lot of forms in AX under the inventory buttons).

To open the screen for testing, go to an item with inventory (Inventory Management - Item Details) and click the “on hand” button. This is the screen we’re talking about. The grid shows mostly fields of the InventSum table, although the dimensions are showing from the InventDim table. So we’ll add a display method on the InventSum datasource and we’ll perform a new query in the display method, querying InventSum so that we can compare the result with a field already on the form.

So first, since this is to be added as a form datasource display method, and used on a grid, we need the InventSum record passed in as a parameter to the display method. Next, we need to get the dimension field values from the inventdim record to be used in a new join. Since this display method is on the InventSum, we need to get the joined inventDim record, which we can get by calling “joinChild” on the inventSum buffer.

display Qty AvailPhysical(InventSum _inventSum)
{
    InventDim       joinDim, dimValues;
    InventDimParm   dimParm;
    InventSum       localSum;

    dimValues.data(_inventSum.joinChild());
    dimParm.initFromInventDim(dimValues);

    select sum(AvailPhysical) from localSum where localSum.ItemId == _inventSum.ItemId
        #InventDimExistsJoin(localSum.InventDimId, joinDim, dimValues, dimParm);

    return localSum.AvailPhysical;
}

</code>

As you can see when I test this, with all dimensions enabled I see my new columns matches the existing column:

And when I turn off all dimension display except for site and warehouse, the display method is still correct:

So the gotcha and somewhat undocumented feature here is really that we need to get the InventDim out of the _inventSum passed in (using joinChild), since we need the exactly related record, not the currently select grid record we can get from the InventDim datasource on the form.

Two more comments:

Methods such as these could turn out to be performance problems, so make sure to cache display methods where possible. Best practice check will tell you this as well, but you could have some security issues here, make sure to check security for any tables you are selecting on, and document the BP deviation!

  Read more...

Oct 6, 2011 - Valid Time State/Date Effective Framework - Part2

In the Part 1 of this article, we went through creating a new table with a valid time state key. You saw how it protects from date overlap and closes gaps automatically. In this article, we’ll see how easy it is to query the table to retrieve the valid record for a given time-frame.

First thing to know is that, AX will by default, without any special keywords, only select the records valid for the current time. So, if we select for our RateID of “DaxMusings” which we created records for in the previous article, we expect to only see one record returned. And that is what happens:

static void ValidTimeStateTest(Args _args)
{
    RateTable   rateTable;
    
    while select rateTable
        where rateTable.RateID == 'DAXMusings'
    {
        info(strFmt("%1: %2 - %3",
            rateTable.RateID,
            rateTable.ValidFrom,
            rateTable.ValidTo));
    }
}

</code>

Your infolog should only output 1 record, regardless of how many records you have in your table. Basically, the system attaches the date ranges with today’s date to the where clause of your query automatically. So how do we query for a different date than today? Using the ValidTimeState keyword:

static void ValidTimeStateTest(Args _args)
{
    RateTable   rateTable;
    date        rateDate = systemDateGet() + 1;
    
    while select validTimeState(rateDate) rateTable
        where rateTable.RateID == 'DAXMusings'
    {
        info(strFmt("%1: %2 - %3",
            rateTable.RateID,
            rateTable.ValidFrom,
            rateTable.ValidTo));
    }
}

</code>

This will still only give you 1 result in the infolog. There is one way to get multiple records from this query, and that is by quering for a date range. In our example from yesterday, we added a rate for today and one for tomorrow. So if we query for a date range between today and tomorrow, we should get both records, as such:

static void ValidTimeStateTest(Args _args)
{
    RateTable   rateTable;
    date        fromDate = systemDateGet(), toDate = systemDateGet() + 1;
    
    while select validTimeState(fromDate, toDate) rateTable
        where rateTable.RateID == 'DAXMusings'
    {
        info(strFmt("%1: %2 - %3",
            rateTable.RateID,
            rateTable.ValidFrom,
            rateTable.ValidTo));
    }
}

</code>

In these examples we’ve been using the Date field type (property on table - see previous article). The same statements will work for the UTCDateTime type, the compiler will check at compile time that the type you’re using for the validTimeSate keyword matches the setting on the table. Note that for UTCDateTime, AX will take into account the timezone of the currently logged in user.

All of these features are available in the query objects as well. By default, the query will behave the same way in that it will automatically filter on the valid time state of today’s date. Same as with the select statement, you can override this behavior with an as-of date or a date range, by setting the options on the query object:

query.ValidTimeStateAsOfDate(rateDate)

query.ValidTimeStateDateRange(fromDate, toDate)

</code>

There are similar methods for UTCDateTime type:

query.ValidTimeStateAsOfDatetime(rateDate)

query.ValidTimeStateDateTimeRange(fromDate, toDate)

</code>

So to re-write the job from earlier to use the query and queryRun objects, your code should look something like this:

static void ValidTimeStateTest(Args _args)
{
    Query       query;
    QueryRun    queryRun;
    RateTable   rateTable;
    date        fromDate = systemDateGet(), toDate = systemDateGet() + 1;

    query = new Query();
    query.addDataSource(tableNum(RateTable)).addRange(fieldNum(RateTable, RateID)).value(queryValue('DAXMusings'));

    query.validTimeStateDateRange(fromDate, toDate);

    queryRun = new QueryRun(query);
    
    if(queryRun.prompt())
    {
        while(queryRun.next())
        {
            rateTable = queryRun.getNo(1);
            info(strFmt("%1: %2 - %3",
                rateTable.RateID,
                rateTable.ValidFrom,
                rateTable.ValidTo));
        }
    }
}

</code> The query dialog that now comes up, will automatically have an extra tab after Range and Sorting, called “Date Options”.

<a href="

Here you can change the date ranges used, or flip to as-of date selection, on top of your usual filters. If you want the one active record, your job should look like this:

static void ValidTimeStateTest(Args _args)
{
    Query       query;
    QueryRun    queryRun;
    RateTable   rateTable;
    date        rateDate = systemDateGet();

    query = new Query();
    query.addDataSource(tableNum(RateTable)).addRange(fieldNum(RateTable, RateID)).value(queryValue('DAXMusings'));

    query.validTimeStateAsOfDate(rateDate);

    queryRun = new QueryRun(query);
    
    if(queryRun.prompt())
    {
        while(queryRun.next())
        {
            rateTable = queryRun.getNo(1);
            info(strFmt("%1: %2 - %3",
                rateTable.RateID,
                rateTable.ValidFrom,
                rateTable.ValidTo));
        }
    }
}

So notice how the query dialog shows both option (as-of as well as date range) so you can flip between the two, you basically provide a "default" in your query object (just like ranges, sorts etc). That's it for querying. Next article, we'll look at UI (Forms) and how they behave with the date effective framework.

  Read more...

Oct 5, 2011 - Valid Time State/Date Effective Framework - Part 1

AX 2012 features a new framework called the date effective framework, or valid time state tables. There are many easy examples of date effective data. The easy example is some sort of rate table, where rates become effective and expire at certain times. Other examples could be bills of material or revisions of items that become effective or expire at certain dates. In previous versions of AX, it was up to the developer to implement the logic for querying and validating the date ranges. AX 2012’s date effective framework takes care of the ground work for you. In this article, we’ll walk through the setup, and show you the automatic functionality that comes with it.

Date effective comes in two flavors. One uses regular date fields, the other UtcDateTime data type. Obviously, the UtcDateTime gives you more granularity to the second (and the nice timezone support that comes with UtcDatetime). For this example, we’ll just stick with an easy example using regular dates.

First, we create a new table and we’ll call it RateTable. We’ll give the table a RateID field which identifies the different rates, and a price for each rate.

On the table’s properties, we’ll set the ValidTimeStateFieldType to “Date”. This will automatically create two new date fields called “ValidFrom” and “ValidTo”.

Next, we’ll add an index on the table, containing our RateID identifier and the two date fields. The index needs to be unique, and set as an alternate key.

On the index’ property sheet, we’ll set ValidTimeStateKey to “Yes” and ValidTimeStateMode to “NoGap” (should default to NoGap when you set timestatekey to YES). The NoGap value tells the framework we do not allow gaps in the date ranges. For example, for the same RateID, NoGap will not allow one record January 1 to February 1 and a second record of March 1 to April 1, since there would be a gap between February 1 and March 1. We can easily test this once our table is set up.

That is pretty much it. Let’s open the table browser by right-clicking and selecting “Open”. Create a new record by hitting CTRL+N on your keyboard. Notice how the record defaults to ValidFrom with today’s date, and ValidTo set to “never” (if you put your cursor on the field, you’ll notice how the “never” value is actually “12/31/2154”). Give it a RateID of “DAXMusings” (yeah!) and save the record (CTRL+S).

Now, if you create another new record (CTRL+N), it will again default in the same date values. If you enter the RateID “DAXMusings” again and try to save the record (CTRL+S), you will get the error “Insert not supported with the values specified for ‘Effective’ and ‘Expiration’. New record overlaps with multiple existing records”.

So, it obviously doesn’t allow this overlap of the same dates. So, change the ValidFrom field to TOMORROW’s date, and save the record (CTRL+S).

If you click yes, you will notice your previously created record will be updated so that its ValidTo date will be changed from never to a date that connects to your new record (if you follow the example, your first record should now contain today’s date in both ValidFrom and ValidTo fields).

That was pretty easy. Stay tuned, we’ll look at how to query this table next.

  Read more...

Sep 28, 2011 - Query and New Related Objects in AX 2012

With AX 2012, some new features were added for queries. For those of you who have access to the DEV manuals, one feature is lightly explained in DEV3, the other is absent in the current development manuals. So, time for a blog post!

Before we begin, I have to point out that there is a slightly annoying trend you will notice. One, the shift away from object IDs and to installation specific IDs. It seems that some new classes in AX 2012 now use element names (classstr, tablestr, etc) instead of tablenum, classnum etc. This is a good thing, however, for objects such as queries, it is annoying that the new classes are not consistent in the use of element identification (old classes use the ID, new classes the string). Secondly, I’m not a fan of the Query classes having “build” in their names, but again, now there’s new classes without “build” in the names, and old classes with “build”. Very inconsistent and annoying. You’ll see what I mean.

So, first new feature, which is touched upon in the DEV3 manual, is the “Filter” object, QueryFilter (not QueryBuildFilter as you may expect). This is a new counterpart to QueryBuildRange. The manual states it is used for outer joins, and filters the data “at a later stage”. Unfortunate choice of words, and not very clear. To understand what’s going on here, you need to understand what happens underneath the covers on the SQL level.

Let’s look at the following scenario. We have a customer table, CustTable, and a sales order table, SalesTable, which has a foreign key relationship to the CustTable based on CustAccount. Let’s say we want to retrieve a list of customers, and optionally any sales orders associated with each customer. To accomplish this, one would use an outer join. In SQL, this would translate as follows:

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

</code> So far so good. Now let’s say we want to show all customers, and show all sales orders associated with each customer, but ONLY the orders with currency EUR… In SQL, this gives us TWO options:

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        AND SALESTABLE.CURRENCYCODE = 'EUR'

</code> or

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        WHERE SALESTABLE.CURRENCYCODE = 'EUR'

</code> So what’s the difference? In the first option, we use AND, which means the currencycode is part of the JOIN ON statement filtering the SALESTABLE. In the second option, using the WHERE keyword, the currencycode is part of the query’s selection criteria… so what’s the difference? If we filter the SALESTABLE using the ON clause, the CUSTTABLE will still show up, even if no SALESTABLEs with currency EUR exist, and it will just filter the SALESTABLE records. However, using a WHERE clause, we filter the complete resultset, which means no CUSTTABLE will be returned if there are no SALESTABLE records exist with EUR as the currency.

That is exactly the difference between QueryBuildRange and QueryFilter when used on an outer join. The QueryBuildRange will go in the ON clause, whereas QueryFilter will go in the WHERE clause. The following job illustrates this, feel free to uncomment the range and comment the filter, and vice versa, and test the results for yourself.

static void QueryRangeFilter(Args _args)
{
    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryFilter             filter;
    QueryRun                queryRun;
    int                     counter = 0, totalCounter = 0;
    
    query = new Query();
    datasource = query.addDataSource(tableNum(CustTable));
    datasource = datasource.addDataSource(tableNum(SalesTable));
    datasource.joinMode(JoinMode::OuterJoin);
    datasource.relations(true);
    datasource.addLink(fieldNum(CustTable, AccountNum),
            fieldNum(SalesTable, CustAccount));
    filter = query.addQueryFilter(datasource,
            fieldStr(SalesTable, CurrencyCode));
    filter.value(SysQuery::value('EUR'));
    //range = datasource.addRange(fieldNum(SalesTable, CurrencyCode));
    //range.value(SysQuery::value('EUR'));
    
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        totalCounter++;
        if (queryRun.changed(tableNum(CustTable)))
            counter++;
    }
    
    info(strFmt("Customer Counter: %1", counter));
    info(strFmt("Total result Counter: %1", totalCounter));
}

</code> So, I can hear you thinking “Why is this useful? I could just use an inner join!”. Good catch! One of the main reasons this was introduced is for the user interface. Some screens use outer joins, which works fine. However, when a user filters on fields on the form, the result may not be what that user expects. With a queryBuildRange (as in AX 2009), the query would only filter the joined datasource. So now some fields are showing as empty and read-only (because there is no actual record since it was filtered), but some fields are still showing up (the parent datasource). In this situation, using QueryFilter makes sense. And in fact, AX by default now uses QueryFilter on Forms for any filters a user adds.

Next, the feature that is not mentioned in the DEV books: QueryHavingFilter. For those of you familiar with SQL statements, you are probably aware of the HAVING statement.

Consider the following scenario. The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them. Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE
	GROUP BY CUSTGROUP
	HAVING COUNT(*) < 4

</code>In AX you can count, group by, but you’ll need to loop over the results and check the counter manually if you want to filter values out. So, in AX 2012, a new query class was added: QueryHavingFilter, that lets you do just that: <pre>static void QueryHaving(Args _args) { Query query; QueryBuildDataSource datasource; QueryBuildRange range; QueryHavingFilter havingFilter; QueryRun queryRun; int counter = 0, totalCounter = 0; CustTable custTable;

query = new Query();
datasource = query.addDataSource(tableNum(CustTable));
datasource.addSelectionField(fieldNum(CustTable, RecId),
        SelectionField::Count);
datasource.orderMode(OrderMode::GroupBy);
datasource.addGroupByField(fieldNum(CustTable, CustGroup));

havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),
        AggregateFunction::Count);
havingFilter.value('<4');

queryRun = new QueryRun(query);
while (queryRun.next())
{
    custTable = queryRun.getNo(1);
    info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));
} }

</pre></code> Note that in this code example, I added a selection field on RecId and used SelectionField::Count. This is not necessary for the having filter to work, the only reason it is in the code example is to be able to show it in the infolog (ie to have the count value available). So it is independent of the HavingFilter!

Unfortunately, the HAVING statement is not yet added to the inline SQL statements of X++. So currently the only way to use this feature is by using query objects.

  Read more...

Sep 27, 2011 - AX 2012 Testing Best Practices

Dave Froslie just announced on his blog that the whitepaper “Testing best practices for AX 2012” is now available for download on Microsoft’s download center.

It is a good read about ALM (Appliction Lifecycle Management) for AX code both in X++ and Visual Studio, and some guidelines and thoughts on testing, unit testing, gathering requirements, peer reviews, etc.

I’ve added the whitepaper download to the ALM/TFS page. I will start posting more new AX2012 content soon, including TFS articles. It’s overdue. There has been the multi-developer single AOS whitepaper, which I thought was a little disappointing, so I’ll make sure to give my thoughts and suggestions on those matters.

  Read more...

Sep 22, 2011 - AX 2012: Multiple Instances of Reporting Services on the same server

Ok, this was a huge deal in AX 2009 in my opinion. During the TAP program of AX 2012, a whitepaper was released on how to do this, but we’ve had to wait until now for Microsoft to make it public. So, yes, it is now possible to have several instances of SQL Reporting running on the same box, connecting to different AOSes. Note that you do need a separate instance of SQL reporting (not one instance connecting to different AOSes), but even that was not possible with AX 2009.

You can find the information on technet at http://technet.microsoft.com/en-us/library/hh389760.aspx.

(Source: EMEADaxSupport blog)

  Read more...

Previous Page: 11 of 16 Next