Dynamic Queries

 

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

    }

}

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

    }

}

 

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

    }

}

Functions in Ax 2012

static void CON_functions(Args _args)

{

    int             i;

    real            r;

    str             s;

    container       con;

    date            d;

   // AifDocumentCreateAttribute

    //i = 1<<4;

    //i = 1>>4;

    //i = (100>101)?1:5;

    //i = abs(10*20.69544);    abs        Retrieves the absolute value of a real number.

    // i = acos(180);          acos       Retrieves the arc cosine of a real number.

    // i = 42373;

    //mydate = any2date(i);                                     any2date

    //s = 1;

    //st = any2enum(s);                                         any2enum

   // x = any2int(s);                                           any2int

   // x = any2int64(s);                                         any2int64

   // x = any2real(s);                                          any2real

   // x = any2str(s);                                           any2str

   // i = asin(1.2);                                            asin

   // i = atan(1/0);                                            atan

   // x = attributeStr(AifDocumentCreateAttribute);             attributeStr

   // beep();                                                   beep

   // x = char2num("RaJendra",1);                               char2num

   // x = classIdGet(obj);                                      classIdGet

   // x = classStr(Global);                                     classStr

                            //Container

    //con = ["Rajendra","chilukuri","410",9440078438];

    //info(strfmt("%1",conPeek(con,5)));                        conPeek

    //con = conPoke(con,2,"Chilukuri's");

    //info(strfmt("%1",conPeek(con,2)));                        conPoke

    //s = conLen(con);

    //info(strfmt("%1",s));                                     conLen

    //con = conIns(con,5,"Hello How are You ??????");

    //info(strFmt("%1",conPeek(con,5)));                        conIns

    //info(strFmt("%1",conFind(con,"410")));                    conFind

    //con = conNull();

    //info(strFmt("%1",conPeek(con,1)));                        conNull

    //con = conDel(con,1,1);

    //info(strFmt("%1" ,conPeek(con,1)));                      // conDel

    //i = corrflagset(0.369,2);

    //i = cos(900);             cos         Retrieves the cosine of a real number.

    //i = cosh(0);              cosh        Argument values outside of the range –250 to 250 result in the following run-time error: "Argument for trigonometric function out of range."

    //i = cTerm(10,100,50);     cTerm       Calculates the number of periods required for the current investment value to yield a target value.

    //x = curext();             curext      Retrieves the extension that is used for the current company.

    //x = curUserId();          curUserId   Retrieves the nonnumeric ID that represents the current user.

    //x = datasetStr(CustomerList);  datasetStr           Retrieves the name of a dataset as a string.

    //d = today();

   // s = date2num(d);          date2num    Converts a date to an integer that corresponds to the number of days since 1 January, 1900.

   // date2Str                  Converts the specified date to a string.

    //utcDateTime         utc2 = 1959-06-17T15:44:33;

    //x = datetime2str(utc2);

    //formStr                   Retrieves the name of a form.

    // i = frac(1.3658);        Retrieves the decimal part of a real number.

    //info(strFmt("%1",funcName())); //Retrieves a string that contains the current function context.  o/p con_functions

    //setPrefix("Hai");

    //setPrefix("Hello");           Retrieves the current execution prefix after successive calls to the setPrefix function.

    //setPrefix("Namaskaram");

    //setPrefix("Whole Ax Team");

    //info(getPrefix());

    //date refDate = str2Date("4/9/2007", 213);

    //date inputDate = str2Date("10/5/2007", 213);

    //int numberOfIntervals;

    //;

    //numberOfIntervals = intvMax(inputDate, refDate, intvScale::YearMonth);

    //info(strFmt("%1",numberOfIntervals));

    //i = logn(100);            Retrieves the natural logarithm of the specified real number.

    //s = match("<abc","abcdef");       Searches for a string or expression within another string.

    //s = min(12,15,19);

    //d = maxDate();            Retrieves the maximum value allowed for a variable of type date.

    //i   = maxInt();

    //x = methodStr(Calculator,exponent);   Validates that the specified method exists in the specified class; if not, a compiler error occurs.

    //d = mkDate(11,1,2016);      Creates a date based on three integers, which indicate the day, month, and year, respectively.

    //str x1 =mthName(1);           Retrieves the name of the specified month

    //s = mthOfYr(today());

    //d = prevyr(today());

    //x = num2char(67);             Converts an integer to the corresponding ASCII character.

    //d = num2date(364);             // Retrieves the date that corresponds to the specified number of days after 01\01\1900.

    //s = power(5,2);

    //i = round(15,4.00);

    //info(strFmt("%1",sessionId()));

    //int seconds = 10;

    //int i1;

    //i = sleep(60);

    //d = str2Date("31/02/2010",213);

    //s = strAlpha("?a*bc123.");        Copies only the alphanumeric characters from a string.

    //i = strCmp("Raj","Raj");

    //strColSeq                         Converts all uppercase characters to lowercase characters and converts all characters with accents to the corresponding unaccented lowercase characters.

    //info(strFmt("%1", strDel("abcdef",2,3)));  Creates a copy of a string with the specified substring removed.

    //info(strFmt("%1",strKeep("Hellohainamastehi","hai"))); returns str2 values based on str1

    //info(strFmt("%1",strRep("Rajendra ",3)));   Repeats a string of characters.

    //info(strFmt("%1",timeNow()));             The number of seconds that have elapsed since midnight.

    //r = trunc(3.12);

    //print strFmt("r = %1",  r);               Truncates a real number by removing any decimal places.

    //pause;

    //info(strFmt("%1",year(today())));         Retrieves the year from a date value.

    info(strFmt("%1",yearDiff(today(),today()-7)));

}

Tables & Classes involved in Purchase  Order

PurchTableType and PurchTableLine classes will get called while creating the PurchaseOrders.
PurchFormLetter_PurchOrder
PurchFormLetter_ApproveJournal
PurchFormLetter_Invoice
PurchFormLetter_PackingSlip
PurchFormLetter_ReceiptsList
classes will be used to post the PurchaseOrder at various document status (packing, invoice etc).
Tables:
PurchTable contains all purchase order headers regardless whether they have been posted or not.
PurchParmTable and PurchParmLine contains detailed information regarding posting Purchase headers and Lines.
VendPackingSlipJour and VendPackingSlipTrans tables contains posted packingslip headers and lines.
VendInvoiceJour and VendInvoiceTrans tables contains all invoiced purchase order headers and Lines.
VendReceiptsJour and VendReceiptsTrans tables contains posted receipt header and lines.
VendPurchOrderJour and VendPurchOrderTrans tables contains Purchase requisition headers and lines.

Tables & Classes involved in Sales Order

SalesTableType and SaleslineType classes will get called while creating the SalesOrders.
SalesFormLetter_Confirm
SalesFormLetter_Invoice
SalesFormLetter_PackingSlip
SalesFormLetter_PickingLlst
classes will be used to post the sales order at various document status (packing, invoice etc).
Tables:
SalesTable contains all SalesOrder headers regardless whether they have been posted or not.
The SalesParmTable and SalesParmLine contains detailed information regarding posting sales headers and Lines.
CustConfirmJour and CustConfirmTrans tables contains all Sales Confirmation headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
CustPackingSlipJour and CustPackingSlipTrans tables contains all sales PackingSlip headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
CustInvoiceJour and CustInvoiceTrans tables contains all sales all Sales Invoice headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.

How to use Event Handler in Microsoft Dynamics AX 2012

        How to use Event Handler in Microsoft Dynamics AX 2012

1)Open your new developer work space and go to AOT then classes node.

2)Right click on Classes node and click on New Class as shown below.

3) By default system will give a name to it. Here in my case it’s Class1. Right click on newly created class and click on Rename shown below.

4) After clicking Rename, give a name called CustTableEventHandler to it. Here I am going to develop an event to be applied on CustTable so that is the reason why I decided this name (CustTableEventHandler). After renaming this class, it looks as shown below.

5) Right click on class CustTableEventHandler then New then Pre- or post-event handler as shown below.

6) Once you click on this, system gives you a method as shown below.

7) Customize the method as shown below.

8) Here args is providing current record to custTable instance and info is displaying the current customer account. The code snippet is below.

public static void custCreateInfo(XppPrePostArgs _args)
{
     CustTable custTable;
     custTable = _args.getThis();

     info(strFmt("Customer account %1 has been created", custTable.AccountNum));
}

This method I support to call from insert method of CustTable with type post event. It means that once insertion is done to CustTable, system will display recently inserted customer account number. It depends on your business requirement what logic you want to apply here. So here you can develop your required business logic or invoke pre built logic.

9) Go to Tables node in AOT then find out CustTable.

10) Go to insert method of CustTable and right click on it then click on New Event Handler Subscription as shown below.

11) After clicking you will get a new Event handler shown below.

12) Rename the event handler to custCreateInfo and set the property as shown below.

13) Now save your work.

14) Go to Customer form and create a new customer. Here I created a new customer account called “Test-000001”.

15) Once this customer is created system will give your infolog as shown below.

Hope this will help you to understand the event handler in Microsoft Dynamics AX 2012.

 

Sales Order and Purchase Order Tables and Classes

Sales Order and Purchase Order Tables and Classes

SalesOrder Classes and Tables:

SalesTableType and SaleslineType classes will get called while creating the SalesOrders.
SalesFormLetter_Confirm
SalesFormLetter_Invoice
SalesFormLetter_PackingSlip
SalesFormLetter_PickingLlst
classes will be used to post the sales order at various document status (packing, invoice etc).
Tables:
SalesTable contains all SalesOrder headers regardless whether they have been posted or not.
The SalesParmTable and SalesParmLine contains detailed information regarding posting sales headers and Lines.
CustConfirmJour and CustConfirmTrans tables contains all Sales Confirmation headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
CustPackingSlipJour and CustPackingSlipTrans tables contains all sales PackingSlip headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
CustInvoiceJour and CustInvoiceTrans tables contains all sales all Sales Invoice headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.

Purchase Order classes and Tables:

PurchTableType and PurchTableLine classes will get called while creating the PurchaseOrders.
PurchFormLetter_PurchOrder
PurchFormLetter_ApproveJournal
PurchFormLetter_Invoice
PurchFormLetter_PackingSlip
PurchFormLetter_ReceiptsList
classes will be used to post the PurchaseOrder at various document status (packing, invoice etc).
Tables:
PurchTable contains all purchase order headers regardless whether they have been posted or not.
PurchParmTable and PurchParmLine contains detailed information regarding posting Purchase headers and Lines.
VendPackingSlipJour and VendPackingSlipTrans tables contains posted packingslip headers and lines.
VendInvoiceJour and VendInvoiceTrans tables contains all invoiced purchase order headers and Lines.
VendReceiptsJour and VendReceiptsTrans tables contains posted receipt header and lines.
VendPurchOrderJour and VendPurchOrderTrans tables contains Purchase requisition headers and lines.

Passing values between forms

For passing parameters from one form to another a special class Args is usually used. 

Example:

The code of button click event of FormA which calls FormB and passes some parameters to that form. <xpp> void clicked()
{

   // Args class is usually used in Axapta for passing parameters between forms
   Args            args;
   FormRun         formRun;
   // Our custom made class for passing complex set of parameters
   FormBParams     formBParams = new FormBParams();
   Array           items = new Array( Types::String );
   int         i;
   ;

   args = new args();

   // Our values which we want to pass to FormB
   // If we want pass just simple string we can use 'parm' method of 'Args' class
   args.parm( strValue.text() );
   // We also can pass enum value to FormB
   args.parmEnum( NoYesEnumValue.selection() );
   args.parmEnumType( EnumNum( NoYes ) );
   // and also can pass a cursor pointing to some record (in our case it is EmplTable )
   args.record( EmplTable );

   // If we want pass more complex set of parameters we can develop our own class
   // just for passing our parameters.
   formBParams.parmSomeDate( someDate.dateValue() );
   formBParams.parmSomeTime( someTime.value() );
   for( i=0; i<ListBox.items(); i++ )
   {
       items.value( i+1,  ListBox.getText( i ) );
   }
   formBParams.parmItems( items );
   // Pass our object to FormB
   args.parmObject( formBParams );

   // Run FormB
   args.name( formstr( FormB ) );
   formRun = classFactory.formRunClass( Args );
   formRun.init();
   formrun.run();
   formrun.wait();

   if( formrun.closedOk() )
   {
       answerFromFormB.text( args.parm() );
   }
   super();

} </xpp>'


The code of init method of FormB <xpp>

 public void init()
 {

   EmplTable       emplTableRecord;
   FormBParams     formBParams;
   Array           items;
   int             i;
   ;
   super();

   // Check for passed arguments
   if( element.args() )
   {
       // get string parameter
       strValue.text( element.args().parm() );

       // get enum parameter
       if( element.args().parmEnumType() == EnumNum( NoYes ) )
       {
           NoYesEnumValue.selection( element.args().parmEnum() );
       }
       // get object parameter
       if( element.args().parmObject() )
       {
           formBParams = element.args().parmObject();
           items       = formBParams.parmItems();
           for( i=1; i<=items.lastIndex(); i++ )
           {
               ListBox.add( items.value(i) );
           }
           someDate.dateValue( formBParams.parmSomeDate() );
           someTime.value( formBParams.parmSomeTime() );
       }
       // get record parameter
       if( element.args().record() && element.args().record().TableId == TableNum( EmplTable ) )
       {
           emplTableRecord =  element.args().record();
           emplName.text( emplTableRecord.Name );
       }
   }

} </xpp>

 The code of ok button click event of FromB <xpp> 

void clicked()
 {

   super();
   element.args().parm( strAnswer.text() );
   element.closeOk();

} </xpp>
The above code is cut out from a demo which you can