Monday, July 8, 2019

How to add document note in D365


class VendAccountDocument_JKS

{



    static void main(Args _args)

    {

        VendTable vendTable;

        DocuType docuType;

        DocuRef docuRef;

        vendTable = VendTable::find('BRMF-000001');

        docuType = DocuType::find('Note');

        if (!docuType ||

            docuType.TypeGroup != DocuTypeGroup::Note)

        {

            throw error("Invalid document type");

        }

        docuRef.RefCompanyId = vendTable.dataAreaId;

        docuRef.RefTableId = vendTable.TableId;

        docuRef.RefRecId = vendTable.RecId;

        docuRef.TypeId = docuType.TypeId;

        docuRef.Name = 'Automatic note';

        docuRef.Notes = 'Added from X++';

        docuRef.insert();

        info("Document note has been added successfully");

    }



}

How to read excel and update record in AX2012 :

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

}

How to find SQL query in D365:


How to find SQL query out of the AOT query:


static void AOTQuery(Args _args)
{

    QueryRun queryRun;

    ;

    queryRun = new QueryRun(queryStr(QueryName));//add any name of query here in place of QueryName

    info(queryRun.query().toString());

}

Create Query dynamically in AOT through code:

Create Query dynamically in AOT through code:

static void CreateQueryInAOT(Args _args)
    {
        TreeNode                treeNodeObj;
        Query                   queryObj; // Extends TreeNode class.
        QueryBuildDataSource    qbds;
        QueryBuildRange         qbr;
        QueryRun                qr;
        CustTable               xrecCustTable;
        str                     queryName = "TestQuery";
        // Macro.
        #AOT
        // Delete the query from the AOT, if the query exists.
        treeNodeObj = TreeNode::findNode(#QueriesPath);
        treeNodeObj = treeNodeObj.AOTfindChild(queryName);
        if (treeNodeObj) { treeNodeObj.AOTdelete(); }
        // Add the query to the AOT.
        treeNodeObj = TreeNode::findNode(#QueriesPath);
        treeNodeObj.AOTadd(queryName);
        queryObj = treeNodeObj.AOTfindChild(queryName);
        // Further define the query.
        qbds  = queryObj.addDataSource(tablenum(CustTable));
        qbr   = qbds.addRange(fieldnum(CustTable, CustGroup));
        qbr.value("Inter");
        // Compile the query.
        queryObj.AOTcompile(1);
        queryObj.AOTsave();
        // Run the query.
        qr = new QueryRun("TestQuery");
        while ( qr.next() )
        {
            xrecCustTable = qr.GetNo(1); // 1 means first data source.
            Global::info(strFmt("%1 , %2",
                xrecCustTable.AccountNum, xrecCustTable.CustGroup));
        }

Sunday, June 23, 2019

Saving last value on the form

We can save last value on the form in AX so that when next time form will open it will show last saved value in the certain control:

We will take example to save value of below mark control on inventory counting form:


Below are the steps to enable this functionality:
1. Add below code snippet in class declaration:

AllOpenPosted allOpenPostedTrans
#define.CurrentVersion(1)
#localmacro.CurrentList
allOpenPostedTrans
#endmacro

2. Add below methods on the InventJournalTable form:

//To save default value in case if no last value available
public void initParmDefault()
{
allOpenPostedTrans= AllOpenPosted::All;
}

//To convert object to container
public container pack()
{
return [#CurrentVersion, #CurrentList];
}

//To convert container to object
public boolean unpack(container _packedClass)
{
int version = RunBase::getVersion(_packedClass);
switch (version)
{
case #CurrentVersion:
[version, #CurrentList] = _packedClass;
return true;
default:
return false;
}
return false;
}

//To save last design name
public IdentifierName lastValueDesignName
{
return element.args().menuItemName();
}

//To save last value
public IdentifierName lastValueElementName
{
return this.name();
}

//To save valueType in this case Form
public UtilElementType lastValueType()
{
return UtilElementType::Form;
}

//To store User Id
public UserId lastValueUserId()
{
return curUserId();
}

//To save Legal entity details
public DataAreaId lastValueDataAreaId()
{
return curext();
}
3. Add below code in Run(above Super) and Close(last in method) form's method:
Run:
xSysLastValue::getLast(this);
AllOpenPosted.selection(allOpenPostedTrans );


Close:
allOpenPostedTrans = AllOpenPosted.selection();
xSysLastValue::saveLast(this);


Compile and open form change value in the "show " control and close form next time form will open with last value in the show field.

I hope this will help !!!!











Create dialog run time and update respective record on Dialog

Below are the method to create Dialog dynamically, inter value, fetch value from dialog and update dialog field automatically :

class DialogDemo extends RunBase
{
    DialogField     fieldAccount;
    DialogField     fieldName; 
    DialogField     fieldGroup;
    VendAccount     vendAccount;
    VendName        vendName;
    VendGroupId     vendGroupId;
 
 }

//Convert object to container
    Public Container pack()
    { 
        return conNull();
    }

//Convert container to object
  Public boolean unpack(container _packedClass)
    {
        return true;
    }

// Dialog layout
Object dialog()
{
Dialog          dialog;
dialog = super();
dialog.caption("Customer information");
dialog.allowUpdateOnSelectCtrl(true);
fieldAccount=dialog.addField(Extendedtypestr(VendAccount),"Vendor Account");
fieldName=dialog.addField(Extendedtypestr(VendName));
fieldName.enabled(false);
dialog.addTabPage("Details");
fieldgroup=dialog.addField(Extendedtypestr(vendGroupId));
fieldgroup.enabled(false);
return dialog;

}

//Fetch value from Dialog
public boolean getFromDialog()
{
    vendAccount=fieldAccount.value(); 
    vendName=fieldName.value();
    vendGroupId=fieldGroup.value();
    return super();
}

public static void main(Args _args)
{
    DialogDemo dialogDemo= new DialogDemo();
    if(DialogDemo.prompt())
    {
    DialogDemo.run();
    }
}
//field value assignment
public void dialogSelectCtrl()
{
VendTable   vendTable;
vendTable=vendTable::find(fieldAccount.value());
fieldName.value(vendTable.name());
fieldGroup.value(vendTable.VendGroup);

}

//To execute logic
Public void run()
{
    info(strFmt("Vend Account: %1", vendAccount));
    info(strFmt("Vend Name: %1", vendName));
    info(strFmt("Vend Group: %1", vendGroupId));
}

Saturday, January 5, 2019

Error after clicking on “ Show all fields” in Dynamics AX

Recently i noticed that "show all fields" functionality is not working on Vendor Inter company form. After further investigation i came to know that this functionality is working on most of the form . However, for few forms it through below error.

I do some research and find that there is a bug in system in below method.

This is bug in system. Actually any form name can not be more than 40 char. but in our case '_ShowAllRecords_'+dictTable.name() is 55 char (InterCompanyTradingRelationSetupCustomer(40), _ShowAllRecords(15)) and hence system throw above error.

As work around I removed _ShowAllRecords and now this functionality is working for intercompany form.

Secondly there is also few KB in LCS that can be explored for permanent solution.

I hope above post help to resolve this issue.

Passing parameter between forms in Dynamics AX

How to: Pass Args from one form to another form in AX 2012

Passing parameter from one form to another is easy in AX and this concept help a lot during development and it can be achieved through Args class.
I am going to demonstrate below simple example to understand how Args works and what are the different options available in dynamics AX.

Form1: POCVehicleForm
Form2: POCInsuranceMaster  
And there is one common field vehicleId in both data source.
Task1: Pass record, string value and enum value from Form1 to Form2.
Task2: Filter data on form 2 according to the vehicle Id from form one.
Solution: Added one button on the POCVehicleForm to pass value on POCInsuranceMaster form.
Add below code snippet on "Insurance Master" clicked method on POCVehicleForm .
void clicked()
{
    MenuFunction        mf;
    Args                args;
    FormRun             formRun;
    ;
    //Initialise Args
    args= new Args();

    //Pass record
    args.record(POCVehicle);

    //Pass string value "Vehicle Id"
    args.parm(Grid_VehicleId.valueStr()); 
  
    //Pass Enam value
    args.parmEnum(VehicleType::Truck);

    //Pass enum type
    args.parmEnumType(enumNum(VehicleType));

    formRun=ClassFactory.formRunClass(args);
    formRun.init();
    formRun.run();
    formRun.wait();
}





Add below code snippet on POCInsurance’s init method.
public void init()
{
    POCVehicle              pocVehicle;
    Query                   query;
    ;

    super();
    // Get record buffer
    pocVehicle=element.args().record(); 

    // Validate if parameter is not null and record belong to right table  
    if(element.args() && element.args().record().TableId== tableNum(POCVehicle))
    {

    // Filter data according to the Vehicle ID    POCInsurenceMaster_ds.query().dataSourceName('POCInsurenceMaster').addRange(fieldNum(PO  CInsurenceMaster,vehicleID)).value(SysQuery::value(pocVehicle.VehicleId));

     // Show String and enum value                 
    info(strfmt("PARM:%1,PARMENUM:%2)", element.args().parm(),element.args().parmEnum()));  

}

Below is the result after clicking on Insurance Master button on POCVehicleForm

Below is the Args important methods:


Args Methods Details
record Get and set current or multiple record from caller table
caller Get or set the instance of the caller object
parm Get or sets string value including string field on caller form
parmEnum Get or set enum value
name Get and set the name of the application object to call
parmObject Get or set an instance of any object to pass on called object like any custom class 
parmEnumType Get or set enum name

Passing parameter from Form to Class through Args in Dynamics AX 2012


Friday, January 4, 2019

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

}


Thursday, January 3, 2019

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”