Nate's Dynamics AX Notebook

Run-time add/remove datasource to filter results

2012-01-18T14:10:00.004-05:00

Sometimes I add a Combobox above a Grid to give the user several 'canned' filters.

In some cases I would like to filter on a table other than the one displayed in the grid. For example, I might want to filter a grid of CustTable records based on related Address table records.

If you want the filter to be active all the time, you can just add the filtering datasource to your form. But if you only want the filter to be active some of the time, you need to be able to dynamically (run-time) add and remove the datasource that does the filtering.

The code below shows how to dynamically add and remove (actually disable) a child datasource for filtering purposes.

public void executeQuery() //on form datasource "formDatasourceName"
{
    QueryBuildDataSource    qbds;
    ;
 
    if(this.query().dataSourceName("formDatasourceName").childDataSourceCount() == 1)
    {
        //if the filter datasource has been added in the past (prior run of executeQuery), disable it
        this.query().dataSourceName("formDatasourceName").childDataSourceNo(1).enabled(false);
    }

    if (booleanFilterResults)
    {
        //this creates a "hidden" exists join to act as a filter
        if (this.query().dataSourceName("formDatasourceName").childDataSourceCount() == 1)
        {
            //if the filter datasource
            qbds = this.query().dataSourceName("formDatasourceName").childDataSourceNo(1);
            qbds.enabled(true);
        }
        else
        {
            qbds = this.query().dataSourceName("formDatasourceName").addDataSource(tablenum(filterTableName));
        }

        qbds.clearRanges();

        qbds.relations(true);  //or use .addLink...
        qbds.joinMode(JoinMode::ExistsJoin);
        qbds.fetchMode(queryfetchmode::One2One);
        
        //filter on some value, in this case a comboBox selection.  
        //Of course you'll call formDatasourceName.executeQuery() in comboBoxFilter modified method
        qbds.addRange(fieldnum(filterTableName, ItemId)).value(queryValue(comboBoxFilter.valueStr()));
    }

    super();
}

Inventory Journal Import

2011-06-08T18:47:00.004-04:00

I wrote a simple class to handle csv imports into Movement Journals.

1. Create a Movement Journal and go into the Lines screen.
2. Select Functions, Import Lines

3. Select a csv file with the columns: ItemId, Warehouse, Location, Batch, Qty, Cost. (you can mod the code to expand this)

4. The lines are added to the journal.

You can download the xpo below, and here are some of the key bits of code.

Create a class (mine is called inventJournalImport) extending runbase. Create a menu item (action) for the class and drop it on the InventJournalMovement form.

The main method of the class is as follows:

client static void main(Args args)
{
    inventJournalImport             inventJournalImport;
    Object                          formRunObject;
    JournalForm                     journalForm;
    InventJournalId                 inventJournalId;
    FormDataSource                  journalTrans_ds;
    ;

    inventJournalImport = new inventJournalImport();
    inventJournalImport.getLast();

    if (!args || !args.caller() || args.caller().name() != formStr(InventJournalMovement))
    {
        throw error(strfmt("This function must be called from the %1 form.", formStr(InventJournalMovement)));
    }

    if (formHasMethod(args.caller(), identifierstr(journalForm)))
    {
        formRunObject = args.caller();

        journalForm = formRunObject.journalForm();
        inventJournalImport.parmJournalForm(journalForm);  //needed to index numOfLines on inventJournalTable

        inventJournalId = journalForm.journalTableData().journalTable().JournalId;
        inventJournalImport.parmInventJournalId(inventJournalId);  //for defaulting on the imported lines

        journalTrans_ds = journalForm.journalTransData().journalTrans().dataSource();  //for .executeQuery, below
    }

    if (inventJournalImport.prompt())
    {
        inventJournalImport.ImportRecords();

        //refresh the grid
        journalTrans_ds.executeQuery();
    }
}

The ImportRecords method is below. There is a dialog in the class that fills filename and transactionDate.

void ImportRecords()
{
    AsciiIo             asciiIo;
    container           con;
    FileIoPermission    fioPermission;

    InventJournalTrans  inventJournalTrans;
    InventDim           inventDim;

    wmsLocationId       wmsLocationId;
    inventLocationId    inventLocationId;
    inventBatchId       inventBatchId;

    boolean             useDefaultInventDim, useDefaultCost;
    int                 imported;
    ;

    if (WINAPI::fileExists(fileName))
    {
        //show wait cursor
        startLengthyOperation();

        // The AsciiIO.new method runs under code access permission.
        fioPermission = new FileIoPermission(fileName,"R");

        if (fioPermission == null)
        {
            info(strfmt("Not able to read file: %1",fileName));
            return;
        }

        // Code access permission scope starts here.
        fioPermission.assert();

        ttsbegin;

        asciiIo = new AsciiIo(fileName,"R");
        asciiIo.inFieldDelimiter(",");
        if (asciiIo != null)
        {
            con = asciiIo.read();

            while (asciiIo.status() == IO_Status::Ok)
            {
                try
                {
                    //1-itemId, 2-warehouse, 3-location, 4-batch, 5-qty, 6-cost
                    inventJournalTrans.clear();
                    inventJournalTrans.TransDate = transactionDate;
                    inventJournalTrans.JournalId = journalId;
                    inventJournalTrans.initFromInventJournalTable(inventJournalTrans.inventJournalTable());

                    inventJournalTrans.ItemId = strLTrim(strRTrim(conpeek(con,1)));

                    inventLocationId = strLTrim(strRTrim(conpeek(con,2)));
                    wmsLocationId = strLTrim(strRTrim(conpeek(con,3)));
                    inventBatchId = strLTrim(strRTrim(conpeek(con,4)));
                    if (inventLocationId || wmsLocationId || inventBatchId)
                    {
                        useDefaultInventDim = false;

                        inventDim.clear();
                        inventDim.InventLocationId = inventLocationId;
                        inventDim.wMSLocationId = wmsLocationId;
                        inventDim.inventBatchId = inventBatchId;
                        inventDim = InventDim::findOrCreate(inventDim);

                        inventJournalTrans.InventDimId = inventDim.inventDimId;
                    }
                    else
                    {
                        useDefaultInventDim = true;
                    }

                    inventJournalTrans.Qty = str2num(strLTrim(strRTrim(conpeek(con,5))));
                    inventJournalTrans.Qty= decround(inventJournalTrans.Qty,InventTable::inventDecimals(inventJournalTrans.ItemId));

                    if (inventJournalTrans.Qty > 0)
                    {
                        //import the cost if this is an addition to inventory
                        useDefaultCost = false;
                        inventJournalTrans.CostPrice          = str2num(strLTrim(strRTrim(conpeek(con,6))));
                        inventJournalTrans.PriceUnit          = 1;
                        inventJournalTrans.CostMarkup         = 0;
                        inventJournalTrans.CostAmount         = inventJournalTrans.calcCostAmount();
                    }
                    else
                    {
                        useDefaultCost = true;
                    }

                    inventJournalTrans.initFromInventTable(inventJournalTrans.inventTable(),false, useDefaultInventDim, useDefaultCost);

                    inventJournalTrans.insertFromCode();
                    imported++;

                    //updates the number of lines count on the journal table
                    journalForm.journalTableData().addTotal(inventJournalTrans);
                }
                catch (Exception::Error)
                {
                    exceptionTextFallThrough();
                }

                con = asciiIo.read();
            }
        }

        // revertAssert is not really necessary here because the method is ending.
        CodeAccessPermission::revertAssert();

        ttscommit;
        info (strfmt("%1 records imported", imported));

        //remove wait cursor
        endLengthyOperation();
    }
    else
    {
        error(strfmt("Import File not found: %1",fileName));
    }
}

That's the bulk of the code. You can download the xpo below to see everything.

SharedProject_InventoryJournalImport.xpo (google docs - shortened via goo.gl - 12kb)

Inventory Turns Report

2011-06-02T15:28:00.001-04:00

AX doesn't really have a good inventory turns report. Here's a simple job that calculates inventory turns by quantity and by value. You can adapt the code to run in a report.

static void InventoryTurns(Args _args)
{
    itemId          itemId;
    inventTrans     inventTrans;
    inventSum       inventSum;
    qty             thisQty, qtyOnHand, totalDailyQtyOnHand, avgDailyQtyOnHand, usageQty;
    Amount          thisValue, valueOnHand, totalDailyValueOnHand, avgDailyValueOnHand, usageValue;
    Real            turnsByQty, turnsByValue;
    date            lastDate, fromDate, toDate;
    ;

    //set the itemID and date range
    itemId = '00001';
    fromDate = mkDate(1,1,2010);
    toDate = mkDate(31,12,2010);

    //****average daily on-hand
    lastDate = systemDateGet(); //initialize with today.

    //inventSum contains today's values for qty and value
    while select inventSum where inventSum.ItemId == itemId && inventSum.Closed == NoYes::No
    {
        qtyOnHand += inventSum.PhysicalInvent;
        valueOnHand += (inventSum.PhysicalValue + inventSum.PostedValue);
    }

    //starting with today, we work backward to the from date and manually calculate what the on-hand qty/value was each day
    while (lastDate >= fromDate)
    {
        if (lastDate <= toDate) //if we're within the date range, then sum the on-hand qty/value for determination of the average
        {
            //add one days worth of the current OnHand figures
            totalDailyQtyOnHand += qtyOnHand;
            totalDailyValueOnHand += valueOnHand;

            //update average calc
            avgDailyQtyOnHand = totalDailyQtyOnHand/((toDate + 1) - lastDate);
            avgDailyValueOnHand = totalDailyValueOnHand/((toDate + 1) - lastDate);
        }

        lastDate --;

        thisQty = 0;
        thisValue = 0;
        while select Qty, CostAmountPosted, CostAmountPhysical from inventTrans
            where inventTrans.ItemId == itemId
            && inventTrans.DatePhysical == lastDate
        {
            thisQty += inventTrans.Qty;
            //use the posted (financial) value if available
            if (inventTrans.CostAmountPosted != 0)
            {
                thisValue += inventTrans.CostAmountPosted;
            }
            else
            {
                thisValue += inventTrans.CostAmountPhysical;
            }
        }
        //calc the new onHand values
        qtyOnHand += -1 * thisQty;
        valueOnHand += -1 * thisValue;
    }

    //****annual usage
    while select inventTrans
        order by DatePhysical desc
        where inventTrans.ItemId == itemId
        && inventTrans.datePhysical >= fromDate  //this should work in place of limits on statusIssue < 3 and statusReceipt < 3
        && inventTrans.datePhysical <= toDate
        && (inventTrans.TransType == InventTransType::Sales
            || inventTrans.TransType == InventTransType::ProdLine
            || inventTrans.TransType == InventTransType::Project
            || inventTrans.TransType == InventTransType::InventTransaction)

    {
        usageQty += -1*inventTrans.Qty;
        usageValue += -1*inventTrans.CostAmountPhysical;
    }

    //****calc turns = usageQty divided by average on-hand qty
    if (avgDailyQtyOnHand == 0)  //divide by zero protection
    {
        turnsByQty = 0;
    }
    else
    {
        turnsByQty = usageQty / avgDailyQtyOnHand;
    }
    if (avgDailyValueOnHand == 0)
    {
        turnsByValue = 0;
    }
    else
    {
        turnsByValue = usageValue / avgDailyValueOnHand;
    }
    
    info(strfmt("Item Id: %1, Turns by Qty: %2, Turns by Value: %3",itemId,turnsByQty,turnsByValue));
}

Accounts Receivable Collections Management

2011-03-29T11:56:00.003-04:00

Today I'm going to give a tour of a custom Collections Management screen. Normally, I wouldn't share business process level customizations, but since the Financial modules are fairly independent of business process, I thought this might be of interest.

Need:
This business unit has several thousand customers and needed a convenient way to manage the processes of collecting on overdue accounts.

The primary goals were to:

easily identify and prioritize customers to contact
enable an informed conversation with the customer by clearly displaying the transactions in question
provide 'relationship management' features - a quick note saying "On May 29th I talked with Seth and he said the check would be cut that day"
make it easy to see how the customer performed in the past when they had an overdue balance

These 4 goals match the 4 tabs shown below.

So, let's begin the tour of the Collections Workbench. I'll mostly let the screenshots do the talking.
Overview Tab

Note: Grid filter is turned on by default for quick searching by customer name, account
Columns

Tickler Date - user is able to set as a reminder to follow up
Name, Customer account - for quick filtering via grid filter (which is turned on by default)
State - shown so we'd know the time zone for telephone calls
First Review - this is the date that the collections agent first clicks onto the transactions tab for the given record. It's a bit casual, but seems to work well.
Notes - Quick view of most recent note. We'll talk about this more on Tab #3 (Contacts/Notes).
Created Date - when the customer first became overdue
Closed Date - when the customer was no longer overdue. We'll talk about this more on Tab #4 (Closed Notes).

Buttons

Refresh Records - we chose to require a manual refresh of these records rather than calculating them in a batch job or real time.
Customers - link the customer record (standard AX)
Contact Person - link the the full list of contacts for that customer (standard AX) - see also Tab #3 (Contacts/Notes)
Open Transaction Editing - link to view/edit open transactions (standard AX)
Aging bucket statistics - link to show AR aging (standard AX)

Tickler Date Buttons
Tickler setup button show the following setup form that each user can use to configure their screen. The dynamics buttons are created (i.e. "Tickler + 10 Days") and persisted in the usage data. When the user clicks the button, the tickler date is 'pushed out' 10 days.

Transactions Tab

The user can view all open transactions and see which ones are contributing to the past due amount.
The user can print on invoice original or copy as the customer has many times 'lost' the invoice.

Contacts/Notes Tab

The contacts are shown so that the user can have a list in front of them during a phone call.

Notes field - this is essentially a 'journal' of the conversations and actions taken regarding this account.

Each time the user clicks the "Insert Stamp" button, a new line is added to the top of the Notes with their user ID/ date and places the cursor ready for them to type. I've used this "Insert Stamp" concept lots of places and the users really like it.

Closed Notes Tab

This tab gives visibility to previous collections records for this customer. In the example above, the customer had an overdue balance from 9/14 to 9/30/2010. On the overview tab, you can see that there is a filter to show only record with no Closed Date. The records on the Closed Notes tab are the opposite.

AP or AR Accruals

2010-12-09T09:48:00.003-05:00

This is a little trick that we've found useful to:

shorten AP check stubs that were many pages long
allow pre-processing of AR checks that settle against many invoices

AR
We had the situation where we were receiving advance notice of a payment coming from a customer (this could be useful for EDI, etc). For us, the checks settle against lots of invoices - often in the thousands - and we wanted to do the work of settling the payment against the proper invoices ahead of time so that when the check actually arrived, it was quick to process.

We did this by creating Payment Journal 'accrual' transaction where there is a debit and credit canceled each other out. The offset account is arbitrary because the transaction just goes in and out. In the example below, you can see two anticipated checks represented. Note that the journal balance is 0 - no net effect.

Use the Function, Settlement button to settle one side of the transactions against the open invoices that will be paid. This leaves a single open transaction to settle against when the check actually arrives. (Alternatively, you can use the open transaction editing screen to do the settlement against the invoices)

AP
On the AP side, we've used this method to handle the situation where a check is paying for many AP invoices. In this case the problem was that the check stubs were very long and caused many checks to be voided so that all the invoice lines could be printed. We solved this by entering an 'accrual' Invoice Journal transaction and settling it against the many invoices. Then the other side of the transaction is settle against by the Payment Journal and only one line shows on the check stub.

Cruft

2010-12-02T13:49:00.000-05:00

I just learned a new word: cruft. (http://en.wikipedia.org/wiki/Cruft)

Mark All for Open Cust Trans and Open Vend Trans

2010-10-27T20:34:00.000-04:00

We have situations where there are lots of open transactions that need to be settled against each other. This can be the case if auto settlement is turned off.

One solution is to add a "Mark All" button to the custOpenTrans or vendOpenTrans forms. This button "checks" the mark checkbox on every line. The user can then uncheck several lines if needed and Update to settle the lines.

The code below is an example of what we used on the open vendor transaction screen. The code is very similar on the AR side. One note: I used vendTable.AccountNum in the code below. That should be generalized to work with any buffer that is passed into the open trans form.

void customMarkAll()
{
    VendTransOpen   localVendTransOpen;
    VendTrans       localVendTrans;
    container       conSum;

    int             linesProcessed;
    ;

    //show wait cursor
    startLengthyOperation();

    element.lock();

    //remove all prior markings
    specOffsetVoucher.deleteSpecifications();
    settle.empty();

    ttsbegin;
    while select localVendTransOpen where localVendTransOpen.AccountNum == vendTable.AccountNum
    {
        localVendTrans = VendTrans::find(localVendTransOpen.RefRecId);

        specOffsetVoucher.create(localVendTransOpen.TableId,
                                             localVendTransOpen.RecId,
                                             localVendTransOpen.AmountCur,
                                             localVendTrans.CurrencyCode);

        settle.insert(localVendTransOpen.RecId,localVendTransOpen.AmountCur);

        linesProcessed++;
    }
    ttscommit;

    element.unLock();

    //calculate totals for display
    conSum = custVendSettle_Vend.openSumRemainAmount(common,currencyCode);
    remainAmountCur = conpeek(conSum,1);
    remainAmountMST = conpeek(conSum,2);
    element.initCashDiscTotal();

    element.initVendBalance();

    //refresh the screen with the calculated totals
    element.redraw();

    //remove wait cursor
    endLengthyOperation();

    box::info(strfmt("%1 Vouchers Marked",linesProcessed));

}

Large Inventory Close Workaround

2010-10-13T16:00:00.003-04:00

The Situation:
We needed to inventory-close 18 months worth of transactions. This happened because a legacy system was the system of record for this period of time and no one was worrying about details like inventory close. When AX became the system of record, focus turned to reconciling accounts and the inventory close.

We chose not to:
We chose not to do the inventory close one month at a time because that would require opening old accounting periods.

Problem:
When we did the inventory close for 18 months (moderate number of transactions), the process would run for 24 hours or so and then we would get an "Out of memory" error.

Workaround Attempts:

The machines were reasonably powerful...but, just in case, we restarted the AOS and client to make sure that they were in the best condition possible. No luck.
We used "Calculation Help" to try to spread out the load of the closing process among several work stations. This shortened the 24 hours, but still ended in "Out of memory".
We set the Maximum Throughputs and Minimum... parameters to favor speed over accuracy. For us, that was an option because this wasn't the system of record - we just need to get to a clean starting point.

More Info:

When the inventory close failed, the status of the Invent Closing record was "Ledger Posting".
All of the inventory settlement records had been created (and didn't roll back) when the inventory close failed.
In looking at the code, it became clear the the "Calculation Help" only applied to creating/processing the inventory settlement records. When it got to posting to the ledger, the original client was the only one doing the work.
In looking at the code, it seemed that Posting with a specification of Total, Item Group, or Item didn't change the transaction size. We had a specification of 'Total'. (that's the only one we tried)

Our Plan:

Create InventSettlement records. We used the records created by one of our failed attempts, but you could also comment out the call to this.updateLedgerPosting(); towards the bottom of classes\InventCostHelp.run().
Create several InventClosing records with status of "Ledger Posting". We created 3 total. You can make these by commenting out some code in classes\InventCostHelp.run() after the inventClosing record is inserted but before any work is done.
Update InventSettlement.Voucher (in SQL Mgmt Studio) to match InventClosing.Voucher so that the InventSettlement records are assigned to the InventClosing records that you created in manageable chunks. Since we were posting with a specification of 'Total' we just split the records roughly in thirds. If you're posting by Item Group, you might want to take care to put all of the same item group in the same voucher.
Post each InventClosing record. Normally, when an inventory close record has failed, all you can do is cancel it. We temporarily commented code so that when we clicked Close on a failed record, it would 'pick up where it left off' and run updateLedgerPosting(). I've included some snippets below on this.

Result:
Success!

Code related to step #4:

classes\InventCostClosing

static void main(Args args)
{
    InventClosing       inventClosing;
    FormDataSource      fd;

    InventCostClosing closing = InventCostClosing::newParameters();

    //NDP 9/24/10 - BEGIN temp change
    if (args && args.caller() && args.dataset() == tablenum(InventClosing))
    {
        inventClosing = args.record();
    }
    InventCostHelp::newInventClosing(inventClosing,true).run();
    /*
    if (! closing.prompt())
        return;

    try
    {
        closing.run();
    }
    catch
    {
        exceptionTextFallThrough();
    }
    */
    //NDP 9/24/10 - END

    if (args && args.caller() && args.dataset() == tablenum(InventClosing))
    {
...

classes\InventCostHelp

public void run()
{
...

    setprefix(InventCostClosing::prefixText(inventClosing.AdjustmentType));

    try
    {
        this.progressInit(InventCostClosing::prefixText(inventClosing.AdjustmentType),inventClosing.CostNumOf,#AviUpdate);
//NDP 9/24/10 - comment out starting here
/*
        if (! this.validate())
            throw error("@SYS18447");

...


        if (doMasterClient && !trueClient)
            masterClient = true;
*/
//NDP 9/24/10 - end comment out here
        if (! doStop && masterClient)
        {
            this.updateLedgerPosting();
            if (inventClosing.RunRecalculation && systemdateget() > inventClosing.TransDate)
            {
                this.runRecalculation();
            }
        }

Hope that helps,
Nate

Dynamics AX Technical Conference 2011

2010-10-05T14:09:00.000-04:00

Registered! Just in time for early-bird pricing. Should be a great time!

Purchase Order Print "Correct" Quantity

2010-09-22T09:47:00.001-04:00

Standard AX functionality when printing an AP Purchase Order is to print the quantity of the Delivery Remainder (PurchLine.RemainPurchPhysical). The Delivery Remainder is lowered as receipts are posted against the PO, so a PO that is printed after receipts have been posted can look confusing. The PO will show a lower quantity and a lower dollar amount.

You can add the following code to class/purchFormLetter.createParmLine to show to total quantity of the PO: the delivery remainder plus the qty already received.

...
else
{
    [newPurchParmLine.ReceiveNow,  newPurchParmLine.RemainBefore      , newPurchParmLine.RemainAfter      ] = this.qtyPurch (_purchLine, naReal());
    [newPurchParmLine.InventNow,   newPurchParmLine.RemainBeforeInvent, newPurchParmLine.RemainAfterInvent] = this.qtyInvent(_purchLine, naReal());
}
//existing code above

//New Code: add the qty that was received already to the delivery remainder
if (purchParmUpdate.SpecQty == PurchUpdate::All && purchParmUpdate.DocumentStatus == DocumentStatus::PurchaseOrder)
{
    newPurchParmLine.ReceiveNow = _purchLine.RemainPurchPhysical + _purchLine.receivedInTotal();
}

Price Unit Excel Calculation

2010-07-14T13:40:00.001-04:00

Here's the Excel calculation that I use when determining what Price Unit to use during imports/conversions to avoid loss of accuracy on small unit prices.

Price Unit =POWER(10,MAX(LEN(A2) - FIND(".",A2) -2,0))
Price = A2*Price Unit
(Where A2 is the original price, i.e. $0.0045)

Everyone loves that Price Unit!

Image Resource can not be saved in temporary folder

2010-02-22T12:49:00.000-05:00

We recently added a custom image to a report. To do this, we added a resource (AOT, Resources node) and put the following code in the executeSection method:

MyReportBitmapControl.imageName(SysResource::getImagePath("MyResourceName"));

This worked find until we ran the report from a Menu Item. Then we started to get an error that said "unable to save the data to c:\...\Temporary Internet Files\myResource.bmp".

The issue ended up being that the Menu Item was set to Run On: Server. When the Menu Item was set to Run On: Client, the problem was fixed.

Reset TTS Level to zero

2010-02-17T11:30:00.001-05:00

When I executed an unbalanced transaction block, I use this code to get back to zero:

static void NDPToolsResetTTS(Args _args)
{
    while (appl.ttsLevel() > 0)
    {
        info(strfmt("Level %1 aborted",appl.ttsLevel()));
        ttsAbort;
    }
}

Find TableName from TableId

2010-02-17T11:13:00.002-05:00

Sometimes I see a tableId in the data (i.e. in a refTableId field) and I wonder what table that is.

I use this job to find it:

static void NDPToolsShowTableNameFromId(Args _args)
{
    SysDictTable sysDictTable;
    int          id = 40015; //enter your ID here
    ;
    sysDictTable = new SysDictTable(id);
    info (sysDictTable.name());
}

Date and Time in DAX

2010-02-12T14:55:00.005-05:00

Just a quick example of the basic date and time functions.

static void time(Args _args)
{
    int                     t,h,m,s;
    Date                    d;
    InteropPermission       perm;
    System.DateTime         dttime;
;

    //method #1
    t = timenow();
    d = systemDateGet(); //preferred to today()
    h = trunc(t/3600);
    m = trunc((t - h*3600)/60);
    s = t - h*3600 - m * 60;
    info (strfmt("%1 %2:%3:%4",d,h,strRFix(int2str(m),2,"0"),s));

    //method #2
    perm = new InteropPermission(InteropKind::ClrInterop);
    perm.assert();
    dttime = CLRInterop::staticInvoke("System.DateTime","get_Now" );
    CodeAccessPermission::revertAssert();
    info (dttime.ToString());
}

Notes:

the DateTime type has the full array of .NET methods (i.e. ToShortTimeString, ToUniversalTime, etc)
systemDateGet() is affected by systemDateSet, making it superior in testing scenarios
strRFix is used to pad zeros onto single-digit minutes (i.e. 10:4 -> 10:04)

Financial Statement Configuration Import/Export

2010-02-02T16:14:00.004-05:00

To move Financial Statement Configuration between companies or environments:

Administration, Periodic, Data export/import, Definition Groups.
Create new definition group (type: standard)
Click 'Table Setup' button and add the tables listed below.
Click Export on the 'sending' system.
On the receiving system, choose Administration, Periodic, Data export/import, Import (you don't need the definition group)

The tables required are:
DimensionSetTable
DimensionSetRuleTable
DimensionPriorityTable
LedgerBalHeaderDim
LedgerRowDef
LedgerRowDefLine
LedgerRowDefLineCalc
LedgerBalColumnsDim

Addition: as noted by the comment to this post, LedgerRowDefLine can be problematic to move because of the ParentRecId field. The script below is one possible workaround. Just make sure that you don't have any data in the SourceRecId field.

static void NDPToolsPackRecId(Args _args)
{
    LedgerRowDefLine    ledgerRowDefLine, ledgerRowDefLineParent;
    boolean             pack = true; //run with true before export.  run with false after import.
    int                 i;

    ;

/*
The script to 'pack/unpack' the recid was straight forward.  These are the steps to use it:

   1. In this job, set the 'pack' variable to true and run the job prior to export in the source environment.
   2. Do the export from the source environment using a standard definition group
   3. Do the import in the destination environment.
   4. In this job, set the 'pack' variable to false and run the job after import in the destination environment.

*/


    ttsbegin;
    if (pack)
    {
        while select forupdate ledgerRowDefLine
        {
            info (strfmt("%1 < %2",ledgerRowDefLine.SourceRecId,ledgerRowDefLine.RecId));
            ledgerRowDefLine.SourceRecId = ledgerRowDefLine.RecId;
            ledgerRowDefLine.update();
            i++;
        }
    }
    else
    {
        while select forupdate ledgerRowDefLine where ledgerRowDefLine.ParentRecId != 0
        {
            select firstonly forupdate ledgerRowDefLineParent where ledgerRowDefLineParent.SourceRecId == ledgerRowDefLine.ParentRecId;
            if (ledgerRowDefLineParent)
            {
                info (strfmt("%1 < %2",ledgerRowDefLine.ParentRecId,ledgerRowDefLineParent.RecId));
                ledgerRowDefLine.ParentRecId = ledgerRowDefLineParent.RecId;
                ledgerRowDefLine.update();

                //clear the temporary storage location.
                ledgerRowDefLineParent.SourceRecId = 0;
                ledgerRowDefLineParent.update();

                i++;
            }
        }
    }
    ttscommit;
    info (strfmt("%1 records updated",i));

Bitmaps in Reports

2010-01-29T09:02:00.001-05:00

One way to add 'custom' images in an AX report is to:

Add the image as a resource in the AOT, Resources node (right click, Create from File)
In the report, Add a Bitmap control and set AutoDeclaration to Yes.
In the report, sometime prior to the rendering of the bitmap (i.e. executeSection prior to super), code:

myBitmapControl.imageName(SysResource::getImagePath("myresourcename"));

Tetris

2010-01-28T07:23:00.002-05:00

This morning I stumbled upon Tetris in standard AX.

Check out the form: tutorial_Tetris.

Good for a smile. :)

Grid column reorder problem

2010-01-19T08:13:00.000-05:00

IntelliMorph lets users re-order (move) grid columns. If you try to drag and drop a grid column but it won't drag, you might be overriding the tabChanged method on your tab control. For some reason, overriding this method kills the ability to re-order columns. Instead, you might override the individual tabPage control pageActivated methods.

Also, you need to have AllowUserSetup set to Yes on the form Design, but that's a bit more obvious.

Record Filter or Query Range values

2010-01-19T07:41:00.002-05:00

A nice article covering how to filter records in AX
http://axassociate.blogspot.com/2007/12/filtering-record-in-dynamics-ax.html

Not specifically covered in the article
Filter for blanks: ""
Filter for non-blanks: !""
Filter for checkbox checked: 1
Filter for checkbox unchecked: 0

SQL Query to Move Definition Groups

2010-01-14T07:20:00.002-05:00

It's not possible to use Definition Groups (Admin,Periodic,Data Export/Import, Definition Groups) to move data held in shared tables. So, here's the quick and dirty sql query that I use (in SQL Server Management Studio) to do the job.

DECLARE @GroupId AS varchar(20)

SET @GroupId = 'FA1' --set this to your Definition Group ID

--change all 8 database names to match your environments
INSERT INTO AX42_DB_TEST.dbo.sysExpImpGroup SELECT * FROM AX42_DB_DEV.dbo.sysExpImpGroup WHERE groupId = @GroupId
INSERT INTO AX42_DB_TEST.dbo.sysExpImpTable SELECT * FROM AX42_DB_DEV.dbo.sysExpImpTable WHERE groupId = @GroupId
INSERT INTO AX42_DB_TEST.dbo.sysExpImpField SELECT * FROM AX42_DB_DEV.dbo.sysExpImpField WHERE groupId = @GroupId
INSERT INTO AX42_DB_TEST.dbo.sysExpImpTableQuery SELECT * FROM AX42_DB_DEV.dbo.sysExpImpTableQuery WHERE groupId = @GroupId

I've also read that when you use standard Data Export/Import move a definition group itself, CRs are stripped out of your conversion code. (http://gotdax.blogspot.com/2009/12/dynamics-ax-import-export-tool-digging.html) This gets around that problem as well.

How to Load Fixed Assets

2010-01-13T11:12:00.009-05:00

Task: load about 2000 fixed assets into Dynamics AX 4.0. Some assets have been previously scrapped, sold, and fully depreciated. Otherwise, the assets are generally part way through straight-line depreciation. Assets fall into 6 groups (i.e. buildings, machinery, etc.) and need to be associated with a financial dimension indicating the department (i.e. shipping, production, etc.).

Minimum Data that you need to import...and where it maps to in AX)
Fixed Asset Number (AssetTable.AssetId, AssetBook.AssetId)
Asset Name (AssetTable.Name)
Asset Group (AssetTable.AssetGroup, AssetBook.AssetGroup)
Placed in Service Date (AssetBook.AcquisitionDate, AssetTable.GuaranteeDate) - GuaranteeDate is temporary, use whatever field you like
Aquisition Price (AssetBook.AcquisitionPrice)
Service Life in Years (AssetBook.ServiceLife)
Service Life in Months (AssetBook.LifeTime) - I could have calculated this during import
Depreciation Periods Remaining (AssetBook.LifeTimeRest)
Accumulated depreciation from legacy system (AssetTable.AssetReplaceCost) - this is temporary, use whatever field you like
plus other data that you may or may not have

Step 1 - Import
Define custom definition group for AssetTable and AssetBook. Field setup as described above.
(Administration, Periodic, Data Export/Import, Definition Groups)

I used the Conversion tab to set some values:

AssetBook book;
;
book.data(assetBook);
book.BookId = 'OurBookId';
book.PostingProfile = 'OurPostingProfileId';
book.Depreciation = NoYes::Yes;
book.LastDepreciationDate = mkDate(31,10,2009);
book.DepreciationConvention = AssetDepreciationConvention::MidMonth1st;
assetBook.data(book);

Note: the LastDepreciationDate is the date that accumulated depreciation was calculated through in the legacy system.

Step 2 - Post Journal for Acquisition
Once you have the Fixed Asset records that you imported in step 1, you will see that they have the status 'Not yet acquired'. To properly record the aquisition, you have to post a Fixed Asset acquisition journal.

In our case, the Ledger balances already reflected the prior purchase of the assets. We needed to record the acquisition, but didn't want it to affect our ledger balances. The simple work-around was to temporarily set the aquisition ledger account and offset account to the same ledger account so there is no net affect. (GL, Setup, Fixed Assets, Posting Profiles)

It's possible to use Proposal > Acquisition to create lines in the Fixed Asset journal and then change the dates* on all the lines (they are defaulted to the acquisition date - but those periods were closed for us). Instead, I wrote a little job to create the journals. (see below)
*when you change this date, the AssetBook.AcquisitionDate is overwritten. see solution below.

Step 3 - Post Journal for (legacy system) Accumulated Depreciation
Since most of our fixed assets were partially depreciated at the time of import, we needed some way to record the prior depreciation. To do this you could use Proposal > Depreciation to get 'caught up'. There are two small issues: 1) if acquisition date was changed during step 2, the depreciation proposal will not know the correct aquisition date. 2) in our case, again, the Ledger balances already reflected the depreciation prior to import.
Our solution was to create a Depreciation Adjustment journal that has a matching ledger account and offset account. Since there isn't a Proposal > Depreciation Adjustment, I wrote a job to do this. (see below)

Step 4 - Ready To Go
The key fields that allow depreciation to 'pick up where it left off' are AssetBook.LifeTimeRest (remaining depreciation periods) and AssetBook.LastDepreciationDate. We set these during the import, so there shouldn't be anything further to do.

Our Journal Creation Job
As noted in Step 2 and 3 - we used a job to create the acquisition journal and depreciation adjustment journal. Here it is:

static void AssetCreateJournal(Args _args)
{
    LedgerJournalName   ledgerJournalName;
    LedgerJournalTable  ledgerJournalTable;
    LedgerJournalTrans  ledgerJournalTrans;
    NumberSeq           numberSeq;
    AssetTable          assetTable;
    AssetBook           assetBook;
    int                 lineNum;
    Date                transDate;
    ;

    transDate = mkDate(31,10,2009);  //Oct 31, 2009 = our conversion date

    ttsbegin;

    //Record the acquisition
    select firstonly ledgerjournalName where ledgerJournalName.JournalName == 'OurJournalName';
    ledgerJournalTable.JournalName = LedgerJournalName.JournalName;
    ledgerJournalTable.initFromLedgerJournalName();
    ledgerJournalTable.insert();

    numberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);

    while select assetBook where assetBook.Status == AssetStatus::NoAcquisition
    {
        lineNum++;
        ledgerJournalTrans.clear();
        ledgerJournalTrans.voucher = numberSeq.voucher();
        ledgerJournalTrans.LineNum = lineNum;
        ledgerJournalTrans.TransDate = transDate;
        ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
        ledgerJournalTrans.initValue();  //sets currencyCode and exchRate, etc
        ledgerJournalTrans.AccountType = LedgerJournalACType::FixedAssets;
        ledgerJournalTrans.AccountNum = assetBook.AssetId;
        ledgerJournalTrans.initAssetBookid();  //sets assetBookId
        ledgerJournalTrans.initAssetPostingProfile(); //sets postingProfile
        ledgerJournalTrans.Txt = "Acquisition";
        ledgerJournalTrans.AssetTransType = AssetTransTypeJournal::Acquisition;
        ledgerJournalTrans.AmountCurDebit = assetBook.AcquisitionPrice;
        ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;
        ledgerJournalTrans.OffsetAccount = ledgerJournalTrans.findOffsetAccount();
        ledgerJournalTrans.Dimension = assetBook.Dimension;
        ledgerJournalTrans.insert();
    }

    ttscommit;
    info (strfmt("Fixed Asset Journal %1 was created with %2 lines",ledgerJournalTable.JournalNum,lineNum));

    ttsbegin;

    //Now do the same thing for depreciation adjustment
    lineNum = 0;

    select firstonly ledgerjournalName where ledgerJournalName.JournalName == 'FA';
    ledgerJournalTable.clear();
    ledgerJournalTable.JournalName = LedgerJournalName.JournalName;
    ledgerJournalTable.initFromLedgerJournalName();
    ledgerJournalTable.insert();

    numberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);

    //during import, the accumulated depreciation was temporarily stored in the assetReplaceCost field
    while select assetTable where assetTable.AssetReplaceCost > 0
    {
        lineNum++;
        ledgerJournalTrans.clear();
        ledgerJournalTrans.voucher = numberSeq.voucher();
        ledgerJournalTrans.LineNum = lineNum;
        ledgerJournalTrans.TransDate = transDate;
        ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
        ledgerJournalTrans.initValue();  //sets currencyCode and exchRate, etc
        ledgerJournalTrans.AccountType = LedgerJournalACType::FixedAssets;
        ledgerJournalTrans.AccountNum = assetTable.AssetId;
        ledgerJournalTrans.initAssetBookid();  //sets assetBookId
        ledgerJournalTrans.initAssetPostingProfile(); //sets postingProfile
        ledgerJournalTrans.Txt = strfmt("Depreciation through %1",transDate);
        ledgerJournalTrans.AssetTransType = AssetTransTypeJournal::DepreciationAdj;
        ledgerJournalTrans.AmountCurCredit = assetTable.AssetReplaceCost; //using our temporary data from import
        ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;
        ledgerJournalTrans.OffsetAccount = ledgerJournalTrans.findOffsetAccount();
        ledgerJournalTrans.Dimension = AssetBook::Find(ledgerJournalTrans.AccountNum,ledgerJournalTrans.AssetBookId).Dimension;
        ledgerJournalTrans.insert();
    }

    ttscommit;

    info (strfmt("Fixed Asset Journal %1 was created with %2 lines",ledgerJournalTable.JournalNum,lineNum));
}

After reviewing and posting the journals, we ran the following job to clean up the records - remove temporary data and fix the aquisition date.

static void AssetFinalizeImport(Args _args)
{
    AssetTable          assetTable;
    AssetBook           assetBook;
    ;

    ttsbegin;

    //during import, the accumulated depreciation was temporarily stored in the assetReplaceCost field
    while select forupdate assetTable
    {
        assetBook = AssetBook::Find(assetTable.AssetId,'OurBookId',true);

        if (assetBook)
        {
            if (assetTable.GuaranteeDate != dateNull())
            {
                assetBook.AcquisitionDate = assetTable.GuaranteeDate;
            }
            //if fully depreciated
            if (assetTable.AssetReplaceCost == assetBook.AcquisitionPrice)
            {
                assetBook.Status = AssetStatus::Closed;
            }
            assetBook.update();

            //clear the temporary fields
            assetTable.AssetReplaceCost = 0;
            assetTable.GuaranteeDate = datenull();
            assetTable.update();
        }
    }

    ttscommit;
}

That's all - hope it's a help.

Data Import Conversion

2010-01-12T11:41:00.003-05:00

The 'trick' to placing code in the conversion tab of a Definition Group is to define a second table buffer and using the .data() method to copy the date back and forth.

Database fields in SSRS page header

2009-12-31T10:00:00.008-05:00

Scenario
A financial report that I'm writing has columns for current year, prior year, etc. The dates that define the columns are calculated based on the users entry of a report parameter date. (i.e. current year start = January 1st of the year of the report parameter date). These dates were declared and calculated in a dataset query and then used as parameters in the TSQL query. This worked fine. I ran into some trouble when I wanted to display the calculated dates in the header of the report. We're using SQL Server 2005 and Visual Studio 2005.

What DID work
1. Create a dataset to calculate the dates based on the report parameters. Select the calculated values into a table.

2. Create an internal report parameter for each calculated date and set the available values and default value based on the dataset above.

3. These internal report parameters can be used in other dataset queries and in the report header.
What I could have done but didn't want to

What I could have done but didn't want to

I could have rewritten the TSQL that calculated the dates in an expression within the report...but didn't want duplicate code.

What didn't work

The date variables that I declared in TSQL were not accessible to the report fields
I tried declaring internal and/or hidden report parameters and setting their values in TSQL. I couldn't find a combination of settings that allowed me to set the value of the report parameters
I tried creating a second dataset that calculated my dates and returned them as a single-row. This seemed promising, but for some reason SSRS restricts referencing fields in the report header. That's a strange one. I read several articles indicating that you can get around this issue by putting a hidden field in the body of the report...and then doing 4 other goofy workarounds to make it consistently available on multi-page reports...yuk.

Microsoft Certifications

2009-12-17T14:45:00.031-05:00

Microsoft learning catalog, including exams for MS Dynamics AX:
http://learning.microsoft.com/Manager/Catalog.aspx

Clear summary of MCBMSP for Dynamics AX
http://www.colaboy.co.uk/Default.aspx?story=448

Example of MCBMSP - Development for Dynamics AX
http://community.dynamics.com/blogs/axdilip/archive/tags/Certification/default.aspx

Advice after taking the required exams
http://axstart.spaces.live.com/Blog/cns!E82F2C8CB173C0A0!455.entry

Notes:

It seems that MCBMSP (MS Cert. Business Mgmt. Solutions Professional) is a flavor of MCITP (MS Cert. IT Professional). I believe the broadest credential for this is MCP (MS Cert. Professional).
After the first exam toward MCBMSP, you become an MCBMSS (MS Cert. Business Mgmt. Solutions Specialist). The broadest credential for this is MCTS (MS Cert. Technology Specialist) - so you'd put MCTS on your business card, not MCBMSS. That good - 6 characters is a bit steep!

Update 1/6/10: Passed MB6-819. 86% This was my first MS exam and I'm impressed that it was fair and balanced - not too easy, not too hard. They give you an overall score, and also a score in each topic area. I actually did worse on the more basic topics.

Update 12/6/10: Passed MB6-821 (AX 2009 MorphX Solution Development). I think Financials (MB6-818) will be next.

Update 1/11/11: Passed MB6-818 (AX 2009 Financials) Next up: MB6-820 (Install/Config) which is looking pretty short!

Development Documentation

2009-12-16T08:03:00.007-05:00

There are several repositories that I use for Dynamics AX development documentation:

MSDN

http://msdn.microsoft.com/en-us/library/cc678142.aspx

Axaptapedia
http://www.axaptapedia.com/Main_Page

Microsoft's main Dynamics AX Page
http://msdn.microsoft.com/en-us/dynamics/ax/default.aspx

Microsoft's Dynamics AX Forum
http://community.dynamics.com/forums/33.aspx

Sunrise Technologies Functional Demos
http://www.sunriseconsult.com/FunctionalDemos.aspx

Dynamics AX videos by Saveen Reddy
http://www.vimeo.com/tag:dynamicsax

Tools

2009-12-15T12:48:00.007-05:00

There are several tools that I find useful almost every day:

Gadwin Printscreen
http://www.gadwin.com/printscreen/
There is a free version. I set it to capture a 'Rectagular Area' to the clipboard. Wonderful.

Cute PDF
http://www.cutepdf.com/
A free PDF writer that's always worked well for me.

Dynamics AX Task Recorder
Available from MS PartnerSource. An amazingly easy way to create user documenation for AX.

Dynamics AX 2009 Keyboard Shortcuts
http://kashperuk.blogspot.com/2008/07/microsoft-dynamics-ax-2009-keyboard.html
I'm not sure who wrote this document, but it was a big help to me.

Format source code for blog posting
http://formatmysourcecode.blogspot.com/
Just right.

Multiple AOS Environment

2009-12-15T12:35:00.002-05:00

Recently I've been working on a site with 3 production AOSs. Code updates were a hassle because we would have to restart each AOS after importing the new code. The following post shares a method to solve this problem and it's worked great for us.

http://dynamics-ax.blogspot.com/2006/04/flush-aos-cache-from-code.html