Delete transactions in Dynamics AX 2012

We're in the process of implementing AX 2012 and we needed to flush out all the transactions from a company. There's a nifty class in AX that's called SysDatabaseTransDelete that does exactly what we need. It loops through the tables in the AOT and, depending on their TableGroup property, will flush out the data.

After cleaning the transactions we needed to delete a few vendors. However we kept getting an error about AOS validation failing. After investigating, we figured out that transactions in VendInvoiceInfoTable still existed... This table is flagged with a new TableGroup value which is TransactionHeader!

In fact, two new TableGroup types have been added in AX2012: TransactionHeader and TransactionLine. We'll need to modify the method SysDatabaseTransDelete.handleTable to support these 2 new cases :

void handleTable(SysDictTable sysDictTable)
{
    TableGroup      tableGroup;

    if (tableSet.in(sysDictTable.id()))
        return;
    tableSet.add(sysDictTable.id());

    if (sysDictTable && !sysDictTable.isTmp() && !sysDictTable.isMap())
    {
        tableGroup = sysDictTable.tableGroup();

        // Handle company specific tables to be deleted
        if (sysDictTable.dataPrCompany())
        {
            switch(tableGroup)
            {
                case TableGroup::Transaction:
                case TableGroup::WorksheetHeader:
                case TableGroup::WorksheetLine:
                //FIX - Support new AX2012 transaction table types
                case TableGroup::TransactionHeader:
                case TableGroup::TransactionLine:
                    this.handleTransTable(sysDictTable);
                    break;
                default:
                    this.handleNonTransTable(sysDictTable);
                    break;
            }
        }
        else
        {
            // Handle global tables to be deleted
            switch(tableGroup)
            {
                case TableGroup::Transaction:
                case TableGroup::WorksheetHeader:
                case TableGroup::WorksheetLine:
                //FIX - Support new AX2012 transaction table types
                case TableGroup::TransactionHeader:
                case TableGroup::TransactionLine:
                    this.handleGlobalTransTable(sysDictTable);
                    break;
                default:
                    break;
            }
        }
    }
}

New website!

I've been working at Haka Solutions for 5 years now and we've never really had a website I was proud to show off... But that's all over now! We've just finished adding all the content and it's now officially launched!

Take a look and let us know what you think.

Most of my AX-related blogging will probably now be featured over there but I will still be posting here also. However, my colleagues will also be contributing with blog content on our new website so I suggest adding it to your favorite or registering the RSS feed in your favorite reader.

http://www.hakasolutions.com

*Note that the website will be moving in about a week to a new host. Performance will greatly improve ^^

Rebuilding SqlDictionary entries... (To avoid at all cost)

Whether or not this problem was the result of something I did, it seemed I just had to fix it. After the synchronization of Rollup 5 on one of our systems, we kept getting a database error for one of the tables. I just hate it when a table won't synchronize...

Since the error messages in AX tend to be so vague all the time, I still didn't know which table was failing. Next logical thing to do: fire up the SQL administration form in Administration/Periodic and run the Check/Synchronization. I quickly found the problem table was SalesParmUpdate and the info given to me was "Add table."

For a great read on data synchronization issues, take a look at this post from an old colleague of mine.

So I took a quick look in the SqlDictionary table and found absolutely no records for SalesParmUpdate (TableId: 1428)... The table existed in the AOT, it also existed in the database, but the records in SqlDictionary were missing. I decided to write a quick job that would recreate the records for a table. There's a few things to keep in mind... While normal UtcDateTime fields require two records (one for the DateTime and one for the Time Zone), the system DateTime fields don't need the Time Zone entry. Also, the RecId field uses an undocumented fieldType of 49.

I only have a few special cases in the code below (Enough for the SalesParmUpdate table) but you will probably need to tweak it a bit more to make it work with your table.

WARNING: PLEASE KNOW WHAT YOU ARE DOING BEFORE PLAYING AROUND WITH THE SQLDICTIONARY TABLE. ^^

static void rebuildSqlDictionary(Args _args)
{
    SqlDictionary   sqlDictionary;
    SqlDictionary   sqlDictionaryTZ;
    SysDictTable    sysDictTable;
    SysDictField    sysDictField;
    Set             tableFields;
    SetIterator     si;
    ;

    ttsbegin;

    sysDictTable = new SysDictTable(tablenum(SalesParmUpdate));

    tableFields = sysDictTable.fields();
    si = new SetIterator(tableFields);
    while (si.more())
    {
        sysDictField = si.value();

        sqlDictionary.clear();
        sqlDictionary.tabId = sysDictField.tableid();
        sqlDictionary.fieldId = fieldext2id(sysDictField.extendedFieldId());
        sqlDictionary.array = sysDictField.arrayIndex();
        sqlDictionary.name = strupr(sysDictField.name(DbBackend::Native, sysDictField.arrayIndex()));
        sqlDictionary.sqlName = sysDictField.name(DbBackend::Sql, sysDictField.arrayIndex());
        sqlDictionary.fieldType = sysDictField.baseType();

        switch (sqlDictionary.fieldType)
        {
            case Types::String :
                sqlDictionary.strSize = sysDictField.stringLen();
                break;
            case Types::UtcDateTime :
                if (!sysDictField.isSystem())
                {
                    sqlDictionaryTZ.clear();
                    sqlDictionaryTZ.data(sqlDictionary);
                    sqlDictionaryTZ.array = 2;
                    sqlDictionaryTZ.fieldType = Types::Integer;
                    sqlDictionaryTZ.sqlName += 'TZID';
                    sqlDictionaryTZ.doInsert();
                }
                break;
            case Types::Int64 :
                if (sysDictField.isSystem()) //RecId
                {
                    sqlDictionary.fieldType = 49;
                }
       }

        //sqlDictionary.flags = sysDictField.flags();
        sqlDictionary.doInsert();

        si.next();
    }

    ttscommit;
}

COM error when exporting data...

I've been getting the following error when trying to export some data to Excel using Definition groups:

Method 'item' in COM object of class 'Range' returned error code 0x800A03EC () which means: .

At first I noticed it was only happening when adding the field RecId to the "Field setup" of the export. The problem isn't really the field itself, but actually the way it was being added. When adding a table to a definition group, it automatically pulls in all the fields of that table (except some system fields like TableId, RecId, DataAreaId, etc.). So when you hit the Setup button, it already contains most of the fields.

The table used to store that selection is SysExpImpField. There's a record for each field and it saves the selected field ids in ConvFieldId. It is important to note that it's actually the extended field id that is saved (Field id + array index). From the setup form we have 2 ways of adding more fields, either by using the lookup or by manually typing the name of the field.

If we use the lookup, it calls the Global::pickField function and returns the extended field id selected. However, if we type the name of the field, it will then use the function fieldname2id. fieldname2id returns the FieldId NOT THE EXTFIELDID...

So I added a quick hack to the edit method SysExpImpField.fieldName :

// BP Deviation Documented
edit fieldName fieldName(boolean set, fieldName name)
{
    if (set)
    {
        this.ConvFieldId = fieldname2id(this.ConvTableId, name);
        //MVaillancourt. Haka. 20100617
        //fieldname2id does not return the extended fieldid. if the id is smaller than 65 536, convert to extended
        if (this.convFieldId < 0x10000)
            this.convFieldId = fieldid2ext(this.convFieldId, 1);
        //MVaillancourt. End
    }

    return fieldid2name(this.ConvTableId, this.ConvFieldId);
}

Forms not saving user layout changes

You may have come across some forms where users complained it wasn't saving the layout changes they were making. The problem is quite easy to fix if the link between the 2 forms is straightforward.

I've had to deal with this issue for a client and this is what it looked like:

A custom invoice history screen was created and it lists all the invoice lines for a customer. The invoice history screen is called from the invoice journal header form. A normal button with the call to the form in the click method was used.

void clicked()
{
    FormRun fr;
    Args args;
    ;
    super();

    args = new Args("CusInvoiceHistory");
    args.parm(custInvoiceJour.OrderAccount);
    fr = new FormRun(args);
    fr.init();
    fr.run();

    fr.wait();
}

Although this will call the form, it will not save any user customization (Window size, column width, column order, etc.). The problem lies in the fact that no Menu Item is used. I quickly changed the Button to a MenuItemButton that points to my form, changed the init of my form to properly initialize my query and that did the trick. There is no reason a call to another form shouldn't be done with the use of a Display Menu Item (I can't think of any at the moment). It is the "standard" way of properly linking the forms together.

Have an exception to that rule? Let me know in the comments.

AX, Android, Emulation.... What now?!

So much to do... so little time!

This little blog of mine never really took off and for the most part, I'm the only one to blame. It's not that I don't want to keep it updated, it just hasn't been anywhere near my top priorities this year (or the last few years for that matter :p). Well, are things about to change? Most likely.

I'm still going to try and keep my posts in line with the work I do... that is Microsoft Dynamics AX development, but since I'm now the owner of an Android smartphone, you might see me post a few Android stuff here and there if I ever get everything setup right. First thing I'll do is probably port my Chip8 emulator to the Android. (No Hellow World nonsense for me) That should get me familiarized with the platform and I'll be able to move on to bigger projects after that.

More to come!

Modify the way AX sends reports by email

This is something I've been asked multiple times, either by customers or colleagues... The standard way AX sends reports by email is not ideal.

The two big issues are:
- Using SysINetMail which uses Outlook integration and requires the user to confirm that AX is trying to send an email using their account.
- The formating of the email is just awful and lacks relevant information.

Luckily for us, the method which sends the email is available for developers to modify. It is found at: Classes/Info/reportSendMail

void reportSendMail(PrintJobSettings p1)
{
    SysINetMail m = new SysINetMail();

    str fileName = 'axaptareport';

    if (p1.format() == PrintFormat::ASCII || p1.format() == PrintFormat::TEXTUTF8)
        fileName = fileName + '.txt';
    else if (p1.format() == PrintFormat::RTF)
        fileName = fileName + '.rtf';
    else if (p1.format() == PrintFormat::HTML)
        fileName = fileName + '.htm';
    else if (p1.format() == PrintFormat::PDF || p1.format() == PrintFormat::PDF_EMBED_FONTS)
        fileName = fileName + '.pdf';

    m.sendMailAttach(p1.mailTo(),p1.mailCc(), p1.mailSubject(),'axapta report', true, p1.fileName(), fileName);
}

From here, instead of using SysINetMail we could use SysMailer or even System.Net.Mail. We can also change the subject, body, attachment name, etc...