Using NOT LIKE in X++ select statements and query ranges

Hi Friends,
In this post I'll like to share how to use NOT LIKE operator in X++.
LIKE keyword is available in X++ but  NOT LIKE is not directly supported the way it works in SQL. It still can be achieved using X++ select statements as well as in query range values.

For illustration, I'll use an example to select all customer group records which do not start with "1" and do not start with "2", using a select statement as well as using a query object in X++.

Query Object:
If we need to write this in query range, then you need to use the power of expressions in the query range value. Visit using expressions in query ranges to get more details.
In the expression use '!1*' to define a not like condition and then separate other values  by a comma (,) as a comma will represent an AND condition in the query range expression. 

To quickly use the code, here it goes:

    CustGroup               custGroup;

    Query                   query;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    QueryRun                qr;

    query = new Query();

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

    qbr = qbds.addRange(fieldNum(custGroup,CustGroup));

    qbr.value(strFmt("%1,%2",strFmt('!%1*','1'),strFmt('!%1*','2')));

    info (qbds.toString());

    qr = new QueryRun(query);

    while (qr.next())

    {

        custGroup = qr.get(tableNum(CustGroup));

        info (custGroup.CustGroup);

    }

Select Statement:
If we need to write in select statement, then you can achieve by enclosing the like condition in brackets and then use a ! operator (! is the NOT operator in X++). Refer Keywords in X++ for details.

So if there are multiple NOT LIKE conditions which needs to be applied in a single statement, then each condition needs to be enclosed in brackets and need to have a ! operator applied as shown below.

You can see that the customer groups table has three records which starts with 1 and 2 but they are not shown in the info log.

To quickly reuse the code here it goes:

 CustGroup CustGroup;

    ;

    while select CustGroup

        where (!(CustGroup.CustGroup like '1*') && !(CustGroup.CustGroup like '2*'))

    {

        info(CustGroup.custGroup);

    }

The above code works well with AX2012 R3/R2/RTM and 2009 versions. I have not tested in any previous versions. The above information as also be found at MSDN  X++, ANSI SQL Comparison: SQL Select

Thanks for reading the blog and keep sharing.

AX2012 R3 : X++ code to get WBS key for a project activity

Hi Friends,
I want to share a piece of code which can be used to get the value of WBS key of an activity for a project. This piece of code works well with AX2012 R3 and AX 2012 R2.
In projects module, we can define Work Breakdown Structure (WBS) for a project. A very nice integration with Microsoft Projects is already available out of the box in standard Microsoft Dynamics AX.
WBS key represents the hierarchy or level of that activity amongst all the project activities.The WBS for a project can be accessed from Projects list and details page from the "Work breakdown structure" button available under the "Plan" group of the action pane as shown below:

When you open this form, you will notice that unique WBS key is reflected against each activity. This value is dynamic, if you indend,outdent,move up, move down an activity, it will change the WBS key value. 
By dynamic value I mean it is not stored as a field in any table.So now lets explore the technical side of it.

 If you look at the form control, it is a display method coming from a method outlineNumber() written on HierarchyTreeTable.

Note this display method is not available directly on the table. If it would have been directly available on the table, then this would not have been an interesting topic to blog :). 
It is written as a display method on the form under the data-source methods as shown below:

So now we see that the display method returns the value from a function OutlineNumberFor() available on object of a class whose variable is declared with the name of controller on the form. So let's move ahead and look at the class declaration of the form, so we notice that this class is ProjWBSUpdateController

Now things get interesting, let us try to find on this form how this class is getting initialized, the best place to find this is init method of the form and you will notice the below code there:

wow...it requires so many parameters, let have a look at the new() method of this class, you will notice that only the first two parameters are mandatory and rest all have a default value assigned to them in the function definition and so are optional:

Additionally after initializing the class object the most important part is the call to updateOutlineNumbersAndPublishInPreOrder() method call. All the magic happens in this method and it makes your class object the shining star of the form ;).

So if we can get the hierarchyID and the calendarID, we can use this class to run all the business rules for us and return the key number. It is always a best practise not to reinvent the whole wheel and use the available classes out of the box.
HierarchyID --> If you try to analyse smmActivities table which stores all the project activities you will find that it is linked to HierarchyTreeTable based on it's recID and from HierarchyTreeTable you can get the hierarchyID.

CalendarID --> You can get it from the project.

So now time to knit all the above piece of code together and make is usable, so here is the job :

To quickly reuse the code here it goes:

static void wfsGetWBSForActivityNumber(Args _args)
{
    ProjWBSUpdateController         controller;
    HierarchyIdBase                         hierarchyId;
    HierarchyTreeTable                    hierarchyTreeTable;
    str                                               recordWBSId;
    smmActivities                              smmActivities;
    ProjTable                                    projTable ;
    CalendarId                                  calendarId;

    smmActivities = smmActivities::find("A000072");
    hierarchyTreeTable = hierarchyTreeTable::findRefRecId(smmActivities.RecId);
 
    calendarId = ProjTable::find(smmActivities.projId()).PSASchedCalendarId;
 
    hierarchyId = hierarchyTreeTable.HierarchyId;

    controller = new ProjWBSUpdateController(hierarchyId,calendarId);
    controller.updateOutlineNumbersAndPublishInPreOrder();
    recordWBSId = controller.outlineNumberFor(hierarchyTreeTable.ElementNumber);
    info(recordWBSId);
}

Below is the result when we run the above code for the below shown project:

Thanks for reading the blog. Have a nice day and keep sharing.

X++ code to reset ListEnumerator

Hi Friends,
Sometimes we need to iterate a list enumerator multiple times. We can use reset() function to easily do this as shown below:

 

AX2012 R2 CU7: X++ code to post Product Receipt for a Purchase Order

Hi Friends,
The below can be used to post the product receipt for a purchase order. It is same as we used to do in previous version but it is good to keep it handy.

The main thing is to pass the parameters in update method. The below job will post the product receipt for the inventory quantities which have been Registered. Please note the purchase order should be confirmed before posting the product receipt.

To reuse it quickly, here it is:


static void Job12(Args _args)
{
    PurchTable  purchTable = PurchTable::find("000032");
    PurchFormLetter     purchFormLetter;
   
    purchFormLetter = purchFormLetter::construct(DocumentStatus::PackingSlip);
    purchFormLetter.update( purchTable,
                            "PA009",
                            systemDateGet(),
                            PurchUpdate::RegisteredAndServices,
                            AccountOrder::None,
                            false,
                            false);
   
    info("Done");
   
}

 

AX2012 R2 CU7 : Creating pay periods lookup in Payroll module

Hi Friends,
Recently I was working on some customizations in Payroll module and was required to create controls to select pay cycle and pay periods. I want to share this information on how we can create lookups for pay periods based on a pay cycle.
In standard AX, Pay cycles and pay periods are defined in payroll > setup > pay cycles and pay periods as shown below:

 

A standard AX example is the dialog of generate pay statements,where user first selects a pay cycle and based on that pay cycle, the pay periods are shown in the control as shown below, the pay periods are specific to the pay cycle:

 

 

So I looked into the class to understand how the lookup is coming and found the following, system was storing all the values in a container and then adding them to the combo box control. So I have tried to highlight the code used in the process:

When user selects a payCycle then the method to create the period list is called

In this method the container to hold the payPeriodList is populated with all the valid values, system is selecting all the pay period and then converting it into a string format and then adding it to container and the combo box selection list

In order to read the user selection the value is fetched from the container based on the selection in the combo Box control

So I followed the same pattern, and create a form as shown below :

1. Created a new reference group control for the pay cycle control, these controls are really helpful in AX2012.

 

 

Now if I open my form, I see all the pay cycles of the system

 

2. Then add a new combo box control to show the pay period lookup

 

Change the auto declaration to true

 

Declare the variables in class, a container to store the data and the variables to hold the user selection

Then copied the createPayPeriod method and removed the unwanted code. The same pattern is used in benefit register report UI builder class, the below code is copied from there:

On the modified of the pay cycle control I called this method:

And we are all set to go. On my form I have now lookups of pay cycle and pay period as per standard AX design.

 

 

Another great example of reusing standard AX code rather then reinventing the wheel. Thanks for reading the blog.

AX2012 R2 CU7: X++ code to create and post route card journal for production order

Hi Friends,
The below code can be used to create and post route card journal in AX2012 R2 CU7 for a production order when we know the Production order number and the operation number.
I have assigned the costing resource defined on the route as the work center ID in this case.

static void createRouteCardJournalSample(Args _args)
{
    ProdJournalTable                      prodJournalTable;
    ProdJournalRoute                     prodJournalRoute;
    ProdRoute                               prodRoute;
    ProdId                                     prodId = 'PO000065';
    OprNum                                  oprNum = 10;
    RouteOprId                              routeOprId;
    ProdJournalCheckPostRoute     prodJournalCheckPostRoute;

    select prodRoute where prodRoute.ProdId == prodId
                        && prodRoute.OprNum == oprNum;

    prodJournalTable.clear();
    prodJournalTable.initValue();
    prodJournalTable.JournalType        = prodjournaltype::RouteCard;
    prodJournalTable.ProdId             = prodId;
    prodJournalTable.JournalNameId      = ProdParametersDim::findDefault().RouteJournalNameId;
    prodJournalTable.Description        =                                                  ProdJournalName::find(prodJournalTable.JournalNameId).Description;
    prodJournalTable.VoucherSeqRecId    = ProdJournalName::find(prodJournalTable.JournalNameId).VoucherSeqRecId;
    prodJournalTable.VoucherDraw        = journalVoucherDraw::Post;
    prodJournalTable.NumOfLines         = 1;
    prodJournalTable.insert();

    //Route card entry
    prodJournalRoute.clear();
    prodJournalRoute.JournalId               = prodJournalTable.journalId;
    prodJournalRoute.ProdId                   = prodId;
    prodJournalRoute.initValue();
    prodJournalRoute.OprNum                = oprNum;
    prodJournalRoute.OprId                    = prodRoute.OprId;
    prodJournalRoute.JobType                = RouteJobType::Process;
    prodJournalRoute.CategoryHoursId   = prodRoute.SetUpCategoryId;
    prodJournalRoute.CategoryQtyId      = prodRoute.ProcessCategoryId;
    prodJournalRoute.DefaultDimension  = prodRoute.DefaultDimension;
    prodJournalRoute.WrkCtrId              = prodRoute.WrkCtrIdCost;
    prodJournalRoute.QtyGood              = 2;
    prodJournalRoute.Hours                   = 1;
    prodJournalRoute.insert();
    info(prodJournalTable.JournalId);
   
    ProdJournalCheckPostRoute = ProdJournalCheckPostRoute::newPostJournal(prodJournalRoute.journalId,true);
    ProdJournalCheckPostRoute.run();
}


Thanks

AX2012 R2 : Understanding purchase requisition [Basics and X++code to create purchase requisitions]

Hi Friends,
In this post I would like to explain about:

1. What is purchase requisition
2. Why companies use purchase requisition
3. Difference between purchase requisition and purchase order
4. Technical dive in X++ code to create purchase requisition for a project.

What is Purchase Requisition:

• Be very clear that a purchase requisition is an internal document for a company it is not a contract with the vendor to buy the goods.
• It is raised by departments to notify the procurement department that they need to purchase some goods to fulfil the requirements.
• This document has details like which items are required, how much quantity, for what purpose, and till which date the items are required.
• There can be more details like from which vendor or for which business units that depends on each companies defined templates but the purpose is same.
• An example is : A production department for a television  manufacturing company can raise a purchase requisition of 1000 panels to fulfil a production demand for the next month.

Why companies use Purchase Requisition:

• The main goal is to authorize the procurement department to engage a vendor for a purchase commitment.
• Another advantage is to have a better internal financial control.
• Normally there are approvers who have the authority to approve the purchase requisition and send the approved requisition to the procurement department. These approvers can be someone from finance departments who work closely with the procurement team. In AX this is handled by workflow very well.
• The purchase department should not change the details of purchase requisition without getting the approval.
• Some companies have limits defined for example the procurement department can purchase orders worth maximum $3000 but if the amount is higher then it needs an approved purchase requisition.

Difference between Purchase requisition and Purchase orders:

• Once a approved purchase requisition is received the procurement department has authority to engage a vendor to fulfil the requirements.
• This agreement between a vendor and a company is known as Purchase Order.
• It has details like the vendor name, address, delivery mode, delivery terms, mode of payment, item details, quantity, specification, taxes applicable, delivery dates, delivery schedule.
• There can be much more complex information embedded in a purchase order like some trade agreement papers, freight provider information etc.

In Microsoft Dynamics AX 2012 R2 we can view Purchase requisition from Procurement and Sourcing --> Common --> Purchase requisition --> All Purchase Requisitions

User can also create new requisitions from this list page. Your user much be attached to a worker in the company otherwise the option to create a new requisition will be disabled.
Also the buying legal entity field will only show the lookup of companies where your user is mapped as a worker.

 



Then user can go ahead and add lines. There is a lot of information we can feed in AX purchase requisitions like buying legal entity, project for which the requisition is required, item, quantity, inventory dimensions, financial dimensions, procurement category, project activity which makes Dynamics AX a great rich application to do such processes :)

My aim here is not to show the complete function steps as there are many good references available
for doing the process, you can refer to the following links :

• One of my favourite blog to understand AX concepts : http://www.slideshare.net/murrayfife/enabling-purchase-requisition-workflows-in-dynamics-ax-2012
• TechNet: http://technet.microsoft.com/en-us/library/gg231099.aspx
• Video : http://dynamicsaxmanuals.com/tag/purchase-requisition/

From here the blog takes a turn towards technical zone:)

I want to share some technical information regarding how we can create purchase requisition from X++ code for a project:
The purchase requisitions are store in  PurchReqTable and the lines are stored in PurchReqLine.
Both the tables are related in PurchReqId which is unique index in PurchReqTable as shown below:

The below job can be used to create a purchase requisition for an item requirement of a project.
We can link a purchase requisition line to a project item requirement.Which means we can define the project activity, procurement category, default vendor for the item, quantity and much more information on the purchase requisition line to have a great visibility of information.

The item requirements for a project are stored as sales lines. In case you do not have any link to project then also you can use the below job to create purchase requisitions by removing the project references from the code:

There are many more fields in PurchReqLine table which can be assigned if you have values to assign to them.

If you want to reuse the code, here it is:
 static void CreatePurchReqJob(Args _args)
{
    PurchReqTable   purchReqTable;
    PurchReqLine    purchReqLine;
    ProjTable       projTable = projTable::find("‪‪‪P000");
    SalesLine       salesLine = SalesLine::findInventTransId("TRANS-00001");
    purchReqTable.clear();
    purchReqTable.initValue();
    purchReqTable.PurchReqId = NumberSeq::newGetNum(PurchReqTable::numRefPurchReqId()).num();
    purchReqTable.PurchReqName = "Testname";
    purchReqTable.ProjId = projTable.ProjId;
    purchReqTable.ProjIdDataArea = projTable.dataAreaId;
    purchReqTable.insert();
    purchReqLine.clear();
    purchReqLine.initValue();
    purchReqLine.InventDimId = salesLine.InventDimId;
    purchReqLine.initFromPurchReqTable(purchReqTable);
    purchReqLine.ItemId = salesLine.ItemId;
    purchReqLine.ActivityNumber = salesLine.ActivityNumber;
    purchReqLine.BuyingLegalEntity = CompanyInfo::find().RecId;
    purchReqLine.InventDimIdDataArea = salesLine.dataAreaId;
    purchReqLine.initFromProjTable(projTable);
    purchReqLine.insert();
    info(purchReqTable.PurchReqId);

}

If you might have read closely then you must have noticed that numRefPurchReqId() method is available on PurchReqTable itself :)

The init methods on the table are quite helpful as they initialize number of fields for us so we do not need to worry unless we have some custom logic to initialize them.
So now that we have a basic understanding of purchase requisitions and how they are different from purchase orders with some pinch of technical flavour I hope this information will be helpful.

Thanks for reading the blog.