How to read excel and update record in AX2012 through X++ code

static void POCUpdatePurchaseDetails_JS(Args _args)

{

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    Dialog dialog = new     Dialog("Excel upload utility");

    dialogField             dialogFilename;

    int row;

    int                     totalUpdatedRecord = 0;

    FileName                filename;

    InventTable             inventTable;

    ItemId                  itemId;

    ItemGroup               itemGroup;

    InventTableModule       inventTableModule;

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"@SYS53125");

    dialog.filenameLookupTitle("Update data from excel file");

    dialog.filenameLookupFilter(["@SYS28576",'*XLSX', "@SYS28576",'*XLS']);

    if (!dialog.run())

        return;

    filename = dialogFilename.value();

    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();

    row=1;

    do

    {

        row++;

        itemId = cells.item(row, 1).value().bStr();

        ttsbegin;

        inventTable = InventTable::find(itemId,true);

        inventTableModule=InventTableModule::find(inventTable.ItemId,ModuleInventPurchSales::Purch,true);

        if (inventTable)

        {

            inventTableModule.Price=str2num(cells.item(row, 2).value().bStr());

            inventTableModule.PriceUnit=str2num(cells.item(row, 3).value().bStr());

            inventTableModule.update();

            totalUpdatedRecord++;

        }

        ttscommit;

        type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    info(strFmt('Data updated, total record=%1 record',totalUpdatedRecord));

}

Error while restarting AX 2012 service

Issue: The database Servername\ModelDatabase is not recognised as a model store

Below error occurred while restarting AX2012 service after database restore:

 

  

Event log error:

Object Server 01:The database Servername\ModelDatabase is not recognized as a model store

Object Server 01:  Fatal SQL condition during login. Error message: "[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user “User name”

Solution: 

1. This issue happen when service account do not have access on the database from where we took backup. So simply AOS component can be uninstalled and installed again which will fix the issue.

2. After database refresh add AX service account in database security section and provide proper permission which will fix the issue.

login. Error message: "[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user “User name”

D365 Finance and Operations

D365 Finance and Operations is the latest version of Microsoft Dynamics ERP. D365 Finance and Operations is a cloud based enterprise resource planning (ERP) service for enterprises and built on Microsoft Azure.

D365 Finance and Operations facilitates and support customer's unique requirement and also provide the flexibility to modify ERP system to align their requirement to support operation in more effective and efficient way without any changes in operation processes.

Moreover, D365FO provide better platform through ERP functionality to run constantly changing business in competitive and challenging environment without hassle of maintaining infrastructure and software.

Refresh Caller form data source in AX 2012

Refresh Caller form data source:
Parent Form: POCVehicleForm
Child Form: POCInsurenceMaster

Write below code snippet on click method of button on POCVehicleForm

void clicked()
{
    MenuFunction        mf;
    Args                        args;
    ;

    args= new Args();
    args.record(POCVehicle);

    mf=new MenuFunction(identifierStr(POCInsurenceMaster), MenuItemType::Display);
    mf.run(args);
}

Overwrite below method on POCInsurenceMaster:

 public void closeOk()

    {

        #Task

        FormRun formRun;

        super();

        // Buffer of calling form.

        formRun = element.args().caller();

        // Check if caller is form

        if(formRun)

        {

            formRun.task(#taskF5);

        }

    }

Open POCInsurenceMaster from POCVehicleForm and once update record and close child form then parent form will be refreshed.

Update multiple record through MultiSelectionHelper class in AX 2012

Main method of class:

public static void main(Args args)

{

    FormDataSource                      formDataSource;

    TableName                           tableName;

    FormRun                             caller = args.caller();

    MultiSelectionHelper                helper = MultiSelectionHelper::createFromCaller(caller);

;

    // Find correct form data source

    for (i = 1; i <= caller.dataSourceCount(); i++)

    {

        formDataSource = caller.dataSource(i);

        if (formDataSource.table() == tableNum(TableName))

        {

            break;

        }

    }

    //Create ranges for the selected records

    helper.createQueryRanges(formDataSource.queryBuildDataSource(), fieldStr(TableName, RecId));

    // Traverse the selected records

    tableName = helper.getFirst();

    ttsBegin;

   

    while(tableName)

    {

        // Logic placeholder-Start

       

        // Logic placeholder-End

        tableName = helper.getNext();    

    }

    ttsCommit;

}

Update multiple selected records on form in AX 2012 through Class and Passing parameter from Form to Class

Passing parameter through Args in AX is very easy important functionality. To demonstrate this i used one form and added one button to call class.
Below are the artefacts:

Table:pocVehicle
Class:POCVehicleMasterClass
ManuItem:POCVehicleMasterClass

Method on Form button click:

void clicked()
{

MenuFunction mf;
  //POCVehicle pocVehicle;

Args args=new Args();
;
args.record(pocVehicle);
mf= new MenuFunction(identifierStr(POCVehicleMasterClass), MenuItemType::Action);
mf.run(args);

} 

Main Methods on Class POCVehicleMasterClass :

Public static void main(Args _args)

{
POCVehicle pocVehicle;

POCVehicleMasterClass myClass;

FormDataSource formdatasource;

Common common;
;

myClass= new POCVehicleMasterClass();

if(_args.record().TableId==tableNum(POCVehicle))

{
//Buffer selected records
common=_args.record();
//formdatasource=_args.record().dataSource();
//Records data source
formdatasource=common.dataSource();
//Passing parameter to class updateRecord method
myClass.UpdateRecord(formdatasource,_args);
}

}

Second method on the class:

Public void UpdateRecord(FormDataSource datasource, Args args)
{

POCVehicle pocvehicle;
FormDataSource formDataSource;
int recordCount, recorUpdated;
;

formDataSource= datasource;
//Count selected records
RecordCount= formDataSource.recordsMarked().lastIndex();
recorUpdated=0;

//Update all selected records
for(pocvehicle=formDataSource.getFirst(true)? formDataSource.getFirst(true): formDataSource.cursor() ; pocvehicle ;pocvehicle=formDataSource.getNext())

{
ttsBegin;
pocvehicle.Insurence= NoYes::No;
pocvehicle.update();
ttsCommit;
recorUpdated++;

}
//Refresh caller form datasource with same pointer

formDataSource.research(true);

info(strFmt('%1 records selected and %2 records updated ', RecordCount,recorUpdated));
  
}

AX Interview Question

Hi friends, Before to dive in actual post i would like to thank to all my friends for your love and appriciation. I hope attached document will help to find out some good question and answer for interview. Happy DAXing!!!

In-Place Data upgrade checklist issue in AX 2012 R3 and AX 2012 R2 upgrade

Issue:

We observed this during AX 2012 R2 and AX 2012 R3 data upgrade other than the Launch Data Upgrade stage, a different stage Presynchronization is blocked: instead of showing the ReleaseUpdateCockpit form but the ReleaseUpdateScriptsHistory form pops up unexpectedly. 

Solution:This turned out that back dated rows were recorded into the log table ReleaseUpdateScriptsHistory (ScriptStage=16: Presynchronization; ScriptStage=17: Launch Data Upgrade).  We have dropped the relevant records from the ReleaseUpdateScriptsHistory:

  delete from ReleaseUpdateScriptsHistory where ScriptStage in (16,17,18)

After dropping data Presynchronization and launch data upgrade worked properly.

Happy DAXing!!!