Friday, May 15, 2020

X++ SysOperation Framework

Firstly, what is a framework:
To understand frameworks we first need to understand libraries. Libraries are a bunch of code that is pre-written and packaged to save our time. When we need to do a task, we just call the appropriate library and it does the job for us. We don’t need to know the details of how the functions inside the libraries work, we just need to know how to call them.

Frameworks are just like libraries in a way that they make our job easier, but we can't call frameworks in the same way as libraries. To use framework, we have to learn the framework, the framework gives us a structure to place and call our code, and not the other way round.

In simple terms framework is to structure what libraries is to code. Using library we reuse code, and using a framework we reuse a class structure.

When we work with X++ there are these set of framework classes that are used all over X++ development.

SysOperation framework:  
The SysOperation is used whenever there is a user interface which triggers a certain functionality. Its quite close to the MVC pattern and work on the similar principles of segregating code to remove dependencies. 

The Model : Data contract
Its the model class from the MVC pattern in which we define attributes we need for our operation, commonly set as parameters by the user in a dialog. A regular class is identified as a SysOperation Data Contract class by adding the DataContractAttribute attribute to its declaraion.

Additionally, if we want a set of methods to be available to us, we can also extend the SysOperationDataContractBase base class. With this class, we can define how our basic dialog will look like to the user. We can define labels, groups, sizes and types of the parameters.

The View : UI Builder
Its an optional class and is the view part from the MVC pattern. Generally AX creates the dialog for us with a standard view, however if are not happy with the standard view of we want to extend it we use the UI Builder class.

The Controller : Controller 
The controller orchestrates the whole operation. It holds information about the operation, such as if it should show a progress form, if it should show the dialog, and its execution mode - asynchronous or not. To create a controller class you should extend the SysOperationServiceController.

Service
While using the MVC we have to understand that not everything is a perfect MVC and as per OOP principles we have to ensure the dependencies between the classes is minimal. Technically one could put the business logic in the controller, however what if the same business logic has to be used outside the controller and without an interaction ? Hence, it a good idea to store the business logic outside the controller and hence we have the service classes. 

The service class stores the business logic. To create a service class we have to extend it from the SysOperationServiceBase class. When constructing your controller, you can indicate which class holds the operation that the controller will trigger.

Monday, April 27, 2020

Check for Localization

Localization needs can break our existing code. Sometimes its required to consider the localized configuration for a given region and then accordinlgy take some actions.

Given below is the example where we are expected to check if the current legal entity is the localized legal entity for India.

use the below macro in the declaration section of the object
#ISOCountryRegionCodes

Now the macro #isoIN would be available and can be used as follows:
SysCountryRegionCode::isLegalEntityInCountryRegion([#isoIN]);

Tuesday, April 07, 2020

Dimension Tables

Step 1: Lets take a simple scenario of creating 2 dimensions or Attributes.
  1. D1
  2. D2


Step 2: These attributes would then have values
    1.1  DXB
    1.2  IND

    2.1  SALES
    2.2  OPS
    2.3  ADMIN

Step 3: These attributes can be combined to create attribute sets. A set would have the individual dimensions and the sequence in which they are used as details
    3.1  SET1
          3.1.1  D1
          3.1.2  D2

    3.2   SET2
          3.2.1  D2
          3.2.2  D1


Step 4: Based on the sets defined above a combination of attribute values could be created
  4.1    SET1
     4.1.1   DXB+SALES
     4.1.2   DXB+OPS
     4.1.3   DXB+ADMIN


  4.2  SET2
    4.2.1    IND+SALES
    4.2.2    IND+OPS
    4.2.3    IND+ADMIN


When the above structure has to be stored in AX tables, it is divided in two parts. The part 1 takes care of storing the schema and the part 2 takes care of storing the values.

Part 1 : the details about the dimensions are stored in
  1. DimensionAttribute = this tables is the dimension master. Each dimension has 1 record in this table. (Step 1)
  2. DimensionAttributeSet = this table maintains the dimension set. (Step 3)
  3. DimensionAttributeSetItem = this table store the details of individual attributes in each set (Step 3.1.1 to 3.2.2)

 Part 2 : The *Value counterparts are :-
  1. DimensionAttributeValue : The individual values ( Step 1.1 to 2.3). There is a EntityInstance field in this table. That’s the relation to the value original table when the dimensions are not custom values.
  2. DimensionAttributeValueSet : The values corresponding to each set. There is a hash value generated for each combination of values. (a hash is a numeric equivalent of a string)  
  3. DimensionAttributeValueSetItem : The individual values for each of the attribute of the set.
  4. FinancialTagCategory: This table stores record of custom financial dimension.
  5. DimensionFinancialTag: this table stores custom financial dimensions value.

The combination of Ledger Account with the DimensionAttributes is stored in a new set of tables referred as ValueGroup Tables. As the name suggests the value group is a group that is created to store values (amounts)
  1. DimensionAttributeValueCombination: Stores combination of Ledger and DimensionAttributes
  2. DimensionAttributeValueGroup: Stores dimension group
  3. DimensionAttributeValueGroupCombination: Store relation of DimensionAttributeValueGroup and DimensionAttributeValueCombination
  4. DimensionAttributeLevelValue: Stores dimension value of ledger dimension

Monday, April 06, 2020

Partially disable dimensionDefaultingController

The requirement being restricting the dimension selection on the dimension controller based on certain business rules.

I had a requirement where the default dimensions on the employee master had to be restricted to allow entry only for a subset of the total dimensions. As shown in the screen shot below the need was to restrict the selection of only D1_Division and D3_ConsGroup on the employee master and disable the rest for data entry


The code for the same has to be written on the Active method of the relevant DataSource on the form.


    DimensionAttributeSetStorage    dimAttrSetStorage;
    DimensionAttribute              dimAttribute;
    DimensionEnumeration            dimEnumeration;

    int ret;

    ret = super();

    dimensionDefaultingController.activated();    
    
    //The dimension controller to be locked to allow only certain dimensions to be entered.
    dimAttrSetStorage = new DimensionAttributeSetStorage();
    // D1_Division
    dimAttribute = DimensionAttribute::findByName('D1_Division');
    if(dimAttribute)
    {
        dimAttrSetStorage.addItem( dimAttribute.RecId, dimAttribute.HashKey, NoYes::Yes );
    }
    // D3_ConsGroup
    dimAttribute = DimensionAttribute::findByName('D3_ConsGroup');
    if(dimAttribute)
    {
        dimAttrSetStorage.addItem( dimAttribute.RecId, dimAttribute.HashKey, NoYes::Yes );
    }

    dimEnumeration = dimAttrSetStorage.save();
    dimensionDefaultingController.setEditability( true, dimEnumeration );


Wednesday, March 11, 2020

Use mapped network drive in SQL Server

To use a mapped drive in SQL server make sure that the mapping is done using the xp_cmdshell procedure.

Before the extended procedure can be used it has to b enabled as shown below

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

thereafter map the drive so that SQL understands it

EXEC XP_CMDSHELL 'net use Z: \\192.168.100.36\nansql'

Tuesday, October 22, 2019

AX2012 cross company query challenge and workaround

Had an encounter with cross company query in AX2012 and listed below are the findings. When we issue the cross company clause there are a few important things that happen.

1. The sql query at the backend is converted into a cross join for most of the joins.
2. The sql query at the backend automatically created conditions for the partions and dataareaids to ensure that cross joins dont distort the data across companies.

Take an example of an select statement as below:-

SELECT crosscompany count(RecId) FROM TSTimesheetLine
join tsTimesheetTable
where TSTimesheetTable.TimesheetNbr == TSTimesheetLine.TimesheetNbr
    && tsTimesheetLine.ProjId != 'Z905'
join TSTimesheetLineWeek
where TSTimesheetLineWeek.TSTimesheetLine == TSTimesheetLine.RecId
&& ( ( ( TsTimesheetLineWeek.Hours[0] + TsTimesheetLineWeek.Hours[1] + TsTimesheetLineWeek.Hours[2] + TsTimesheetLineWeek.Hours[3] + TsTimesheetLineWeek.Hours[4] + TsTimesheetLineWeek.Hours[5] + TsTimesheetLineWeek.Hours[6] + TsTimesheetLineWeek.Hours[7] ) > 0 ) )
notExists join supportHrsView  
where supportHrsView.TsTimesheetLineRef == TSTimesheetLine.RecId 

Now, my requirement is to match the highlighted condition across the dataAreaId as the supportHrsView is a shared table for me. Basically i dont want the compiler to apply a dataAreaId condition on this table. 

This statement is translated to SQL as follows
SELECT COUNT(T1.RECID) 
FROM TSTIMESHEETLINE T1 
CROSS JOIN TSTIMESHEETTABLE T2 
CROSS JOIN TSTIMESHEETLINEWEEK T3 
WHERE (T1.PARTITION=@P1) 
AND ((T2.PARTITION=@P2) AND ((T2.TIMESHEETNBR=T1.TIMESHEETNBR AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T1.PROJID<>@P3))) 
AND ((T3.PARTITION=@P4) AND ((T3.TSTIMESHEETLINE=T1.RECID AND (T3.DATAAREAID = T1.DATAAREAID) AND (T3.PARTITION = T1.PARTITION)) AND ((((((((T3.HOURS+T3.HOURS)+T3.HOURS2_)+T3.HOURS3_)+T3.HOURS4_)+T3.HOURS5_)+T3.HOURS6_)+T3.HOURS7_)>@P5))) 
AND NOT (EXISTS 
(
SELECT 'x' FROM AFZSUPPORTHRSVIEW T4 
WHERE (
(T4.PARTITION=@P6) 
AND (T4.TSTIMESHEETLINEREF=T1.RECID AND (T4.DATAAREAID = T1.DATAAREAID) AND (T4.PARTITION = T1.PARTITION))
)
)

)

Please note the following facts:
1. All the joins in the query are translated to cross joins. 
2. There are 4 set of data in this and these have an alias as T1, T2, T3 and T4

As we know that cross joins results into cartisan product of the two tables it can result in a huge result set and hence the compiler takes care to ensure that the data is not mixed up between the different sets by enforcing a partition and dataarea id condition even though it is not explicitly provided in the select statement. 
1. Please note that there is a partition condition applied for each of the resultsets T1 to T4. 
2. Please note that there is a dataAreaId condition applied for each resultset where the dataAreaId for T1 is applied on T2, T3, and T4


Coming back to what my requirement is i want a way to ensure that the dataAreaId condition applied on T4 (T4.DATAAREAID = T1.DATAAREAID) is skipped. The workaround to get around this is to apply an operator on the join with supportHrsView, so if i change my condition in the select query as follows:
where supportHrsView.TsTimesheetLineRef == TSTimesheetLine.RecId + 0 

Now, the compiler skips the forced dataAreaId and Partition join and the SQL query issued by the compiler to SQL is as follows: 

SELECT COUNT(T1.RECID) 
FROM TSTIMESHEETLINE T1 
CROSS JOIN TSTIMESHEETTABLE T2 
CROSS JOIN TSTIMESHEETLINEWEEK T3 
WHERE (T1.PARTITION=@P1) 
AND ((T2.PARTITION=@P2) AND ((T2.TIMESHEETNBR=T1.TIMESHEETNBR AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T1.PROJID<>@P3))) 
AND ((T3.PARTITION=@P4) AND ((T3.TSTIMESHEETLINE=T1.RECID AND (T3.DATAAREAID = T1.DATAAREAID) AND (T3.PARTITION = T1.PARTITION)) AND ((((((((T3.HOURS+T3.HOURS)+T3.HOURS2_)+T3.HOURS3_)+T3.HOURS4_)+T3.HOURS5_)+T3.HOURS6_)+T3.HOURS7_)>@P5))) 
AND NOT (EXISTS 
(
SELECT 'x' 
FROM AFZSUPPORTHRSVIEW T4 
WHERE ((T4.PARTITION=@P6) AND (T4.TSTIMESHEETLINEREF=(T1.RECID+@P7)))
)
)

Please note that the condition T4.DATAAREAID = T1.DATAAREAID is now not applied and we get the desired results. 

Saturday, September 21, 2019

Merge Queries

Had a requirement where two Queries created using the dynamics query framework classes, had to be merged together. My business case was as follows.

Business Case: Required to create a report which would be run for a selected no of employees (query1). Within this selected set of employee certain data was required for a further finer selection of employees ( query2). The query2 was a subset of employee like managers and part time employees with the selected query1.

It was required that the Query2 is appended to the original Query1 and the filters are copied so that the results can be achieved.

if ( filterQuery != null )
{
    //start by looping for all the datasources in the source query and find the common datasource in target query
    //if a common datasource is found, then merge the ranges. If not found try to find the parent if a common parent
    //is found then add the datasource below the right parent and merge the ranges         

    for (int ctr = 1; ctr <= filterQuery.dataSourceCount(); ctr ++)
    {
//check if a common datasource/table exists between the two queries
qdbCurrentSource = filterQuery.dataSourceNo(ctr);
qdbCommon = finalQuery.dataSourceTable(qdbCurrentSource.table());

if (!qdbCommon) //if a common table is not found then look for a parent
{
    parentTable = qdbCurrentSource.parentDataSource().file();

    if (parentTable)
    {
qdbCommonParent = finalQuery.dataSourceTable(parentTable);
if (qdbCommonParent) //if the parent is found then add the current datasource to the common parent
{
    qdbCommon = qdbCommonParent.addDataSource(qdbCurrentSource.table());
    qdbCommon.fetchMode(QueryFetchMode::One2One); //IMPORTANT without this the query can get seperated
 
    for( int intLinkCtr=1; intLinkCtr<= filterQuery.dataSourceNo(ctr).linkCount(); intLinkCtr ++)
    {
link = filterQuery.dataSourceNo(ctr).link(intLinkCtr) ;
if ( link.relatedField() == 0)
{
    qdbCommon.relations(true); //this only works between the parent and current datasource
}
else
{
    qdbCommon.joinMode( filterQuery.dataSourceNo(ctr).joinMode() );
    qdbCommon.addLink( link.field(), link.relatedField() );
}
    } //link counter

    SysQuery::mergeRanges(finalQuery, filterQuery, ctr, false, true);
    SysQuery::mergeFilters( filterQuery, finalQuery,ctr,true,false);
} //common parent
    }
 
    if (!qdbCommon)
    {
qdbCommon = finalQuery.addDataSource(filterQuery.dataSourceNo(ctr).table());                     
qdbCommon.relations(true);
    }
 
}
else
{
    SysQuery::mergeRanges(finalQuery, filterQuery, ctr, false, true);
    SysQuery::mergeFilters( filterQuery, finalQuery,ctr,true,false);
}
    }

    SysQuery::copyDynalinks(finalQuery,filterQuery);
} //filterQuery is null