Wednesday, June 18, 2014

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()));
        }
    }
}

2 comments:

  1. Hi Faisal,

    Why are you using customerInvoices Table and what Customization have you done on it?

    From whee you are importing the Excel sheet?

    Could you please explain clearly

    ReplyDelete
  2. I will recommend anyone looking for Business loan to Le_Meridian they helped me with Four Million USD loan to startup my Quilting business and it's was fast When obtaining a loan from them it was surprising at how easy they were to work with. They can finance up to the amount of $500,000.000.00 (Five Hundred Million Dollars) in any region of the world as long as there 1.9% ROI can be guaranteed on the projects.The process was fast and secure. It was definitely a positive experience.Avoid scammers on here and contact Le_Meridian Funding Service On. lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. if you looking for business loan.

    ReplyDelete

I will appreciate your comments !

Download large bacpac (sandbox database) to DEV environment much faster

As the LCS website gets slower and slower and the database backups get bigger and bigger.  Use AZCopy to download objects out of LCS asset l...