Tuesday, 24 July 2012

Import default dimension into a table (eg. Customer table) [AX 2012]

I hear this question a lot. How do I update dimensions or import default dimensions? I wrote a job to import a csv file.
Lets take the example below with 4 dimensions.
2012-07-23_1718
Create a csv file to import with the following columns.
CustNum,Dim1,Dim2,Dim3,Dim4
2012-07-23_1737
The result should be something like this.
2012-07-23_1736
Below is the job I wrote to import the dimensions. When you run it, it will request a file path to be imported.
Not the cleanest code but good enough for a quick one.

Code:
static void ImportCustomerDimension(Args _args)
{
    AsciiIO     asciiIO;
    Filename    filename;
    NoYesId     skipFirstLine;
    Container   line;
    Dialog      dialog;
    DialogField dialogFileName, dialogSkipFirstLine;
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    Container                       combinedContainer;
    CustTable                       custTable;
    CustAccount                     custAccount;
    Counter                         counter;
    DimensionAttributeValue         dimAttributeValue;
    //Dialog
    dialog                  = new Dialog("Import Default Dimension");
    dialogFileName          = dialog.addField(extendedTypeStr(Filenameopen), "File name");
    dialogSkipFirstLine     = dialog.addField(extendedTypeStr(NoYesId), "Skip first line");

    if (dialog.run())
    {
        filename      = dialogFileName.value();
        skipFirstLine = dialogSkipFirstLine.value();
    }
    asciiIO = new AsciiIO(filename, 'R');
    if (!asciiIO || asciiIO.status() != IO_Status::Ok )
    {
        throw error (strfmt("@SYS19312",filename));
    }

    asciiIO.inRecordDelimiter('\r\n');
    asciiIO.inFieldDelimiter(',');
    if (skipFirstLine)
        line = asciiIO.read();
    while (asciiIO.status() == IO_status::Ok)
    {
        line = asciiIO.read();
        if (line)
        {
            //get customer account number
            custAccount = conpeek(line,1);
            counter = 1; //Dimension starting point
            combinedContainer = conNull();
            dimAttributeValue = null;
            
            //Build dimension container
            dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
            while select dimAttr
                order by Name
                    where dimAttr.Type != DimensionAttributeType::MainAccount
                join RecId from dimAttrSetItem
                    where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
                          dimAttrSetItem.DimensionAttributeSet == dimensionSetId
            {
                counter++;
                //only insert into container if it has a value
                if (conPeek(line, counter))
                {
                    combinedContainer += [dimAttr.Name, conPeek(line, counter)];
                    //info(strFmt("%1 %2",dimAttr.Name, conPeek(line, counter)));
                    dimAttributeValue = AxdDimensionUtil::validateFinancialDimensionValue(dimAttr, conPeek(line, counter));
                }
            }
            //insert the count in the first value of the container - number of dimensions divided by 2 - since we have name and value
            combinedContainer = conIns(combinedContainer, 1, int2str(conLen(combinedContainer)/2));
            ttsBegin;
            custTable = CustTable::find(custAccount, true);
            custTable.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(combinedContainer);
            custTable.update();
            ttsCommit;
        }
    }
}
This job can be modified to help import into other tables. I just wrote this to illustrate how it is possible.

1 comment:

Warren said...

Thank you it was very helpful