Dynamic Queries in Ax 2012

To Select All Customers from Custtable

static void CON_DynamicQuery(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbd;

    CustTable               custTable;

   

    q   = new Query();

    qbd = q.addDataSource(tableNum(custTable));

    qbd.addSortField(fieldNum(CustTable,AccountNum),SortOrder::Ascending);

    qr = new QueryRun(q);

    while(qr.next())

    {

      custTable = qr.get(tableNum(custTable));

      info(strFmt("%1",custTable.AccountNum));

       

    }

   

}

   To Select Customers Between Range

static void CON_DynamicQuery(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

   

    q       = new Query();

    qbds    = q.addDataSource(tableNum(custTable));

    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);  // order by

    qbr     = qbds.addRange(fieldNum(custTable,AccountNum)); // For all customers

    qbr.value(queryRange('US-001','US-009')); // for particular range

  

    qr = new QueryRun(q);

    while(qr.next())

    {

        custTable = qr.get(tableNum(custTable));

        info(custTable.AccountNum);

       

    }

}

                                Where Condition

static void Job34(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

   

    q   = new Query();

    qbds = q.addDataSource(tableNum(custTable));

    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    qbds.addSortField(fieldNum(custTable,CustGroup),SortOrder::Descending);

    qbr = qbds.addRange(fieldnum(custTable,CustGroup));

    qbr.value(queryRange('10','30'));

   

    qr  = new QueryRun(q);

    while(qr.next())

    {

        custTable   = qr.get(tableNum(custTable));

        info(strFmt("%1--%2",custTable.CustGroup,custTable.AccountNum));

    }

}

                                CrossCompany

static void CON_DynamicStatement(Args _args)

{

    CustTable       custTable;

   

    Query                   query;          

    QueryBuildDataSource    qbds;

    QueryRun                qr;

   

    query   = new Query();

    query.allowCrossCompany(true);

   // query.addCompanyRange('USMF');

   // query.addCompanyRange('IBM');

    qbds  = query.addDataSource(tableNum(custTable));

    qr    = new QueryRun(query);

    while(qr.next())

    {

      

            custTable = qr.get(tableNum(custTable));

            info(strFmt('%1-%2',custTable.AccountNum,custTable.dataAreaId));

    

    }

   

}

                                Aggregate Function -- min,max,sum,count,computedcolumn,database

static void CON_Dynamic(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q = new Query();

    qbds = q.addDataSource(tableNum(custTrans));

    qbds.addSelectionField(fieldNum(custTrans,AmountMST),Selectionfield::Min);

    qbds.addSelectionField(fieldNum(custTrans,RecId),SelectionField::Count);

   

    qr      = new QueryRun(q);

    while(qr.next())

    {

        custTrans = qr.get(tableNum(custTrans));

        info(strFmt("%1",custTrans.AmountMST));

    }

   

}

                                first only,first fast

static void CON_Dynamic(Args _args)

{

    int                     i;

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q   = new Query();

    qbds= q.addDataSource(tableNum(custTable));

    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    qbds.firstOnly(true);  //firstonly --firstfast

    qr = new QueryRun(q);

  

    while(qr.next())

    {

        custTable = qr.get(tableNum(custTable));

        info(strFmt("%1",custTable.AccountNum));

    }

}

                                forUPdate

static void CON_DynamicforUpdate(Args _args)

{

    int                     i;

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

    Con_Calc                obj;

    q   = new Query();

    qbds= q.addDataSource(tableNum(Con_Calc));

    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    //qbds.firstFast(true);

    qbds.addSortField(fieldNum(Con_Calc,Name));

    qr = new QueryRun(q);

    while(qr.next())

    {

        obj = qr.get(tableNum(Con_Calc));

        obj.selectForUpdate(true);

        ttsBegin;

        if(obj.Name =="Siva")

        {

            obj.Name="Sivakumar";

            obj.update();

        }

        ttsCommit;

        info(strFmt("%1",obj.Name));

    }

}

                                                GroupBy

static void CON_DynamicGroupBy(Args _args)

{

    Query           q;

    QueryRun        qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q = new Query();

    qbds = q.addDataSource(tableNum(custTrans));

    //qbds.addSortField(fieldNum(custTable,AccountNum));

    qbds.addSelectionField(fieldNum(custTrans, AmountMST),SelectionField::Max);         //Aggregate

    qbds.addGroupByField(fieldNum(custTrans,AccountNum),OrderMode::GroupBy);     //group by

      

    qr = new QueryRun(q);

  

    while(qr.next())

    {

        custTrans = qr.get(tableNum(custTrans));

        info(strFmt("%1--%2",custTrans.AccountNum,custTrans.AmountMST));

    }

   

}

                                                Joins

static void CON_DynamicJoin(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbdsCustTable,qbdsCustTrans;

    QueryBuildRange         qbr;

    CustTable               custTable;

    CustTrans               custTrans;

    CON_Car                 car;

    CON_RentCar             rent;

    q   = new Query();

    qbdsCustTable = q.addDataSource(tableNum(CON_Car));                     //Data Source 1

    //qbdsCustTable.addSortField(fieldNum(CON_Car,CarId),SortOrder::Descending);

    qbdsCustTrans = qbdsCustTable.addDataSource(tableNum(CON_RentCar));    //Data Source 2

    qbdsCustTrans.relations(false);

    qbdsCustTrans.joinMode(JoinMode::InnerJoin);                          //Join Type

    qbdsCustTrans.addLink(fieldNum(CON_Car,CarId),fieldNum(CON_RentCar,CarId));

   

    qr = new QueryRun(q); 

    while(qr.next())

    {

      

        car = qr.get(tableNum(CON_Car));

        rent = qr.get(tableNum(CON_RentCar));

        info(strFmt("%1--%2",car.CarId,rent.CarId));

    }

}

                                Multiple Tables Join

static void CON_DyamicQUeries(Args _args)

{

    Query                   query = new Query();

    QueryBuildDataSource    salesTableDS;

    QueryBuildDataSource    salesLineDS;

    QueryBuildDataSource    custTableDS;

    QueryRun                qr;

    CustTable               custTable;

    SalesTable              salesTable;

    SalesLine               salesLine;

  

    salesTableDS    = query.addDataSource(tableNum(SalesTable));

   

    salesLineDS     = salesTableDS.addDataSource(tableNum(SalesLine));

    salesLineDS.relations(true);

    //salesLineDS.fetchMode(QueryFetchMode::One2One);

    salesLineDS.joinMode(JoinMode::InnerJoin);

    salesLineDS.addLink(fieldNum(SalesTable, SalesId), fieldNum(SalesLine, SalesId));

    custTableDS     = salesTableDS.addDataSource(tableNum(CustTable));   

    custTableDS.relations(false);   

    custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));

    //custTableDS.fetchMode(QueryFetchMode::One2One);

    custTableDS.joinMode(JoinMode::InnerJoin);

    //info(salesTableDS.toString());.

    qr = new QueryRun(query);

    while(qr.next())

    {

        salesTable= qr.get(tableNum(salesTable));

        salesLine = qr.get(tableNum(salesLine));

        CustTable = qr.get(tableNum(custTable));

   

        info(strFmt("%1---%2---%3",salesTable.SalesId,salesLine.SalesId,custTable.AccountNum));

    }

}

Validtimestate

static void QueryCurrent(Args _args)
{
     CustInterestVersion interestVersion;
     CustInterest interest;
     Date asOfDate = 1\1\2002;

 while select validtimestate(asOfDate) * from interestVersion join interest
       where interestVersion.CustInterest == interest.RecID
      {
          info(strFmt("%1, %2, %3, %4", interest.InterestCode, interestVersion.GraceDays,
        interestVersion.ValidFrom, interestVersion.ValidTo));
      }
}

In AsOfDate or AsOfDateRange mode, a new keyword ValidTimeState is introduced.
ValidTimeState(date1) can be used to query the records that are effective at a specific time.
ValidTimeState(date1, date2) can be used to query the records that are effective during a time
period. Date1 and Date2 can be the type of Date or UtcDateTime, depending on the ValidFrom and
ValidTo type of the valid time state table. For example, the following code sample returns the records
that are effective on 1/1/2002:

                                                Full Text Index

static void CON_FullTextIndex(Args _args)

{

    Query           q;

    QueryRun        qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    Con_Calc                obj;

    q       = new Query();

    qbds    = q.addDataSource(tableNum(Con_Calc));

    qbr     = qbds.addRange(fieldNum(Con_Calc,Name));

    qbr.rangeType(QueryRangeType::FullText);

    qbr.value("Raj kumar");

    qr      = new QueryRun(q);

  

    while(qr.next())

    {

        obj = qr.get(tableNum(Con_Calc));

        info(obj.Name);

    }

}

Example

https://gfeaxblog.wordpress.com/2018/04/09/d365-full-text-search/

Path
Sales and Marketing >> Setup >> Search

1. Search parameter
2. Search criteria

All sales orders

Add invalid Item on sales line and then enter Tab

Product search form will be opened

Technical 

Form MCRSalesQuickQuote

Methods --> prepareSearch

DS     InventItemDimTmpFilter

Methods --> excuteQuery

Class   MCRInventSearch.executeSearch()

How to add new field in Product search form

Steps 

1. Add new field in MCRProductSearchView  view
2. Add newly added field in Search criteria form (Sales and Marketing >> Setup >> Search)
3. Click on update search data button on Search criteria form
4. Add new datasource on MCRSalesQuickQuote form
5. In the Datasource init method add below code
6. Add required field on grid

//In my case, I have added CustVendExternalItem table as DS

public void init()

{

       QueryBuildDataSource    qbds    = this.queryBuildDataSource();

        qbds.addLink(fieldNum(InventItemDimTmpFilter, ItemId), fieldNum(CustVendExternalItem, ItemId));

       

        qbds.addGroupByField(fieldNum(CustVendExternalItem, ExternalItemId));

}

Greater than or euqal to / Less than or equal to (<= or >=) in Query class in AX 2012 or D365

Greater than or equal to:

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value(SysQuery::range(today(), dateMax()));

or

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value((queryRange(today(), dateMax()));

Less than or equal to:

 queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value(SysQuery::range( dateNull(), today()));

or

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value((queryRange(dateNull(), today())); 

Select Statements

               

       


     CrossCompany

static void CON_SelectStatement(Args _args)

{

    CustTable      custTable;

    container      companies = ['USMF','IBM'];

    while select crossCompany:companies * from custTable

    {

        info(custTable.AccountNum);

       

    }

   

}

                                                firstFast 

static void CON_SelectfirstFast(Args _args)

{

    CustTable       custTable;

   

    while select firstFast  custTable

    info(custTable.AccountNum);

}

                                                firstOnly

static void CON_SelectfirstOnly(Args _args)

{

    CustTable       custTable;

   

    while select firstOnly custTable

    {

        info(strFmt("%1--%2",custTable.AccountNum,custTable.dataAreaId));

    }

}

                                                forUpdate

static void CON_SelectforUpdate(Args _args)

{

   Con_Calc     con_Calc;

   

    ttsBegin;

    while select forUpdate con_Calc where con_Calc.Name=="Satya"

    {

        con_Calc.Name="Satya CH";

        con_Calc.update();

        info(con_Calc.Name);

    }

    ttsCommit;

   

}

static void CON_Selectwhere(Args _args)

{

    CustTable       custTable;

   while select * from custTable where custTable.CustGroup=="10"

    {

        info(strFmt("%1--%2",custTable.CustGroup,custTable.AccountNum));

    }

}

                                                Groupby

static void CON_SelectGroupby(Args _args)

{

    CustTable       custTable;

    CustTrans       custTrans;

   

    while select minof(AmountMST) from custTrans group by AccountNum

    {

    info(strFmt("%1--%2",custTrans.AccountNum,custTrans.AmountMST));

    }

}

                                                count

static void CON_Selectcount(Args _args)

{

    CustTable       custTable;

    CustTrans       custTrans;

    int             a=0;

    while select count(RecId) from custTable

    {

       a = custTable.RecId;

    }

    info(strFmt("%1",a));

}

                                change Company

static void CON_SelectchangeCompany(Args _args)

{

    CustTable       custTable;

    info(custTable.dataAreaId);

    changeCompany('IBM')

    {

        custTable = null;

        while select custTable

        {

            info(strFmt('%1',custTable.dataAreaId));

        }

    }

}

                                Relational Operator

static void CON_Select(Args _args)

{

   CustTable       custTable;

   CustTrans       custTrans;

    int             a=0;

  while select custTable where custTable.AccountNum=="DE_005" && custTable.AccountNum=="DE_006"

    {

        info(strFmt("%1",custTable.AccountNum));

    }

}

                               

static void CON_Select(Args _args)

{

   CustTable       custTable;

   CustTrans       custTrans;

    int             a=0;

  while select custTable where custTable.AccountNum =="DE_005" || custTable.CustGroup =="10"

    {

        info(strFmt("%1--%2",custTable.AccountNum,custTable.CustGroup));

    }

}

                                              Joins

static void CON_SelectJoin(Args _args)

{

   CON_Car      car;

   CON_RentCar  rent;

    // Inner Join

  /*  while select car join rent where car.CarID == rent.CarID

    {

        info(strFmt("%1--%2", car.CarID,rent.CarID));

    }

   */

   //Outer Join

  /*  while select car outer join rent where car.CarID == rent.CarID

    {

        info(strFmt("%1--%2", car.CarID,rent.CarID));

    }

  */

  //Exists

   /* while select car exists join rent where car.CarID == rent.CarID

    {

        info(strFmt("%1--%2", car.CarID,rent.CarID));

    }

   */

  

   //Not Exists

    while select car notExists join rent where car.CarID == rent.CarID

    {

        info(strFmt("%1--%2", car.CarID,rent.CarID));

    }

 

}

Data Bases in Ax 2012 R3

Data Bases in Ax

1. Transaction DataBase
2. Model Store
3. BaseLine Model Store

There are three database components: the Microsoft Dynamics AX transaction database, the model store, and the baseline model store.

• The Application Object Server (AOS) connects to the Microsoft Dynamics AX database to process transactions. 
• The AOS connects to the model store to display forms and reports. 
• The baseline model store is used to upgrade X++ code to Microsoft Dynamics AX 2012, and to analyze application updates before they are applied.

In Microsoft Dynamics AX 2012 R3 and Microsoft Dynamics AX 2012 R2, the model store and the transaction data are stored in separate databases. 

In earlier versions of Microsoft Dynamics AX 2012, the model store and transaction data are stored in a single database.

How to Change Date format in Ax 2012

static void CON_DateFormat(Args _args)
{
    date todaydate = today();
    str s;

    s = date2str(todaydate,123,DateDay::Digits2,DateSeparator::Hyphen,
DateMonth::Short,DateSeparator::Hyphen,DateYear::Digits4);

    info(strFmt("Today date is " + s));
}

Types of Form Templates in AX 2012

Types of Form Templates in AX 2012:

There are seven different predefined form templates in ax 2012.

• ListPage
• DetailsFormMaster
• DetailsFormTransaction
• SimpleListDetails
• SimpleList
• TableOfContents
• Dialog
• DropDialog

ListPage – 

                 A list page is a form that displays a list of data related to a particular entity or business object. A list page provides provisions for displaying data and taking actions on this data. Every module has at least a couple of list pages. List pages are further classified as primary and secondary list pages. A secondary list page will only display a subset of data from the primary list page. Example, CustTableListPage, VendTableListPage, ProjProjectsListPage. Best practice is to have ListPage as a suffix in the name of the form for all list pages.

DetailsFormMaster: 

              This template is used for forms which display data for stand-alone entities or business objects. Example includes Customers, Vendors, and Projects etc. If you look at the forms for these, i.e. CustTable, VendTable, ProjTable, their style property will be set to DetailsFormMaster.

DetailsFormTransaction: 

             This template is used for forms which display data for entities which have child records associated with it. In other words, if the data being displayed is header-lines type in nature, use this template. Example, Sales orders, Purchase orders etc. If you look at the style property of SalesTable, VendTable, their properties will be set to DetailsFormTransaction.

SimpleListDetails – 

             This template is used to display primary fields in a list and detailed data in a tab page. This style is useful to view all records in a form and selecting a particular record will bring up their details. Example includes HcmSkillMapping, etc.

SimpleList – 

            This template is a very basic form which displays data in a grid. No extra or fancy detail is displayed. This style is best suited for forms where data being shown is not very detailed in nature or has limited fields. Example includes AifAction, etc.

TableOfContents – 

          This template is the new style which should be adopted for all parameter forms in Dynamics AX 2012. Take a look at any parameters form and its style property will be set to TableOfContents. This style sets all the tabs as a hot link in the left hand side navigation pane. Clicking on the link will bring up the controls on that tab page. This style is a very neat and appealing UI design which is surely welcome. Example includes Custparameters, vendparameters, etc.

Dialog –  

         This template is used on forms which show static data or are intended to capture some user input for further actions. These forms are mostly modal in nature and should be dismissed before any further actions can be taken.

DropDialog – 

          This template is used for forms that are used to gather quick user inputs to perform an action. Drop dialog forms are generally attached to an action pane button. They appear to be dropping from the menu button when clicked. Example includes HcmWorkerNewWorker,  HcmPositionWorkerAssignmentDialog.

How to Install Complete Ax 2012 R3

http://shafeaa.blogspot.in/2015/03/install-dynamics-ax-2012-r3-step-by.html



EP Installation Ax 2012 R3

https://community.dynamics.com/ax/b/rudrasdynamicsthoughts/archive/2014/04/30/ax2012r3-ep-step-by-step-installation-and-configuration

Cubes Ax 2012 R3

http://tech.alirazazaidi.com/install-and-configure-analysis-services-for-dynamics-ax-2012-r3/

How to Create Sales Quotation through X++ code in Ax 2012

static void CreateSalesQuotation(Args _args)
{
    AxSalesQuotationTable       axSalesQuotationTable;
    AxSalesQuotationLine        axSalesQuotationLine ;
    SalesQuotationTable         salesQuotationTable;
    SalesQuotationLine          salesQuotationLine;
    CustTable                   custTable;
    InventTable                 inventTable;
    SalesQty                    salesQty;
    SalesPrice                  salesPrice;
    SalesLineDisc               salesLineDisc;
    SalesLinePercent            salesLinePercent;
    container                   conItems = ["ItemA","Dell","HP"];
    int                         i =0;
    InventDim                   inventDim;
    SalesQuotationEditLinesForm salesQuotationEditLinesForm;
    ParmId                      parmId;
    SalesQuotationUpdate        salesQuotationUpdate;
    ;

    salesQty                    = 10;
    SalesPrice                  = 100;
    salesLineDisc               = 1;
    salesLinePercent            = 1;
    axSalesQuotationTable =  new AxSalesQuotationTable();
    axSalesQuotationTable.parmCustAccount('Jaga');
    axSalesQuotationTable.parmCurrencyCode('USD');
    axSalesQuotationTable.save();

    for(i = 1; i<= conLen(conItems); i++)
    {
        axSalesQuotationLine =  new AxSalesQuotationLine();//::construct(smmQuotationLine);

        axSalesQuotationLine.axSalesQuotationTable(axSalesQuotationTable);

        axSalesQuotationLine.parmQuotationId(axSalesQuotationTable.parmQuotationId());
        axSalesQuotationLine.axSalesQuotationTable(axSalesQuotationTable);
        axSalesQuotationLine.parmItemId(conPeek(conItems,i));

        axSalesQuotationLine.parmSalesQty(salesQty);
        axSalesQuotationLine.parmSalesPrice(SalesPrice);
        axSalesQuotationLine.parmLineDisc(SalesLineDisc);
        axSalesQuotationLine.parmLinePercent(SalesLinePercent);
        inventDim.InventSiteId      = "1";
        inventDim.InventLocationId  = "11";


        axSalesQuotationLine.parmInventDimId('002504');
        axSalesQuotationLine.parmcurrencyCode('USD');
        axSalesQuotationLine.save();
    }

    //Quotation status -- sent
    if(!axSalesQuotationTable.salesQuotationTable()) return;
    salesQuotationEditLinesForm =
    SalesQuotationEditLinesForm::construct(DocumentStatus::Quotation); //DocumentStatus
    parmId                      = salesQuotationEditLinesForm.parmId();


    salesQuotationEditLinesForm.initParmSalesQuotationTable(axSalesQuotationTable.salesQuotationTable());

    salesQuotationEditLinesForm.parmId(parmId);
    salesQuotationEditLinesForm.parmTransDate(systemdateget());

    salesQuotationEditLinesForm.prePromptInit();

    salesQuotationEditLinesForm.initParameters(NoYes::No, //printFormletter,
                                               NoYes::No,
//transferHours2Forecast,
                                               NoYes::No,
//transferExpenses2Forecast,
                                               NoYes::No,
//transferFees2Forecast,
                                               NoYes::No,
//transferItems2Forecast,
                                               'Competency',
//reasonCode,
                                               NoYes::No);
//usePrintManagement)
    salesQuotationEditLinesForm.run();

    /// Quotation status -- Confirmation
    if(!axSalesQuotationTable.salesQuotationTable())
        return;
    salesQuotationEditLinesForm =
    SalesQuotationEditLinesForm::construct(DocumentStatus::Confirmation); //DocumentStatus
    //Quotation o Confirm
    parmId                      = salesQuotationEditLinesForm.parmId();


    salesQuotationEditLinesForm.initParmSalesQuotationTable(axSalesQuotationTable.salesQuotationTable());

    salesQuotationEditLinesForm.parmId(parmId);
    salesQuotationEditLinesForm.parmTransDate(systemdateget());

    salesQuotationEditLinesForm.prePromptInit();

    salesQuotationEditLinesForm.initParameters(NoYes::No, //printFormletter,
                                               NoYes::No,
//transferHours2Forecast,
                                               NoYes::No,
//transferExpenses2Forecast,
                                               NoYes::No,
//transferFees2Forecast,
                                               NoYes::No,
//transferItems2Forecast,
                                               'Competency',
//reasonCode,
                                               NoYes::No);
//usePrintManagement)
    salesQuotationEditLinesForm.run();

}