Thursday, May 4, 2017

MICROSOFT DYNAMICS AX 2012 : DIFFERENCE BETWEEN QUERYBUILDRANGE AND QUERYFILTER

DIFFERENCE BETWEEN QUERYBUILDRANGE AND QUERYFILTER


Let's look at the following scenario. We have a customer table, CustTable, and a sales order table, SalesTable, which has a foreign key relationship to the CustTable based on CustAccount. Let's say we want to retrieve a list of customers, and optionally any sales orders associated with each customer. To accomplish this, one would use an outer join. In SQL, this would translate as follows:

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM


So far so good. Now let's say we want to show all customers, and show all sales orders associated with each customer, but ONLY the orders with currency EUR... In SQL, this gives us TWO options:

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        AND SALESTABLE.CURRENCYCODE = 'EUR'


or

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        WHERE SALESTABLE.CURRENCYCODE = 'EUR'


So what's the difference? In the first option, we use AND, which means the currencycode is part of the JOIN ON statement filtering the SALESTABLE. In the second option, using the WHERE keyword, the currencycode is part of the query's selection criteria... so what's the difference? If we filter the SALESTABLE using the ON clause, the CUSTTABLE will still show up, even if no SALESTABLEs with currency EUR exist, and it will just filter the SALESTABLE records. However, using a WHERE clause, we filter the complete resultset, which means no CUSTTABLE will be returned if there are no SALESTABLE records exist with EUR as the currency.

That is exactly the difference between QueryBuildRange and QueryFilter when used on an outer join. The QueryBuildRange will go in the ON clause, whereas QueryFilter will go in the WHERE clause. The following job illustrates this, feel free to uncomment the range and comment the filter, and vice versa, and test the results for yourself.

static void QueryRangeFilter(Args _args)
{
    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryFilter             filter;
    QueryRun                queryRun;
    int                     counter = 0, totalCounter = 0;
    
    query = new Query();
    datasource = query.addDataSource(tableNum(CustTable));
    datasource = datasource.addDataSource(tableNum(SalesTable));
    datasource.joinMode(JoinMode::OuterJoin);
    datasource.relations(true);
    datasource.addLink(fieldNum(CustTable, AccountNum),
            fieldNum(SalesTable, CustAccount));
    filter = query.addQueryFilter(datasource,
            fieldStr(SalesTable, CurrencyCode));
    filter.value(SysQuery::value('EUR'));
    //range = datasource.addRange(fieldNum(SalesTable, CurrencyCode));
    //range.value(SysQuery::value('EUR'));
    
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        totalCounter++;
        if (queryRun.changed(tableNum(CustTable)))
            counter++;
    }
    
    info(strFmt("Customer Counter: %1", counter));
    info(strFmt("Total result Counter: %1", totalCounter));
}

DATE FUNCTIONS IN AX 2012 AND AX 2009 : MICROSOFT DYNAMICS AX 2012 :X++ CODE DAY MONTH YEAR TIME ETC

static void date_Functions(Args _args)

{
    Transdate    d;
    ;
  
    d = today();
  
    info(strfmt("Date - %1",d));
  
    //Gets the month for the given date...
    info(strfmt("Month - %1",mthofYr(d)));
  
    //Gets the month name from the given date...
    info(strfmt("Month Name - %1",mthname(mthofYr(d))));
  
    //Gets the day for the given date...
    info(strfmt("Day - %1",dayOfMth(d)));
  
    //Gets the day name from the given date...
    info(strfmt("Day Name - %1",dayname(dayOfMth(d))));
  
    //Gets the year for the given date...
    info(strfmt("Year - %1",year(d)));
  
    //Gets the current weekday number from the date...
    info(strfmt("Weekday number - %1",dayOfwk(d)));
  
    //Gets the day of the year from the given date...
    info(strfmt("Day of year - %1",dayOfyr(d)));
  
    //Gets the week of the year from the given date...
    info(strfmt("Week of the year - %1",wkofyr(d)));
}

MICROSOFT DYNAMICS AX 2012 : SETUP DIFFERENT DATE FORMATS DD/MM/YY OR REGION SPECIFIC ON AX SSRS REPORT

SETUP DIFFERENT DATE FORMATS ON AX SSRS REPORT


Hi

If you want to setup a different Format Date on a SSRS report,

1- Modify date format for all date textbox present in the SSRS Report using the Format function, like =Format(Fields!TransDate.Value, "dd/MM/yyyy")

2- If you want follow the User Timezone, for all date textbox you can use ConvertUtcToAxUserTimeZoneForUser method, like =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, =Fields!TransDate.Value, "d", Parameters!AX_RenderingCulture.Value)

MICROSOFT DYNAMICS AX 2012 : HOW TO USE QUERYHAVINGFILTER IN AX 2012: QUERY WITH GROUP CONDITION

How to use QueryHavingFilter in AX 2012

 QueryHavingFilter:

Consider the following scenario. The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.
Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE
        GROUP BY CUSTGROUP
        HAVING COUNT(*) < 4

In AX you can count, group by, but you'll need to loop over the results and check the counter manually if you want to filter values out. So, in AX 2012, a new query class was added: QueryHavingFilter, that lets you do just that:

static void QueryHaving(Args _args)
{
    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryHavingFilter       havingFilter;
    QueryRun                queryRun;
    int                     counter = 0, totalCounter = 0;
    CustTable               custTable;
   
    query = new Query();
    datasource = query.addDataSource(tableNum(CustTable));
    datasource.addSelectionField(fieldNum(CustTable, RecId),
            SelectionField::Count);
    datasource.orderMode(OrderMode::GroupBy);
    datasource.addGroupByField(fieldNum(CustTable, CustGroup));
   
    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),
            AggregateFunction::Count);
    havingFilter.value('<4 o:p="">
   
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        custTable = queryRun.getNo(1);
        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));
    }
}


Note that in this code example, I added a selection field on RecId and used SelectionField::Count. This is not necessary for the having filter to work, the only reason it is in the code example is to be able to show it in the infolog (ie to have the count value available). So it is independent of the HavingFilter!


MICROSOFT DYNAMICS AX 2012 : CODE TO GET ON HAND OF AN ITEM STOCK COUNT

MICROSOFT DYNAMICS AX 2012 : GET ON HAND OF AN ITEM STOCK COUNT
static void stockOnHand(Args _args)
{
    InventOnhand        inventOnHand;
    InventDim           inventDim;
    InventDimParm       inventDimParm;
    InventQty           stockOnHand;
    ItemId              itemid = "00001aTestInter";
 
    inventDim.InventSiteId = "SITE"; // site
    inventDim.InventLocationId = "WAREHOUSE"; // warehouse
 
 
    inventDimParm.initFromInventDim(inventDim);
 
    inventOnHand = InventOnhand::newParameters(itemid,inventDim,inventDimParm);
 
    stockOnHand = inventOnHand.availPhysical();
 
    info (strFmt(" Stock on Hand for Item %1 is %2",itemid,stockOnHand));
 
}

MICROSOFT DYNAMICS AX 2012 : CODE TO DEPLOY SERVICE FROM AX

DEPLOY AX SERVICES FROM CODE

Below is the script for automatically deploy or undeploy  AIF service.
In this example update the port with current company and deploy the service.
static void AIFservices(Args _args)
{
    AifPort  port;
    AifPortName portName = "servicename";
    AifPortManager::undeployPort(portName);
    port = AifPort::find(portName,true);

    if(port)
    {
        ttsBegin;
        if(port.Company != curext() )
        {
            port.Company = curext();
            port.update();
        }
        ttsCommit;
    }
    AifPortManager::deployPort(portName);
}

AX 2012 CHECK IF DIMENSION EXIST OR NOT X++ CODE

CHECK IF DIMENSIONS EXIST OR NOT


The following code is used to check if the Dimension exist for item.
while select inventTable where inventTable.DefaultDimension == 0
    {
     
         if(EcoResStorageDimensionGroupItem::findByItem(inventTable.DataAreaId, inventTable.ItemId).StorageDimensionGroup
            && EcoResTrackingDimensionGroupItem::findByItem(inventTable.DataAreaId, inventTable.ItemId).TrackingDimensionGroup)
        {
            continue; // exist
        }
        else
        {
             // not exist do some thing.
        }
}

OOPS CONCEPT IN MICROSOFT DYNAMICS AX 2012

Oops concept in AX
Class : Class is the 1st OOPs concept .Class defines the characteristics of objects which includes its attributes , fields  properties and behavior . Let us say we have a class called car , then the color , model number , top speed can be its attributes and properties . Accelerating , breaking , turning will be its behavior

Objects: Objects can be considered as a thing  that performs a set of related functions .Programming objects are used to model real worlds objects. An object is also an instant of a class . For our class Car , Ferrari  will be our object

Instance : One can have an instance of a class; the instance is the actual object created at runtime.  The set of values of the attributes of a particular object is called its state. The object consists of state and the behaviour that’s defined in the object’s class.

Method :Also called as  functions in some programming languages , methods defines the  behavior of particular objects . For our Car class , turning() , breaking ()  will be our methods .

Inheritance : a parent class can inherit its behavior and state to children classes. This concept was developed to manage generalization and specialization in OOP .Lets say we have a class called Car and Racing Car . Then the attributes like engine no. , color of the Class car can be inherited by the class Racing Car . The class Car will be Parent class , and the class Racing Car will be the derived class or child class

Abstraction : representing only the important details without including all the details . For example the car Ferrari can be treated as simple car only .

Encapsulation: The wrapping up of data and functions into a single unit is called as encapsulation. For example the class car has a method turn ()  .The code for the turn() defines how the turn will occur . So we don’t need  to define how Mercedes will turn and how the Ferrari will turn separately . turn() can be encapsulated with both.

Polymorphism: Its an important OOPs concept , Polymorphism means taking more than one forms .Polymorphism allows the programmer to treat derived class members just like their parent class’s members. More precisely, Polymorphism in object-oriented programming is the ability of objects belonging to different data types to respond to calls of methods of the same name .If a Dog is commanded to speak(), this may elicit a bark(). However, if a Pig is commanded to speak(), this may elicit an oink(). Each subclass overrides the speak() method inherited from the parent class Animal. 


*******************************************************************************************************************

Basic OOPS Concept for Fresh Developers
OOPs  Object Oriented Programming System
Object-oriented programming (OOP) is a programming paradigm that uses “Objects “and their interactions to design applications and computer programs.

There are different types of OOPs are used, they are
1.                   Object
2.                   Class
3.                   Data Abstraction & Encapsulation
4.                   Inheritance
5.                   Polymorphism
6.                   Dynamic Binding
7.                   Message Passing

1) Object :
Object is the basic unit of object-oriented programming. Objects are identified by its unique name. An object represents a particular instance of a class. There can be more than one instance of an object. Each instance of an object can hold its own relevant data.
An Object is a collection of data members and associated member functions also known as methods.
For example whenever a class name is created according to the class an object should be created without creating object can’t able to use class.
The class of Dog defines all possible dogs by listing the characteristics and behaviors they can have; the object Lassie is one particular dog, with particular versions of the characteristics. A Dog has fur; Lassie has brown-and-white fur.
2) Class :
Classes are data types based on which objects are created. Objects with similar properties and methods are grouped together to form a Class. Thus a Class represents a set of individual objects. Characteristics of an object are represented in a class as Properties. The actions that can be performed by objects become functions of the class and is referred to as Methods.

For example consider we have a Class of Cars under which Santro Xing, Alto and WaganR represents individual Objects. In this context each Car Object will have its own, Model, Year of Manufacture, Colour, Top Speed, Engine Power etc., which form Properties of the Car class and the associated actions i.e., object functions like Start, Move, Stop form the Methods of Car Class.No memory is allocated when a class is created. Memory is allocated only when an object is created, i.e., when an instance of a class is created.
3) Data abstraction & Encapsulation :The wrapping up of data and its functions into a single unit is called Encapsulation.
When using Data Encapsulation, data is not accessed directly, it is only accessible through the functions present inside the class.
Data Abstraction increases the power of programming language by creating user defined data types. Data Abstraction also represents the needed information in the program without presenting the details.
Abstraction refers to the act of representing essential features without including the background details or explanation between them.
For example, a class Car would be made up of an Engine, Gearbox, Steering objects, and many more components. To build the Car class, one does not need to know how the different components work internally, but only how to interface with them, i.e., send messages to them, receive messages from them, and perhaps make the different objects composing the class interact with each other.
4) Inheritance :
Inheritance is the process of forming a new class from an existing class or base class.
The base class is also known as parent class or super class, the new class that is formed is called derived class.
Derived class is also known as a child class or sub class. Inheritance helps in reducing the overall code size of the program, which is an important concept in object-oriented programming.
It is classifieds into different types, they are
·                     Single level inheritance
·                     Multi-level inheritance
·                     Hybrid inheritance
·                     Hierarchial inheritance
5) Polymorphism :
Polymorphism allows routines to use variables of different types at different times. An operator or function can be given different meanings or functions. Polymorphism refers to a single function or multi-functioning operator performing in different ways.
Poly a Greek term ability to take more than one form. Overloading is one type of Polymorphism. It allows an object to have different meanings, depending on its context. When an exiting operator or function begins to operate on new data type, or class, it is understood to be overloaded.
6) Dynamic binding :
It contains a concept of Inheritance and Polymorphism.
7) Message Passing :
It refers to that establishing communication between one place to another.
Related Posts Plugin for WordPress, Blogger...