Ax's white space aversion

I can think of a few places where I would like to output some nice white space to the end user, cushioning their existence from the vagaries of life...  But Ax 2009 isn't letting me:

• In table fields. I can confirm that trailing spaces are removed, which is a shame when you want to store pre generated rows of data that are to be exported to plain text files, in a host system that will then parse fixed length fields, padded with either zeros or white space.
• In the label text. Try as I might, that important white space from the right is cruelly taken away from me. My formatting and design of Ax forms is taken to new heights of frustration and anger. Well, perhaps I exaggerate a little after all we should be changing the margin fields and not manually adding spaces to the labels after the full colon.
• Directly in the editor, the white space is removed from the end of the lines.  It doesn't interfere normally, but when using the @ operator for strings it can upset your precious formatting.

Use the \n in strings to create a new line... And use labels.

I've discovered this aversion to trailing white space from Microsoft years ago ever since my Hotmail account's password used to have a trailing white space in it... And now it doesn't any more. Same password but minus the w/s. Is white space actually dangerous?

Problem of the slow loading customer invoice journal

The title makes this blog post sound like a Scooby Doo caper.

With the GLP-EE (Eastern Europe) patch installed in an Ax 2009 SP 1 instance we found that the customer invoice journal was loading slower and slower.  The form was nothing short of painful.  It seemed to be okay when working with invoices from Spain, Morocco, Italy...

After restarting both Ax and MSSQLServer instances it was time to roll my sleeves up and investigate with the SQL Server Profiler.  While you and I may still be an amateur with the tool a quick perusal of the output identifies the problem query.  After the restart of Ax I relaunched the profiler and immediately opened the invoice journal.  Finding the problematic sql can be difficult due to the use of sp_cursorfetch, hiding from us the original query.  The sp_cursorprepexec is eventually identified, and we can see the sql used in the cursor that is being reused all the time.

Next up was shouting at the silicon gods and then creating a new table index (CustInvoiceTrans) within Ax, to include a new field introduced from the aforementioned patch - RefReturnInvoiceTrans_W.

Maybe should have just added the field to the InvoiceIdx, huh?

With our new index we can compare query times and the difference is huge.

All that I can say is that Microsoft owes me a beer.

Bug: Version Control

Not using version control on your code is like playing Russian Roulette over the whole lifetime of your project.  You might not 'lose' anything at first, but later on you may regret the omission.

I had a strange bug upon opening the Ax Client related the to the inbuilt version control system, and didn't know where it came from

Translation: Error

A quick look in the logs, indicated the cause of the problem...

   Object Server 01:  
   [Microsoft][SQL Native Client][SQL Server]El nombre de columna 'MODIFIEDBY' no es válido. 
   INSERT INTO SYSVERSIONCONTROLMORPHXITE2541 (ITEMPATH,MODIFIEDBY,CREATEDBY,RECVERSION,RECID) OUTPUT INSERTED.CREATEDDATETIME VALUES (?,?,?,?,?) 

What fool did this then?...  Me!

For some reason we had added the ModifiedBy, ModifiedDatetime, etc, etc fields to the entity!  I may have done this accidentally when I added the fields to entities such as LedgerTable, TaxTable, TaxData...  Some of them had been added to the version control, and some of them were not.

Adjusting Sales/Purchase Tax

When importing invoices from an external system you find penny differences between how tax is calculated between these two applications.  Integrating thousands of invoices can surmount to a whole free beer a day appearing on your ledger books, much to the disdain of our client's accountant.  A decision was therefore taken to send me to New Zealand for the week to fix the problem and force Ax to accept some different tax rounding rules.

A penny difference in how Ax and an external system calculates taxes.

When registering a sales invoice it is possible to manually adjust the tax amount, per tax code.

This is what we want to do, via X++.

Fortunately a blogger called KiwiAxGuy has already achieved half of the work for us, adjusting tax on journals and free text invoices.  No need for those plane tickets after all!

static void XXX_AdjustSalesTaxesJob(Args _args)
{    
    //Manually adjust sales taxes
    TaxRegulation       taxRegulation;
    SalesTable          salesTable;
    SalesTotals         salesTotals;
    ;
    salesTable         = SalesTable::find('12A0012');

    if (salesTable)
    {
        salesTotals = SalesTotals::construct(salesTable, SalesUpdate::All);
        salesTotals.calc();

        // Launch the form to manually adjust the taxes (TaxTmpWorkTrans)
        // --------------------------------------------------------------
        //Tax::showTaxes(salesTotals.tax(), salesTable);

        // Or do it by X++
        // ---------------
        ttsBegin;
        // EDIT: I've just realised that this method has been modified in the GLP-EE patch
        // taxRegulation = TaxRegulation::newTaxRegulation(salesTotals.tax(), null);
        taxRegulation = TaxRegulation::newTaxRegulation(salesTotals.tax(), null, salesTable, null, null);
        taxRegulation.allocateAmount(498.54);
        taxRegulation.saveTaxRegulation();
        ttsCommit;
    }
}

---

The same can be done for purchase invoices.  In my example once more we have a penny difference in the tax calculation, it should be 13.60.

Purchase order, with 14.60 PLN of taxes we need to apply

static void XXX_AdjustPurchTaxesJob(Args _args)
{
    TaxRegulation       taxRegulation;
    PurchTable          purchTable;
    PurchTotals         purchTotals;
    ;
    purchTable         = PurchTable::find('129/2012');

    if (purchTable)
    {
        purchTotals = PurchTotals::newPurchTable(purchTable, PurchUpdate::All);
        purchTotals.calc();

        // Launch the Form to manually adjust the taxes (TaxTmpWorkTrans)
        // --------------------------------------------------------------
        //Tax::showTaxes(purchTotals.tax(), purchTable);

        // Or do it by X++
        // ---------------
        ttsBegin;
        taxRegulation = TaxRegulation::newTaxRegulation(purchTotals.tax(), null, null, purchTable, null);
        if (taxRegulation.taxLinesExist())
        {
            taxRegulation.allocateAmount(13.60);
            taxRegulation.saveTaxRegulation();
        }
        ttsCommit;
    }
}

HOWEVER.  What happens when we have two tax codes in the same invoice?

Two tax codes, one value to change.

For me it didn't work.  The TaxRegulation class allocated the change in amount to the second tax line, but didn't save the changes in the underlying temporary table :(  Check out the knowledge base article KB2028633 as I'm confident that it's the same issue (more Ax 2009, GLP-EE pains).
You people with the AX 2009 Eastern European patch have been warned.

Our changes are all recorded in the TaxWorkRegulation entity, should you need to check your changes across invoices.

TaxWorkRegulation - The entity that stores the adjusted taxes we have applied... Where is my second line?

BUT, "After posting is successful, all records in this table related to the transaction are deleted." and so within TaxTrans we have the columns SourceTaxAmountCur (calculated sales tax amount shown in the current currency) and SourceRegulateAmountCur (amount that the sales tax is being adjusted to) to compare.

Edit:  Found another possible bug, or it's my lack of knowledge.  Registering changes to taxes for the Purch* entities, I could only do it by changing the HeadingTableId and HeadingRecId values to that of PurchParmTable (343), and not PurchTable (345).  This is probably something to do with rearranging the purchase lines.

Remember people:  Taxes are hard.  Let's go tax-free shopping!

When buf2buf is not enough

When the global buf2buf is not enough! Copying two heterogeneous table buffers, but with practically the same field names.
Edit: Peter Chan beat me to it! The only advantage to using my function is an extra semi colon :)
Neither of our methods check to see if the field type is the same...

///Copy two different table buffers but with the same field names.
///May be better to use a Map...
static void bufFields2BufFields(
    Common  _from,
    Common  _to
    )
{
    DictTable   dictTableFr = new DictTable(_from.TableId);
    FieldId     fieldIdFr;
    DictTable   dictTableTo = new DictTable(_to.TableId);
    FieldId     fieldIdTo;
    FieldName   fieldName;
    ;
    fieldIdFr   = dictTableFr.fieldNext(0);
    fieldName   = dictTableFr.fieldName(fieldIdFr);
    fieldIdTo   = dictTableTo.fieldName2Id(fieldName);

    while (fieldIdFr && ! isSysId(fieldIdFr))
    {
        if (fieldIdTo)
        {
            _to.(fieldIdTo)     = _from.(fieldIdFr);
        }
        
        fieldIdFr           = dictTableFr.fieldNext(fieldIdFr);
        fieldName           = dictTableFr.fieldName(fieldIdFr);
        fieldIdTo           = dictTableTo.fieldName2Id(fieldName);
    }
}

AX 2012 Number Sequence, X++

It's feels like having to learn all of this stuff over again.  Here is a brief overview, and there is a white paper available as well, for AX 2012.

static void tutorial_NumberSequenceJob(Args _args)
{
    // AX 2012 Number Sequence, X++
    // Only for sequences marked as continuous
    // ---------------------------------------
    NumberSequenceReference nsRef;    
    NumberSeqScope    scope;
    ProductReceiptId  receipt;
    
    scope      = NumberSeqScopeFactory::createDataAreaScope(curext());
    nsRef      = NumberSeqReference::findReference(extendedTypeNum(VendAccount), scope);
    if (nsRef)
    {
        receipt         = NumberSeq::newGetNum( nsRef ).num();
    }
    
    info(strFmt("New Supplier Id is '%1'", receipt));
}

AX 2012 Post Purchase Order Intercompany Confirmation

I've been working with AX 2012, whoo-hoo!  Can't find anything in the new interface any more, they've moved a lot of the purchase/sales document processing code to run as a service and then I find myself helping junior developers with SSRS (reporting) without ever having touched the product.  All fun in my personal opinion, and am looking forward to playing some more with the product in the future.

Confirming a Purchase Order in X++ has already been done, but I thought I'd include the use case of when a client is marked as 'InterCompany', thereby the purchase orders are mimicked in another company but as a Sales Order for a supplier.  The issue is that once the sales order copy is created, it does not get confirmed as well (nor may it not accept the delivery note automatically once it is marked as dispatched).

Below is the code in it's simplest form.  Note: Ensure InterCompanyAutoCreateOrders and InterCompanyDirectDeliver are selected for the Sales Order.

static void tutorial_POConfirmJob(Args _args)
{    
    
    // Post Purch Confirmation, cross company 
    // --------------------------------------
    PurchFormLetter purchFormLetter;
    PurchTable      purchTable;
    ;
    changeCompany('qca')
    {
        purchTable      = purchTable::find('QCA-000041');
        purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
        purchFormLetter.update(purchTable, strFmt('%1-%2', purchTable.PurchId, VendPurchOrderJour::numberOfPurchOrder(purchTable)+1));
    }
}

To plug the above into the existing framework try creating the following method in the SalesConfirmJournalPost class.

/// <summary>
///    Runs after posting a journal.
/// </summary>
public void postJournalPost()
{
    SetEnumerator   se = ordersPosted.getEnumerator();
    PurchFormLetter purchFormLetter;
    PurchTable      purchTable;
    
    ttsbegin;
    while (se.moveNext())
    {
        salesTable = SalesTable::find(se.current(),true);
        if (salesTable && strLen(salesTable.InterCompanyCompanyId) > 0
                    && strLen(salesTable.InterCompanyPurchId) > 0)
        {
            // Post Purch Confirmation, cross company.
            changeCompany(salesTable.InterCompanyCompanyId)
            {
                purchTable      = purchTable::find(salesTable.InterCompanyPurchId);
                if (purchTable
                        && purchTable.DocumentState < VersioningDocumentState::Confirmed)
                {
                    purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
                    purchFormLetter.update(purchTable, 
                            strFmt('%1-%2', purchTable.PurchId, 
                              VendPurchOrderJour::numberOfPurchOrder(purchTable)+1));
                    
                    if (purchTable::find(salesTable.InterCompanyPurchId).DocumentState 
                                  == VersioningDocumentState::Confirmed)
                    { 
                        info(strfmt('Intercompany PO '%1' has also been Confirmed.', purchTable.PurchId));
                    }
                }
            }

        }
    }
    ttscommit;
}

Obviously, please test the code. Yours truly will not be held responsible for creating phantom stock in one of your companies!

Form Running Totals

I can think of two use cases when applying running totals to a grid on a form. Taking the LedgerTransAccount form (Account Transactions) as an example:

Running totals by transaction date

Add a real field to the grid, with a 'XXX_RunningBalanceMST' DataMethdod with the 'LedgerTrans' DataSource.
Add the following method to the LedgerTrans table:

public AmountMST XXX_RunningBalanceMST()
{
    LedgerTrans     ledgerTrans;
    ;
    select sum(AmountMST) from ledgerTrans
            where ledgerTrans.AccountNum  == this.AccountNum
               && ( (ledgerTrans.TransDate < this.TransDate)
                 || (ledgerTrans.TransDate == this.TransDate && ledgerTrans.RecId <= this.RecId) );
    return ledgerTrans.AmountMST;
}

Add the cache method to the form datasource (LedgerTrans) init() method, after the super(); call.

    ledgerTrans_ds.cacheAddMethod(tablemethodstr(LedgerTrans, XXX_RunningBalanceMST));

Running totals by user defined filters, dynalinks and ordenations:

This time add a disply method directly to the datasource.

//BP Deviation Documented
display AmountMST XXX_runningBalanceMST2(LedgerTrans _ledgerTrans)
{
    QueryRun    qr = new QueryRun(ledgerTrans_qr.query());
    LedgerTrans localLedgerTrans;
    AmountMST   amountMST;
    ;
    while (qr.next())
    {
        localLedgerTrans    = qr.get(tablenum(LedgerTrans));
        amountMST           += localLedgerTrans.AmountMST;
        if (localLedgerTrans.RecId == _ledgerTrans.RecId)
        {
            break;
        }
    }
    return amountMST;
}

This is definately the slowest procedure.  I'd avoid applying this modification to be honest.
We would have to implement our own caching on the results, using a map, with RecId as our key and the result as the value.  Initialise/empty the map in the executeQuery() method and check if the _ledgerTrans.RecId key exists in the map before launching into the loop in the method.  The optimisation is undertaken here.

EDIT: Includes user filters on the form columns:-

//BP Deviation Documented
display AmountMST XXX_runningBalanceMST(LedgerTrans _trans)
{
    LedgerTrans localLedgerTrans;
    AmountMST   amountMST;
    ;
    localLedgerTrans    = this.getFirst();
    while (localLedgerTrans)
    {
        amountMST           += localLedgerTrans.AmountMST;
        if (localLedgerTrans.RecId == _trans.RecId)
        {
            break;
        }
        localLedgerTrans    = this.getNext();
    }
    return amountMST;
}

Thanks to Jan B. Kjeldsen for the heads-up.

That company container you have always been looking for

// Inner function to return a list of non virtual companies
// --------------------------------------------------------
container companyContainer(NoYes _includeVirtual = NoYes::No)
{
    DataArea            dataarea;
    container           retVal;
    ;
    while select id, isVirtual from dataarea index hint id
    {
        if (_includeVirtual == NoYes::No && dataarea.isVirtual == NoYes::Yes)
        {
            continue;
        }
        retVal += dataarea.id;
    }
    return retVal;
}

I have used the above inner function in two Jobs now, placed in the function declaration section.  Example usage:

static void tutorial_Job_companyCurrency(Args _args)
{
    container               conComps,
                            conComp;
    Counter                 cntComps;
    DataAreaId              dataareaId;
    CurrencyCode            currencyCompany;

    container companyContainer(NoYes _includeVirtual = NoYes::No)
    {
        DataArea            dataarea;
        container           retVal;
        ;
        while select id, isVirtual from dataarea index hint id
        {
            if (_includeVirtual == NoYes::No && dataarea.isVirtual == NoYes::Yes)
            {
                continue;
            }
            retVal += dataarea.id;
        }
        return retVal;
    }
    ;

    conComps        = companyContainer();

    for (cntComps=1; cntComps <= conlen(conComps); cntComps++)
    {
        dataareaId  = conpeek(conComps, cntComps);
        conComp     = [dataareaId];

        currencyCompany     = (select crosscompany : conComp CompanyInfo).CurrencyCode;
        warning(strfmt("Company: %1; Currency: %2", dataareaId, currencyCompany));
    }
}

Finally, below is an example when the customers table is shared across companies, using virtual companies in the query.

    DataArea            dataArea;
    VirtualDataAreaList virtualDataAreaList, 
                        virtualDataAreaListNow;
    ;
    select virtualDataAreaListNow
        where virtualDataAreaListNow.id == curext();

    while select dataArea
        where dataArea.isVirtual == false
//            && dataArea.Id != curext()
        join virtualDataAreaList
            where virtualDataAreaList.id == dataArea.id
            &&    virtualDataAreaList.virtualDataArea == virtualDataAreaListNow.virtualDataArea
    {
        changecompany(dataArea.Id)
        {
            info(strFmt('%1: %2', dataArea.Id, CustTable::find('CL000000').openBalanceMST()));
        }
    }

Let's put that in a tidy container:

static void BigJob(Args _args)
    container                   conCompanys;

    container companyContainer()
    {
        DataArea            dataarea;
        container           retVal;
        VirtualDataAreaList virtualDataAreaList,
                            virtualDataAreaListNow;
        ;
        select virtualDataAreaListNow
            where virtualDataAreaListNow.id == curext();

        while select dataArea
            where dataArea.isVirtual == false
            join virtualDataAreaList
                where virtualDataAreaList.id == dataArea.id
                &&    virtualDataAreaList.virtualDataArea == virtualDataAreaListNow.virtualDataArea
        {
            retVal += dataarea.id;
        }
        return retVal;
    }
    ;
    conCompanys = companyContainer();
    // Do work
}

Decimal Number Formatting

Well I can't explain why but we kept losing decimal places when converting a real to a string.  The below function does *not* truncate the real value to two decimal places.  Note that the function is located in a utility class, and will execute on the server.

public static server str getExchRateCalc(real _fixedExchRate)
{
;
    new InteropPermission(InteropKind::ClrInterop).assert();
    return System.String::Format("{0,0:G}",(_fixedExchRate));
}