Home > dynamics ax 2009 > how to import dynamics ax 2012 prospects data using x++

how to import dynamics ax 2012 prospects data using x++


hi guys,

how to upload data prospects to ax 2012 database using x++, this is the source code that I created:

//created by ronny 2014-02-19
//upload prospect from xls file

static void HPG_ImportProspects(Args _args)
{
    Filename FileNamePath  = 'C:\\upload\\Prospect-20140219.xlsx';

    #WinApi
    #File

    SysExcelApplication application = SysExcelApplication::construct();
    SysExcelWorkbooks   workbooks   = application.workbooks();
    SysExcelWorkbook    workbook;
    SysExcelWorksheets  workSheets;
    SysExcelWorksheet   workSheet;
    SysExcelCells       cells;
    SysExcelCell        cell;

    int                 i=0, j=0, k=0;
    Filename            Filename;
    int                 temporary=1;
    int ii;
    DirPartyTable party;
    DirPersonName person;
    DirPerson pers;
    smmbusreltable summbus;
    boolean caninsert;
    boolean _isPerson;
    str tDate;
    str tTime;
    str dicky;

    tDate = date2str(systemDateGet(), 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);
    tTime = time2Str(TimeNow(), TimeSeparator::Colon, TimeFormat::AMPM);

    _isPerson = true;

    if (!WinAPI::fileExists(FileNamePath))
            throw error(strfmt("@SYS109820", FileNamePath));
    if (workbooks.open(FileNamePath, false /*Update links*/, true /*Read only*/))
    {
        workbook   = workbooks.item(1);
        workSheets = workbook.worksheets();
        workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
        cells      = workSheet.cells();
        i=2;
        while (true)
            {
                if (cells.item(i, 1).value().bStr() == "" || i > 5)
                    break;
                else
                {
                    ttsbegin;
                    try{
                        caninsert = true;
                        _isPerson = true;
                        if (cells.item(i, 4).value().bStr() == "Y")
                        {
                            _isPerson = false;
                        }

                        if(_isPerson)
                        {
                            pers.initValue();
                            //dicky = cells.item(i, 9).value().bStr();
                            //temporary = str2int(cells.item(i, 9).value().bStr()); //0:none, 1:single, 2:married, 3:divorced, 4:widowed
                            //temporary = 1;
                            pers.MaritalStatus = str2int(cells.item(i, 9).value().bStr());
                            pers.Gender = str2int(cells.item(i, 10).value().bStr()); //1:male, 2:woman
                            pers.NameSequence = 5637144576; //do not change
                            pers.BirthMonth = str2int(cells.item(i, 11).value().bStr());
                            pers.BirthDay = str2int(cells.item(i, 12).value().bStr());
                            pers.BirthYear = str2int(cells.item(i, 13).value().bStr());
                            pers.AnniversaryMonth = str2int(cells.item(i, 14).value().bStr());
                            pers.AnniversaryDay = str2int(cells.item(i, 15).value().bStr());
                            pers.AnniversaryYear = str2int(cells.item(i, 16).value().bStr());
                            pers.PartyNumber = cells.item(i, 1).value().bStr();
                            pers.Name = cells.item(i, 2).value().bStr();
                            pers.LanguageId = "en-us"; //do not change
                            pers.NameAlias = cells.item(i, 3).value().bStr();

                            if(pers.validateWrite())
                            {
                                pers.insert();
                            }
                            else
                            {
                                caninsert = false;
                            }
                        }
                        else
                        {
                            party.initValue();
                            party.PartyNumber = cells.item(i, 1).value().bStr();
                            party.Name = cells.item(i, 2).value().bStr();
                            party.LanguageId = "en-us"; //do not change
                            party.NameAlias = cells.item(i, 3).value().bStr();
                            party.Memo = cells.item(i, 17).value().bStr();

                            if(party.validateWrite())
                            {
                                party.insert();
                            }
                            else
                            {
                                caninsert = false;
                            }
                        }

                        summbus.initValue();
                        summbus.Blocked = 0;
                        summbus.TaxGroup = "PPN10%";
                        if(_isPerson)
                        {
                            summbus.Party = pers.RecId;
                        }
                        else
                        {
                            summbus.Party = party.RecId;
                        }
                        summbus.OneTimeRelation = 0;
                        summbus.CreditMax = 0;
                        summbus.MandatoryCreditLimit = 0;
                        summbus.InclTax = 1;
                        if(_isPerson)
                        {
                            summbus.BusRelAccount = pers.PartyNumber;
                        }
                        else
                        {
                            summbus.BusRelAccount = party.PartyNumber;
                        }
                        summbus.DirectMail = 1;
                        summbus.Memo = cells.item(i, 17).value().bStr();
                        summbus.BusRelTypeId = "Prospect";
                        summbus.OpenToTime=57600;
                        summbus.OpenFromTime=28800;
                        summbus.Imported = 0;
                        summbus.LineOfBusinessId=cells.item(i, 5).value().bStr();
                        summbus.Currency = "IDR";
                        summbus.CustGroup = "010";
                        summbus.SDX_SalesCodeId = cells.item(i, 6).value().bStr();

                        if(summbus.validateWrite())
                        {
                            summbus.insert();
                        }
                        else
                        {
                            caninsert = false;
                        }

                        person.initValue();
                        person.FirstName = cells.item(i, 7).value().bStr();
                        person.LastName = cells.item(i, 8).value().bStr();
                        //person.ValidFrom = str2datetime(tDate + " " + tTime, 321);
                        person.ValidTo = str2datetime("2154/12/31 23:59:59", 321);

                        if(_isPerson)
                        {
                            person.Person = pers.RecId;
                        }
                        else
                        {
                            person.Person = party.RecId;
                        }

                        if(person.validateWrite())
                        {
                            person.insert();
                        }
                        else
                        {
                            caninsert = false;
                        }

                        /* kunci di sini untuk latihan*/
                        //caninsert = false;

                        if(caninsert)
                        {
                            ttsCommit;
                        }
                        else
                        {
                            ttsAbort;
                        }
                        //info(strFmt("%1", pers.RecId));
                    }
                    catch
                    {
                        info(strFmt("Gagal di baris %1", i));
                        ttsAbort;
                    }
                    i++;
                }
        }
    }
    info(strFmt("Beres upload sebanyak %1",i-3));
}

you can use xls template for data
Prospect-20140221

  1. No comments yet.
  1. No trackbacks yet.

Please kindly Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: