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 :)



 

Types of Maps in Axapta

1. X++ Maps: It can be used as a temp data store for the given scope of a process. This takes us less over head, and is much quicker than a TempTable.

Map class allows you to associate one value (the key) with another value. Both the key and value can be any valid X++ type, including objects. The types of the key and the value are specified in the declaration of the map. The way in which maps are implemented means that access to the values is very fast.

Below is a sample code that sets and retrieves values from a map.

static void UseOfMaps(Args _args)
{
    CustTable           custTable;
    Map                 map;
    MapEnumerator       mapEnumerator;
    CustAccount         accountNumber;
    int                 counter = 0;
    ;

    map = new Map(Types::String, Types::Integer);

    //store into map
    while select custTable
    {
        accountNumber = custTable.AccountNum;
        if (!map.exists(accountNumber))
        {
            map.insert(accountNumber,1);
        }
        else
        {
            map.insert(accountNumber,map.lookup(accountNumber)+ 1);
        }
    }


    //retrieve from map by using MapEnumerator
    mapEnumerator = map.getEnumerator();
    while (mapEnumerator.moveNext())
    {
        accountNumber       = mapEnumerator.currentKey();
        info(strfmt("%1,%2",mapEnumerator.currentKey(),mapEnumerator.currentValue()));
    }
}

2. AOT Maps: A map can unify the access to similar columns and methods that are present in multiple tables. You associate a map field with a field in one or more tables. This enables you to use the same field name to access fields with different names in different tables. Methods on maps enable you to create or modify methods that act on the table fields that the map references.

EXAMPLE:
I have created a Map by navigating to AOT>Data Dictionary>Maps and right click and new and gave it name ‘MapTest’

I have created 4 fields in under Fields node in Map (drag and drop from EDT)

Now the next thing I need to do is to associate the fields in map with the fields in different tables, let say I am taking two tables (CustTable and VendTable).

Notice that above, four fields that I have created in Maps also exist in CustTable as well as VendTable with different names.
To associate fields, go to Mapping node, right click it and click New mapping, and enter the table that you want to associate in Mapping Table field. Like

And the associate fields with fields in MAP

Now I have created a method called printInfo under method node in Maps, which print the value of the map field AccNumber.

public void printInfo()
{
info(strFmt(“Map : AccountNum :%1″,this.AccNumber));
}
Similiarly I have create same methods under method nodes of CustTable and VendTable which are printing their respective AccountNumber fields


Now finally I have created a job see below I  am not describing every line as I have added comments above the line.

When I run this job see what happens

Containers and it's Functions vs Temporary Tables

Containers are dynamic and have no limits. They can contain elements of
almost all data types: boolean, integer, real, date, string, container,
arrays, tables, and extended data types. However, objects may not be stored
in containers.

Containers in AX are used very often. It’s easy to work with them. But…
data in containers are stored sequentially, and thus retrieved sequentially.
This means that containers provide slower data access if you are working with
_large numbers_ of records. In case of large numbers of records use temporary
tables.

Containers vs Temporary Tables

There is question arises why we use temporary tables when we have containers . These containers which can store almost every  data type values and also while there are lot of functions available for containers. Answer is that on temporary temple we can set indexes on fields and by this way we can fetch data much faster. Although data is stored in container  sequentially but on insertion  a new copy is generated which is  performance over head. When data is increases in container, container starts to be heavy.  Similar when you passed temporary table to any method it passed by reference. But when we passed container to method, a new copy is generated and used in method. Container will used only when fewer values will be manipulated. In the case of larger set of data, we have to use temporary tables.

Declaration of containers.

container  firstContainer;

container  secondContainer = [3,"Ali","RAza"];

Insert value:

 There are two functions used for insert a value in container, conPoke and  conIns

 ConIns

 secondContainer=conIns(secondContainer,2,"Zaidi");  

conPoke:

 secondContainer=conpoke(secondContainer,2,”test"); 

There difference between conPoke and conIns is that conIns, insert a new value at location, and rest of value is shifted to one next Index. conPoke replace the value at insert location.

Read value from container.

The value from container can be read with conPeek function,  this method take two parameter, first one is container  and second parameter is for getting index. The conPeek function read value of any type so and read for any type.

_value  = conPeek(secondContainer,1); //Read 3 

Removing the value from Container:

 Condel function is used to remove the value from container.

secondContainer =conDel(secondContainer,2,1);

conDel Function Overview:

container conDel(container container, int start, int number)

Parameter	Description
container	The container from which to remove elements.
start	The one-based position at which to start removing elements.
number	The number of elements to delete.

conNull function is used to clear all the value from container as

secondContainer=conNull() ;

confind:

 This method find the index of the value which is required to be searched , if value is not found zero will be return

_found =conFind(secondContainer,"RAza");

info(int2str(_found));

_found =conFind(secondContainer,"Abc");

 Loop through container:

 Usually we have to loop through the container.

_lenght = conLen(secondContainer);

for (counter =1; counter <=_lenght; count++)

{
  info(strfmt("%1",conPeek(firstcontainer,counter)));
}

Happy Daxing:)