That Windows temporary directory that you've always been looking for

Use the following snippet to obtain the executing computer's temporary directory:

    str dirTmp = (isRunningOnServer() ? 
        WinAPIServer::getTempPath() : WinAPI::getTempPath());
    ;
    info(dirTmp);   // 'C:\Docs and Stuff\JDoe\Local Config\Temp\'

However... We're actually doing a bit more work inside that WinAPIServer::getTempPath() function as we're checking server-side for I/O access and dll interop permission so don't be surprised if you generate and error the first time you run it. Here's my call stack with the error:

Error en la solicitud de permiso de tipo 'FileIOPermission'.

(S)\Classes\FileIOPermission\demand
(S)\Classes\WinAPIServer\getTempPath - line 13
(S)\Classes\EVE_CONGenerateFile\saveLocalFile - line 7
(S)\Classes\EVE_CONGenerateFileProvisional\sendToHost - line 29
(S)\Classes\EVE_CONFileExporter\mainOnServer - line 31
(C)\Classes\EVE_CONFileExporter\main - line 4
(C)\Classes\MenuFunction\run
(C)\Classes\FormFunctionButtonControl\Clicked - line 16

The answer was in this post, indicating that we now need to assert 'FileIOPermission' before calling WinAPIServer::getTempPath()... Which personally feels wrong to me. I added the following hack before the call:

    FileIOPermission    _permHACK = new FileIOPermission('','r');
    str                 tempPath;                           
    ;
    _permHACK.assert();
    tempPath = (isRunningOnServer() ? 
        WinAPIServer::getTempPath() :WinAPI::getTempPath());
    // revert previous assertion
    CodeAccessPermission::revertAssert();

Later on in the code when obtaining read/write permission to the aforementioned temporary directory I was getting 'Varias llamadas a CodeAccessPermission.Assert/Multiple calls to CodeAccessPermission.Assert' error so don't forget to add the last line above, and revert our assertions before performing a second Assert. The alternative being to create a Set of these permission classes and assert multiple times.

Array initialization fun-ness

You don't normally see people publish a TODO item on their blog but this one is really bugging me:

//TODO: Find a simple way of initialization and assigning a 'static' array of integers (or strings!).

Let's hit the MSDN first and see the four ways to declare our int arrays:

// A dynamic array of integers
int i[]; 
 
// A fixed-length real array with 100 elements
real r[100]; 
 
// A dynamic array of dates with only 10 elements in memory
date d[,10]; 
 
// A fixed length array of NoYes variables with 100 elements
// and 10 in memory
noYes e[100,10]; 

That's sweet but we're not assigining the values to these arrays. And it's this which is really annoying me. This is what we would like to do in our psuedo non X++ compliant code:

int     iPeso[13] = {5, 4, 3, 2, 1, 9, 8, 7, 6, 5, 4, 3, 2};

However, I found an interesting comment in the aforementioned MSDN that leads me to believe that it can't be done:

"You use a separate statement to initialize each element in an array."

Sadness:

    int     iPeso[13];
    ;
    iPeso[1]   = 5;
    iPeso[2]   = 4;
    iPeso[3]   = 3;
    iPeso[4]   = 2;
    iPeso[5]   = 1;
    iPeso[6]   = 9;
    iPeso[7]   = 8;
    iPeso[8]   = 7;
    iPeso[9]   = 6;
    iPeso[10]  = 5;
    iPeso[11]  = 4;
    iPeso[12]  = 3;
    iPeso[13]  = 2;

My investigations with the Array class however brings us one small point of shining light to the blog entry, from Jay Hofacker:

//To reset all elements of an array type, assign a value to element 0
int myArray[10];
;
myArray[0]=0; //reset all elements of the array to their default value

As a final thought, I'm wondering if we should use the container class to save our fingers, disregarding the conversion between data types of each iteration:

container cPeso = [5, 4, 3, 2, 1, 9, 8, 7, 6, 5, 4, 3, 2];

mainOnServer madness

It had to happen. So let me explain.

We've seen the mainOnServer trick used in a few classes now and it was time to copy/paste the concept. The idea is simple enough and the comment in the PurchFormLetter class is crystal clear:

Main method is split to two parts to reduce the interaction between tiers. main method runs on the client. It gets parameters from Args class and passes them to mainOnServer method, which does the main job.

In my case it was importing 10 to 20,000 ledger entries from a text file. Not a simple import either as we had to translate values from another host system and perform numerous validations. It all made sense to read the text file into a TextBuffer and send it across to the server tier.

The first problem occurred when attempting to export the list of generated errors to Excel. I would be using a TextBuffer to create a tab separated file with a list of table headers and then iterate over buffer fields of the problematic rows. The X++ method performPushAndFormatting of the class SysGridExportToExcel wasn't working as expected, mainly due to a problem with the clipboard. So... Here are two ways to clear the clipboard, neither of which was working - why was that?

    TextBuffer   tbErr   = new TextBuffer();
    ;
    tbErr.setText('');
    // Clear clipboard 1/2    
    tbErr.toClipboard();
    // Clear clipboard 2/2
    WinApi::emptyclipboard();

I tried and tried and couldn't clear the clipboard on my machine for some reason. Finally I completely gave up on the SysGridExportToExcel::performPushAndFormatting(...) idea and went for the approach of generating a csv file and then letting the OS decide which application should open it. I adapted the idea to use a TextBuffer as well, writing the contents to a file on the machine... Which also didn't work.

    FileIoPermission _perm;
    str tmpFileName;
;
    tmpFileName = strfmt('%1ax_export\\%2',WinAPI::getTempPath(),"test.csv");
    _perm = new FileIoPermission(tmpFileName,'RW');
    _perm.assert();
    tbErr.toFile(tmpFileName);

    //Open CSV file in registered app 
    WinAPI::shellExecute(tmpFileName);

The code executes with no problems whatsoever until the last command which could never find the file we had generated. I ended up using Excel && Com (example) to create the output but this could take a very long time but there was still some niggling doubt in my mind as to what the source of the problem really was.

Agghhh! We were executing on the server all along! Simply by creating a static client method it would have resolved my issues.

I'm just going to copy and paste my code below, hightlighting the bits you'd need to change were you to use it (at your own risk!). It's not generic enough to simply pass a query or buffer into a method, but adapt it as you see fit:

static client void exportErrorsToExcel(EVE_HRImportHistory importHistory)
{
    QueryRun                qr;

    Common                  ljTrans;        // EVE_DOMImportLedgerJournalTrans
    int                     cnt     = 0;

    DictTable               dt      = new DictTable(tablenum(EVE_DOMImportLedgerJournalTrans));
    FieldId                 fieldId;
    SysDictField            fieldDict;
    int                     fieldNumDim = fieldNum(EVE_DOMImportLedgerJournalTrans, Dimension);

    TextBuffer              tbErr   = new TextBuffer();
    str                     sTab    = '\t';
    str                     sNL     = '\r\n';
    int                     iTab    = strlen(sTab);

    // Update Excel Output columns - 33
    container               moneyCols               = [18,19];
    // 0 = string, 1 = ??, 2 = money, 3 = date, 4 = ??
    container               formatCols              = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,3,0,2,2,0,0,0,0,0,0,0,0,0,0,0,0];
    // 1 == left  2 == right  3 == center
    container               alignmentCols           = [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,1,1,1,1,1,1,1,1,1,1,1,1,1];
    ;

    // Add title rows
    fieldId = dt.fieldNext(0);
    while (fieldId)
    {
        if (!isSysId(fieldId))
        {
            // Heading Initialisations
            fieldDict = new SysDictField(dt.id(), fieldId);
            tbErr.appendText(fieldDict.label());
            tbErr.appendText(sTab);
        }
        fieldId = dt.fieldNext(fieldId);
    }
    tbErr.delete(tbErr.size()-iTab+1,iTab);      // Avoid last column
    tbErr.appendText(sNL);
    cnt++;

    // Query
    qr = EVE_DOMDirectDebitImporter::getImportHistoryBatch(importHistory.ID, true);
    while(qr.next())
    {
        ljTrans = qr.getNo(1);

        fieldId = dt.fieldNext(0);
        while (fieldId)
        {
            if (!isSysId(fieldId))
            {
                if (fieldId != fieldNumDim)   // Dimensions!  Exclude from report :)
                {
                    tbErr.appendText(strfmt("%1",ljTrans.(fieldId)));
                } else {
                    tbErr.appendText("EXCLUIDO");
                }
                tbErr.appendText(sTab);
            }
            fieldId = dt.fieldNext(fieldId);
        }
        tbErr.delete(tbErr.size()-iTab+1,iTab);
        tbErr.appendText(sNL);
        cnt++;
    }

    tbErr.toClipboard();
    SysGridExportToExcel::performPushAndFormatting(moneyCols, alignmentCols, formatCols, cnt);

    tbErr = null;
}

cannot execute a data definition language command

Hello?  What did I do this time?  Here's the beef, in Spanish:

No se puede ejecutar un comando de lenguaje de definición de datos en  ().  La base de datos SQL ha emitido un error.
Problemas durante la sincronización del diccionario de datos SQL.  Error en la operación.
Error de sincronización en 1 tabla(s)

The table on the right looks innofensive enough but a quick Google finds us the solution. It indicated that I was using a reserved word, such as those defined in the Ax\System Documentation\Tables\SqlDictionary. Within the Ax client therefore I changed the columns Data to LineData, Error to ActionError, Line to LineNo but alas - no joy.

I thought that dropping into the SQLServer Manager would tell us a different story, and came to the conclusion that the Data column must die... Bah still no joy.

Finally we created a brand new table and then start copying columns across, one by one from the original. For each new column it was a case of compiling and then opening the table from the AOT to see if it generated the error... It would appear that the word 'TRAN' is verboten - forbidden, and we never knew. With our new table and a new column name in the database now I have the field with an underscore appended after it: TRANSACTION_. Wierdness.

What I still don't know is where to look for these magic words.

Mismatching Sequence field values

Here comes the science poop:

Record with RecId 5637206524 in table 'Proyectos' has mismatching Sequence field values. Original value was 5637206523, new value is 5637206524.
No se puede editar un registro en Proyectos (ProjTable).
El número de registro no coincide con el número original. Si la tabla usa la caché completa, puede que la caché se esté vaciando. Reinicie su trabajo si es el caso.

It's not immediately clear what caused this for me. At first I'd put it down to the fact that I was stepping through code && iterating over a ProjTable with a cursor and my work colleague had updated the record I was attempting to change.

Now however I suspect that I was updating a 'stale' record as the error is only generated on the second pass through the loop.  I changed my code so that the first thing I did with my cursor is to obtain a copy of the cursor buffer with the find operation.

    for (cursorProjTable = ProjTable_ds.getFirst(true) ? ProjTable_ds.getFirst(true) :
        ProjTable_ds.cursor() ; cursorProjTable.RecId ; cursorProjTable = ProjTable_ds.getNext() )
    {
        projTableCpy.clear();
        projTableCpy = ProjTable::find(cursorProjTable.ProjId, true);

        this.op1(projTableCpy);
        this.op2(projTableCpy);
        this.op3(projTableCpy);

        // Update the record
        ttsbegin;
        projTableCpy.EVE_ProjWfStatus   = EVE_ProjWfStatus::Canceled;
        projTableCpy.Status             = ProjStatus::Completed;
        projTableCpy.update();

        // Use ProjStatusUpd the helper class to update the status of the project
//        projTableCpy.clear();
//        projTableCpy = ProjTable::find(cursorProjTable.ProjId, true);
//        projTableCpy.EVE_ProjWfStatus   = EVE_ProjWfStatus::Canceled;
//        projStatusUpd = ProjStatusUpd::construct(projTableCpy,ProjStatus::Completed);   //, true True for subprojects
//        projStatusUpd.run();
        ttscommit;
    }

As you can see from my pseudo-code above, I originally used the ProjStatusUpd class to peform my update but... I have the suspicion that something going on in there was causing our error.

The only useful link I could find on the web was to try selecting the record at the latest possible moment.  I removed the error, but without discovering exactly what was the root cause.

Edit : Daniel Kuettel has found a possible cause and provided a fix for a AX2009 SP1 instance.

SysRecordTemplate terror

Cool funcitionality can sometimes burn you.  Take the Record Templates functionality for example.  Let's quote the advantages straight out of the horse's mouth:

The Good
"Record templates help you to speed up the creation of records in Microsoft Dynamics AX. You can create the following types of templates:

• Templates that are only available to you.
• Templates that are available to all users using the selected company
• Templates that are related to system tables such as Users"

A bit too dry?  There is an example in the DynamicsAxTraining blog which more effectively shows it's time-saving advantages.

The Bad

OK...  Here comes the bad.  What happens if we have a company level template record or two, but later on want to import a set of rows from Excel via our own X++ class?  We receive all of the imported rows, via the table buffer's initValue() (well, it's the super() call) with the template values automatically applied.  We didn't need that!  I'm also not the only one who has found this problem:

This is easy enough to work around, just create a new item with minimal fields set and make a template from that item and set it as default using the SysRecordTemplateTable form.

As a great philosopher once said, that is really quite meh.

The Ugly
Roll up your sleeves, and let's dive in.  First a few objects of interest.

• SysRecordTemplateSystemTable - Used as template storage for system tables
• SysRecordTemplateTable - Used as template storage for Company level
• xSysLastValue - Class used as template storage for User level
• SysRecordTmpTemplate - Lists/filters for invalid templates?

I my case of importing ProjTable records, here is a pseudo stack trace of what I can see going on:
    ProjTable.initValue()
    ????.???()
    ClassFactory.createRecord()
    SysRecordTemplate.createRecord()

What we really want to do before a batch import is:

• Load the record template for this table (if it exists!), it's a container. (Tip: See how to iterate over the Company record templates, and it's data contents by a clever Jovan Bulajic)
• Allow that 'blank'/empty rows are allowed.
• Update the table record template so that the default template is the empty row
• Reserialize our container so that the NEXT time an initValue is called for this table, it will be blank.

SysRecordTemplate.createRecord Here we can see why we don't have the pop up asking us which template to use when importing from Excel - We have no GUI and we're probably in a transaction.  Worse still, it uses only the Company templates and ignores any of our own (User) templates.  And even worse than that...

    if (ttsLevel || !hasGUI())
        recordValues = storageCompany.findDefaultTemplate();
    else
        recordValues = this.promptSelect(userData, condel(companyData,1,1), copyData, allowBlank);

SysRecordTemplateStorage.findDefaultTemplate is, in my opinion, bugged for what we want to do.  Opening it up we find that it iterates through the container of record templates until it finds the one selected as default.  If it finishes the loop without finding one, it then bizarrely chooses the first in the list.  Nooooo!  Even if we deselected all of the elements as default, we would still end up with a non-blank record template for that table.

The only way I can think of getting around the problem is one of the following (and clear the cache!):

1. Removing the company level templates from the table, importing files from Excel, and adding them again at the end of the process.  Not a good approach for many reasons.  Especially as the method SysRecordTemplate::deleteTemplates() disappears from Ax4.0 to Ax2009, and try as I might I can't seem to execute the following code on the table:

SysRecordTemplate srtTable;
;
delete_from srtTable where srtTable.TableId == common.tableId;

2. Calling SysRecordTemplateStorageCompany.replaceData to swap out the default record template data with an 'empty' buffer at the start of the process. As ugly as the above suggestion.

3. We could edit those Sys* files...  Yeeeees (the sound of evil cackling can be heard) but there is a problem with that as...

My break points in one of the aforementioned system classes (the classFactory?) is crashing the Ax service for the dev environment.  People are looking at me with daggers in their eyes, and buying them a coffee all the time while it starts up again is getting expensive.

Drawing to a convoluted conclusion then, here is what I modified in SysRecordTemplate.createRecord():

    if (ttsLevel || !hasGUI())
    {
        //<zzz> No template at all, thanks.  We're importing via Excel, for example.
        //recordValues = storageCompany.findDefaultTemplate();
        //</zzz>
    }
    else
        recordValues = this.promptSelect(userData, condel(companyData,1,1), copyData, allowBlank);

As always, do let us know if there is a simple way of deactivating these templates while in a transaction or during a process without a GUI associated.  I'd be happy to amend corrections to the above post from your comments.  Oh, and one final link: Using record templates in code for Dynamics Ax 4.0