Thursday, September 20, 2012

How to Import CSV files and how make Log files in Ax 2012 through X++

Through following code we can import data in Data base and can make log file for details !!!!

static void CsvFileReadAndInsert(Args _args)
{
    Dialog      dialog  = new Dialog();
    DialogField dialogField;
    AsciiIo importFile,logFile;
    str filePath,fileNameOnly;
    filetype type;
    container record;
    CustTable custTable;
    CustAccount custAccount;
    CustName custName;
    CustGroupId custGroupId;
    CustCurrencyCode custCurrencyCode;
    DirPartyTable dirPartyTable;
    int totalRecords, totalInserted,totalFail;
;
    dialogField=dialog.addField(extendedTypeStr(FilenameOpen),"Select File","Select file to import");
    dialog.caption("File Picker");
    dialog.filenameLookupFilter(['csv','*.csv']);
    if(!dialog.run())
        return;
    [filePath, fileNameOnly, type] = fileNameSplit(dialogField.value());
    importFile = new AsciiIo(dialogField.value(), 'R');
    if((!importFile) || (importFile.status() != IO_Status::Ok))
    {
        warning("Error in opening import file");
        throw(Exception::Error);
    }
    importFile.inFieldDelimiter(",");
    logFile = new AsciiIo(filePath + "\\Customer Import Log File.txt", 'a+');
    if((!importFile) || (importFile.status() != IO_Status::Ok))
    {
        warning("Error in opening log file");
        throw(Exception::Error);
    }
        logFile.write(strfmt("Log Details"));
        logFile.write(strfmt("Import Date: %1  Import Time: %2",date2str(today(),123,2,-1,2,-1,4),time2str(timenow(),1,1)));
    try
    {
    ttsbegin;
    record = importFile.read();
    while(importFile.status() ==  IO_Status::Ok)
    {
        record = importFile.read();
        if(!record)
            break;
        totalRecords = totalRecords + 1;
        custAccount = "";
        custAccount = conpeek(record,1);
        custName=conpeek(record,2);
        custCurrencyCode=conpeek(record,2);
       
        if(custAccount == "")
        {
             logFile.write("Account Num is blank");
             totalFail = totalFail + 1;
             continue;
        }
        select custTable
        where custTable.AccountNum == custAccount;
        if(custTable.RecId)
        {
            logFile.write((strfmt("Customer %1 already exists.",custAccount)));
            custTable.clear();
            custTable = null;
            totalFail = totalFail + 1;
            continue;
        }
            custTable.AccountNum = custAccount;
            custTable.CustGroup=custGroupId;
            custCurrencyCode=custCurrencyCode;
            custTable.insert();
           
       
       
        totalInserted = totalInserted + 1;
    }
    logFile.write((strfmt("Import Log:")));
    logFile.write((strfmt("Total records: %1,added/imported in to the database: %2 and rejected / NOT imported into the database: %3",totalRecords,totalInserted,totalFail)));
    ttscommit;
    }
    catch(Exception::Error)
    {
        Throw(Exception::Error);
    }
}

Happy Coding !!!!

How to read CSV files in AX 2012 through X++ code


Through following code we can read CSV files

static void ReadCsvFile(Args _args)
{
    #File
    IO  iO;
    CustAccount custAccount;
    CustName custname;
    FilenameOpen        filename = "d:\\jit.csv";//To assign file name
    Container           record;
    boolean first = true;
    ;
    iO = new CommaTextIo(filename,#IO_Read);
    if (! iO || iO.status() != IO_Status::Ok)
    {
        throw error("@SYS19358");
    }
    while (iO.status() == IO_Status::Ok)
    {
        record = iO.read();// To read file
        if (record)
        {
            if (first)  //To skip header
            {
                first = false;
            }
            else
            {
             
                custAccount = conpeek(record, 1);//To peek record
                custname = conpeek(record, 2);
                info(strfmt('%1--%2',custAccount,custname));
            }
        }
    }
}

Happy X++ coding!!!

How to create Query in AX 2012 through X ++ code

With the following job we can create and execute the query :

Static void Querytest(Args _Args)
{
    Query query;
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildDataSource queryBuildDataSource1;
    QueryBuildRange queryBuildRange;
    QueryRun  queryRun;
    CustTable custTable;
    DirPartyTable DirPartyTable;
    ;
    query=new query();      //Initialize Query
    queryBuildDataSource=query.addDataSource(tableNum(CustTable));   //Add Query
    queryBuildDataSource.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending); //Add sorting
    queryBuildRange=queryBuildDataSource.addRange(fieldNum(CustTable,AccountNum)); // Add Range
    queryBuildRange.value(queryValue('11')+('*')); // Add Query Value
    queryBuildRange=queryBuildDataSource.addRange(FieldNum(CustTable,AccountNum));
    queryBuildRange.value(queryValue('22')+('*'));
   
    //queryBuildDataSource1=queryBuildDataSource.addDataSource(tableNum(CustTrans)); //Add other Data Source for Joining
    //queryBuildDataSource1.relations(true);// Enable Relation
    // queryBuildDataSource1.joinMode(JoinMode::InnerJoin);//Define Join Mode
   
    queryRun= new QueryRun(query);   //Pass query to run it
    while(queryRun.next())
    {
        custTable=queryRun.get(tableNum(CustTable));
        print(strFmt("Customer Name, Account num and Currency are  %1 , %2 and %3",DirPartyTable::findRec(CustTable.Party).Name,CustTable.AccountNum,CustTable.Currency));
        pause;
    }

}

Happy AX coding!!!!!

Tuesday, September 11, 2012

Important function in x++ for AX 2009 and AX 2012

Important function in X++
I would like to share some important function in x++ Like subStr(), strCmp(),strDel() , strFind (),strfmt () , strLen (),strLwr (),strUpr (),strRep(),systemDateGet(),today(),trunc (),boxExample (),conins(), conLen(), conPeek(), conNull() which seems to easy but some time became very tough to recall in between to coding, so don’t worry and keep coding…….
Sub String in X++
// for cut a string from given posititon SubStr("ABCDEFGHIJ",7,-4) returns the text string "DEFG" (the specified sub-string).

static void subStr(Args _args)
{
  str s;
  real  r;
   ;
    r = strLen("jitendraknit@gmail.com");
    s = subStr("jitendraknit@gmail.com",12, 2);

     print(strFmt("s=%1 and r=%2",s,r));
     pause;
}

String Comparison in X++

static void strCmp(Args _args)
{
  int i=2;
  str s1,s2;
   ;
     s1="string 1";
     //s2="string 1";
     s2="string 2";
     i = strCmp(s1,s2);
       if (0 == i)
        {
          print "s1 and s2 are the same";
        }
        else
        {
            print "s1 and s2 are different";
        }
                 pause;
 }


String Deletion in X++
static void strDel(Args _args)
{
 str s;
 ;
   s = strDel("Jitendrakumar", 5, 2);
     print s;
       pause;//for cut the string size first give from where it will cut thn give till thn it will cut
}


Find characters in string in X++
static void strFind(Args _args)
{
 int i;
  ;
   i = strfind("jitendrakumar", "jit", 0, 3);
    if (1 == i)
      {
        print "Characters are found in string";
      }
    else
      {
        print "Characters are NOT found in string";
      }
        pause;
 }
Strfmt()in X++
static void strfmt(Args _args)
 {
  str s1 ="testing";
  int  s2 = 2;
  real s3 =4.56;
  str  s;
          ;
   s = strfmt("string =%1,Integer = %2, Real = %3, ", s1,s2,s3);
   print s;
   pause;
 }

Length of string in X++
// It will return no. of characters in given string
static void strLen(Args _args)
{
int i;
 ;
    i = strLen("jitendra");
    print(strFmt("i=%1",i));
    pause;
}

Convert string in lower  case in X++

static void strLwr(Args _args)
{
  str l;
   ;
      l = strLwr("JITENDRA");
      print(strFmt("l=%1",l));
      pause;
 }

Convert string in upper  case in X++
static void strUpr(Args _args)
{
  str u;
   ;
      u= strUpr("jitendra");
      print(strFmt("u=%1",u));
      pause;
 }

Repetition of string in X++

static void strRep(Args _args)
{
  str r;
    ;
  r = strRep("xyz ", 5);
  print(strFmt("r=%1",r));
  pause;
}

Convert date into string in x++
// we can get date according to own format
static void systemDateGet(Args _args)
{
  date d;
  str d1,d2,d3 ;
    ;
        d = systemdateget();
        d1 =  date2str(d,123,2,-1,2,-1,4);
        d2 =  date2str(d,231,2,-1,2,-1,2);
        d3 =  date2str(d,321,2,-1,2,-1,4);
   
    print(strFmt("dates are %1,%2,%3,%4",d,d1,d2,d3));
    pause;
 }


Truncate Real value in X++
// will round off all digit after decimal(.)
static void trunc(Args _args)
{
real t;
;
    t = trunc(4.6789);
    print strfmt("t = %1",  t);
    pause;
}

Creation of Dialog in X++
static void dialogTest(Args _args)
{
Dialog dialog;
DialogGroup dialogGroup;
DialogField dialogField;
;
dialog = new Dialog("Test Dialog");
dialogGroup = dialog.addGroup("Customer Details");
dialogField = dialog.addField(extendedTypeStr(Custaccount),"Account Number");
dialog.run();  
}


Create box for prompt in X++

static void boxExample(Args _args)
{

 if(box::yesNo("Are u sure to close this form",dialogbutton::No,"Box Title")==dialogbutton::yes)
{
    print("Closing.......");
    pause;
}
else
{
    print("Not closing......");
    pause;
}

}

Container Operation in X++
// conins to insert into container
// conLen() for the size of the container
// conPeek() to get contents of container item
// conNull() to Assign null value
static void conpeek(Args _args)
{
container c;
int i;
 ;
  c=conNull();
  c =["item1", "item2","jit"];
  c= conIns(c,2,"test");
   for (i = 1 ; i <= conLen(c) ; i++) 
   {
      print conPeek(c, i);
  
 }
 pause;
 }

Monday, September 10, 2012

How to update item bar code in ax 2009



static void updateItemBarCode(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int row;
    Str custID,len;
    Name name;
    FileName filename;
    IntrastatItemCodeId     commCode;
    InventItemBarcode  inventItemBarCode;
    InventTable  inventTable;
    Sysdim      dim;
    ItemId      itemId;
    ItemBarCode itemBarCode;
    BarcodeSetupId barcodeSetupId;
    InventDimId inventDimId;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename ="C:\\New folder (2)\\Barcode2.xlsx";
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    do
    {
       row++;
       itemId=cells.item(row, 1).value().bStr();
   ttsbegin;
    inventTable = InventTable::find(itemId);
    if (inventTable)
    {
        inventItemBarCode.itemBarCode=cells.item(row, 2).value().bStr();
        inventItemBarCode.barcodeSetupId=cells.item(row, 4).value().bStr();
        inventItemBarCode.inventDimId=cells.item(row, 3).value().bStr();
        inventItemBarCode.itemId=itemId;
        inventItemBarCode.insert();
    }
    ttscommit;
    type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
}

How to create General Journal in AX 2012 by X++ code


With following code we can create general journal with default dimention and ledger Account
just change default values according to youe system data and simply run the job


static void Demo_CreateGLJrl(Args _args)
{
    AxLedgerJournalTable   axLedgerJournalTable ;
    AxLedgerJournalTrans   axLedgerJournalTrans;
    container            accPattern;
    container            offSetPattern;
    container            dimPattern1;
    ;
    axLedgerJournalTable = new AxLedgerJournalTable();
    axLedgerJournalTrans = new AxLedgerJournalTrans();
    dimPattern1 = [1,"Department", "000"];
    axLedgerJournalTable.parmDefaultDimension(AxdDimensionUtil::getDimensionAttributeValueSetId(dimPattern1));
    axLedgerJournalTable.parmJournalName("GenJrn");
    axLedgerJournalTable.save();
    axLedgerJournalTrans.parmJournalNum(axLedgerJournalTable.ledgerJournalTable().JournalNum);
    axLedgerJournalTrans.parmTransDate(systemDateGet());
    axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Ledger);
    accPattern = ["420100", "420100", 0];
    axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accPattern));
    offSetPattern = ["420200", "420200", 0];
    axLedgerJournalTrans.parmOffsetAccountType(LedgerJournalACType:: Ledger );
    axLedgerJournalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId( offSetPattern));
    axLedgerJournalTrans.save();
    info(strFmt("Journal %1 created with finential Dimension %2", axLedgerJournalTable.ledgerJournalTable().JournalNum,axLedgerJournalTable.ledgerJournalTable().DefaultDimension));
}

Wednesday, September 5, 2012

Generate number Sequence in AX 2012

Generate number Sequence

Suppose we want create number sequence for Test field on form in General  ledger module
Consideration: EDT-Test, Table-TestTable and Form- TestTable

Step1. Create new EDT with name Test
Step 2. Modify load module() method on  NumberSeqModuleLedger class

{
     datatype.parmDatatypeId(extendedTypeNum(Test));
     datatype.parmReferenceHelp(literalStr("Test"));
     datatype.parmWizardIsManual(NoYes::No);
     datatype.parmWizardIsChangeDownAllowed(NoYes::No);
     datatype.parmWizardIsChangeUpAllowed(NoYes::No);
     datatype.parmWizardHighest(999);
     datatype.parmSortField(30);
     datatype.addParameterType(NumberSeqParameterType::DataArea, true, false);
     this.create(datatype);
}

Step 3.Create a method on LedgerParameters Table

     client server static NumberSequenceReference numRefTest()
{
     return NumberSeqReference::findReference(extendedTypeNum(Test));
}

Step 4.Write and run following job

static void NumberSeqLoadAll(Args _args)
{
    NumberSeqApplicationModule::loadAll();

}


Step 5.Then run the wizard

Organization Administration -> CommonForms -> Numbersequences->Numbersequences-> Generate -> run the wizard.

Step 6.Now we have to check the number sequence  is correctly working  for that write a job:

static void NumSeq(Args _args)
{
    NumberSeq  numberSeq;
    Test num;
    ;
    numberSeq = NumberSeq::newGetNum(ProjParameters:: numRefTest ());
    num = numberSeq.num();
    info(num);
}


Step 7.Now we want that Number Sequence in form level(Test Table):


 Write below code in class declaration  
public class FormRun extends ObjectRun
{
    NumberSeqFormHandler numberSeqFormHandler;

}

 Step 8.Write the NumberSeqFormHandler() in form methods node.

NumberSeqFormHandler numberSeqFormHandler()
{
    if (!numberSeqFormHandler)
    {
       numberSeqFormHandler = NumberSeqFormHandler::newForm(LedgerParameters:: numRefTest ().NumberSequenceId,
                                                             element,
                                                             TestTable_DS,
                                                             fieldNum(TestTable, Test)
                                                            );
    }
    return numberSeqFormHandler;
}


Step 9.Write the  Create(),Delete(),Write() , Validate Write(),Link Active() on the Data source methods node.
 Create() Method
void create(boolean append = false,
            boolean extern = false) 
{
    element.numberSeqFormHandler().formMethodDataSourceCreatePre();

    super(append);

    if (!extern)
    {
        element.numberSeqFormHandler().formMethodDataSourceCreate(true);
    }
}

Delete() Method

public void delete()
{
    element.numberSeqFormHandler().formMethodDataSourceDelete();
    super();
}

Write()Method

public void write()
{
    super();
    element.numberSeqFormHandler().formMethodDataSourceWrite();
}



Validate Write() Method

public boolean validateWrite()
{
    boolean         ret;
    ret = super();
    ret = element.numberSeqFormHandler().formMethodDataSourceValidateWrite(ret) && ret;
    if (ret)
    {
        TestTable.validateWrite();
    }
    return ret;
}

Link Active() Method

public void linkActive()
{
    ;
    element.numberSeqFormHandler().formMethodDataSourceLinkActive();
    super();
}
Step 10.Finally add Close() method on form
void close()
{
    if (numberSeqFormHandler)
    {
        numberSeqFormHandler.formMethodClose();
    }
    super();
}