Running the CRT samples on AX7

The RetailSDK delivered with AX7 includes a lot of great sample code making it easier to get things rolling when learning how to customise the solution.

One of the sample projects is the CommerceRuntimeSamples. Before you are able to run it you need to do a couple of tweaks though. If not it will crash and burn with error messages that do point you in the right direction on how to fix it. This blog post is made to safe you some time and getting it fixed without having to go troubleshooting.

The first error that pops up is this one:

operatinguniterror

A configuration error in the Microsoft.Dynamics.Commerce.Runtime.dll. The reason is that it cannot find the right Operation Unit Id. It is specified in the commerceruntime.config and all you have to do is put in the operating id of the channel you would like to work with. In this case I use Houston, which has the id 039:

operating-unit

Next is this error:

storageerror

A storage exception in the same dll and same piece of code. This time we have to look in the app.config file. In here we have a list of connection strings that all point at database names as they appeared in AX 2012 Retail. In AX7 demo servers all data for all channels is in the AxDB database so the fix is to change the database names in the connection strings to AxDB:

catalog-db-name

Now you are able to run the samples and start debugging to learn.

debugactive

 

 

Debugging Commerce Runtime in the New Dynamics AX (AX7)

 

The New Dynamics AX has certainly shown us new ways of working and for the developer the learning curve has been pretty steep.

With everything being Visual Studio for the developers new work routines are implemented and that goes for debugging as well.

Now, the Visual Studio debugger is great. So we are not losing any ground. We do however have to think a bit different in order to nail those tricky troubleshooting situations. In this post I will show how to debug the Commerce Runtime part of the Retail Server.

 

Step 1 – Getting the IIS ready

Navigate to the Retail Server folder. One way to get there is to open the IIS Manager. Locate and select the RetailServer in the Sites list to the left and select Explore. In the web.config file change

<compilation debug="false" targetFramework="4.5" />

to

<compilation debug="true" targetFramework="4.5" />

When you save the file you cannot save it to its original position. Save it to your desktop or somewhere else neutral and then copy it to the original folder.

Restart the IIS

 

Step 2 – Set the breakpoint

Now start up Visual Studio and open the solution CommerceRuntime in the RetailSDK folder \RetailSDK\Code\CommerceRuntime.

In the solution explorer locate the Discount.cs file in Runtime.PricingEngine project and add a breakpoint. Just to keep it simple I have put it into the constructor:Breakpoint Discount constructor

 

Step 3 – Start the Modern POS

Now it is time to fire up the Modern POS. Just do a login and leave it there for a while.

 

Step 4 – Get the debugger ready

Go back to Visual Studio, go to the Debug menu and select Attach to process.

Change the code type from automatic to Managed (v4.6, v4.5, v4.0)

AX7POSDebugCodeType

Mark Show processes from all users, select the w3wp.exe processes and click Attach.

AX7POSDebugProcess

I know that you probably only need to attach to one of them; but it is much easier just selecting them all.

Take a quick look at your breakpoints to make sure that they are active. If not you might be missing a reference in the commerceruntime file.

Breakpoint active Discount constructor

Step 5 – Back to the POS to get the debugger activated

What we need to do now is to get the POS to require an instance of the Discount object on the retail server. In this example I add a pair of sunglasses to the basket:

Add product to get the Discount object instantiated

That gets us into the debugger and we can do what we need to do in order to see how it works or start troubleshoot.

Debugger activated

I hope this helps you get a bit deeper into the retail server or other services related to the New Dynamics AX.

 

 

 

Locate duplicate values (or the opposite) in a table

The way X++ handles SQL statement often lacks a bit compared to what you can do in standard TSQL.

One of the areas is if you want to get a set of records from a table where a specific value in a field only occurs once in the table. You could traverse through the records and compare values as you go along. That could pull down performance significantly depending on the record count. Another way of doing it is to group by the field while counting the records in e.g. RecId. Then traversing this result set and look at the value in RecId could give you either the unique or not unique value depending on what you need.

A better way would be to let the SQL server do some of the work and consequently return as few records as possible. Here is a job that illustrates this and the group by version mentioned above.

AX 2012 queries support the HAVING clause. That can in some scenarios do the same and a bit more elegant than this.

 

static void DEMO_NoDuplicates(Args _args)
{
    CustGroup custGroup;
    CustGroup custGroup2;
    PaymTermId lastPaymTermId;

    info("TRAVERSE COUNT");

    while select count(recid) from custGroup
        group by PaymTermId
    {
        if (custGroup.RecId == 1)
        {
            while select custGroup2
                where custGroup2.PaymTermId == custGroup.PaymTermId
            {
                info(custGroup2.PaymTermId);
            }
        }
    }

    info("USE JOIN");
    
    while select custGroup
        order by PaymTermId
        notexists join custGroup2
            where custGroup.RecId != custGroup2.RecId
               && custGroup.PaymTermId == custGroup2.PaymTermId
    {
        info(custGroup.PaymTermId);
    }
}


Using “Create document service” wizard results in a method xMLMapUnitId does not exist

And that is – in AX 2012 – absolutely correctly observed by the compiler. The method was present in earlier versions of AX but went missing in AX 2012 and was replaced by the method xmlMapUnitOfMeasureSymbol.

So if you use the wizard on for example on a query using ProdTable you might end up with an error telling you that this.mapPolicy().xMLMapUnitId() does not exist. Replace it with this.mapPolicy().xmlMapUnitOfMeasureSymbol() and you are good to go. It will also tell you that this.axUnitId() is incorrect which also is true. The correct method name is axUnitOfMeasureSymbol().

Once you know it … 🙂

How to delete a label file in AX 2012

If you end up with an obsolete label file in AX 2012 and want to delete it you cannot just right click and select delete from the popup menu.

To get rid of the label file you can do the following:

  1. Create a new model. In this example named DeleteMe
  2. Move your label file to the DeleteMe model
  3. Use AXUTILs delete function to delete DeleteMe
  4. Restart the AOS

You have successfully removed the label file from your installation.

Manipulating data while ignoring a TTSABORT

OK, the subject of this post will probably be categorised as dangerous and by some as borderline stupid. The content should NOT be considered used in everyday work since it very easily could give you massive inconsistency in data and a broken system. There! I said it and I do not want to be the one saying I told you so afterwards. This blog post is only for information and not a recommendation.

So what is all the fuzz about? Well, sometimes it could be nice allowing some data to be inserted, updated or deleted although a TTSABORT command or an error is thrown. That could be relevant in regards of a log being updated no matter what the result of a job is or the likes.

The mechanism is known from the batch queue that has its status updated no matter how the job finishes.

 

The trick is using the UnitOfWork and UserConnection framework within the TTS-scope. This allows you to create a connection to the database that is not a part of the TTS but is running its own show.

And this is where it gets dangerous/stupid in some scenarios. Imagine a inventory transactions customisation manipulating data in some circumstances within the TTS and some without. The result could be data almost impossible to recover to a consistent state again.

 

In this example we want to update the Tax Group Id on the Customer groups and log the changes to a table no matter what happens.

I have created a table – DEMO_Log – with CustGroup as the only field. We would like this table to receive a new record not depending on success nor failure in the update of the CustGroup table.

Next step is to create a class doing the work. In this case it is called DemoIgnoreTTSAbort and it has a run method like this:

private void run()
{
    info("BEFORE UPDATING");
    this.showInfo();
    
    ttsBegin;
    this.updateCustGroup();
    ttsAbort;

    info("AFTER NORMAL UPDATE");
    this.showInfo();
    
    ttsBegin;
    this.updateCustGroup2();
    ttsAbort;

    info("AFTER ALTERNATIVE UPDATE");
    this.showInfo();
}

It starts by showing the current records in a resume like this

private void showInfo()
{
    CustGroup custGroup;
    DEMO_Log log;

    select count(RecId) from custGroup
        where custGroup.TaxGroupId;

    select count(RecId) from log;

    info(strFmt("Customer groups with Tax group id: %1", custGroup.RecId));
    info(strFmt("Log records: %1", log.RecId));
}

The idea is to give us a count of customer groups with Tax group ids and the count of records in our log table.

Then we – inside a TTS scope – uses the updateCustGroup method to try updating the groups like this:

private void updateCustGroup()
{
    CustGroup custGroup;

    while select forUpdate custGroup
        where ! custGroup.TaxGroupId
    {
        custGroup.TaxGroupId = 'TX';
        custGroup.update();
        this.insertInLog(custGroup.CustGroup);
    }
}

Each CustGroup record with no TaxGroupId content is updated with ‘TX’ and a log is inserted using the method insertInLog that goes like this:

private void insertInLog(CustGroupId _custGroupId)
{
    DEMO_Log log;
 
    Log.CustGroup = _custGroupId;
    log.insert();
}

I think that one is pretty self explaining…

The TTS scope is then ended with an TTSABORT so no records within the scope is updated/inserted.

We then pull the showInfo once more to se if anything has happened. And nothing has. No surprise.

 

The next part is a new TTS scope and a method (updateCustGruop2) which is almost identical to updateCustGroup is used:

private void updateCustGroup2()
{
    CustGroup custGroup;
  
    while select forUpdate custGroup
        where ! custGroup.TaxGroupId
    {
        custGroup.TaxGroupId = 'TX';
        custGroup.update();

        this.insertInLog2(custGroup.CustGroup);
    }
}

 

The only difference between updateCustGroup and updateCustGroup2 is that it calls insertInLog2 instead of insertInLog after updating each record.

This method is the key to all this and it looks like this:

private void insertInLog2(CustGroupId _custGroupId)
{
    DEMO_Log log;
    UnitofWork unitOfWork;
    UserConnection userConnection;

    userConnection = new UserConnection();
    unitOfWork = new UnitofWork();

    log.CustGroup = _custGroupId;
    log.insert();

    unitOfWork.insertonSaveChanges(log);
    unitOfWork.saveChanges(userConnection);
}

Compared to insertInLog it starts by adding to new variables – unitOfWork and userConnection which gives us an extra connection to the database not included in the TTS scope. First is a basic instantiation followed by the insert of the DEMO_Log record like in the insertInLog method. The next statement is where we tell unitOfWork to insert the log record(s) upon the call of the saveChanges method. There is a deleteOnSaveChanges and updateOnSaveChanges if you want something else than inserts.

Finally we call the saveChanges using the above declared userConnection.

Ending the run method with another call of the showInfo reveals that although we abort our TTS scope and the customer groups remain unchanged the log is fully updated:

Message (05:09:54 am)
BEFORE UPDATING
Customer groups with Tax group id: 0
Log records: 0
AFTER NORMAL UPDATE
Customer groups with Tax group id: 0
Log records: 0
AFTER ALTERNATIVE UPDATE
Customer groups with Tax group id: 0
Log records: 7

Once again: Remember that this is only to be used with extreme caution …

Changing privileges on multiple SQL server tables for a user

This is not really a Dynamics AX thing but with BI in mind it kind of relates.

The thing is that sometimes you have users that needs access to AX tables and views on the SQL server to maintain and develop BI. And often these users should have access to almost every table except a few. Payroll tables are a good example.

In a normal situation you would use schemas to handle this but since AX creates all tables as part of the dbo schema this is not an option. So I created this little script for a colleague to accelerate the proces:

DECLARE @sqlStatement NVARCHAR(max)
DECLARE @user NVARCHAR(30)
DECLARE @filter NVARCHAR(10)

-- ==========
-- Initialize 
-- ==========

SET @sqlStatement = '';
SET @user = 'contoso\abc';
SET @filter = 'payroll%';

-- ================
-- Get REVOKE lines
-- ================

SELECT @sqlStatement = @sqlStatement + 'REVOKE SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V')

-- ================
-- Get GRANT lines
-- ================

SELECT @sqlStatement = @sqlStatement + 'GRANT SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V') AND NAME NOT LIKE '' + @filter + ''

-- ==============================
-- Execute privilege change query
-- ==============================

EXECUTE sp_executesql @sqlStatement;

What it does is that first it creates 3 variables. One for an SQL statement, one for the user identity and one for filtering what tables NOT to grant access to. In this case we want user ABC in the contoso domain to have SELECT rights on all tables except those beginning with “Payroll”.

It then starts by creating the REVOKE SELECT statements for all tables and views (User tables only) and adds them to the @sqlStatement variable.

Then it traverses through the tables and views to make the GRANT SELECT part on all tables NOT matching the filter in the @filter variable.

Finally the created statement is executed using the sp_executesql stored procedure.

It is not the fastest statement in the world; but it gets the job done.