Excel Import in ax 2012

 static void ExcelImport(Args _args)

{

    int row = 1;

    int counter = 0;

    int column = 1;

    Dialog  _dialog;

    DialogField _file;

    SysExcelApplication application;

    SysExcelWorkbooks   workbooks;

    SysExcelWorkbook    workbook;

    SysExcelWorksheets  worksheets;

    SysExcelWorksheet   worksheet;

    SysExcelCells   cells;

    COMVariantType  type;

    ItemId itemId;

    EcoResProduct ecoResProduct;

    FileName    filename;

    _dialog = new Dialog("Please select the file to load");

    _dialog.addText("Select file:");

    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();

    if (_dialog.closedOK())

    {

        application = SysExcelApplication::construct();

        workbooks = application.workbooks();

        //specify the file path that you want to read

        filename =_file.value(); //ExcelSheet File Name

        try

        {

            workbooks.open(filename);

        }

        catch (Exception::Error)

        {

            throw error('File cannot be opened');

        }

        workbook = workbooks.item(1);

        worksheets = workbook.worksheets();

        worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number

        cells = worksheet.cells();

        try

        {

            do

            {

                row++;

                if (cells.item(1, 1).value().bStr() == "ItemId")

                {

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

                }

                else

                {

                    throw error("The 'ItemId' field should be in 1st column");

                }

                if (itemId)

                {

                    ecoResProduct = EcoResProduct::findByDisplayProductNumber(itemId,true);

                    if (ecoResProduct)

                    {

                        counter++;

                        //ttsBegin;

                        //ecoResProduct.ECommerceAvailable_BB = NoYes::No;

                        //ecoResProduct.update();

                        //ttsCommit;

                    }

                    else

                    {

                         info(strFmt("Item %1 not processed", itemId));

                    }

                }

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

            }

            while (type != COMVariantType::VT_EMPTY);

            info(strFmt("%1 item updated", counter));

        }

        catch (Exception::Error)

        {

            workbooks.close();

            CodeAccessPermission::revertAssert();

            application.quit();

            ttsabort;

        }

        workbooks.close();

        CodeAccessPermission::revertAssert();

        application.quit();

    }

}

Update Recordset for Updating other company records by Crosscompany in Ax 2012

Update Recordset by crossCompany

  while select BB_CompanyId from bB_RippleTable where bB_RippleTable.BB_PriceExpiration ==            NoYes::Yes
    {
      // You must disable update method, database log and alerts, otherwise compiler will throw an    error, for example:
        inventTable.skipDataMethods(true);
        inventTable.skipDatabaseLog(true);
        inventTable.skipEvents(true);
        update_recordSet crossCompany inventTable
        setting RepairFee_BB = 100
        where inventTable.ItemId == "fit123"
        && inventTable.dataAreaId == bB_RippleTable.BB_CompanyId;
     
        info('done');
    }

///note : some time it will not work for all users the user must have admin rights to do, so in that
// case we doing in different way as below

   while select CompanyId from bB_RippleTable where bB_RippleTable.PriceExpiration == NoYes::Yes
    {
        changeCompany(bB_RippleTable.CompanyId)
        {
            ttsBegin;
            inventTableModule = null;
            select forUpdate inventTableModule
                where inventTableModule.ItemId == _itemid
                && inventTableModule.ModuleType == ModuleInventPurchSales::Purch
                && inventTableModule.dataAreaId == bB_RippleTable.CompanyId;

            if(inventTableModule.RecId != 0)
            {
                inventTableModule.PriceExpirationDate_BB = _date;
                inventTableModule.update();
                info(strFmt("@BBX5747",inventTableModule.PriceExpirationDate_BB,bB_RippleTable.CompanyId));
            }
            ttscommit;
        }
    }

How to disable Standard delete button in D365 Form

Disabled delete button in D365

FormCommandButtonControl delButton;

    public void init()
    {
        #SysSystemDefinedButtons

        super();
delButton = this.control(this.controlId(#SystemDefinedDeleteButton)) as FormCommandButtonControl;
        delButton;.visible(false);
    }

COC Methods for Extending form control enable or visible in D365

Form Control Methods in D365


[ExtensionOf(formstr(CustFreeInvoice))]
public final class CustFreeInvoice_Form_Extension
{
    void init()
    {
        FormRun formRun = this;
        FormCheckBoxControl ReleaseOrderOnCreditLimit = formRun.design().controlName(formControlStr(CustFreeInvoice,ReleaseOrderOnCreditLimit));

        FormDataSource CustInvoiceTable_DS = formRun.dataSource(formDataSourceStr(CustFreeInvoice,CustInvoiceTable));
         
        next init();

   /*
Other way to get control name
                                                                formRun.design().controlName('TaxVATNum'),
                                                                formRun.design().controlName('VATNum'),
                                                                formRun.design().controlName('NumCheckCtrl'),
                                                                formRun.design().controlName('ViesCheckCtrl'),

*/

        ReleaseOrderOnCreditLimit.visible(false);

}

}

Sysda classes in D365

Sysda Classes with example 

class RunnableClass1
{     
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
 
//SysDa insetopertaion includes using two API's
//SysDainsertobject - used to create insert_recordset statement
//SysDainsertstatement - used to perform insert_recordset operation

//sample query
    //insert_recordset  CustomRDPTmpTable(AccountNum, BankAccount, ContactPersonId, CustGroup, Currency)
    //select AccountNum, BankAccount, ContactPersonId, CustGroup, Currency from CustTable
    //where CustTable.AccountNum == 'BRMF-000001'
public static void main(Args _args)
    {

//first part- using sysdainsertobject
//Target table
CustomRDPTmpTable customtable;
   
//initialize sysdainsertobject 
var insertobject = new SysDaInsertObject(customtable);

//add fields to be inserted in target table
insertobject.fields()
.add(fieldStr(CustomRDPTmpTable, AccountNum))
.add(fieldStr(CustomRDPTmpTable, BankAccount))
.add(fieldStr(CustomRDPTmpTable, ContactPersonId))
.add(fieldStr(CustomRDPTmpTable, Currency))
.add(fieldStr(CustomRDPTmpTable, CustGroup));

//insert_recordset  CustomRDPTmpTable(AccountNum, BankAccount, ContactPersonId, CustGroup, Currency)

//Second part- creating select statement using SysDaqueryobject
//source table
CustTable custtable;

//intialize sysdaqueryobject
var qe = new SysDaQueryObject(custtable);

//select fields from source table
//select AccountNum, BankAccount, ContactPersonId, CustGroup, Currency from CustTable
var s1 = qe.projection()
.add(fieldStr(CustTable, AccountNum))
.add(fieldStr(CustTable, BankAccount))
.add(fieldStr(CustTable, ContactPersonId))
.add(fieldStr(CustTable, Currency))
.add(fieldStr(CustTable, CustGroup));

//adding where clause  
qe.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(custtable, fieldStr(CustTable, AccountNum)),
new SysDaValueExpression('BRMF-000001')));

//now select query is complete
//select AccountNum, BankAccount, ContactPersonId, CustGroup, Currency from CustTable
//where CustTable.AccountNum == 'BRMF-000001'

//third part - Assign the query to insert statement
insertobject.Query(qe);

//fourth part - using SysDainsetstatement
//execute the query using executequery method
var insertstatement = new SysDaInsertStatement();

ttsbegin;
insertstatement.executeQuery(insertobject);
ttscommit;

//printing target values
CustomRDPTmpTable customloc;

select * from customloc where AccountNum == 'BRMF-000001';
Info(any2Str(customloc.AccountNum) + " " + any2Str(customloc.Currency) + " " + any2Str(customloc.CustGroup));

    }

}



containerLineQueryObject.whereClause(new SysDaEqualsExpression(

                    new SysDaFieldExpression(containerLineInventDim, fieldStr(InventDim, InventDimId)),

                    new SysDaFieldExpression(_containerLine, fieldStr(WHSContainerLine, InventDimId)))

            .and(new SysDaEqualsExpression(

                    new SysDaFieldExpression(containerLineInventDim, fieldStr(InventDim, InventStatusId)),

                    new SysDaFieldExpression(_loopInventDim, fieldStr(InventDim, InventStatusId)))));
------------------------------------------------------------------------------------------------------

For the Update_recordset, you’ll create QueryObjects the same as Insert_recordset(), and pass that to a update object and setup the ‘settingClause’.  This will also need to be extended.



        AllFieldTypesTable source;

        var qe = new SysDaQueryObject(Source);



        var uo = new SysDaUpdateObject(source);

        uo.settingClause()

           .add(fieldStr(AllFieldTypesTable, Id), new SysDaPlusExpression(new SysDaFieldExpression(source, fieldStr(AllFieldTypesTable, Id)), new SysDaValueExpression(100)))

           .add(fieldStr(AllFieldTypesTable, String), new SysDaValueExpression("Banana"));





        new SysDaUpdateStatement().execute(uo);

Form Methods by COC Extension in D365

Form Datasource Method in Extension

[ExtensionOf(formDataSourceStr(LogisticsElectronicAddress,LogisticsElectronicAddress))]

final class CustomLogisticsElectronicAddressform_Extenison

{

public void modified()

    {

          FormDataObject fdo = any2Object(this) as FormDataObject;

          FormDataSource fds = fdo.datasource();

          LogisticsElectronicAddress logisticsElectronicAddress = fds.formRun().dataSource(formDataSourceStr(smmOutlookMyContacts,LogisticsElectronicAddress)).cursor();

/// In other way we can get form datasource value by as below
/// FormDataSource  logisticsElectronicAddress_DS = element.logisticsElectronicAddress_DS  as FormDataSource  

          next modified();

          logisticsElectronicAddress.enableFields();

    }

}

Form Datasource Field Method in Extension

[ExtensionOf(formDataFieldStr(InventJournalCount,JAD_InventJournalTrans,FailureReasonId))]

final class JADInventJournalCountJAD_InventJournalTransDSField_Extension

{

    public void modified()

    {

        FormDataObject fdo = any2Object(this) as FormDataObject;

        FormDataSource fds = fdo.datasource();

        JAD_InventJournalTrans jAD_InventJournalTrans = fds.formRun().dataSource(formDataSourceStr(InventJournalCount,JAD_InventJournalTrans)).cursor();

        InventJournalTrans  inventJournalTrans = fds.formRun().dataSource(formDataSourceStr(InventJournalCount,InventJournalTrans)).cursor();

        next Modified();

            

 jAD_InventJournalTrans.Description =  JAD_WarehouseFailureReason::find(jAD_InventJournalTrans.FailureReasonId, JAD_WarehouseFailureReasonType::Counting).Description;

 jAD_InventJournalTrans.InventJournalTrans = inventJournalTrans.RecId;

    }

}

Get select field range value from AOT Query dialog parameter in Dynamics AX

this.Query().dataSourceTable(tableNum(<Table>)).rangeField(fieldNum(<Table>,<Field>)).value();

Accessing AXFORM global variables in EventHandler methods Dynamics 365 for finance and operations

Note: This is only for privately declared variables in FORM


Step 1:
[ExtensionOf(formStr(PurchTable))]
final class DevFormPurchTable_Extension
{
PurchTableType parmpurchTableType()
{
return purchTableType;
}
}

Step 2:
class DevPurchTableEventHandler
{
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
[FormControlEventHandler(formControlStr(PurchTable, PurchCreateFromSalesorderPurchase), FormControlEventType::Clicked)]
public static void PurchCreateFromSalesorderPurchase_OnClicked(FormControl sender, FormControlEventArgs e)
{
FormRun formRun = sender.formrun();
PurchTableType purchTableTypeGet;
purchTableTypeGet = formRun.parmpurchTableType();
info(strFmt("%1",purchTableTypeGet.parmPurchTable().PurchId));
}
}

List of Table Methods in Ax 2012

Table Methods in Ax 2012

initValue():

If we create a new record from the table browser or a form the table method initValue() is executed. It is used to set a default value for the fields. 

let's overwrite initvalue in our custom Table 

public void initValue()

{

super();

this.custGroupId = "10";

this.CustAccount = "1101";

}

After adding this method, open custom table through Table browser and press ctrl+n to create a new record. The field custGroupId will now have the default value 10 and custaccount  = 1101.

modifiedField():

Each time the value of a field is changed the method modifiedField() is called. It is useful to initialize the values of other fields if the value of the current field is changed.

And overwrite this in modified method of custom table it works when custgroupid is modified the currency value goes empty.

public void modifiedField(fieldId _fieldId)

{

switch(_fieldId)

{

case fieldnum(CusTable, custGroupId):

    this.CurrencyCode ="";

    //this.CurrencyCode="";

   // this.orig();

    break;

default:

    super(_fieldId);

}

}

After adding this method, open custom table using Table browser and try to modify custGroupId of an existing record, then you will notice that CurrencyCode is immediately set to blank.

validateField():

Method validateField() is used for validation only and will return true or false. If the return value is false, the application user will be prevented to continue changing a field value.

public boolean validateField(fieldId _fieldIdToCheck)

{

    boolean ret;

    ret = super(_fieldIdToCheck);

    if (ret)

    {

    switch (_fieldIdToCheck)

    {

    case fieldnum(CusTable, cusname):

        if (strlen(this.cusname) <= 3)

        ret = checkFailed("Customer name must be longer than 3 characters.");

    }

    }

    return ret;

}

After adding this method open custom table press Ctrl+N, in the new record try to enter less than 3 characters for field custName, Ax will throw warning message stating “Customer name must be longer than 3 characters.” And you will be asked to enter value again. Thus we validate the data to be entered for a specific field.

validateWrite():

Method validateWrite() will just check mandatory fields and is triggered when the record . Checks made by validateWrite() are the same as the super() call in validateField().So if your condition is not related to the value an application user enters in a specific field, you should put the validation in validateWrite().

ValidateDelete():

While deleting a record if we want to put any validation we can use this method. Here once I delete a record populating a info that deleted record.

public boolean validateDelete()
{
boolean ret;
ret = super();
info(this.AccountNum);
return ret;
}

ValidateWrite():

This method will get to fire when we update a record. here I am using to check mandatory field for address AccountNum

public boolean validateWrite()
{
boolean ret;
;
if(this.Address != "")
ret = super();
else
warning(" Please fill the address value");
return ret;
}

insert() and update():

Insert() and update() are rarely overridden. However, if you need to ensure a field has a certain value upon inserting a record, you can initialize your field before calling super() in insert().  Some special cases might also require overriding these methods; for example, if you need to synchronize the content of a saved record to another table.

Using X++ for entering data requires a bit more than using the user interface like forms. Only the table methods called will be executed.

find() :-

All tables should have at least one find method that selects and returns one record
from the table that matches the unique index specified by the input parameters.
The last input parameter in a find method should be a Boolean variable called
'forupdate' or 'update' that is defaulted to false. When it is set to true, the caller object
can update the record that is returned by the find method.

static CusTable find(CustAccount   _custAccount,

                      boolean       _forUpdate = false)

{

    Custable cusTable;

    ;

    if (_custAccount)

    {

        if (_forUpdate)

            custable.selectForUpdate(_forUpdate);

        select firstonly cusTable

            index hint AccountIdx

            where cusTable.CustAccount== _custAccount;

    }

    return cusTable;

}

exists() :-

As with the find method, there should also exist an exists method.
It basically works the same as the find method, except that it just returns true if a
record with the unique index specified by the input parameter(s) is found.
In the next example from the InventTable you can see that it returns true if the
input parameter has a value AND the select statement returns a value.

static boolean exist(ItemId itemId)
{
return itemId && (select RecId from inventTable
index hint ItemIdx
where inventTable.ItemId == itemId
).RecId != 0;
}

Display Method:

         Indicates that the methods return value is to be displayed on a forms (or) Reports .The value cannot be altered in the form or report

Take the new method in a table, and then drag that method into the grid and set data source properties. In that field is non-editable.

We can create display method on the

1. Table methods

2. Form methods

3. Form data source methods

4. Report methods

5. Report design methods

Display Name names ()

{

    CustTable   custTable;

    ;

    return  CustTable::find(this.CustAccount).Name;

}

X++ Code to Upload Budget In Dynamics 365

Upload Budget Transaction  in AX 7

//Am here written in Button Clicked in form Extension

[ExtensionOf(formStr(BudgetTransaction))]
final class Fcc_BudgetUpload_Extension
{
   
    /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    [FormControlEventHandler(formControlStr(BudgetTransaction, BudgetLineUpload), FormControlEventType::Clicked)]
    public  void BudgetLineUpload_OnClicked(FormControl sender, FormControlEventArgs e)
    {
        AsciiStreamIo                                   file;
        Array                                           fileLines;
        FileUploadTemporaryStorageResult                fileUpload;

        BudgetTransactionHeader budgetTransactionHeader;
        BudgetTransactionLine transLine;
        Fcc_BudgetLineUpload StagingTmp;
        TransDate   transDate;
        Name        AccountStructure;
        BudgetType  budgetTypeupd;
        RefRecId    headerRecid;
        Name        MainAccount,Depart,businessUnit,Costcenter;
        LineNumber  lineNumber;
        CurrencyCode    currency;
        Amount          transAmount;
        Name      budgetTypestr;
        boolean    first = true;
        str        tds;
        container record;
        FormDataSource   BudgetTransactionLine_Ds;
        int     linenum =1;

        BudgetTransactionLine_Ds = sender.formRun().dataSource(formDataSourceStr(BudgetTransaction,BudgetTransactionLine));
        budgetTransactionHeader =  sender.formRun().dataSource(formDataSourceStr(BudgetTransaction,BudgetTransactionHeader)).cursor();

        StagingTmp.skipDataMethods(true);
 


        fileUpload = File::GetFileFromUser() as FileUploadTemporaryStorageResult;
        file = AsciiStreamIo::constructForRead(fileUpload.openResult());
        if (file)
        {
            if (file.status())
            {
                throw error("@SYS52680");
            }
            file.inFieldDelimiter(',');
            file.inRecordDelimiter('\r\n');
        }
        ttsbegin;
        while (!file.status())
        {
            record = file.read();
            if (conLen(record))
            {

                if(first)
                {
                    first = false;
                }
                else
                {
                    tds         = strReplace(conPeek(record,1),"/","-");
                    transDate   = str2Date(tds,123);//conPeek(record,9);
                    MainAccount = conPeek(record,2);
                    businessUnit = conPeek(record,3);
                    Costcenter   = conPeek(record,4);
                    Depart       = conPeek(record,5);
                    currency     = conPeek(record,6);
                    budgetTypestr = conPeek(record,7);
                    transAmount = conPeek(record,8);
               
                 

                    StagingTmp.clear();
                    StagingTmp.HeaderRecId= budgetTransactionHeader.RecId;
                    StagingTmp.TransDate = transDate;
                    StagingTmp.LedgerDimension = this.generateLedgerDimension(MainAccount,[Depart,businessUnit,Costcenter]);
                    StagingTmp.CurrencyCode = currency;
                    StagingTmp.TranscationAmount = transAmount;
                    StagingTmp.BudgetType = str2enum(budgetTypeupd,budgetTypestr);
                    StagingTmp.LineNumber  = linenum;
                    StagingTmp.insert();
                    linenum++;

                }
            }
        }
            ttsCommit;

        if(StagingTmp)
        {
            while select StagingTmp
            {
                transLine.BudgetTransactionHeader = StagingTmp.HeaderRecId;
                transLine.Date = StagingTmp.TransDate;
                transLine.LineNumber = StagingTmp.LineNumber;
                transLine.BudgetType =StagingTmp.BudgetType;
                transLine.LedgerDimension = StagingTmp.LedgerDimension;
                transLine.TransactionCurrency = StagingTmp.CurrencyCode;
                transLine.editBudgetType(true,transLine.BudgetType);
                transLine.editTransactionCurrencyAmount(true,StagingTmp.TranscationAmount);
                transLine.insert();
            }
        }

        BudgetTransactionLine_Ds.reread();
        BudgetTransactionLine_Ds.research();
        BudgetTransactionLine_Ds.refresh();
    }


//New  Method to return Ledger Dimension

    public DimensionDynamicAccount   generateLedgerDimension(MainAccountNum  _MainAccountNum, container    _conData)
    {
        int hierarchyCount;
        int hierarchyIdx;
        RecId                   dimAttId_MainAccount;
        LedgerRecId            ledgerRecId;
        RefRecId recordvalue;
        DimensionAttribute     dimensionAttribute;
        DimensionAttributeValue dimensionAttributeValue;
        DimensionSetSegmentName DimensionSet;
        DimensionStorage         dimStorage;
        MainAccount              mainAccount;

        LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
        DimensionAttributeValueContract  ValueContract;
        List   valueContracts = new List(Types::Class);
        dimensionAttributeValueCombination dimensionAttributeValueCombination;

        container                  _conD =["Department","BusinessUnit","Costcenter"];
        mainAccount = MainAccount::findByMainAccountId(_MainAccountNum);

        recordvalue = DimensionHierarchy::getAccountStructure(mainAccount.RecId,Ledger::current());
        hierarchyCount = DimensionHierarchy::getLevelCount(recordvalue);
        DimensionSet = DimensionHierarchyLevel::getDimensionHierarchyLevelNames(recordvalue);

        for(hierarchyIdx = 1;hierarchyIdx<=hierarchyCount;hierarchyIdx++)
        {
            if(hierarchyIdx == 1)
           continue;

            dimensionAttribute = DimensionAttribute::findByLocalizedName(DimensionSet[hierarchyIdx],false,"en-us");
            if(dimensionAttribute)
            {
                dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,conPeek(_conData,hierarchyIdx));

                if(dimensionAttributeValue)
                {
                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(dimensionAttribute.Name) ;
                    ValueContract.parmValue(dimensionAttributeValue.CachedDisplayValue);
                    valueContracts.addEnd(ValueContract);
                }
            }
        }

        LedgerAccountContract.parmMainAccount(_MainAccountNum);
        LedgerAccountContract.parmValues(valueContracts);
        dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
        dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
        ledgerRecId = dimensionAttributeValueCombination.RecId;

        return ledgerRecId;
    }

}

Passing Multiple Records From One form to Another In Ax 2012

voidclicked()

{

    int         recordsCount;

    SalesTable  salesTable;

    container   con;

    Args        args;

    str         multiSelectString;

    args = newArgs();

    // gets the total records selected

    recordsCount = salesTable_ds.recordsMarked().lastIndex();

    salesTable= salesTable_ds.getFirst(1);

    while(salesTable)

    {

        // storing recid of selected record in container

        con = conIns(con,1, salesTable.RecId);

        // converting container to string with comma separated

        multiSelectString = con2Str(con,’,’);

        salesTable= SampleTable_ds.getNext(); // moves to next record

    }

   

    // passing string

    args.parm(multiSelectString);

    // calling menu item

    newMenuFunction(menuitemDisplayStr(NewFormMenuItem), MenuItemType::Display).run(args);

}

Now in the above method we have already prepared our args in a container and have passed that to a new form of menu item "NewFormMenuItem"

In order to retrieve passed arguments in the recipient from. Override the init() method of new form as shown

public void init()

{

    container   con;

    int         i;

    str         multipleRecords;

   

    super();

   

    // to get string value from caller

    multipleRecords = element.args().parm();

    // string to container

    con = str2con(multipleRecords,”,”);

    // for sorting

    for(i = 1;i<= conLen(con) ;i++)

    {

        salesTable_ds.query().dataSourceTable(Tablenum(SalesTable)).addRange(fieldNum(SalesTable,RecId)).value(SysQuery::value(conPeek(con,i)));

    }

}