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...

Field security and record templates

We came across an issue where a user that does not have access to edit a mandatory field on a table was not able to save a record created from a template that has the field predefined.

Consider the case where we want to allow a user to create items but we do not want him to be able to modify the item group. We prevent the user from modifying the field through security. All the items are created through templates which have an item group already defined... The problem is when the new record is created from the template, before copying the value over, it checks if the user has edit access on the field.

To avoid this problem, a simple modification can be made in ClassFactory/createRecord

The call to
sysRecordTemplate.createRecord();

needs to be modified to
sysRecordTemplate.createRecord(false);

This will ignore the AllowEditOnCreate (Edit access in security) property when transferring over the values from the template to the new record.