X++ code For MultiselectLookup form from MultipleTables in ax 2012

public void lookup()

{

    Query query;

    QueryBuildDataSource qbds1,qbds2,qbds3;

    SysLookupMultiSelectCtrl msc;

   

    super();

    query = new Query();

    qbds1 = query.addDataSource(tableNum(CustTable));

    qbds1.fields().dynamic(NOYES::Yes);

    qbds1.fields().addField(fieldNum(CustTable,AccountNum)); //Lookupform contains only accountnum

    qbds2 = qbds1.addDataSource(tableNum(DirPartyTable));

    qbds2.fields().dynamic(NOYES::Yes);

    qbds2.fields().addField(fieldNum(DirPartyTable,Name)); //Lookupform contains only Name

    qbds3 = qbds2.addDataSource(tableNum(LogisticsElectronicAddress));

    qbds3.fields().dynamic(NOYES::yes);

    //Lookupform contains only Locator 

    qbds3.fields().addField(fieldNum(LogisticsElectronicAddress,locator));

   

    qbds2.relations(true);

    qbds3.relations(true);

    msc=SysLookupMultiSelectCtrl::constructWithQuery(element,StringEdit,query);

}

X++ code to Export data to Excel sheet in ax 2012

void clicked()

{

   SysExcelApplication  xlsApplication;

   SysExcelWorkBooks    xlsWorkBookCollection;

   SysExcelWorkBook     xlsWorkBook;

   SysExcelWorkSheets   xlsWorkSheetCollection;

   SysExcelWorkSheet    xlsWorkSheet;

   SysExcelRange        xlsRange;

   VendTable            vendtable;

   LogisticsElectronicAddress LogisticsElectronicAddress;

   str contact;

   int recordscount;

   LedgerJournalTrans  ledgerJournalTran;

   LedgerJournalTable  ljt;

   VendBankAccount VendBankAccount;

   int                  row = 1;

   str                  fileName;

   ;

    super();

   // recordsCount = ledgerJournalTrans_ds.recordsMarked().lastIndex();  // Total number of marked records.

    //ledgerJournalTran    = ledgerJournalTrans_ds.getFirst(1);

   ljt= element.args().record();

   //Filename

   fileName = @"C:\Users\shivakumar.p\Desktop\TableExcel";

   //Initialize Excel instance

   xlsApplication           = SysExcelApplication::construct();

   //Open Excel document

   //xlsApplication.visible(true);

   //Create Excel WorkBook and WorkSheet

   xlsWorkBookCollection    = xlsApplication.workbooks();

   xlsWorkBook              = xlsWorkBookCollection.add();

   xlsWorkSheetCollection   = xlsWorkBook.worksheets();

   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

     while select LedgerJournalTran where LedgerJournalTran.JournalNum==ljt.JournalNum //join vendtable

    {

   //Excel columns captions

   xlsWorkSheet.cells().item(row,1).value(date2str(LedgerJournalTran.TransDate,213,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits2,DateSeparator::Slash,DateYear::Digits4));

   xlsWorkSheet.cells().item(row,2).value(LedgerJournalTran.offsetAccountName());

   xlsWorkSheet.cells().item(row,3).value(LedgerJournalTran.AmountCurDebit);

        vendtable =VendTable::find(DimensionAttributeValueCombination::find(LedgerJournalTran.LedgerDimension).DisplayValue);

   xlsWorkSheet.cells().item(row,4).value(vendtable.AccountNum);

   xlsWorkSheet.cells().item(row,5).value(LedgerJournalTran.AccountName());

   xlsWorkSheet.cells().item(row,6).value(enum2str(LedgerJournalTran.OffsetAccountType));

   xlsWorkSheet.cells().item(row,7).value(VendTable::find(DimensionAttributeValueCombination::find(LedgerJournalTran.LedgerDimension).DisplayValue).bankAccountNum());

   xlsWorkSheet.cells().item(row,8).value(VendTable::find(DimensionAttributeValueCombination::find(LedgerJournalTran.LedgerDimension).DisplayValue).email());

   xlsWorkSheet.cells().item(row,9).value(VendTable::find(DimensionAttributeValueCombination::find(LedgerJournalTran.LedgerDimension).DisplayValue).phone());

   xlsWorkSheet.cells().item(row,10).value(LedgerJournalTran.Txt);

   row++;

         //ledgerJournalTran = ledgerJournalTrans_ds.getNext();

    }

   //Fill Excel with CustTable AccountNum and Name fields (only 10 records)

   /*while select custTable

   {

      if(row == 10)

        break;

      xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);

      xlsWorkSheet.cells().item(row,2).value(custTable.Name());

      row++;

   }

   */

   //Check whether the document already exists

   if(WinApi::fileExists(fileName))

      WinApi::deleteFile(fileName);

   //Save Excel document

   xlsWorkbook.saveAs(fileName);

   //Open Excel document

   xlsApplication.visible(true);

   //Close Excel

   //xlsApplication.quit();

   //xlsApplication.finalize();

}

X++ code for Creating Vendor ,Record Type::Person in ax 2012

X++ code for Creating Vendor ,Record Type::Person in ax 2012

static void VendorPersonsXppImport(Args _args)

{

    #define.Name("Alex Anikievzz")

    #define.FirstName("Alexzz")

    #define.LastName("Anikievzz")

    #define.PostalAddressName("Postal address")

    #define.City("Alpharetta")

    #define.County("FULTON")

    #define.Country("USA")

    #define.Street("1125 Sanctuary Pkwy #300")

    #define.State("GA")

    #define.ZipCode("30009")

    #define.ElectronicAddressName("Electronic address")

    #define.Locator("alexani@microsoft.com")

    #define.LocatorExt("")

    #define.VendAccount("Alex1zz")

    #define.VendGroup("10")

    #define.Currency("USD")

    #define.CostCenter("OU_4803")

    #define.Department("OU_2311")

    #define.ExpensePurpose("Training")

    DirPerson                   dirPerson;

    DirPersonName               dirPersonName;

    LogisticsLocation           logisticsLocation;

    LogisticsPostalAddress      logisticsPostalAddress;

    LogisticsElectronicAddress  logisticsElectronicAddress;

    VendTable                   vendTable;

    try

    {

        ttsbegin;

        //Person

        dirPerson.clear();

        dirPerson.initValue();

        dirPerson.Name = #Name;

        if (dirPerson.validateWrite())

        {

            dirPerson.insert();

            if (dirPerson)

            {

                //Person name

                dirPersonName.clear();

                dirPersonName.initValue();

                dirPersonName.FirstName = #FirstName;

                dirPersonName.LastName = #LastName;

                dirPersonName.Person = dirPerson.RecId;

                dirPersonName.ValidFrom = DateTimeUtil::utcNow();

                dirPersonName.ValidTo = DateTimeUtil::maxValue();

                if (dirPersonName.validateWrite())

                {

                    dirPersonName.insert();

                }

                else

                    throw error("Person name");

                //Location

                logisticsLocation = LogisticsLocation::create(#PostalAddressName, NoYes::Yes);

                //Party location

                DirParty::addLocation(dirPerson.RecId, logisticsLocation.RecId, true, true, false, [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Home).RecId]);

                //Postal address

                logisticsPostalAddress.clear();

                logisticsPostalAddress.initValue();

                logisticsPostalAddress.City = #City;

                logisticsPostalAddress.County = #County;

                logisticsPostalAddress.CountryRegionId = #Country;

                logisticsPostalAddress.Street = #Street;

                logisticsPostalAddress.State = #State;

                logisticsPostalAddress.ZipCode = #ZipCode;

                logisticsPostalAddress.Address = LogisticsPostalAddress::formatAddress(

                #Street, #ZipCode, #City, #Country, #State, #County);

                logisticsPostalAddress.Location = logisticsLocation.RecId;

                logisticsPostalAddress.ValidFrom = DateTimeUtil::utcNow();

                logisticsPostalAddress.ValidTo = DateTimeUtil::maxValue();

                if (logisticsPostalAddress.validateWrite())

                {

                    logisticsPostalAddress.insert();

                }

                else

                    throw error("Postal address");

                //Location

                logisticsLocation = LogisticsLocation::create(#ElectronicAddressName, NoYes::No);

                //Party location

                DirParty::addLocation(dirPerson.RecId, logisticsLocation.RecId, false, true, false);

                //Electronic address

                logisticsElectronicAddress.clear();

                logisticsElectronicAddress.initValue();

                logisticsElectronicAddress.Location = logisticsLocation.RecId;

                logisticsElectronicAddress.Type = LogisticsElectronicAddressMethodType::Email;

                logisticsElectronicAddress.Locator = #Locator;

                logisticsElectronicAddress.LocatorExtension = #LocatorExt;

                //logisticsElectronicAddress = DateTimeUtil::utcNow();

               // logisticsElectronicAddress.ValidTo = DateTimeUtil::maxValue();

                if (logisticsElectronicAddress.validateWrite())

                {

                    logisticsElectronicAddress.insert();

                }

                else

                    throw error("Electronic address");

                //Vendor

                vendTable.clear();

                vendTable.initValue();

                //vendTable.Currency = "";

                vendTable.AccountNum = #VendAccount;

                //vendTable.AccountNum = NumberSeq::newGetNum(VendParameters::numRefVendAccount()).num();

                vendTable.Party = dirPerson.RecId;

                vendTable.VendGroup = #VendGroup;

                vendTable.initFromVendGroup(VendGroup::find(#VendGroup));

                vendTable.Currency = #Currency;

            //    vendTable.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(

               // [3, "CostCenter", #CostCenter, "Department", #Department, "ExpensePurpose", #ExpensePurpose]);

                if (vendTable.validateWrite())

                {

                    vendTable.insert();

                }

                else

                    throw error("Vendor");

            }

        }

        else

            throw error("Person");

        ttscommit;

    }

    catch

    {

        error("Error!");

        return;

    }

    info(" Vendor Created Succesfully !!!");

}

X++ Code To Create Periodic Journal with Multiple lines and also create and post new General journal entry for that created periodic journal in ax 2012

static void CON_PeriodicJournal_Multiplelines(Args _args)

{

    LedgerJournalTrans          ledgerJournalTrans,LedgerJournaltrans1;

    LedgerJournalTable          ledgerJournalTable;

    ledgerJournalname           ledgerJournalname;

    NumberSeq                   numberSeq;

    NumberSequenceTable         numSeqTable;

    container                   cont1,cont2,ledgerDimension,offSetAcctPattern;

    DimensionDynamicAccount     ledgerDim, offsetledgerDim;

    ledgerJournalCheckPost      ledgerJournalCheckPost;

    int                         cnt;

    DimensionDefault            DimensionDefault;

    LedgerJournalPeriodicCopy   LedgerJournalPeriodicCopy;

    CopyMove                    CopyMove;

    LedgerJournalId             periodicLedgerJournalId,genjourLedgerJournalId;

    LedgerJournalACType         periodicACType,periodicOffSetACType;

    DimensionDynamicAccount     periodicAccount,periodicoffsetAccount ;

    AmountCurCredit             credit;

    RecId                       RecId;

     // find ledgerjournalname record

    select firstonly   ledgerjournalname where LedgerJournalName.JournalName =='PerJrn';

    //create ledger journal table

    ledgerjournaltable.journalname      = ledgerjournalname.journalname ;

    ledgerjournaltable.initfromledgerjournalname();

    ledgerjournaltable.name             = " May Periodic Journal";

    ledgerJournalTable.insert();

   //Header Created

    /////Line 1 Created

    //ledgerJournalTable = LedgerJournalTable::find("00553");

      try

        {

            ttsbegin;

            ledgerJournalTrans.clear();

            ledgerJournalTrans.initValue();

            ledgerJournalTrans.JournalNum   = ledgerJournalTable.JournalNum;

            ledgerJournalTrans.TransDate    = today();

            ledgerJournalName = ledgerJournalName::find(ledgerJournalTable.JournalName);

            periodicLedgerJournalId = ledgerJournalTrans.JournalNum ;

            select firstOnly numSeqTable

                where numSeqTable.RecId  == ledgerJournalName.NumberSequenceTable;

            if (numSeqTable)

            {

                numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);

                ledgerJournalTrans.voucher = numberseq.voucher();

            }

            //ledgerJournalTrans.Voucher              = voucher;

            ledgerJournalTrans.AccountType          = LedgerJournalACType::Vend;

            ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;

            // Main account dimensions

            periodicACType = ledgerJournalTrans.AccountType;

            periodicOffSetACType = ledgerJournalTrans.OffsetAccountType;

            cont1=conNull();

            cont2=conNull();

            ledgerDimension =conNull();

            cont2 += ['BusinessUnit','082','Department','023'];

            cnt =2;

            if(ledgerJournalTrans.AccountType  == LedgerJournalACType::Ledger)

            {

                cont1+=['MainAccount','112100',2];

                cont1+=cont2;

                ledgerDim=AxdDimensionUtil::getLedgerAccountId(cont1);

                if(ledgerDim==0)

                {

                        offSetAcctPattern = ['112100','112100'];

                        ledgerDim = AxdDimensionUtil::getLedgerAccountId(offSetAcctPattern);

                }

                ledgerJournalTrans.LedgerDimension  = ledgerDim;

            }

            else

            {

                ledgerDim = DimensionStorage::getDynamicAccount( '000021',ledgerJournalTrans.AccountType);

                ledgerJournalTrans.LedgerDimension  = ledgerDim;

                ledgerDimension +=cnt;

                ledgerDimension +=cont2;

                DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

                ledgerJournalTrans.DefaultDimension = DimensionDefault;

            }

            periodicAccount = ledgerDim;

            cont1=conNull();

            cont2=conNull();

            ledgerDimension =conNull();

            cont2 += ['BusinessUnit','082','Department','023'];

            cnt =2;

            if(ledgerJournalTrans.OffsetAccountType  == LedgerJournalACType::Ledger)

            {

                cont1+=['MainAccount','112140',2];

                cont1+=cont2;

                offsetledgerDim=AxdDimensionUtil::getLedgerAccountId(cont1);

                if(ledgerDim==0)

                {

                        offSetAcctPattern = ['112140','112140'];

                        offsetledgerDim = AxdDimensionUtil::getLedgerAccountId(offSetAcctPattern);

                }

                ledgerJournalTrans.OffsetLedgerDimension  = offsetledgerDim;

            }

            else

            {

                offsetledgerDim = DimensionStorage::getDynamicAccount( '000021',ledgerJournalTrans.AccountType);

                ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;

                ledgerDimension +=cnt;

                ledgerDimension +=cont2;

                DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

                ledgerJournalTrans.OffsetDefaultDimension = DimensionDefault;

            }

            periodicoffsetAccount = offsetledgerDim;

            ledgerJournalTrans.CurrencyCode          = "USD";

            ledgerJournalTrans.AmountCurCredit       = 6000;

            ledgerJournalTrans.FreqCode              = PeriodUnit::Month;

            ledgerJournalTrans.FreqValue             = 1;

            credit = ledgerJournalTrans.AmountCurCredit;

            if (ledgerJournalTrans.validateWrite())

            {

                ledgerJournalTrans.insert();

            }

            ttscommit;

        }

        catch(Exception::Error)

        {

            info(strFmt('Catched an error in row: %1',ledgerJournalTrans.OffsetDefaultDimension));

        }

        info(strFmt('Periodic journal inserted %1',ledgerJournalTable.JournalNum));

        ////Line 2 Created

        try

        {

            ttsbegin;

            ledgerJournalTrans.clear();

            ledgerJournalTrans.initValue();

            ledgerJournalTrans.JournalNum   = ledgerJournalTable.JournalNum;

            ledgerJournalTrans.TransDate    = today();

            ledgerJournalName = ledgerJournalName::find(ledgerJournalTable.JournalName);

            periodicLedgerJournalId = ledgerJournalTrans.JournalNum ;

            select firstOnly numSeqTable

                where numSeqTable.RecId  == ledgerJournalName.NumberSequenceTable;

            if (numSeqTable)

            {

                numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);

                ledgerJournalTrans.voucher = numberseq.voucher();

            }

            //ledgerJournalTrans.Voucher              = voucher;

            ledgerJournalTrans.AccountType          = LedgerJournalACType::Vend;

            ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;

            // Main account dimensions

            periodicACType = ledgerJournalTrans.AccountType;

            periodicOffSetACType = ledgerJournalTrans.OffsetAccountType;

            cont1=conNull();

            cont2=conNull();

            ledgerDimension =conNull();

            cont2 += ['BusinessUnit','082','Department','023'];

            cnt =2;

            if(ledgerJournalTrans.AccountType  == LedgerJournalACType::Ledger)

            {

                cont1+=['MainAccount','112100',2];

                cont1+=cont2;

                ledgerDim=AxdDimensionUtil::getLedgerAccountId(cont1);

                if(ledgerDim==0)

                {

                        offSetAcctPattern = ['112100','112100'];

                        ledgerDim = AxdDimensionUtil::getLedgerAccountId(offSetAcctPattern);

                }

                ledgerJournalTrans.LedgerDimension  = ledgerDim;

            }

            else

            {

                ledgerDim = DimensionStorage::getDynamicAccount( '000021',ledgerJournalTrans.AccountType);

                ledgerJournalTrans.LedgerDimension  = ledgerDim;

                ledgerDimension +=cnt;

                ledgerDimension +=cont2;

                DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

                ledgerJournalTrans.DefaultDimension = DimensionDefault;

            }

            periodicAccount = ledgerDim;

            cont1=conNull();

            cont2=conNull();

            ledgerDimension =conNull();

            cont2 += ['BusinessUnit','082','Department','023'];

            cnt =2;

            if(ledgerJournalTrans.OffsetAccountType  == LedgerJournalACType::Ledger)

            {

                cont1+=['MainAccount','112140',2];

                cont1+=cont2;

                offsetledgerDim=AxdDimensionUtil::getLedgerAccountId(cont1);

                if(ledgerDim==0)

                {

                        offSetAcctPattern = ['112140','112140'];

                        offsetledgerDim = AxdDimensionUtil::getLedgerAccountId(offSetAcctPattern);

                }

                ledgerJournalTrans.OffsetLedgerDimension  = offsetledgerDim;

            }

            else

            {

                offsetledgerDim = DimensionStorage::getDynamicAccount( '000021',ledgerJournalTrans.AccountType);

                ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;

                ledgerDimension +=cnt;

                ledgerDimension +=cont2;

                DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

                ledgerJournalTrans.OffsetDefaultDimension = DimensionDefault;

            }

            periodicoffsetAccount = offsetledgerDim;

            ledgerJournalTrans.CurrencyCode          = "USD";

            ledgerJournalTrans.AmountCurCredit       = 4000;

            ledgerJournalTrans.FreqCode              = PeriodUnit::Month;

            ledgerJournalTrans.FreqValue             = 1;

            credit = ledgerJournalTrans.AmountCurCredit;

            if (ledgerJournalTrans.validateWrite())

            {

                ledgerJournalTrans.insert();

            }

            ttscommit;

        }

        catch(Exception::Error)

        {

            info(strFmt('Catched an error in row: %1',ledgerJournalTrans.OffsetDefaultDimension));

        }

Periodic journal lines Created:

        //info(strFmt('Periodic journal inserted %1',ledgerJournalTable.JournalNum));

    ledgerJournalTable.clear();

    //General Ledger create

    select firstonly   ledgerjournalname where LedgerJournalName.JournalName =='GenJrn';

    //create ledger journal table

    ledgerjournaltable.journalname      = ledgerjournalname.journalname ;

    ledgerjournaltable.initfromledgerjournalname();

    ledgerjournaltable.name             = " General Journal";

    genjourLedgerJournalId= ledgerjournaltable.JournalNum;

    ledgerJournalTable.insert();

    info(strFmt("General journal is %1",ledgerjournaltable.JournalNum));

//General Journal Header Is Created as below:

    //ledgerJournalTrans1.clear();

    //ledgerJournalTrans1.initValue();

    //if(genjourLedgerJournalId)

    //{

       //ledgerJournalTrans = ledgerJournalTrans::find(periodicLedgerJournalId,ledgerJournalTrans.Voucher,true);

      while  select ledgerJournalTrans where ledgerJournalTrans.JournalNum==periodicLedgerJournalId

        {

       ttsBegin;

       ledgerJournalTrans1.initValue();

       ledgerJournalTrans1.data(ledgerJournalTrans);

       ledgerJournalTrans1.JournalNum=ledgerjournaltable.JournalNum;

        //ledgerJournalTrans1.initValue();

        select firstOnly numSeqTable

                where numSeqTable.RecId  == ledgerJournalName.NumberSequenceTable;

            if (numSeqTable)

            {

                numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);

                ledgerJournalTrans1.voucher = numberseq.voucher();

            }

        if(!ledgerJournalTrans1.validateWrite())

        {

           throw error("ERROR");

        }

        else

        {

            ledgerJournalTrans1.insert();

        }

       ttsCommit;

        }

General journal lines Created

        //Post Journal

        ledgerJournalCheckPost      = ledgerJournalCheckPost::newLedgerJournalTable(ledgerJournaltable,Noyes::Yes);

        LedgerJournalCheckPost.run();

        //Lastdate updated

        while  select forUpdate ledgerJournalTrans where ledgerJournalTrans.JournalNum==periodicLedgerJournalId

        {

        ttsBegin;

        ledgerJournalTrans.LastTransferred=today();

        ledgerJournalTrans.update();

        ttsCommit;

        }

}

Info        Message (02:10:22 pm) Periodic journal inserted 00737

Info        Message (02:10:22 pm) General journal is 00738

Info        Message (02:10:22 pm) Number of vouchers posted to the journal: 2