Import Data from Excel Using Dialog (X++)

Hi all,

Today I am going to tell you guys how to Import Data from an Excel file into Ax Table using a Dialog.

Step 1 : Create a Table (ProductType) with three fields as following :

Step 2: Create a class "ImportExcel" that extends Runbase framework and with the following methods :

class ImportExcel extends RunBase
{
   #excel
   #file

   FileNameOpen               fileNameOpen;
   DialogField                     dialogfileNameOpen;

   COMVariant                  comVariant1;
   COMVariantType          comVariant2;
   SysExcelApplication      app;
   SysExcelWorkbooks      workbooks;
   SysExcelWorkbook       workbook;
   SysExcelWorksheets     worksheets;
   SysExcelWorksheet       worksheet;
   SysExcelCells                 cells;
   SysExcelCell                   rcell;

   #define.CurrentVersion(1)
   #localMacro.CurrentList
       fileNameOpen
   #endMacro
}
 

public Object dialog()
{
    DialogRunBase       dialog = super();
;
    dialog.caption("@SYS7444");
    dialogfileNameOpen = dialog.addFieldValue(typeid(FileNameOpen), fileNameOpen);
    dialog.filenameLookupFilter(["@SYS28576",#XLSX, "@SYS28576",#XLS]);
    return dialog;
}


public boolean getFromDialog()
{
    boolean ret;


    ret = super();

    fileNameOpen = dialogfileNameOpen.value();

    return ret;
}


public container pack()
{
    return [#CurrentVersion, #CurrentList];
}

public boolean unpack(container _packedClass)
{
    Version version = RunBase::getVersion(_packedClass);
    ;
    switch (version)
    {
        case(#CurrentVersion) :
            [version, #CurrentList] = _packedClass;
            break;

        default :
            return false;
    }

    return true;
}

public boolean validate(Object calledFrom)
{
    boolean ret;

    ret = super(calledFrom);

    if(!fileNameOpen)
        ret = checkFailed(strfmt('File name cannot be left blank'));

    if(fileNameOpen && !WinApi::fileExists(fileNameOpen))
        ret = checkfailed('File not found');

    return ret;
}

public static void main(Args args)
{
    ImportExcel      importMapping;
    ;

    importMapping  =   new ImportExcel();

    if (importMapping.prompt())
    {
        importMapping.run();
    }
}

public void run()
{
    try
    {
        ttsbegin;
        this.importExcel();
      
        ttscommit;
    }
    catch (Exception::Error)
    {
        ttsabort;
        exceptionTextFallThrough();//to avoid Compiler error
    }
}

And finally the ImportExcel method of the class :

void importExcel()
{
    #AviFiles
    ProductType                 productType;
    int                         row;
    SysOperationProgress        progress1;
    ;
    //Define parameters
    app = SysExcelApplication::construct();
    Workbooks = app.Workbooks();
    COMVariant1 = new COMVariant();
    COMVariant1.bStr(fileNameOpen);
    Workbook = Workbooks.add( COMVariant1);
    Worksheets = Workbook.worksheets();
    Worksheet = Worksheets.itemFromNum(1);
    Cells = Worksheet.Cells();

    progress1   =   new SysOperationProgress();
    progress1.setCaption("Excel import");
    progress1.setAnimation(#AviTransfer);

    delete_from  productType ;

    row = 1;
    do
    {
       progress1.setText(strfmt("Importing row %1", row));
       productType.ID   = cells.item(row,1).value().bStr();
       productType.Type = cells.item(row,2).value().bStr();
       productType.Description = cells.item(row,3).value().bStr();

       productType.insert();
       row++;
       COMVariant2 = cells.item(row, 1).value().variantType();

    }
    while( COMVariant2!= COMVariantType::VT_EMPTY);
    info(strfmt("%1 rows imported", row-1));
} 








Happy Daxing:)

Display methods in Axapta

A display method can be created on either a table (in which case it is available for all forms using that table), or on the datasource of a specific form (in which case it is available only on that form).

Display methods must be created using a specific signature, which varies depending on the location of the method.

Display methods on tables :

When declared on a table, display methods must be preceded with the keyword display and expect no parameters. The following example is a standard display method from the SalesTable table.

<xpp> display CustName customerName() {

   return this.partyTable_CustAccount().Name;

} </xpp>

Display methods on form datasources :

When declared on a form datasource, display methods must have one non-optional parameter.The parameter is a buffer of the same type as that of the datasource on which the method is declared. It is essential as, when viewing a grid on a form, the method must know for which row the return value is to be calculated.

As an Example consider the "ProjTransRevenue" form.It contains "ProjRevenueTrans" as it's datasource.I have a customized method "jss_categoryName" which is a display method.

 

display CategoryName jss_categoryName(ProjRevenueTrans _projRevenueTrans) //datasource parameter
{
   return CategoryTable::find(_projRevenueTrans.CategoryId).CategoryName;
}

  

Caching :

One significant side-effect of using display methods can be the impact on performance.To alleviate this, display methods on tables can be cached by using the cacheAddMethod method of the datasource object. To enable caching of a display method, call cacheAddMethod() from the init method of the datasource. This ensures that the display will only be calculated when necessary and can result in a significant performance improvement.

<xpp> public void init() {

   super();

   // Enable caching of the document handling display fields
   dirPartyTable_ds.cacheAddMethod(tablemethodstr(DirPartyTable, showDocHanIcon));

} </xpp>

Happy Daxing:)

Difference between "Element and This" keyword in AX

Element and This points to the same object in some cases and different in other cases.

In a form method element and this are same and will point to fromRun object, but in the formDataSource method element still points to the formRun object and this points to the formDataSource object.  You cannot use element to refer table or class object.

 It means if your code is placed in a Form datasource method then "element" will point to "FormRun" object and "this" will point to  the  datasource object.

Happy Daxing:)

Insert_Recordset and update_recordset and Delete_from in Axapta

1) Insert_Recordset() :

Insert_recordset copies data from one or more tables directly into one resulting destination table on a single server trip.It is used to insert multiple records from one table to another at a single time.

Syntax:

insert_recordset  DestinationTable  (  ListOfFields  )

select  ListOfFields1  from  SourceTable  [ where  WhereClause  ]

[ join  ListOfFields2  from  JoinedSourceTable 

[ where  JoinedWhereClause  ]]

Example :

static void insert_recordsetExample(Args _args)
{
 PlayerTable        players;
 PlayerTableDemo    playr;
 ;

 players.Name = "Kunal";
 players.Contact = 9711734743;
 players.insert();

 players.Name = "Sahil";
 players.Contact = 9711066466;
 players.insert();

 Insert_Recordset playr(Name,Contact)
 select Name,Contact from players;

}

2) Update_Recordset() :

The update_recordset operator can be used to update a chunk of records in a table in one database operation.

static void update_recordsetExample(Args _args)
{
    ProjCostTrans       projCostTrans;
    ;

    update_recordset projCostTrans setting Cancelled = NoYes::No
    info("Process completed.");
}

It updates all records of ProjcostTrans with Cancelled field as No.

3) delete_from() :

As with the insert_recordset and update_recordset operators, there is also an option for deleting a chunk of records. This operator is called delete_from and is used as the next example shows:

static void delete_fromExample(Args _args)
{
 CarTable   cartable;
 ;
 delete_from cartable where cartable.mileage ==0;
}   

Happy Daxing:)

Difference between Insert() vs doinsert(), update() vs doinsert(),delete() vs dodelete() methods of Axapta

 

1) Update() vs doupdate() method:

 

The doUpdate table method updates the current record with the contents of the buffer. This method also updates the appropriate system fields.

The doUpdate method should be used when the update method on the table is to be bypassed. Suppose you have overridden the update method of the table but sometime there is a situation when you don't want the code written in the overridden update method to be executed and at the same time want any selected record of that table to be updated. In such situation you should call the table.doupdate() method instead of table.update() method.

 

CustTable custTable;
    ttsBegin;
      select forUpdate custTable
      where custTable.AccountNum == '4000';
      custTable.CreditMax = 5000;
      custTable.update();
    ttsCommit;

 

The example selects the table custTable for update. Any records with the AccountNum equal to 4000 are updated (in this case only one). The CreditMax field is changed to 5000.  

static void Job1(Args _args)
{
    CustTable custTable;
    ttsBegin;
    select forUpdate custTable
    where custTable.CreditMax == '3000';
    if (custTable)
    {
       custTable.CreditMax = 1000;
       custTable.doUpdate();
    }

    ttsCommit;

}

2) Insert() vs doinsert() method :

 

The insert method updates one record at a time.

CustTable custTable;
;
ttsBegin;
 
select forUpdate custTable;
custTable.AccountNum = '5000';

custTable.insert();
 
ttsCommit;

 

The doInsert method generates values for the RecId field and other system fields, and then inserts the contents of the buffer into the database. This operation is used when the insert method on the table is to be bypassed. 

 

ttsBegin;

myTable.name = 'Flemming Pedersen';
myTable.value = 100;

myTable.doInsert();

ttsCommit; 

 

3) delete() vs dodelete() method:

The delete method can be overridden, for example, to add extra validation before records are deleted.
If you override the delete method, the original version of the delete method can be executed instead by calling the doDelete method. It is equivalent to calling super() in the delete method; doDelete executes the base version of the delete method.

ttsBegin;

while select forUpdate myTable
    where myTable.AccountNum == '1000'
{
    myTable.delete();
}
ttsCommit;


ttsBegin;
while select forUpdate myTable
    where myTable.AccountNum >='200';
{
    myTable.doDelete();
}
ttsCommit;

Happy Daxing:)

 

Update Table across all Companies in Axapta

This example updates a table across all companies in Axapta.

static void  UpdateProjBudgetTable(Args _args)
{
    DataArea                dataArea;
    DataAreaId            dataAreaId;
    ProjBudgetTable    projBudgetTable;
    ;
    while select dataArea where dataArea.isVirtual == NoYes::No
    {
      dataAreaId = dataArea.id;
      changeCompany(dataAreaId)
      {
            projBudgetTable = null;
            ttsbegin;
            while select forupdate projBudgetTable
            {
                if(projBudgetTable.PrincipalType == "Charterer")
                {
                    projBudgetTable.PrincipalType = "Owner";
                }
                else if(projBudgetTable.PrincipalType == "Owner")
                {
                    projBudgetTable.PrincipalType = "Charterer";
                }
                projBudgetTable.doUpdate();
            }
            ttscommit;
        }
    }
}

Happy Daxing :)

Crosscompany to get data from other companies / Get the data from other companies

Cross company Keyword:

CrossCompany is the Keyword to get the data from other companies in AX.

static void CrossCompanyExample(Args _args)
{
  CustTable   _custtable;
  ;
  while select crosscompany * from _custtable
  {
  info(strfmt("%1 %2",_custtable.AccountNum,_custtable.dataAreaId));
  }
}

It will list the account numbers and the company associated for all the companies in Ax.

Consider listing customers from selected companies.There are two ways to do it.

1) Using  a Container :

static void CrossCompanyExample(Args _args)
{
  CustTable   _custtable;
  Container   list = ["hksa","idss"];
  ;
  while select crosscompany:list * from _custtable
  {
  info(strfmt("%1 %2",_custtable.AccountNum,_custtable.dataAreaId));
  }
}

2) Using a Query :

static void UseOfCrossCompanywithQuery(Args _args)
{
    Query                   query = new Query();
    QueryBuildDataSource    qbds  = query.addDataSource(tableNum(CustTable));
    QueryRun                queryRun;
    CustTable               custTable;
    ;
    query.allowCrossCompany(true);
    query.addCompanyRange("hksa");
    query.addCompanyRange("idss");
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
       custTable = queryRun.get(tableNum(CustTable));
       info(strfmt("%1 %2",custTable.AccountNum,custTable.dataAreaId));
    }
} 

Happy Daxing :)