Resolving those Ax errors are so much easier... When they're in English.

As a native English speaker working in mainland Spain I find myself presented with a few Ax error messages that causes some head scratching and initiate a search on the web.

...Which produces nothing. No results whatsoever in Bing, Google nor from texting the Uruguayan guru in the other office.

The best way to resolve the error is to get the direct translation from your native environment language and find the equivalent in the EN_US spiel of Microsoft. Go to your Ax program directory, mine is 'C:\Archivos de programa\Microsoft Dynamics AX\' and then from there navegate to the '50\Client\Bin\' directory. I'm working in Spanish, so I'll open the 'AxSysES.ktd' in notepad and physically search for the exact phrase in there, noting down the line number. Finally it's a case of opening 'AxSysEN-US.ktd' and jumping to the equivalent line number. THIS phrase will be the one most likely to give you results from searching the web.

update_recordset && delete_from - How many rows was that anyway?

We wanted to know how many rows were updated when using the update_recordset statement and at first it was a case of attacking the database twice, but with the same where clause. Thus:

LedgerTrans        tbl1;
;
select count(RecId)
from tbl1
where tbl1.EVE_Transferred == EVE_LedgerTransTransferred::PermanentlyExcluded;

print tbl1.RecId;
pause;

update_recordset tbl1
setting EVE_Transferred = EVE_LedgerTransTransferred::ProvisionallyExcluded
where tbl1.EVE_Transferred == EVE_LedgerTransTransferred::PermanentlyExcluded;

We would get the number of rows to be updated from the first statement. However the cleanest way to do this was using the RowCount() method, whose documentation is best described as 'spartan'.

LedgerTrans        tbl1;
;
update_recordset tbl1
setting EVE_Transferred = EVE_LedgerTransTransferred::ProvisionallyExcluded
where tbl1.EVE_Transferred == EVE_LedgerTransTransferred::PermanentlyExcluded;

print tbl1.RowCount();
pause

The trick works both for update_recordset and delete_from statements. Yet again this was a case of reading the MSDN documentation twice over to discover this one - especially the code section.

Sending a plain text file to the Ax server tier

Seems simple enough. The only thing we want to avoid here however is having to use special file I/O permissions writing files to a server directory. Also I couldn't find any special file upload form control but Santosh Singh got there first using the WinApi class.

First lets create a button on the form and override the usual clicked() event:

void clicked()
{
    str                 fileName;
    FileNameFilter      filter      = ['*.txt', '*'];
    BinData             binData     = new BinData();
    Args                args        = new Args();
    ;
    
    super();
    
    // Obtain the path to the file on the client
    fileName = WinApi::getOpenFileName(element.hWnd(), filter, '', 'Upload import file');
    if (binData.loadFile(fileName))
    {
        args.object(binData);
        EVE_CONFileImporter::mainOnServer(args);
    }
}

In the code above we probably should have used the AsciiIo class instead of BinData, for example. That may bite us later as it would simplify things.

We're now to parse the file within a class method, running on the server tier. New lines are supposed to be separated by '\n' but we can use any character to split our text data:

void importErrors(BinData _binData)
{
    TextBuffer  textBuffer  = new TextBuffer();
    str         line;
    int         posEnd = 0, posStart = 0;
    int         nlLen, bufferSize;
    // '\r\n' - Windows : '\n' - *nix : ',' - CSV : '\t' - Tabbed
    #define.newLine('\n') 
    ;
    
    if (!_binData || _binData.size() == 0)
    {
        return false;
    }

    // One Über string
    textBuffer.setText(_binData.getStrData());

    // Super-Size me
    bufferSize      = textBuffer.size();
    nlLen           = strlen(#newLine);

    // Parse the file line by line
    while (posEnd < bufferSize)
    {
        // When you specify the start position for substring, use 1 for the first character in content. MSDN example
        posStart    = (posStart == 0 ? posStart + 1 : posEnd + nlLen);
        textBuffer.find(#newLine,posStart);
        posEnd      = textBuffer.matchPos();
        if (posEnd == 0)
        {   // EOF
            posEnd  = bufferSize;
        }
        line = textBuffer.subStr(posStart, posEnd - posStart - nlLen - 1);
        if (strlen(line) > 0)
        {
            info(line);
        }
    }
}

I had a problem with trimming the end control character from the end of the line so do test the above code for yourself in case you are losing an important number digit from the end of everyone's newly imported pay check!

Obtaining datasource totals, filtered

We would like to see some sub-totals at the top of a form, whose data grid contains all of the data that is unfiltered.

Firstly we have the following method available, but it's not clear to me if it returns to us only the rows loaded in the cache, or if it's the total number of rows over the whole datasource:

Datasource_ds.numberOfRowsLoaded()

The suggestion therefore is to use the SysQuery::countTotal(...) method which has absolutely no documentation whatsoever associated with it. (Furthermore the SysQuery::countLoops(...) contains exactly the same code in my version of Ax 2009. Confusing.)

Next up is applying the query filters for each of the sub-totals to be shown. We need to create a handle to the QueryBuildRange and in my example below apply a filter on the EVE_MembershipStatus enum field.

Now here's my final problem. My form could be 'stand alone' or it could be called from another form and have the datasource automatically filtered, in my case by ProjId - from the Projects table. I've still not worked out how to apply the activeLinking dynamically but for now here is the science:

public void executeQuery()
{
    QueryBuildDataSource    queryBuildDataSource;
    QueryBuildRange         memStatus, callerRng;
    int                     actTot, pendTot, susTot;
    Common                  callerRecord = element.args().record();  
    FieldId                 fIdProjId = fieldnum(ProjTable, ProjId); 
    ;

    queryBuildDataSource    = EVE_ProjCustTable_ds.query().dataSourceTable(tablenum(EVE_ProjCustTable));

    // Create range to filter on
    memStatus   = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(EVE_ProjCustTable, EVE_MembershipStatus));
    memStatus.enabled(true);
    if (callerRecord.RecId && callerRecord.TableId == tablenum(ProjTable))
    {   // Called from a related form (ProjTable).
        callerRng           = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(EVE_ProjCustTable, ProjId));
        callerRng.value     (queryValue(callerRecord.(fIdProjId)));
        callerRng.enabled(true);
    }
    
    // Calculate our totals - This is NOT a 'select count(*)' 
    //   but will iterate over all rows from the server to the client!
    memStatus.value(queryValue(EVE_RegistrationStatus::Active));
    actTot  = SysQuery::countTotal(new QueryRun(EVE_ProjCustTable_ds.query()));

    memStatus.value(queryValue(EVE_RegistrationStatus::Waiting));
    pendTot = SysQuery::countTotal(new QueryRun(EVE_ProjCustTable_ds.query()));
    memStatus.value(queryValue(EVE_RegistrationStatus::Terminate));
    susTot  = SysQuery::countTotal(new QueryRun(EVE_ProjCustTable_ds.query()));
    
    // Present our work
    TotalActTxt.text(strFmt("%1", actTot));
    TotalPenTxt.text(strFmt("%1", pendTot));
    TotalSusTxt.text(strFmt("%1", susTot));

    // Remove previously applied filters
    memStatus.enabled(false);
    if (callerRecord.RecId > 0) callerRng.enabled(false);
    
    super();
}

Put quite simply my form is a little slower now, so I'd use a different approach (select count(RecId)...) for bigger record sets! Finally the example code above is executed within the executeQuery() method and so the sub-totals won't get updated when new rows are added or existing ones modified (hint: overwrite the x_ds.delete() and x_ds.write() methods).

Edit: SysQuery::countTotal doesn't work for temporary datasources.  See here.

The FtpWebRequest class

Requirement: The client needs us to send two files via FTP during the process of synchronizing the General Ledger between Axapta and their own propriatary system.

Once more axaptapedia.com saves the day. This time pointing us to the FtpWebRequest class in the .NET framework inside Dynamics AX. The Axaptapedia page is complete except for one wafer-thin omission... Exception handling.

Let me save your time and eyes and tell you now that it is of the type Exception::CLRError, and I've also converted the Job to a server static function, below and added the necessary permissions. Here comes the science if you wish to continue:

/// http://www.axaptapedia.com/FtpWebRequest
/// Params:
/// _inputFile:         "C:/tmpServer/test.xml"
/// _destinationFile:   "ftp://123.234.34.45/BD01/test.xml"
/// _user:              "user"
/// _password:          "pass"
/// _useProxy:          false
public client server static str uploadFileFTP(str _inputFile,     str _destinationFile,
                                str _user,          str _password,
                                boolean _useProxy = false)
{
    str                         retVal;
    object                      ftpo;
    object                      ftpResponse;

    InteropPermission           permI         = new InteropPermission(InteropKind::ClrInterop);
    FileIOPermission            permIO        = new FileIOPermission(_inputFile,'r');
    Set                         permissionSet = new Set(Types::Class);

    System.Net.FtpWebRequest    request;
    System.IO.StreamReader      reader;
    System.IO.Stream            requestStream;
    System.Byte[]               bytes;
    System.Net.NetworkCredential credential;
    System.String               xmlContent;
    System.Text.Encoding        utf8;

    System.Net.FtpWebResponse   response;

    System.Exception            netExcepn;
    ;

    try
    {
        // Permissions
        permissionSet.add(permI);
        permissionSet.add(permIO);
        CodeAccessPermission::assertMultiple(permissionSet);

        // Read file
        reader  = new System.IO.StreamReader(_inputFile);
        utf8    = System.Text.Encoding::get_UTF8();
        bytes   = utf8.GetBytes( reader.ReadToEnd() );
        reader.Close();

        // little workaround to get around the casting in .NET
        ftpo    = System.Net.WebRequest::Create(_destinationFile);
        request = ftpo;

        credential = new System.Net.NetworkCredential(_user, _password);
        request.set_Credentials(credential);
        request.set_ContentLength(bytes.get_Length());
        request.set_Method("STOR");

        if (_useProxy)
        {   // "Bypass" a HTTP Proxy (FTP transfer through a proxy causes an exception)
            request.set_Proxy( System.Net.GlobalProxySelection::GetEmptyWebProxy() );
        }

        requestStream = request.GetRequestStream();
        requestStream.Write(bytes,0,bytes.get_Length());
        requestStream.Close();

        ftpResponse = request.GetResponse();
        response = ftpResponse;

    }
    catch (Exception::CLRError)
    {
        netExcepn = CLRInterop::getLastException();
        error(netExcepn.ToString());
    }
    catch
    {   // Error desconocido / Unknown error
        error("@SYS83461");
    }

    if (response)
    {
        // info(response.get_StatusDescription());
        retVal = response.get_StatusDescription();
    }
    return retVal;
}

A few of my own recommendations

• As a stinky consultant that works on the client site, in a bank, wearing a tie that sends you screaming out of the door, I have very limited rights to my machine and I can't install services. I can therefore recommend WarFTP as a standalone executable FTP Server for all of your testing needs. Out of the box it comes as pretty locked down so read the faq when you can't work out why the new user you just created can't use the service.
• Execute on the server. Not only is it going to be faster generating our export file from the General Ledger but we'll have only one source of problems instead of the 'n' client machines out there each with their own firewall or McAfee Fascist settings.
• Use the following snippet to obtain the executing computer's temporary directory: (isRunningOnServer() ? WinAPIServer::getTempPath() :WinAPI::getTempPath()).

Finally this whole exercise was in my case for naught as we later discovered that the client was using Trivial File Transfer Protocol [1] [2]! I think my next post should be on how to execute a batch file from within Axapta to do all of the above but with a special FTP client...