Import Chart of Accounts

Posted by

It is a tedious task to create ledgers in Chart of Accounts(COA) every time a set of new ledgers are required to be added. What if there is an import utility that could ease the task. Just with the click of a button the new ledgers are added and you are good to start using it.

All you will have to do is to prepare your new ledger codes in a CSV file in a particular template and upload it directly in the COA. Below are the fields required int he csv file.

AccountNumber,AccountName,AccountType,CoaName

Where CoaName is the name of the chart of account to which the new ledgers are to be added. There are cases where an AX instance has multiple COA to handle multiple legal entities and different business scenarios. Let us be specific so that the correct COA set is affected. First we need to create a button in the chart of accounts form as shown below.

Below are the button properties.

Over ride the clicked method of the button and write the below code.

void clicked()
{
    #AviFiles
    SysOperationProgress            vvProgress;
    FileName                        filename;    
    int                             maxRows = 0, row = 0, excelColumn = 1;    
    Dialog                          d;
    DialogField                     df1, df2;
    CommaTextIo                     file;
    container                       rec;
    MainAccount                     mainAccount1;
    boolean                         recordsExist = false, ret = true, uploadedWithWarnings = false;
    AccountNum                      accountNum;
    Name                            name,coa;
    AccountType_IN                  accountType;
    int                             discMeth;
    str                             uom,method;
    vvProgress = new SysOperationProgress();
    vvProgress.setCaption("COA upload : " );
    vvProgress.setAnimation(#AviUpdate);
    vvProgress.setText("Uploading lines.");
    d       = new Dialog("Import COA Mapping");
    df1     = d.addField(ExtendedTypeStr("FilenameOpen"));

    if (d.run())
    {
        file = new CommaTextIo(df1.value(), 'r');
        file.inFieldDelimiter(',');
        rec = file.read();
        rec = file.read();

        while (rec)
        {
            accountNum                      = conPeek(rec, 1);
            name                            = conPeek(rec, 2);
            accountType                     = conPeek(rec, 3);
            coa                             = conPeek(rec, 4);
            if (ret)
            {
                ttsBegin;                
                {
                    vvProgress.setText(strFmt("%1 of %2. Processing.", row-1, maxRows));
                    vvProgress.setCaption("Importing COA Lines...");
                    vvProgress.setAnimation(#AviTransfer);
                    mainAccount1.clear();
                    mainAccount1.MainAccountId           = conPeek(rec, 1);
                    mainAccount1.Name                    = conPeek(rec, 2);
                    mainAccount1.Type                    = conPeek(rec, 3);
                    mainAccount1.CurrencyCode            = 'INR';
                    mainAccount1.LedgerChartOfAccounts   = LedgerChartOfAccounts.RecId;
                    if(accountType == 4 || accountType  == 2)
                    {
                        mainAccount1.DebitCreditProposal    = DebCredProposal::Debit;
                    }
                    else if(accountType == 5 || accountType  == 1)
                    {
                        mainAccount1.DebitCreditProposal    = DebCredProposal::Credit;
                    }
                    mainAccount1.insert();
                    vvProgress.setText(strfmt('Processing Line Number %1 for COA %2.', row, LedgerChartOfAccounts.Name ));
                }
				ttscommit;
			}
            rec = file.read();
            row++;
        }
        info(strFmt('%1 records processed', row));
    }
}

If you want more fields to be added that can be done by modifying the template and adding those fields also in to above code.

Happy Daxing!

Leave a Reply

Your email address will not be published. Required fields are marked *