Thursday, October 20, 2011

Get Infolog details from code

On the Dynamics AX Community forums, the question was asked how to get detailed information on the type, message and prefixes of messages in the infolog. The original post can be found here. This type of code usually happens in modifications that need to somehow keep a log of sorts, so that the infolog details can be retrieved from somewhere even after a user has closed the infolog screen.

Well, it all revolves around containers. The infolog class has a "copy" and a "cut" method that return the contents of the infolog, in container form. The elements in the container are containers themselves, each one corresponding to a line in the infolog. So you get the container, loop over it, and for each iteration you can grab the sub-container and extract the details like the exception type, action class, helptext, etc.

I guess a code sample says more than a thousand words:

static void InfologParse(Args _args)
{
    container   infologCon, infoline;
    Exception   exception;
    int         i;
    str         message;
    str         helpURL;
    ClassName   actionClassName;
    container   actionClassOptions;
    ;

    // Put test data in infolog
    setPrefix("First Prefix");
    error("test error");
    warning("test warning");
    
    setPrefix("One more level");
    info("infolog!");
    
    // Get the infolog data and clear the messages (cut)
    infologCon = infolog.cut();

    for(i=1; i<=conLen(infologCon); i++)
    {
        infoline = conPeek(infologCon, i);

        exception = conPeek(infoline, 1);
        message = conPeek(infoline, 2);
        helpURL = conLen(infoline) > 2 ? conPeek(infoline, 3) : '';
        if(conLen(infoline) > 3 && conPeek(infoline, 4))
        {
            actionClassName    = classId2Name(conPeek(infoline, 4));
            actionClassOptions = conPeek(infoline, 5);
        }

        info(strFmt("Type: %1; Prefix: %2; Message: %3",
            exception,
            // replace the \t by / so we can see all prefixes
            strReplace(message, '\t', '/'),
            // copy the message from the last occurance of the \t to the end
            subStr(message,
                strScan(message, '\t',
                strLen(message),
                -strLen(message)) + 1,
                strLen(message))));
    }
}


Wednesday, October 19, 2011

Consuming External Webservices in AX 2012

For those of you who have read my post on the Windows Azure App, may recall my shortcut for fixing the missing bindings and other app.config settings. I've been meaning to dig into this further and come to a solution, but for the Azure post being constrained by my promised "10-minute app" I stuck with the ugly solution. Recently I was talking with the MSDN team about AIF related articles they are wanting to do, and I brought up this issue. They pointed out they had not seen this issue and asked if I had followed the whitepaper on consuming webservices. Now, there's not necessarily a lot to it, but in looking over the whitepaper I found one tiny little thing, which makes a world of difference and solves my issue: AIFUtil class! This sparked the idea of doing a follow-up on this, and due to a late night conversation on Twitter related to this I figured I really need to get this on my blog. The issue as I'm about to explain is a .NET fact and not an AX 2012 issue per se. In fact, as you'll see, AX 2012 has a way to fix the issue.

For this code walkthrough, I will use a free online web service for a currency converter. You can find the WSDL at http://www.restfulwebservices.net/wcf/CurrencyService.svc?wsdl. In case you this link is down by the time you read this, or if you just want to try something else, check XMethods for other free available web services online.

To get started, we'll create a new Visual Studio 2010 Class Library project, which I will name DAXMusings.Proxies


Next, we add a service reference. Right-click on the References node in your solution and select "Add Service Reference". In the address field, type our service URL http://www.restfulwebservices.net/wcf/CurrencyService.svc?wsdl and click "Go". In the Namespace field, type "CurrencyService". Click OK to generate the proxies.


Besides the proxy classes being generated by Visual Studio, it also puts all the web service bindings and information in the app.config file of your project. You can open it by double clicking on the app.config in the Solution Explorer.


Now, when an application loads a config file, it looks for the application's executable name and .config at the end. So on the AX client the Ax32.exe.config gets loaded. On the server side, Ax32Serv.exe.config file. Of course, our code is in the app.config, which is not helpful, it will never be loaded.
Let's see what happens. On the project, right-click and select "Add DAXMusings.Proxies to AOT".


Next, in the properties of the project, set the "Deploy to Client" property to "Yes".


Save the project and click Build / Deploy Solution. This will build and deploy your solution to the AX client.


Next, let's open the AX client. If you still had the client open, close it first and re-open. To do a quick and dirty test on the client, let's create a new job. If not open yet, open a developer workspace using CTRL+SHIFT+W. In the AOT, right-click and select New > Job.


In the code, we'll just create an instance of the service client, and call the service:


static void Job7(Args _args)
{
    DAXMusings.Proxies.CurrencyService.CurrencyServiceClient  service;
    DAXMusings.Proxies.CurrencyService.Currency currency;
    System.Exception ex;

    try
    {
        service = new DAXMusings.Proxies.CurrencyService.CurrencyServiceClient();
        currency = service.GetConversionRate(
            DAXMusings.Proxies.CurrencyService.CurrencyCode::USD,
            DAXMusings.Proxies.CurrencyService.CurrencyCode::EUR);
    
        info(strFmt('%1', CLRInterop::getAnyTypeForObject(currency.get_Rate())));
    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();   
        info(CLRInterop::getAnyTypeForObject(ex.ToString()));
    }
}

Now, if you try to run this service, you get the error "Object 'CLRObject' could not be created". Not very helpful, and trying to catch a CLR Exception won't work either. If you look in the Windows Event Viewer, all you'll find is a warning that Dynamics AX is unable to load your assembly's config file. I'm unsure how to actually get the exception details in AX, so if anyone knows let me know. What I've done to get this, is basically create a static method in Visual Studio that I can debug. The error message I got out of that is:
Could not find default endpoint element that references contract 'CurrencyService.ICurrencyService' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.
So yes, the is the actual issue at play. The endpoint is in the app.config (in the output it becomes DAXMusings.Proxies.dll.config, check your user folder under AppData\Local\Microsoft\Dynamics AX\VSAssemblies where assemblies are deployed... check my blog post on Assembly deployment) and not in the config file of the executing host for our class library (AX32.exe.config).
And that is exactly what AIFUtil fixes! Change the code to the following:


static void Job7(Args _args)
{
    DAXMusings.Proxies.CurrencyService.CurrencyServiceClient  service;
    DAXMusings.Proxies.CurrencyService.Currency currency;
    System.Exception ex;
    System.Type type;

    try
    {
        type = CLRInterop::getType('DAXMusings.Proxies.CurrencyService.CurrencyServiceClient');
        service = AifUtil::createServiceClient(type);
        //service = new DAXMusings.Proxies.CurrencyService.CurrencyServiceClient();
        currency = service.GetConversionRate(
            DAXMusings.Proxies.CurrencyService.CurrencyCode::USD,
            DAXMusings.Proxies.CurrencyService.CurrencyCode::EUR);

        info(strFmt('%1', CLRInterop::getAnyTypeForObject(currency.get_Rate())));
    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(CLRInterop::getAnyTypeForObject(ex.ToString()));
    }
}

And yes, that one works! If you look into the createServiceClient() method, you'll notice it actually loads the class library's config file. Nice! Problem solved!!

So, as a final note, on Twitter the question was asked, how do I differentiate between development and production? First I didn't get the question, but I get it now. If you are calling a custom service you've made, you may have a version of the service for development, and a separate version for production. Of course, the class library points to one and only one URL. So how do we make it point to different places in different environments without changing the code between the environments?

Change the config file? This would work, but the class library's config file is stored in the model store and downloaded by the client/server. It can't be changed unless it's changed in the AOT, the Visual Studio project is rebuilt, at which point the client/server will download the new version from the model store. So, you could copy/paste all the app.config settings into the AX32(serv).exe.config file and change it there. Then you won't need to use the aifUtil::createserviceclient. In any case, this is very impractical, especially for services running on the client side!

We can just go the AX route, and store the URL in a parameter table somewhere. Then, at runtime, we change the end point address with the following code (replace the hardcoded the localhost url with a parameter).


static void Job7(Args _args)
{
    DAXMusings.Proxies.CurrencyService.CurrencyServiceClient  service;
    DAXMusings.Proxies.CurrencyService.Currency currency;
    System.ServiceModel.Description.ServiceEndpoint endPoint;
    System.Exception ex;
    System.Type type;

    try
    {
        type = CLRInterop::getType('DAXMusings.Proxies.CurrencyService.CurrencyServiceClient');
        service = AifUtil::createServiceClient(type);
        //service = new DAXMusings.Proxies.CurrencyService.CurrencyServiceClient();
        endPoint = service.get_Endpoint();
        endPoint.set_Address(new System.ServiceModel.EndpointAddress("http://localhost/HelloWorld"));
        
        currency = service.GetConversionRate(
            DAXMusings.Proxies.CurrencyService.CurrencyCode::USD,
            DAXMusings.Proxies.CurrencyService.CurrencyCode::EUR);

        info(strFmt('%1', CLRInterop::getAnyTypeForObject(currency.get_Rate())));
    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(CLRInterop::getAnyTypeForObject(ex.ToString()));
    }
}


That's all I got. Have fun with your SOA architecture! And as usual, this walkthrough was added to the other ones on the AX 2012 Developer Resources page!

Tuesday, October 18, 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 '';
}

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)));
}

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]'));
}
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":
If we go into SQL, this is what our view's design now looks like:

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!

Friday, October 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!

Thursday, October 13, 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));
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());
}


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:


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.

Monday, October 10, 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;
}


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!

Wednesday, October 5, 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));
    }
}


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));
    }
}


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));
    }
}


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)


There are similar methods for UTCDateTime type:

query.ValidTimeStateAsOfDatetime(rateDate)

query.ValidTimeStateDateTimeRange(fromDate, toDate)


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));
        }
    }
}

The query dialog that now comes up, will automatically have an extra tab after Range and Sorting, called "Date Options".


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.


Tuesday, October 4, 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.