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.
Create a csv file to import with the following columns.
The result should be something like this.
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.

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                  = 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));

    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
                //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));
            custTable = CustTable::find(custAccount, true);
            custTable.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(combinedContainer);
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