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!?

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.

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.