xRecord.setTmp() and inactive configuration keys

Be carefull when using setTmp method on table buffers. If your table is assigned a configuration key, turning it off will make this line of code throw an error at runtime: "Table '<table name>' of type TempDB cannot be changed to table of type InMemory".

For example, \Classes\SysRecordTemplateEdit\setFormDataSourceRecord has this problem. It loops through form datasources and tries to set them all to InMemory withouth checking first if they are TempDB or not.

In order to avoid this error, you can use the following construction:

    if (!myTable.isTempDb())
    {
        myTable.setTmp();
    }

More about TempDB tables on MSDN.

Exporting to Excel with Microsoft.Dynamics.AX.Fim.Spreadsheets.* classes

While looking for a way to export to Excel in batch, I investigated what they do in financial statements (LedgerBalanceSheetDimPrintExcelEngine class).

Pros:

• It is possible to use these .NET components in batch on the server side
• Execution is way faster than that of the SysExcel* classes

Cons:

• It looks like it is not possible to create more columns than there are letters in the English alphabet
• Less flexible comparing to standard SysExcel classes, so manual formatting will likely be needed in the end. 

static void AnotherWayToExportToExcel(Args _args)
{
    #define.ReadWritePermission('RW')
    #define.FileName('c:\myFile.xlsx')
    #define.ExcelColumnWidth(15)
    #define.ExcelCellFontSize("Microsoft.Dynamics.AX.Fim.Spreadsheets.CellFontSize")
    #define.Size9("Size9")    
 
    CustTable custTable;
 
    Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet spreadsheet;
    Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties cellProperties;    
    Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties columnProperties;    
 
    void addColumn(str _name)
    {
        columnProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties();
        columnProperties.set_Width(#ExcelColumnWidth);
        spreadSheet.InstantiateColumn(columnProperties);
 
        cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();
        cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));
        cellProperties.set_Bold(true);
 
        spreadSheet.AddStringCellToWorkbook(_name, cellProperties);
    }    
 
    new FileIOPermission(#FileName, #ReadWritePermission).assert();
 
    spreadSheet = new Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet();
 
    if (!spreadSheet.CreateSpreadsheet(#FileName))
    {
        throw error(strFmt("@SYS72245", #FileName));
    }
 
    addColumn("Customer name");
    addColumn("Balance");    
 
    while select custTable
    {
        spreadSheet.MoveToNextRowInWorkbook();        
 
        cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();
        cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));        
 
        spreadSheet.AddStringCellToWorkbook(custTable.name(), cellProperties);            
        spreadSheet.AddNumberCellToWorkbook(real2double(custTable.openBalanceCur()), cellProperties);                    
    }
 
    spreadSheet.WriteFile();
    spreadSheet.Dispose();
 
    CodeAccessPermission::revertAssert();    
}

Output:

Manupulating forms from code - continued...

After publishing a post about manipulating the Marking form from code, I got some feedback (special thanks to Maxim Gorbunov).

I would like my readers to remember, that forms should never be used like that in business logic executed on a daily basis. If this is something that should be run more often than once, this logic should preferably be run on the server, and in the case with the Marking form, it should rather be TmpInventTransMark::updateTmpMark() method called directly. You will need to prepare parameters for it, though, so you will first need to figure out how the form does that.

In other words, please use out-of-the-box solutions you find on the Internet with care.

Manipulating the Marking form from code

Businesses buy AX, because they would like to automate their processes. Sometimes they would like to automate more, than standard AX allows. And if business logic is built into forms, it is sort of hard to automate.

One of our clients had to migrate open sales and purchase orders to AX, and mark their lines against each other afterwards. Apparently, calling InventTransOrigin::updateMarking method was not enough, as sales and purchase lines were still not pointing to each other in the reference fields. So, I tried to use the standard Marking form for that ad-hoc task.

Below is the code sample that takes a purchase line lot ID, opens the Marking form, selects the target sales order line and marks it (or unmarks, if it was marked before). Just like you do it by hand.

static void main(Args _args)
{
    #define.TmpInventTransMarkDsNo(2)
    #define.InventTransOriginDsNo(4)
    #define.InventTransOriginMarkDsNo(5)
    #define.MarkNowControlName('markNow')
 
    #define.PurchLineLotId("GSC-000983")
    #define.SalesLineLotId("GSC-000982")
 
    FormRun formRun;
    Args args;
    TmpInventTransMark tmpInventTransMark;
    SalesLine salesLine;
    InventTransOrigin salesLineOrigin;
    PurchLine purchLine;
    InventTransOrigin inventTransOriginMark;
    FormCheckBoxControl markNowCheckBox;
 
    purchLine = PurchLine::findInventTransId(#PurchLineLotId);
 
    args = new Args();
    args.name(formStr(InventMarking));
    args.record(purchLine);
 
    // Work-around: first if-statement in
    // Classes\ReqCalc\argsItemId method expects a caller
    args.caller(new InventMarkingEmulator());
 
    formRun = classfactory.formRunClass(args);
    formRun.init();
    formRun.run();
 
    // The temp table is filled in when this active() is called
    formRun.dataSource(#InventTransOriginDsNo).active();
 
    tmpInventTransMark = 
        formRun.dataSource(#TmpInventTransMarkDsNo).cursor();
    inventTransOriginMark = 
        formRun.dataSource(#InventTransOriginMarkDsNo).cursor();
 
    ttsbegin;
 
    // Find the required reference and mark it    
    formRun.dataSource(#TmpInventTransMarkDsNo).first();
    Debug::assert(tmpInventTransMark.RecId != 0);
    do
    {
        if (inventTransOriginMark.InventTransId == #SalesLineLotId)
        {
            markNowCheckBox = 
                formRun.design().controlName(#MarkNowControlName);
            markNowCheckBox.value(1);
            markNowCheckBox.clicked();
 
            formRun.closeOk();
            break;
        }
    } while (formRun.dataSource(#TmpInventTransMarkDsNo).next());
 
    ttscommit;
}

Figuring out where some table field is modified

Sometimes I need to find out where a particular table field is modified in the X++ code. Normally, I call "Used by" form,  filter records out by Reference = Write, put manually breakpoints in the corresponding X++ lines and then run the scenario to see which breakpoint is eventually hit.

However, if there are too many cross-references, I don't bother adding breakpoints manually. Instead, I add a button to xRefReferencesUsedByTypedTree form, set its Label property to "Add breakpoint" and MultiSelect to "Yes", and then override its clicked method like this:

void clicked()
{
    container breakpoints;
    boolean enable = true;
 
    xRefReferences xRefReferencesLocal;
 
    breakpoints = infolog.breakpoint();
 
    for (xRefReferencesLocal = XRefReferences_ds.getFirst(true) ?
        XRefReferences_ds.getFirst(true) : XRefReferences_ds.cursor();
        xRefReferencesLocal;
        xRefReferencesLocal = XRefReferences_ds.getNext())
    {
        if (xRefReferencesLocal.line > 0)
        {
            breakpoints += [xRefReferencesLocal.path()];
            breakpoints += [xRefReferencesLocal.line];
            breakpoints += [enable];
        }
    }
 
    infolog.breakpoint(breakpoints);
}

 Now, after opening "Used by" form, I simply click "Ctrl+A" and then the new "Add breakpoint" button, so all required breakpoints are in place.

How one new table relation may break your code somewhere

Yesterday I spent some time figuring out why intercompany functionality on my box stopped working after upgrade to CU3. No errors were thrown - it just didn't create IC chains, although the setup was correct. There were no recent changes in the TFS history that would help understand why that happened.

Whyle debugging, I found that the quiry used to fetch sales lines was broken in some method. In the standard, it linked SalesLine and InventTable by using QueryBuildDataSource.relations() method, passing true as a parameter. In the customized version of SalesLine, another relation to InventTable was added, linking a new SalesLine field with InventTable.ItemId field. So, the relations method desided that this is the new relation that should be used when building a query for IC chain creation.

It was not an issue before the upgrade, as the old relation was still selected properly. But somehow the upgrade updated IDs or whatever that determined in which priority the relations should be used by relations method.

So, beware of that effect next time you add a table relation.

ID change in Dynamics AX data dictionary

The other day we upgraded to Cumulative Update 3 for Dynamics AX 2012. After that we got some problems in the SqlDictionary table - several table and field IDs did not much those in the AOT anymore.

One of our developers found this post, which contained a job fixing such issues. We had to correct the job a bit, otherwise it failed when trying to process Views or update field IDs that had been "swapped" during upgrade (e.g. before: fieldId1 = 6001, fieldId2 = 6002; after installing CU3: fieldId1 = 6002, fieldId2 = 6001).

This is the final version of the job. I know the change violates DRY principle, but for an ad-hoc job it is probably OK :)

static void fixTableAndFieldIds(Args _args)
{
    Dictionary dictionary = new Dictionary();
    SysDictTable dictTable;
    DictField dictField;
    TableId tableId;
    FieldId fieldId;
    SqlDictionary sqlDictionaryTable;
    SqlDictionary sqlDictionaryField;
 
    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;
 
    while (tableId)
    {
        dictTable = new SysDictTable(tableId);
 
        setPrefix(dictTable.name());
 
        if (!dictTable.isSystemTable() && !dictTable.isView())
        {
            //Finds table in SqlDictionary by name in AOT, if ID was changed.
            //Empty field ID represents a table.
            select sqlDictionaryTable
                where sqlDictionaryTable.name == dictTable.name()
                && sqlDictionaryTable.fieldId == 0
                && sqlDictionaryTable.tabId != dictTable.id();
 
            if (sqlDictionaryTable)
            {
                info(dictTable.name());
                //Updates table ID in SqlDictionary
                if (ReleaseUpdateDB::changeTableId(
                    sqlDictionaryTable.tabId,
                    dictTable.id(),
                    dictTable.name()))
                {
                    info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
                }
            }
 
            fieldId = dictTable.fieldNext(0);
 
            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);
 
                if (!dictField.isSystem())
                {
                    //Finds fields in SqlDictionary by name and compares IDs
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId != 0
                        && sqlDictionaryField.fieldId != dictField.id();
 
                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -dictField.id(),
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Pre-update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
 
            fieldId = dictTable.fieldNext(0);
 
            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);
 
                if (!dictField.isSystem())
                {
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId < 0;
 
                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -sqlDictionaryField.fieldId,
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Final update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -sqlDictionaryField.fieldId));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;
}