AX 2012 - Import Free Text Invoices - Header and lines - from one excel file

I got a requirement to upload free text invoices into AX 2012 from one excel file. All the header and lines information was in one excel sheet so I had to came up with different solution than the import process described on many blogs or can be done through built in AX classes. However, those import process work perfectly if you have data into seperate files or only for one record.

Here is the  job which I used to import free text invoices into AX 2012. In this job I used a table CustomerInvoices (new created table) which contains the data of all excel file. You can easily find out how to import data from excel then instead of using this table you can directly use the excel rows to get the data and insert into Free Text Invoice tables.

static void CreateFTInvoicesFromTable(Args _args)

{

    CustInvoiceTable    custInvoiceTable;

    CustInvoiceLine     custInvoiceLine;

    CustTable           custTable;

    CustomerInvoices    customerInvoices; // Customized table

    Map                 customerFTI = new Map(Types::String, Types::String);

    Set                 failedCustAccount = new Set(Types::String);

    MapEnumerator       mapEnum;

    SetEnumerator       setEnum;

    LineNum             lineNum;

    ttsBegin;

    while select customerInvoices

            order by customerInvoices.AccountNum

    {

        if (!customerFTI.exists(customerInvoices.AccountNum) && !failedCustAccount.in(customerInvoices.AccountNum))

        {

            select custTable

                where custTable.AccountNum == customerInvoices.AccountNum;

            custInvoiceTable.clear();

            if (custTable.RecId != 0)

            {

                custInvoiceTable.OrderAccount = custTable.AccountNum;

                custInvoiceTable.modifiedField(fieldNum(CustInvoiceTable, OrderAccount));

                custInvoiceTable.InvoiceId = customerInvoices.InvoiceId;

                custInvoiceTable.insert();

                lineNum = 0;

                customerFTI.insert(customerInvoices.AccountNum, custInvoiceTable.InvoiceId);

            }

            else

            {

                if (!failedCustAccount.in(customerInvoices.AccountNum))

                {

                    failedCustAccount.add(customerInvoices.AccountNum);

                }

            }

        }

        if (custInvoiceTable.RecId != 0)

        {

            custInvoiceLine.initFromCustInvoiceTable(custInvoiceTable);

            custInvoiceLine.LedgerDimension = customerInvoices.LedgerDimension;

            custInvoiceLine.Description     = customerInvoices.Description;

            custInvoiceLine.TaxGroup        = customerInvoices.TaxGroup;

            custInvoiceLine.TaxItemGroup    = customerInvoices.TaxItemGroup;

            custInvoiceLine.Quantity        = customerInvoices.Quantity;

            custInvoiceLine.UnitPrice       = customerInvoices.UnitPrice;

            custInvoiceLine.AmountCur       = customerInvoices.Quantity * customerInvoices.UnitPrice;

            custInvoiceLine.ParentRecId     = custInvoiceTable.RecId;         

            lineNum += 1;

            custInvoiceLine.LineNum = lineNum;

            custInvoiceLine.insert();

        }

    }

    ttsCommit;

    mapEnum = customerFTI.getEnumerator();

    info(strFmt('Following Free Text Invoices are successfully created.'));

    while (mapEnum.moveNext())

    {

        info(strFmt('Customer Account : %1 , Invoice Id : %2', mapEnum.currentKey(), mapEnum.currentValue()));

    }

    if (!failedCustAccount.empty())

    {

        info(strFmt('Following Customer Accounts are not valid.'));

        setEnum = failedCustAccount.getEnumerator();

        while (setEnum.moveNext())

        {

            info(strFmt('Customer Account: %1', setEnum.current()));

        }

    }

}