viernes, 18 de noviembre de 2011

Excel Macro to the Rescue!

I'm currently importing fixed assets into the system from an Excel spreadsheet. This is one of the reasons why I like developing for ERPs such as Ax 2009 as I find both the data itself and the process of importing the data very interesting. After learning X++ I started to love learning about how a company 'works'.

The process of exporting/importing data is undertaken via the Administration area, Period panel. The functionality that it offers us is both scary and wonderful. We're exporting Journal lines (adquiring fixed assets) and updating their dates of adquisition to those of an open financial year. The oldest asset we have was bought in 1942!

I'll not document the whole process here, but in the exported Excel document we has data from the LedgerJournalTrans and LedgerJournalTrans_Asset entities, filtered by a Daily identifier. We create a new daily in the production system and then need to update the relevant field in the Excel spreadsheet to correspond with the new identifier. The problem was that in the Excel spreadsheet the LedgerJournalTrans_Asset.RefRecId field was effectively a numeric value in a text formatted column.
I couldn't just update the initial cell and then drag down the value (incrementing by one for each new cell) to the bottom of the sheet.  The column was formatted as text.  I tried doing this in a new numeric column and when I posted the values (without formatting) on top I had difficulty importing the sheets again into the system.  I think it had something to do with how the numbers were presented (I maybe changed the formatting of the cell) and have still not divined the reason.  In the end I resorted to an Excel Macro to update my values, starting from the 'A7' cell:
Sub SelectNextTextNumValue()
'
' SelectNextTextNumValue Macro
' Increase the LedgerJournalTrans_Asset.RefRecId for each row
'
    Dim continue As Boolean
    Dim iCnt As Integer
    Dim sRec As String
    Dim dRec As Double    
     
    iCnt = 1
    continue = True
    sRec = "1638000000"
    
    Range("A7").Select

    Do While continue = True
        ActiveCell.Offset(1, 0).Range("A1").Select
        If ActiveCell.FormulaR1C1 = "" Then
            continue = False
        Else
            dRec = CDbl(sRec)
            dRec = dRec + 1
            sRec = CStr(dRec)
            ActiveCell.FormulaR1C1 = CStr(sRec)        
        End If
        iCnt = iCnt + 1
    Loop

End Sub
Note: Just had to buy the other people using the Ax development instance a coffee as the import appears to hang everyone elses client until it had finished. It's not my fault!?

viernes, 11 de noviembre de 2011

Operation HelpText

Just received a real swine of an email from the boss.  We have in our Extended Data Type (EDT) lots of entities that have the Label type property but with no HelpText.  What this means is that in some forms as the user tabs from field to field those without a HelpText value will continue showing the previous EDT's value in the form's status bar at the bottom.  It's a bug in my opinion and the status bar should surely be empty.

I started manually working at this, merely copying the Label to the HelpText property value.  We should really be careful here as some EDTs are used interchangeably, between Vendors and Customers for example, so you have been warned.
As you can see, I started manually stepping through our work updating stuff as we go...  Tedious. 
I know, why don't we iterate over the AOT and find these babies using X++!

static void listEDTWithoutHelpText(Args _args)
{
    #TreeNodeSysNodeType
    TreeNode              edtNode   = TreeNode::findNode("Data Dictionary\\Extended Data Types");
    TreeNodeIterator      ir        = edtNode.AOTiterator();
    TreeNode              childNode;
    str                   label;
    ;
    if (ir)
    {
        childNode = ir.next();
        while (childnode)
        {
            // Check for:
            //   Has a label, Not extended from another entity, (Below VAR/USR), No HelpText.
            label = childNode.AOTgetProperty("label");
            if (label
                && !childNode.AOTgetProperty("extends")
                && (!strStartsWith(label, "@SYS")         // Modify this stuff yourself!
                    && !strStartsWith(label, "@SYP")      // Modify
                    && !strStartsWith(label, "@ESP"))     // Modify
                && !childNode.AOTgetProperty("HelpText"))
            {
               info(strfmt("EDT :%1, %2", childNode.AOTname(), childNode.AOTgetProperty("label")));
            }

            childNode = ir.next();
        }
    }
}


We're nearly there.  We need to de the same with Base Enum types, and also there is an omission above in so much that if we use some @SYS labels in our own entities, such as 'Name', then they won't be caught.
Now we need to pass the list to the junior developer to fix :)

EDIT*I* needed to fix it :(

Below is code listing the Base Enum types, but adding them to a project as well! Update the 'Project2' value before running.

static void listBaseEnumsWithoutHelp(Args _args)
{
    #define.projNameToAddEDTs ('Project2')
    #TreeNodeSysNodeType
    TreeNode              edtNode   = TreeNode::findNode("Data Dictionary\\Base Enums");
    TreeNodeIterator      ir        = edtNode.AOTiterator();
    TreeNode              childNode;
    str                   label;
    ProjectNode              projNode;

    void findProjNode(str _projName)
    {
        ProjectListNode       listp  = infolog.projectRootNode().AOTfindChild("Shared");
        TreeNodeIterator      irp    = listp.AOTiterator();
        ;
        if (irp)
        {
            projNode = irp.next();
            while (projNode)
            {
                if (projNode.AOTgetNodeType() == #NT_PROJECT_NODE
                    && projNode.AOTname() == _projName)
                {
                   return;
                }
                projNode = irp.next();
            }
        }
        // Not found
        projNode = null;
    }
    ;
    if (ir)
    {
        findProjNode(#projNameToAddEDTs);

        childNode = ir.next();
        while (childnode)
        {
            label = childNode.AOTgetProperty("label");
            if (label
                //&& !childNode.AOTgetProperty("extends")
                && (!strStartsWith(label, "@SYS")         
                    && !strStartsWith(label, "@SYP")      
                    && !strStartsWith(label, "@ESP"))     
                && !childNode.AOTgetProperty("Help")) //HelpText
            {
                info(strfmt("ENUM :%1, %2", childNode.AOTname(), childNode.AOTgetProperty("label")));
                if (projNode)
                {
                    projNode.addNode(childNode);
                }
            }

            childNode = ir.next();
        }
    }
}

All in all a far less painful update process now.

domingo, 6 de noviembre de 2011

Select Statement with an Outer Join (note)

The join statement is a source of initial head scratching for most developers familiar with the ANSI sql script.  MSDN has a useful example on a standard join statement, where the order by clause is in a strange place for most of us - after the from keyword but before the join.  We have no on in the psuedo sql used in X++.

It's a similar issue for outer join, but I was caught out with the where keyword placement as well.  The Ax 2012 example of the outer join is below, translated to Ax 2009 and I've added a second where keyword in there as well.  The reason that I've highlighted this was originally I had the salesTable.PaymSpec == '' clause at the bottom of the query and therefore returning all rows from SalesTable. To me it seemed so wierd at first to see two where keywords in there! 

static void Job10(Args _args)
{
    SalesTable      salesTable;
    SalesLine       salesTableLine;
    struct struct4;
    ;
    struct4 = new struct
        ("str SalesID;"
        + "date DeliveryDate;"
        + "real LineNum;"
        + "real SalesQty"
        );

    while
    SELECT
            *
        FROM
            salesTable
        WHERE
            salesTable.PaymSpec == ''
    OUTER JOIN salesTableLine
        WHERE
            salesTable.SalesID == salesTableLine.SalesID
            && salesTableLine.SalesQty < 0
    {
        struct4.value("SalesID", salesTable.SalesID);
        struct4.value("DeliveryDate", salesTable.DeliveryDate);
        struct4.value("LineNum", salesTableLine.LineNum);
        struct4.value("SalesQty", salesTableLine.SalesQty);
        info(struct4.toString());
    }
}

Now all you people need to do is think about where an order by will go...  Yep, above the where clause.

domingo, 30 de octubre de 2011

The 'Washed' Layer

When opening up the Compare tool on a modified object we are able to compare the sys layer with our modifications in the var or usr layer, for example. 


There is another option available called the washed layer, as explained from Inside Microsoft Dynamics™ AX 4.0:

A few simple best practice issues can be resolved automatically by a best practice “wash.” Selecting the washed version shows you how your implementation differs from best practices. To get the full benefit of this, select the Case Sensitive check box on the Advanced tab.


Note that if it is an object that we created and is not part of the original framework then the Washed option will be the only one available!  We usually prepend the company name or other three character letters to all objects that we create in the AOT and so I was confused at first why my object had no sys layer.

martes, 25 de octubre de 2011

Google -> MSDN

If like me you are at times lost floating in the ocean of Ax you will find the Google/Bing search engines extremely useful for answering your doubts.  By prepending 'axapta msdn' to the queries (the 'ax' term isn't as good, imho) we can usually jump straight into the API we are looking for.  One of the frustrating things with this approach, however, is that you are probably using a different version of Ax than the page that we have landed on.  The class structure hasn't changed too much it's true but we have more confidence in reading the correct version of the documentation.  Searching for ReportSection for example we could land in any of the following:
My understanding is that AX 2012 is now 'released' and I think it's a pity that we can't just change to a 'aa621385(AX.60).aspx' to jump straight into AX 2012 should we want to...  A lot of the links I select from Google go straight to Axapta 4.0.

As a final note MSDN has this 'Language Filter' option which is useless as we are all X++ gurus.  I wonder if it would be far more useful to be able to select a filter on the Ax version that you work with, where a redirection occurs when we first arrive should the equivalent page exist.

lunes, 17 de octubre de 2011

Error 112


Amusingly I've just read someone else's blog post about the reason for error 112...  You can never find blog posts the second time around, can you?  So in the end it looks like at least two of us on the planet have just run out of hard disk space!  It could also be caused by having read-only access to the AOT directory.

viernes, 14 de octubre de 2011

localmacro insanity

Sometimes writing a piece of legible and easily understood code just isn't enough. We have to obfuscate it all in the name of reusability. We have the Business Relationship form, which is as permissive an entity as you can get, and needed to 'separate' it between two types of relationships - legal and people. The client will see two designs and consequently will require two sets of business rules such as obligatory mandatory fields. All this of course generated from the same form.

Creating mandatory form fields on-the-fly requires us to both set the mandatory field to true on the datasource as well as physically adding code to the validateWrite method (link). For our particular use case we did this at form level rather than at the smmBusRelTable entity as we are bulk importing over 150,000 rows and the form will be the only point of access to the data. Despite all of that we must always try to put the buisiness logic at the table level if at all possible.

First of all in the init method of the form we could write the following piece of code:
smmBusRelTable_ds.object(dt.fieldName2Id('Name')).mandatory(true);
We would then repeat the code for the other fields depending upon the relationship type. However... We could use an inner function which would then avoid some repetition and would not confuse unfamiliar developers to the code base:
    DictTable dt = new DictTable(smmBusRelTable.TableId);
    
    void mandatory(str fieldName)
    {
        smmBusRelTable_ds.object(dt.fieldName2Id(fieldName)).mandatory(true);
    }
    ;
    
    switch (personTypeParam)
    {
        case EVE_PersonType::Organization:
            mandatory('Name');
            mandatory('NameAlias');
            mandatory('Phone');
            mandatory('EVE_DocumentType');
            mandatory('EVE_DocumentId');
            mandatory('EVE_DUPCI');
        break;

        case EVE_PersonType::Person:
            // More fields
        break;
    }
That isn't actually too bad. However as I indicated before we now need to perform checks in the validateWrite part of the form's smmBusRelTable datasource. It's not a real problem in my case as there are only a few fields but what about using the power of macros?

The next piece of code is in my opinion less legible/readable and we won't be able to debug/step through it, but I do like it! Lets throw away the previous code and start over. Firstly we've declared the field list of obligatory mandatory in the ClassDeclaration:
    // EVE_PersonType::Organization list of mandatory fields
    #localmacro.Organization
        %1(Name)
        %1(NameAlias)
        %1(Phone)
        %1(EVE_DocumentType)
        %1(EVE_DocumentId)
        %1(EVE_DUPCI)
    #endmacro

    // EVE_PersonType::Person list of mandatory fields
    #localmacro.Person
        %1(EVE_Name)
        %1(EVE_MiddleName)
        %1(EVE_Surname)
        %1(EVE_BirthDate)
        %1(EVE_Gender)
        %1(EVE_DocumentType)
        %1(EVE_DocumentId)
        %1(EVE_DUPCI)
        %1(Phone)
    #endmacro
You will notice an additional %1 and parenthesis around the field names. We'll be calling each of those fields with a function name passed in as an argument. When the #Person macro appears in our code we will be repeatingly call '%1(fieldName)' in the code for each line.

The form's init method will now call the below:
void setObligatoryControls()
{
    DictTable dt = new DictTable(smmBusRelTable.TableId);

    // Mandatory function fragment
    #localmacro.mandatory
        smmBusRelTable_ds.object(dt.fieldName2Id('%1')).mandatory(true);
    #endmacro
    ;

    switch (personType)
    {
        case EVE_PersonType::Organization:
            #Organization(#mandatory)
        break;

        case EVE_PersonType::Person:
            #Person(#mandatory)
        break;
    }
}
It's a similar approach within the validateWrite method:
public boolean validateWrite()
{
    boolean         ret;
    DictTable       dt = new DictTable(smmBusRelTable.TableId);
    
    // Mandatory function fragment
    #localmacro.mandatory
        if (!smmBusRelTable.%1)
        {   //Se debe rellenar el campo %1.
            ret = checkFailed(strfmt("@SYS110217", dt.fieldObject(dt.fieldName2Id('%1')).label()));
        }
    #endmacro
    ;

    ret = super();

    if (ret)
    {
        switch (personType)
        {
            case EVE_PersonType::Organization:
                #Organization(#mandatory)
            break;

            case EVE_PersonType::Person:
                #Person(#mandatory)
            break;
        }
    }

    return ret;
}
To get your head around it all, try reading the article in Axaptapedia that gave me the idea.

lunes, 10 de octubre de 2011

SELECT DISTINCT fieldId FROM tableId ORDER BY fieldId

The DISTINCT clause doesn't happen in Ax 2009 and earlier, maybe niether in the 2012 version. There is however a trick to obtain this funcionality.

Query getDistinctQuery(TableId _tableId, FieldId _fieldId)
{
    Query                   query;
    QueryBuildDataSource    qbdsUtilElements;
    QueryBuildFieldList     qbdsFieldList;
    ;

    query = new Query();
    qbdsUtilElements = query.addDataSource(_tableId);
    qbdsUtilElements.update(false);
    //qbdsUtilElements.addGroupByField(_fieldId);
    qbdsUtilElements.addSortField(_fieldId);
    qbdsUtilElements.orderMode(orderMode::GroupBy);
    qbdsFieldList = qbdsUtilElements.fields();
    qbdsFieldList.dynamic(false);
    qbdsFieldList.clearFieldList();        // >> COMPLETE WIERDENESS
    qbdsUtilElements.addSelectionField(_fieldId, SelectionField::Max);
    return query;
}
The SelectionField::Database doesn't work (nor do we know what it does) and I'm not really sure why we have to add the SelectionField enum in the first place. Without that optional SelectionField enum second parameter for some reason when the query is run and all of the table fields appear to be readded to the sql and we don't get the desired result.

Obviously the above function can be changed to include more fields.


Edit: 23/02/2012 
I'm revisiting this with a second example that does not require the above addSelectionField statement.

Below is the SQL that the query example will generate:
SELECT JournalNum, AccountNum, Due, PaymMode, TransDate, CurrencyCode 
FROM LedgerJournalTrans 
WHERE JournalNum = N'000004_008'
GROUP BY LedgerJournalTrans.JournalNum, LedgerJournalTrans.AccountNum, LedgerJournalTrans.Due, LedgerJournalTrans.PaymMode, LedgerJournalTrans.TransDate, LedgerJournalTrans.CurrencyCode

And the query example code:
    Query                   qry;
    QueryBuildDataSource    qbr1;
    QueryBuildRange         range;
    QueryBuildFieldList     qbdsFieldList;
    ;
    
    qry = new Query();
    qbr1 = qry.addDataSource(tablenum(LedgerJournalTrans));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, JournalNum));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, AccountNum));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, Due));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, PaymMode));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, TransDate));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, CurrencyCode));

    range = qry.dataSourceTable(tablenum(LedgerJournalTrans)).findRange(fieldNum(LedgerJournalTrans, JournalNum));
    if (!range)
        range = qry.dataSourceTable(tablenum(LedgerJournalTrans)).addRange(fieldNum(LedgerJournalTrans, JournalNum));
    range.value('000004_08');

    qbdsFieldList = qbr1.fields();
    qbdsFieldList.dynamic(false);
    qbdsFieldList.clearFieldList();
    
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, JournalNum));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, AccountNum));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, Due));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, PaymMode));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, TransDate));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, CurrencyCode));

    //element.query(qry);

    return qry;


Edit: 18/09/2014 
Finally let us not forget the quick-to-implement yet inefficient-to-run trick of using a collection class for holding unique values, a Set, to check if we have already treated the entity during a loop.

static void ACT_UniqueTest(Args _args)
{
    Set                 stAccountNums = new Set (Types::String);
    LedgerJournalTrans  ledgerJournalTrans;
    ;
    
    while select ledgerJournalTrans 
        where ledgerJournalTrans.AccountType == LedgerJournalACType::Cust
            && ledgerJournalTrans.JournalNum == '000002_017'
    {
        if (stAccountNums.in(ledgerJournalTrans.AccountNum))
            continue;
        stAccountNums.add(ledgerJournalTrans.AccountNum);

        info(strFmt('%1 - %2', 
            ledgerJournalTrans.AccountNum,
            CustTable::blocked(ledgerJournalTrans.AccountNum)));
    }
}

miércoles, 5 de octubre de 2011

I *really* wanted that 'two-column-50%' feeling

MorphX is great for throwing some controls on a form and and presenting the information from the database table.  If however the form is complex or will be one of the main forms used within the application it is well worth taking a few minutes out to present the information in a beter format...  Did I say a few minutes?


The first attempt had controls with their text cut off and all presented in 2 columns with a third effectively empty.  What I created was two Groups, their Frame set to None, and manually fill each one with controls or more sub-groups so that they balance out in total height of control content.  A little 'meh' but it will do.  Next I manually set their Width properties to 0 (changing the WidthMode, see here).  We will be hitting the X++ to next set their width values. I also set the General tab's ColumnSpace value to 10, and it's LeftMargin + RightMargin to a value of 5. The Columns value is set to 2 of course. Everything has it's AutoDeclaration set to Yes of course.


What I ended up doing was going to the land of WinAPI and attempt to get a handle on the General tab's rectangle dimensions - see the getWindowRect API and the RECT object it returns.  From there it was a case of some simple math, thus:

public void run()
{
    int            iWidth, x, y, k, l;
    ;

    SysListPageHelper::handleRunPreSuper(element, ctrlTabForm, 3, 2);
    super();
    
    [x, y, k, l]    = WinApi::getWindowRect(General.hWnd());
    iWidth          = (k - x
                        - 20 // General.ColumnSpace + General.LeftMargin + General.RightMargin
                        - 20 // Magic fudge factor
                        ) / 2;
    
    GeneralGrpCol1.width(iWidth);
    GeneralGrpCol2.width(iWidth);

    SysListPageHelper::handleRunPostSuper(element, ctrlTabForm);
}
It's not perfect, and I shall have to test it some more but it's far better than before!  As you can see from the above code there is a fudge factor which means that I'm missing something in the total width calculation - any suggestions?


Another example of the WinAPI::getWindowRect can be seen in a blog post capturing Ax form screenshots.  Finally, as we're in WinAPI land, I refer you to a useful post as to how to obtain screen sizes and the like.

All of this work simply because I was a web developer where everything on the page can be designed to 'float' around each other... In conclusion then I DON'T recommend this approach!

sábado, 1 de octubre de 2011

E-Learning suffrance

As part of my personal brain training with Ax 2009 I started off with reading the Microsoft Official Training Materials for Microsoft Dynamics® - more specifically the 80011* DEVeloper material they had. It was a great kick-off point by sitting down for 4 days and working through the first two or three modules that they offered. With copious amounts of head scratching, coffee and crying on the course tutor's shoulder we learnt a lot in a little time.

Now however after 6 months I'm an expert... And those of you who have been working in the ERP industry for last 20 will know how much of a joke that last sentence is. Therefore I applied myself to the Best Practices White Paper for Ax 2009. It's a popular resource related to developing with the product and well worth a perusal. It really should be done and dusted if you intend to write MorphX code as part of your career.

Next up was to broaden my horizons and get a better grasp of the individual modules available. The E-Learning catalogue provides an interesting offering of resources to the training materials available in pdf, and also in electronic plus interactive format. Furthermore you can see what modules you have completed and those still available. Best yet your boss can see how you are progressing with that fascinating multi-company, multi-currency Financials module. While having paper in my hand is always best I enjoyed sitting at my desk at work plodding through the catalogues available, sound off (the text is read out by a clear speaking American lady but I just can't handle having to listen to more than one woman telling me what to do in my life, thanks), and then click away at the Next button until the mini-tests arrive. My theory is that 50% of the difficulty of the exams vary more depending upon your own experience with ERP than the product itself.

And now it's all gone (for me). I had access via Customer Source but now I'm left with only my company's Partner Source but with no access to the E-Learning catalogue. I get the following message in a page with 240+ down votes:

You do not have unlimited organizational access to E-Learning for Microsoft Dynamics and related business products.

Enroll in a Partner Service Plan today to receive access!
Sadness.

jueves, 22 de septiembre de 2011

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.

sábado, 17 de septiembre de 2011

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.

jueves, 15 de septiembre de 2011

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!

martes, 13 de septiembre de 2011

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.

jueves, 1 de septiembre de 2011

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

jueves, 25 de agosto de 2011

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.

sábado, 20 de agosto de 2011

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];

lunes, 15 de agosto de 2011

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;
}

miércoles, 10 de agosto de 2011

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.

viernes, 5 de agosto de 2011

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.

martes, 2 de agosto de 2011

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

lunes, 25 de julio de 2011

A null value not allowed has been selected in the database.

I'm getting the error:
No se puede seleccionar un registro en Proyectos (ProjTable).
Se ha seleccionado un valor NULL no permitido en la base de datos.
When we have a null value selected from the database, the table buffer is unable to show the values to the form, nor the table view in the AOT. Here is what I found in SQLServer:
In my case the answer was easy - these are string fields so I could edit them directly in the db.
UPDATE [DynamicsAXDev].[dbo].[PROJTABLE]
   SET [EVE_PROJWFSENTCOMMENT] = ''
 WHERE [EVE_PROJWFSENTCOMMENT] is null;
I'm wondering if it was because I'm importing records using an 'old' ProjTable as my colleague informs me that he added a new field to the table recently. 

If you have a large table with both many columns and rows then you will need to use some SQL and Select columns with NULL values only. I'll copy the code here to make me look intelligent:
declare @col varchar(255), @cmd varchar(max) 
DECLARE @TableName Varchar(100) 
SET @TableName='ProjTable' 

DECLARE getinfo cursor for 
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID 
WHERE t.Name = @TableName 
 
OPEN getinfo 
 
FETCH NEXT FROM getinfo into @col 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ' + @TableName + ' WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end' 
    EXEC(@cmd) 
 
    FETCH NEXT FROM getinfo into @col 
END 
 
CLOSE getinfo 
DEALLOCATE getinfo
Do check out MSDN's Null Values for Data Types article as to what to insert into these evil null value fields when they appear.

Even after performing all of the above I was still receiving the error message. It was time to close and reopen my Ax client.

miércoles, 20 de julio de 2011

Dialog class and the 'Next' big thing

Many effects are very easy to achieve in Ax 2009 but others leave us searching the interweb for a solution.  We needed a 'Next' (or 'Continue') and 'Cancel' button in a simple message box with instructions.  For some reason the standard 'Ok' button text was not sufficient. 

   // Dialog with instructions
    Dialog                          dialog;
    FormBuildCommandButtonControl   okButton;
    #define.okButton('OkButton')    //See Dialog class
    ;
    
    dialog = new Dialog("@SYS76421");

    // Instructions, Instructions, Instructions
    dialog.addText("@EVE580");
    okButton = dialog.formBuildDesign().control(#okButton);
    if (okButton)
    {
        // Select Next to resume (EN) / Selecciona Siguiente para continuar (ES)
        okButton.helpText("@EVE581");
        // Next (EN) / Siguiente (ES)
        okButton.text("@SYS6441");
        okButton.defaultButton(true);
    }

    dialog.run();
    if (dialog.closedOK())
    {
        Box::info("We closedOK!");
        return;
    } else {
        return;
    }

As always, someone cleverer than me was able to grab a handle to what was going on.  In my version of Axapta the commented code in the Dialog.initButtons() function will get you on your way to get a handle on those Dialog buttons.

viernes, 15 de julio de 2011

Enumerator value, it's symbol

I stumbled across an useful tid-bit of code on the web yesterday, courtesy of Preston A. Larimer.
/// enumName('valueCriteria', valueCriteria::Max) returns Max
static str enumName(str _Enum, anyType _enumValue)
{
   DictEnum dictEnum;
   ;
   dictEnum = new DictEnum(xutilelements::getNode(
       xutilelements::find(utilelementtype::Enum,_Enum)).applObjectId());

   return dictEnum.index2Symbol(_enumValue);
}
It took me so long to find as I was searching for the 'value' field but no matter what I did I was receiving the asociated translated text - ValueCriteria ⇒ "Maximum" in the above ejample.

There are no checks, however, so add them for empty parameter values.

Edit:  Ohh I just found a similar post where someone else not only got there first, but they arrived in a less sweaty state as well.
strfmt("%1", enum2symbol(enumnum(WorkTimeControl), workTimeControl));

domingo, 10 de julio de 2011

Button hell

You've been asked to modify an entity such as that the purchase order or projects tables/forms. Part of the changes implies that you have to disable certain functionality and/or input controls on the form. Hey, it's nice to have this level of detail in the client requirements document isn't it?

First of all we found that adding new fields and related helper tables was easy. It's one of the advantages of Ax 2009 that we're getting quite used to when compared to other frameworks.

Implementing the 'side effects' was a little more strenuous, such as when a project is cancelled we had to apply checks to see if there are any open purchase orders beforehand or we're required to deactivate any associated budget/predicted costs.

Now finally the easy part, deactivating the buttons or sub-menu options on the form... 

Eeek!?

It's not as trivial as you first imagine. And it takes getting used to. Let's take a look at the ProjTable form. We have two methods for updating the buttons and the form input controls, setButtonAccess and setFieldAccess. As the form is so complex they've logically split up the work within setButtonAccess into sub groups such as setButtonInvoice and setButtonFunction. But it's what each one of these then do which takes a little more time to get to grips with. Each method will instantiate the same class and use a method to control the button's enabled or visibility status, thus:
    ProjStatusType projStatusType = projTable.status();
    ;
    ctrlInvoices.enabled(projStatusType.ctrlInvoices());
It's a helper class that we really should familiarize ourselves with and some of its functionality is used to enable or disable the Invoice control, depending upon the project. It's a little complex; however, as we have both project stages and project types to contend with. Here is a simplistic view of these classes:
The ProjStatusType class (which is constructed from a sub class such as ProjStatusType_Created or ProjStatusType_ReportedFinished) instanciates the ProjTableType class which is actually constructed from a subclass such as ProjTableType_Internal or ProjTableType_Cost.  In my brain it took 5 minutes to understand plus another 10 as the Morph X editor is not the most friendly environment to work in.  So when we call the ctrlInvoices() method we're possibly calling a method in ProjStatusType or ProjStatusType_Created or ProjTableType or ProjTableType_Internal, we'll have to go and investigate each class each time we need to change the business logic on each button or input control.  Eeek!  This is exactly what object orientated design is about, however.

When we start, we may just IGNORE the helper class and implement a simple switch statement on the ProjTable_ds.active() method like the pseudo code in the following one and then go for a coffee happy in the knowledge that we've finished ahead of schedule:
    switch (projTable.Status)
    {
        case ProjStatus::Approved :
            CtrlProjValidateSetupEmpl.enabled(projTable.Type != ProjType::Internal);
            CtrlProjValidateSetupCategory.enabled(projTable.Type != ProjType::Internal);
            CtrlPosting.enabled(true);
            CtrlExternal.enabled(true);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(false);
            break;

        case ProjStatus::PendingApproval :
        case ProjStatus::Created :
            CtrlProjValidateSetupEmpl.enabled(projTable.Type == ProjType::Internal);
            CtrlProjValidateSetupCategory.enabled(projTable.Type == ProjType::Internal);
            CtrlPosting.enabled(false);
            CtrlExternal.enabled(false);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(true);
            break;

        case ProjStatus::Canceled :
            CtrlProjValidateSetupEmpl.enabled(false);
            CtrlProjValidateSetupCategory.enabled(false);
            CtrlPosting.enabled(false);
            CtrlActivity.enabled(false);
            CtrlExternal.enabled(false);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(false);
            break;

        default :
            CtrlProjValidateSetupEmpl.enabled(false);
            CtrlProjValidateSetupCategory.enabled(false);
            CtrlPosting.enabled(false);
            CtrlActivity.enabled(false);
            CtrlExternal.enabled(false);
            projTable_ds.object(fieldnum(ProjTable, Dimension)).enabled(false);
            break;
    }
Easy to understand isn't it? Took us a few minutes and no one else ever looks at our code anyway, right?

Problemo, as they say in Spanish.  It's not that maintainable for others, or ourselves in the long term, plus what happens if we have changed the ctrlInvoice button on the ProjTable form but not from other references to the project table.  These classes are used not only on this particular form but all over the place and if we took just a little effort to modify them to the clients requirement we will be pleasantly surprised to find out that we can't create an invoice for a closed project associated with a supplier as we modified the helper class method in that other form.  Less buggy code, and our original ProjTable form is still relatively clean without that switch statement.

Adding a new method to deactivate a button using this technique requires at the very least new methods to be created in two different classes, more so if we are applying business logic to various project types and statuses in use.

Developer tip: Use the ClipX application for those repetitive copy/paste operations. I can also recommend listening to some really racy music to make your life seem 'not so bad' and pass your time away.

Finally another advantage of using this approach is that after we've wired in the class to each button we can recompile the class and change the business logic without having to recompile/restore the original ProjTable form.  Sweet!

And then suddenly it's all broken.

There's an option at the top of the project form to include sub-projects. Our classes and inheritance applied to our buttons is suddenly rendered useless when we have a project that's marked as closed but has a child project that is in progress.... The code just marks the visibility/enabled option as true rather than trying to recurse though the sub-projects checking for their status.  All of our helper classes' architecture aren't wired up to recurse through the child projects.  Bah, humbug.

I also see in the code that the management of the buttons (setButtonAccess) is handled in a different function to that of the enabling/disabling of the fields (setFieldAccess). Fine, but... The two functions are not called from the same parent functions which seems wrong to me. setButtonAccess Is called when we requery the form, change record in the form or change the selected record's status while setFieldAccess called when we change or update the record in the form. I don't see why these to functionalities are launched at different times in the form life cycle.

In conclusion

Many of these complex forms use these helper classes.  After a little effort they do make our life easier and create less buggy code.  Take a little time to learn them and investigate what they do.