Dynamics AX: 2018

Monday, September 17, 2018

End-to-end applications that work great on their own and even better together

Turn relationships into revenue

Dynamics 365 for Sales

Empower sellers with insights to personalize relationships, predict customer needs, and increase sales.

LEARN MORE

Dynamics 365 for Retail

Create personalized shopping experiences that unify digital, in-store, and back office operations.

LEARN MORE

Sunday, July 29, 2018

Retail

1- Go to Retail > Common > Organization

2- Click the Organization Hierarchy tab button to create a new organization hierarchy.

3- When the new window opens, click New and assign a name to the new organization hierarchy (i.e. Retail Store).

4- Then click the Assign Purpose button to open the Organization hierarchy purposes window. In here we need to link the Organization Hierarchy you just created in step 3 with the organizational chart portion of AX. To accomplish this, click the Add button to add a newAssigned Category. Once you have created the assigned category, click the Set as Default button. This will set your configuration as the default one to carry on later on the process.

5- When step 4 has been completed, click the Retail Assortment menu on the left and follow the same process you executed in step 4.

6- When finished you should see the following

7- Then go to Organization Hierarchy and click theOrganization Hierarchy view. When the Hierarchy Designer opens, click the Edit button.

8- Click the Insert button and choose Legal Entity

9- The legal Entity (i.e. CEU) will appear in the Retail Store designer.

10- Click as Save as Draft. On the Save Changes windows choose “Save as draft” and click OK.

11- Then you to Retail > Common > Retail Channels > Retail Stores

12- Click New Retail Store and fill the required fields.

13- Go to the miscellaneous fast tab and make sure you have your payment methods. On this, for some reason in my environment, the Credit Card Payment method needed to be setup to 2 and not 02.

14- If all goes OK, you should see the following.

15- Now, go back to Hierarchies to add your new Retail Store to it under your organization. For this, click the Editbutton and choose “Keep working on this draft” and click OK.

16- Click the Insert button and choose Retail Channel.

17- From the Retail Channel window choose your new retail store, and click OK.

18- If all is ok, you should see something similar to the picture below.

19- Save your new model as draft again.

20- In order to make the model “official” within AX, we need to publish it. Do this by clicking Publish and Close the Viewer.

21- Select the effective date when you want this model to be active and click the Publish button.

22- You should see the following message.

Once you have created your retail store(s), then you cancreate assortments and assign them to products and/or product groups to be publish into the retail channels. The assortment(s) will then be transferred to the AXRetailPOS database through an N-JOB.

Tuesday, July 17, 2018

Connect and Integrate over 70 Systems

Provides secure, high performance crawling and indexing
Connects to a wide variety of systems
Manages sophisticated security scenarios
Maps security entitlements with source systems into search
Ensures results are trimmed to expose only content the user has access to
We will build new connectors as needed if one doesn’t already exist

Search Indexing Connectors for applications

Use Our Out-of-the-Box Connectors to Connect Azure Search, Elasticsearch, and SharePoint Search to Your Enterprise Systems:

Wednesday, June 6, 2018

Batch jobs in AX 2012

Today, I will demo about batch jobs in AX 2012. At first, I will let you explore the Batch Server Overview on msdn. You have to follow all the steps as given bellow:
1. Configure an AOS instance as the batch server. (Click System administration > Setup > System > Server configuration).

2. Create a new Batch Group, name it ProcessD (Click System administration > Setup > Batch group).

3. Add available batch server for batch group that has been created.

Now we have to create and schedule a batch job. Let's say we are going to schedule a batch which will fetch the number of purchase orders that are invoiced and add the count in a table named PurchaseOrders. So let's add a custom table Purchase Orders adding two fields DateTill and TotalPurchaseOrder.

Now create a class "ProcessingDataBatch", set it run at server. Also make sure you extend it with RunBaseBatch to make it behave like a batch job.

Thursday, April 26, 2018

Fetch number of children associated with a worker

Little code snippet as provided below can help you get the number of children associated with a particular worker.

public static void getEmployeeTotalChildren(Args _args)
{
DirPartyRelationship partyRelationship;
DirRelationshipTypeTable relationshipTypeTable;
DirPerson dirPerson;
HcmWorker worker;
DirPartyTable partyTable;

///get the worker by personnel number
worker = HcmWorker::findByPersonnelNumber('NNNNN');

select firstonly dirPerson
where dirPerson.RecId == worker.Person
join partyTable
where partyTable.PartyNumber == dirPerson.PartyNumber;

select count(RecId) from partyRelationship
join RelationshipTypeId, SystemType from relationshipTypeTable
where partyRelationship.ParentParty == partyTable.RecId
&& relationshipTypeTable.RelationshipTypeId == partyRelationship.RelationshipTypeId
&& relationshipTypeTable.SystemType == DirSystemRelationshipType::Child;

info(strFmt("Total Children of Worker %1, are %2", worker.name(), partyRelationship.RecId));
}

Wednesday, March 28, 2018

How joins in X++ select statement are translated into T-SQL

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.

1. join in X++:

select AccountNum from custTable
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;

CROSS JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.TAXGROUPID, T2.RECID
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
  AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
   AND (T2.CUSTGROUP=T1.CUSTGROUP))

2. outer join in X++:

select AccountNum from custTable
    outer join AccountID from custBankAccount
    where custBankAccount.CustAccount == custTable.AccountNum;

LEFT OUTER JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.ACCOUNTID, T2.RECID
FROM CUSTTABLE T1 LEFT OUTER JOIN CUSTBANKACCOUNT T2
ON (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT))
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))

3. exists join in X++:

select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum;

EXISTS (SELECT 'x'...) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND EXISTS (SELECT 'x'
            FROM CUSTTABLE T2
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
              AND  (T1.CUSTACCOUNT=T2.ACCOUNTNUM)))

4. notexists join in X++:

select AccountNum from custBankAccount
    notexists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum;

NOT (EXISTS (SELECT 'x'...)) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND NOT (EXISTS (SELECT 'x'
                 FROM CUSTTABLE T2
                 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
                   AND  (T1.CUSTACCOUNT=T2.ACCOUNTNUM))))

5. join after exists join in X++:

select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;

EXISTS (SELECT 'x'...CROSS JOIN...) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND EXISTS (SELECT 'x'
            FROM CUSTTABLE T2 CROSS JOIN CUSTGROUP T3
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
               AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)) 
              AND (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) 
               AND (T3.CUSTGROUP=T2.CUSTGROUP)))

6. exists join after exists join in X++:

select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum
    exists join custGroup
    where custGroup.CustGroup  == custTable.CustGroup
       && custGroup.TaxGroupId == "Std";

EXISTS (SELECT 'x'... EXISTS (SELECT 'x'...)) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))

AND EXISTS (SELECT 'x' FROM CUSTTABLE T2 
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
               AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)) 
               AND EXISTS (SELECT 'x' 
                           FROM CUSTGROUP T3 
                           WHERE (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) 
                              AND ((T3.CUSTGROUP=T2.CUSTGROUP) AND (T3.TAXGROUPID=?)))))

Finding out which tables were updated by an operation in AX

At times when troubleshooting Microsoft Dynamics AX you need to know which tables were updated by a particular operation, for example after performing a posting. This could be if you want to be sure that all the right tables are updated correctly after an upgrade, if you have unexpected results on one customer/vendor etc or if you have made an update the system and you want to be sure that everything is ok.

It is possible to use the SQL tracer for this, but for functional people it can be at times be a bit challenging to interpret these results, so here is a tip for how you can easily find out which tables were updated when performing an operation in AX. In this example we will show the effect on tables from a Sales Invoice posting in Microsoft Dynamics AX 2012 as this is quite a complex process that affects many tables.

The whole technique here relies on using a standard report ‘Size of company accounts’ in a novel way. This report returns an output of all the tables in AX that have data in them. In this article we will see how we can use this report to print a snapshot of the tables before and after an operation so that when we print it ‘after’ we will see which tables got data populated into them from an AX operation.

The steps are as follows

1. Go to the AOT and locate the Class ‘SysCompanySizeDP’ and open up the method called ‘insertIntoTempTable’. Change the highlighted function from ‘tableid2Pname’ to instead ‘tableid2name’. The reason we are changing the function is that it is easier to locate the tables in the AOT when the report prints the tables using the technical names. If we didn’t do this, the report would for example print the name of a table like ‘Customer Invoice Journal’ instead of ‘CustInvoiceJour’

2. Now go to System Administration\Reports\Database\Size of company accounts and run the report. Note the technical names

3. Export this report to Excel

4. Open up the saved Excel sheet and notice that the report content is copied over

5. Now let’s make a sales order invoice posting

6. We go back to System Administration and run the company size report again and once more save it, this time under a different name, for example ‘Company Size 2’

7. Now we copy the tables in the sheet from the last run ‘Company size report’ to the first saved Company size Excel sheet

So that we have a sheet with 2 different ‘Company size’ report executions – Before and after the Sales Order invoice posting

8. Now add a new column ‘Difference’ with a simple calculation extracting cell ‘C’ from cell ‘H’ and copy it across the rows as highlighted on
the screenshot in red below. Notice also the black box, that shows already some tables that have been affected

9. Now in order to easily identify which tables were affected by the posting we can do this trick, apply a filter in the ‘Difference’ column
and unmark ‘blanks’ in the display of the cells’ values

RESULT

We can now see easily which tables were updated by the Sales Order posting. The same technique can naturally be used by any other kind of operation in AX, such as a Journal posting or a Master Planning run – all it relies on is just tracing which tables were updated since the last run of the report. Have fun !