Create Worker-User relations in AX 2012 through X++ code

A relation between the worker and the system user records (System administration > Common > Users > User relations) is necessary to approve BOMs, enter timesheets or expense reports in Dynamics AX. The user records can be easily imported from the Active Directory, the Employee records can be migrated from a legacy ERP or a 3^rd party system through the DIXF interface.
The DIXF in Dynamics AX CU9/CU10 can also fill the missing link between the two, but a simple idempotent X++ job can also be a viable solution to mass create missing DirPersonUser relations. The below script tries to match the users by the e-mail address, then by the First-Last name, then by the Last-First name etc.

// Align the users and the workers by the e-mail address first, and the name second
public static void generateDirPersonUser()
{
    DirPersonUser               dirPersonUser, dirPersonUser2;
    UserInfo                    userInfo;
    SysUserInfo                 sysUserInfo;
    HcmWorker                   hcmWorker;
    LogisticsElectronicAddress  electronicAddress;
    DirPerson                   dirPerson;
    Name                        rearrangedName;
    DirNameSequence             dirNameSequence;
    DirPersonName               dirPersonName;
    while select userInfo
        join sysUserInfo
            where sysUserInfo.Id == userInfo.id
        notexists join dirPersonUser2
            where dirPersonUser2.User == userInfo.id
    {
        dirPerson.clear();
        if (sysUserInfo.Email) // 1st attempt
        {
            select firstonly hcmWorker
                join dirPerson
                    where dirPerson.RecId == hcmWorker.Person
                join electronicAddress
                    where electronicAddress.Locator == sysUserInfo.Email
                       && electronicAddress.RecId == dirPerson.(DirPartyTable::electronicAddressType2primaryFieldId(LogisticsElectronicAddressMethodType::Email);
        }
        if (! dirPerson && strAlpha(userInfo.name))
        {
            dirPersonName.clear(); 
            [dirPersonName.FirstName,
             dirPersonName.MiddleName,
             dirPersonName.LastName] = str2con(strReplace(userInfo.name, '.,', ' '), ' ');
            while select dirNameSequence // 2nd, 3rd etc. attempt
            {    
                rearrangedName = dirPerson.name(dirPersonName,  DirNameSequence.DisplayAs);         
                select firstonly hcmWorker
                    join dirPerson
                        where dirPerson.RecId == hcmWorker.Person
                           && dirPerson.Name  == rearrangedName;
                if (dirPerson)
                    break;
            }
        }
        if (dirPerson && hcmWorker::getMaxEmploymentEndDate(hcmWorker.RecId) > DateTimeUtil::utcNow())
        {
            dirPersonUser.clear();
            dirPersonUser.initValue();
            dirPersonUser.PersonParty = dirPerson.RecId;
            dirPersonUser.User = userInfo.id;
            dirPersonUser.ValidFrom = hcmWorker::getMinEmploymentStartDate(hcmWorker.RecId);
            dirPersonUser.ValidTo = hcmWorker::getMaxEmploymentEndDate(hcmWorker.RecId);
            if (dirPersonUser.validateWrite())
            {
                dirPersonUser.insert();
            }
        }
    }
}

Happy DAXing...

Assign a date to DateTime in AX 2012

How to read/ initialize date to DateTime in AX 2012

I was working with a client where we were seeing an issue with a data import and the table HCMPositionWorkerAssignment's ValidFrom and ValidTo date fields were importing wrong for the worker's position in AX2012.

We wanted to set the ValidFrom to the date of 1/1/2017 (not derived from an AX function) and the ValidTo as the maximum date.  The maximum date seemed to be set to the valid of 'Never' but that was actually a max date of 12/31/2154.

There is often a 'developer' issue of remembering how to set dates not using an AX function.  Unless you do it all the time, its always a subject that takes a few minutes to remember how to do things with. Don't lie... You know its true...  If you're coming from AX 4.0, welcome to the UTC shock.

AX 4.0 always stored the data type 'Date' behind the scenes as a DateTime but just hacked off the time part on the AX end. There was a time EDT which was stored in seconds. Not too bad as long as you don't mind dividing by 60 all the time... There were some huge improvements in AX 2009 and 2012 wiht the UTCDateTime functionality. The biggest is the ability to see the date and time in AX as it is in SQL as well as be able to dynamically display timezone changes to the users based on where they are located.

Anyways.. There can be issues with the code needed to do paragraph two above. Here is how to do it:

HCMPositionWorkerAssignment.ValidFrom  = DateTimeUtil::newDateTime(1\1\2017, 0);HCMPositionWorkerAssignment.ValidTo    = DateTimeUtil::maxValue();

The maxValue() function will put the dateTime as the max date of 2154.  The newDateTime() function will allow you to set the field for what you specify if you use MM\DD\YYYY.  Make sure the slashes are '\' and not '/'.  It MUST be 1\1\2017 and not be 1/1/2017; note the back vs forward slashes.  There do not need to be single colon's around the variable.

For, In terms of worker it can be applied like this:

ValidFrom = hcmWorker::getMinEmploymentStartDate(hcmWorker.RecId);
ValidTo = hcmWorker::getMaxEmploymentEndDate(hcmWorker.RecId);

Happy DAXing...

Import / read date from excel to table.

Issue when import or read date type value from excel or csv file

Hi,

In excel or csv file let it be in string format itself. after that check your date format in system (AOS machine) whether it is MM/dd/yyyy or dd/MM/yyyy...

Example-1,

Suppose, your system date is in MM/dd/yyyy and you mapped Trans date in column 1 then:

 

Str                 dueDate;

dueDate                      = conpeek(con,1);
TableTMP.TransDate = str2date(dueDate,213);

Example-2,

Suppose, your system date is in dd/MM/yyyy and you mapped Trans date in column 1 then:

 

TableTMP.TransDate = str2date(conpeek(con,1),123);

Happy DAXing...

How to get today's +1 date in Dynamics AX X++?

info(date2StrXpp(today()+1)); // date of tomorrow

info(DateTimeUtil::toStr(DateTimeUtil::addDays(DateTimeUtil::utcNow(),-1))); // date of yesterday

info(date2StrXpp(systemDateGet()+1));

ValidFrom  <= DateTimeUtil::newDateTime(today(), 0, DateTimeUtil::getCompanyTimeZone())

HCMPositionWorkerAssignment.ValidFrom  = DateTimeUtil::newDateTime(1\1\2017, 0);HCMPositionWorkerAssignment.ValidTo    = DateTimeUtil::maxValue();

ValidFrom = hcmWorker::getMinEmploymentStartDate(hcmWorker.RecId);
ValidTo = hcmWorker::getMaxEmploymentEndDate(hcmWorker.RecId);

How to insert records in all companies through X++ code

How to insert records through crossCompany key word:

Test-1: 

static void Job2(Args _args)
{
    TableTMP    tableTMP; // we can take a temp table for testing
    DataArea    dataArea;
    
    ttsBegin;
    while select 
        crossCompany 
            id from dataArea
    {
        changeCompany(dataArea.id)
        {
            tableTMP = null;
            tableTMP.AccountNum = "Test03";
            tableTMP.Name       = "Test3 name";
            tableTMP.insert();
        }
    }
    ttsCommit;
}

Test-2: 

Let’s try to understand how CrossCompany and ChangeComany working with a simple job in AX 2012/2009.

1) CrossCompany:

CrossCompany keyword is access data from more than one or all the companies data which company specific.

Lets see the select statements with crosscompany’s use.

All Companies through Select Statement:

This job will access all vendor records from all the companies.

static void CrossCompanyDemo(Args _args)
{
VendTable vendTable;
;
While Select CrossCompany * from vendTable
{
info(Strfmt(“%1 : %2”, vendTable.AccountNum, vendTable.dataAreaId));
}
}

Few Companies(more than one and less than all) through Select Statement:

The below job will access the records of vendors of some specific companies.

static void CrossCompanySample(Args _args)
{
VendTable vendTable;
container con;
;
con = [ ‘TEST’, ‘AXT’, ‘DAX’];
While Select CrossCompany : Con * from vendTable
{
Info(Strfmt(“%1 : %2”, vendTable.AccountNum, vendTable.dataAreaId));
}
}

Now, it’s time for queries objects..

Through Dynamic Query:

Lets see the use of CrossCompany in Dynamic query through a job.

Static void CrossCompanysam(Args _args)
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
VendTable vendTable;
;
query = new Query();
qbds = query.addDataSource(TableNum(VendTable));
query.allowCrossCompany(True);
query.addCompanyRange(‘TEST’); // You can ignore the range statements if you want all the companies.
query.addCompanyRange(‘DAX’);
queryRun = new QueryRun(query);
while(queryRun.next())
{
vendTable = queryRun.getNo(1);
Info(Strfmt(“%1 : %2”, vendTable.AccountNum, vendTable.dataAreaId));
}
}

Through Static AOT Query:

You can understand how to use the crosscompany in Static AOT query with the below image.

To access all the companies, you need to set AllowCrossCompany property to YES.

ChangeComany:

Aim: To find out the number of customers in each company by using ChangeCompany keyword.

Static void crosscompanyjob(Args _args)

{

Dataarea    area;

Custtable   cust = null;

;

While select area

Where area.isVirtual == noyes::No // this filtration is needed to avoid virtual companies.

{

ChangeCompany(area.id)

{

cust = null; // It is mandatory to clear the cust buffer because if you won’t clear it, cust will refer to the old data (first select(below) execution) and takes the same for all the next selections.

Select count(recid) from cust;

{

info(strfmt(“%1 — %2”, area.Id, cust.RecId));

}

}

}

}

Test- 3: X++ Query object:

One way to create query in dynamics Ax is Query object. You can add by following way.

Query query;

QueryRun queryRun;

QueryBuildRange queryBuildRange;

QueryBuildDataSource queryBuildDataSource;

query = new Query();

queryBuildDataSource = query.addDataSource(TableNum(CustTable));

query.allowCrossCompany(true);

//Executes query built so far

queryRun = new QueryRun(query);

queryRun.allowCrossCompany(true);

This is most fastest way to get cross company data.

While select Query:

There two ways for Get cross Company query and or Query inside the change company statement.

Cross company Keyword:

Add cross company query inside the while select statement to extract data all legal entities in Dynamics AX as  follow code.

Custtable _CustTable;

While select crosscompany * from _Custtable

{

Info (_Custtable.AccountNum + “   :  “ + int642str(_Custtable.DataAreaId);

}

If you want to restrict the query to fetch data from limited legal Entities you have to use a container as follow.

Container _ListOfLegalEntites ={ ‘mazang’,’Jubergi’,’Urdu Bazar’};

While select  crosscompany:_ListOfLegalEntites * from _Cust

{

Info(_Custtable.AccountNum + “  :  “ + _Custtable.DataAreaId);

}

Change Company Keyword:

One method is to use change company keyword. It is most time consuming method. To reduce its time, we have rest the table to null when Change Company happened. In Dynamics All legal entities name stored at  DataArea table we use this table to get the list of datarea and used inside the changeCompany  query on it as follow.

DataArea _DataArea;

Custtable _Custtable;

While select Id from _DataArea

changeCompany(_DataArea.Id)

{

_Custtable= null;

While select * from _Custtable

{

Info(_Custtable.AccountNum +”  :   “ + _Custtable.AreaId);

}

}

If you did not reset the table buffer , result will be too heavy, reason buffer contains old data as well. To reset or set null inside the change company statement and before query on buffer will be performance bust as well as design pattern.

Test-4: 

You can create a cross-company query by using the crossCompany keyword on the X++ select statement. You have the option of adding a container variable of company identifiers immediately after the crossCompany keyword (separated by a colon). The container restricts the selected rows to those with a dataAreaId that match a value in the container.

The following code example populates a table buffer with all the BankAccountTable rows that have a dataAreaId of either cm1 or cm2 or dat. This example assumes that the user has authority to access data for these three companies. The first two dataAreaId values that are found will be printed.

X++

static void JobDemoCrossCompany(Args _args)
{
    BankAccountTable tabBAT; // saveDataPerCompany == true.
    container conCompanies = [ 'cm1', 'cm2', 'dat' ];
    str 4 sCompanyPrevious = " "; // Maximum length is 4 characters.
    int iCountCompanies = 0;
    ;
    while select
        crossCompany
            : conCompanies
        * from tabBAT
        order by dataAreaId
    {
        if ( sCompanyPrevious != tabBAT.dataAreaId )
        {
            info( tabBAT.dataAreaId + " = tabBAT.dataAreaId" );
            iCountCompanies++;
            if ( iCountCompanies >= 2 )
            {
                break;
            }
            sCompanyPrevious = tabBAT.dataAreaId;
        }
    }
    return;
}

Query Class

In X++ code you can use the Query .allowCrossCompany property method to achieve the same result as you can with the crossCompanykeyword on a select statement. The calls to the Query .addCompanyRange method are the same as appending a container of companies to the crossCompany keyword.

You cannot perform data source level filtering by company in a cross-company query. This is why the call to qbds3 .company is commented out in the following code example.

X++

static void JobDemoAllowCrossCompany(Args _args)
{
    BankAccountTable tabBAT; // saveDataPerCompany == true.
    Query qry2;
    QueryBuildDataSource qbds3;
    QueryRun qrun4;
    str sCompanyPrevious = "   ";
    int iCountCompanies = 0;
    int iTableNumBAT;
    ;
    qry2 = new Query();
    qry2.allowCrossCompany( true );
    qry2.addCompanyRange( 'dat' );
    qry2.addCompanyRange( 'twf' );
    
    iTableNumBAT = tableNum( BankAccountTable );
    qbds3 = qry2 .addDataSource( iTableNumBAT );
    //qbds3.company( 'dat' );
    
    qrun4 = new QueryRun( qry2 );
    
    while ( qrun4.next() )
    {
        if ( qrun4.changed( iTableNumBAT ) )
        {
            tabBAT = qrun4.get( iTableNumBAT );
    
            if ( sCompanyPrevious != tabBAT.dataAreaId )
            {
                print( tabBAT.dataAreaId + " = tabBAT.dataAreaId" );
                iCountCompanies++;
                if ( iCountCompanies >= 2 )
                {
                    break;
                }
                sCompanyPrevious = tabBAT.dataAreaId;
            }
        }
    }
    pause;
    return;
}

Happy DAXing......

AX 2012: Create a Batch Job through SysOperation Framework

How to extract Item master through batch job

In this post we’ll learn how to create a very basic custom Batch job using SysOperation framework. We’ll use the base controller class SysOperationServiceController and develop a custom service operation class to achieve the goal.

Requirement:

To create a Batch job to extract inventory on-hand and insert records into a custom table IDT_MaterialExtractionTMP, IDT_MaterialDefaultOrderSettingTMP & IDT_MaterialUnitConversionTMP.

Project overview:

The project shows how simple yet powerful the SysOperation framework is for developing custom batch jobs as opposed to RunBase framework since the minimum development needed to create a fully functional batch job is to create a custom service operation class defining a single method giving the implementation for the batch operation to be performed.

Development steps:

1. Create a service operation class IDT_MaterialExtractionBJobService having the following class declaration:

2. Create sub-methods in the class giving a suitable name like initFrom.... having the following definition:

3. Create a new method in the class giving a suitable name like processData having the following definition:

[ SysEntryPointAttribute(false) ]
public void processData()
{
    InventTable                 inventTable;
    int                         counter = 0;
    InventDimId                 inventDimId = "AllBlank";
    //SysTableBrowser             sysTableBrowser = new SysTableBrowser();
    //IDT_MaterialExtractionBJobService   IDT_MaterialExtractionBJobService.processData

    //Determines the runtime
    if (xSession::isCLRSession())
    {
        info('Running in a CLR session.');
    }
    else
    {
        info('Running in an interpreter session.');

        //Determines the tier
        if (isRunningOnServer())
        {
            info('Running on the AOS.');
        }
        else
        {
            info('Running on the Client.');
        }
    }

    delete_from materialExtractionTMPExt
        where materialExtractionTMPExt.ItemDataAreaId == curext();
    delete_from materialUnitConversionTMPExt
        where materialUnitConversionTMPExt.ItemDataAreaId == curext();
    delete_from materialDefaultOrderSettingTMPExt
        where materialDefaultOrderSettingTMPExt.ItemDataAreaId == curext();

    //materialExtractionTMP.clear();
    while select ItemId,NameAlias,dataAreaId,IDT_Quality_required,ItemBuyerGroupId,FSProductApprovalStatus,
                 PdsShelfLife,PrimaryVendorId,PmfProductType,PdsBestBefore,Product from inventTable
        where inventTable.NameAlias != "Obsolete"
        &&    inventTable.NameAlias != "Use to Depletion"
        &&    inventTable.FSProductApprovalStatus == FSProductApprovalStatus::Approved
    {
        ttsBegin;
        materialExtractionTMP.ItemId                    = inventTable.ItemId;
        materialExtractionTMP.NameAlias                 = inventTable.NameAlias;
        materialExtractionTMP.ItemName                  = inventTable.itemName();
        materialExtractionTMP.ItemDataAreaId            = inventTable.dataAreaId;
        materialExtractionTMP.IDT_Quality_Required      = inventTable.IDT_Quality_required;
        materialExtractionTMP.ItemBuyerGroupId          = inventTable.ItemBuyerGroupId;
        materialExtractionTMP.FSProductApprovalStatus   = inventTable.FSProductApprovalStatus;
        materialExtractionTMP.PdsShelfLife              = inventTable.PdsShelfLife;
        materialExtractionTMP.PrimaryVendorId           = inventTable.PrimaryVendorId;
        materialExtractionTMP.PmfProductType            = inventTable.PmfProductType;
        materialExtractionTMP.PdsBestBefore             = inventTable.PdsBestBefore;
        materialExtractionTMP.CostGroupId               = inventTable.CostGroupId;
        materialExtractionTMP.IDT_DrawingNumber         = inventTable.IDT_DrawingNumber;
        materialExtractionTMP.IDT_StorageTemperature    = inventTable.IDT_StorageTemperature;
        materialExtractionTMP.ProdPoolId                = inventTable.ProdPoolId;

        this.initFromInventItemLocation(inventTable, inventDimId);
        this.initFromInventItemGroupItem(inventTable);
        this.initFromInventModelGroupItem(inventTable);

        this.initFromEcoResTrackingDim(inventTable);
        this.initFromEcoResStorageDim(inventTable);

        this.initFromInventTableModule(inventTable);

        this.initFromInventItemSetupSupplyType(inventTable);
        this.initFromInventItemSetup(inventTable, inventDimId);
        this.initFromSiteSpecificOrderSettings(inventTable, inventDimId);

        this.initFromReqItemTable(inventTable);
        this.initFromUnitOfMeasureConversion(inventTable);
        this.initFromEcoResCategory(inventTable);
        this.initFromCustVendExternalItem(inventTable);

        materialExtractionTMP.insert();
        ttsCommit;
        //counter++;
    }
    //sysTableBrowser.run(tableNum(IDT_MaterialExtractionTMP));

}

4. Create a new Action type menu item IDT_MaterialExtractionBJobService  pointing to SysOperationServiceController.

5. Set the parameters of the action menu item to the service operation just created, IDT_MaterialExtractionBJobService .processData.

6. Compile the service operation class and generate incremental CIL.

7.Click on the action menu item to run the batch job. Check the Batch processing checkbox to run the job in CLR runtime which is the batch server execution environment.

8. Click System administration > Inquiries > Batch jobs to view the status of the job. You may also click on the Log button to view the messages written to infolog during the job execution.

AX 2012: Create a Batch Job through SysOperation Framework

How to extract Inventory on-hand through batch job

In this post we’ll learn how to create a very basic custom Batch job using SysOperation framework. We’ll use the base controller class SysOperationServiceController and develop a custom service operation class to achieve the goal.

Requirement:

To create a Batch job to extract inventory on-hand and insert records into a custom table IDT_InventOnhandExtractionTMP.

Project overview:

The project shows how simple yet powerful the SysOperation framework is for developing custom batch jobs as opposed to RunBase framework since the minimum development needed to create a fully functional batch job is to create a custom service operation class defining a single method giving the implementation for the batch operation to be performed.

Development steps:

1. Create a service operation class IDT_InventOnhandExtractionService having the following class declaration:

2. Create sub-methods in the class giving a suitable name like initFrom.... having the following definition:

3. Create a new method in the class giving a suitable name like processData having the following definition:

[ SysEntryPointAttribute(false) ]

public void processData()

{

    InventTable             inventTable;

    InventSum               inventSum;

    InventDim               inventDim;

    InventDimId             inventDimId = "AllBlank";

    //SysTableBrowser         sysTableBrowser = new SysTableBrowser();

    //Determines the runtime

    if (xSession::isCLRSession())

    {

        info('Running in a CLR session.');

    }

    else

    {

        info('Running in an interpreter session.');

        //Determines the tier

        if (isRunningOnServer())

        {

            info('Running on the AOS.');

        }

        else

        {

            info('Running on the Client.');

        }

    }

    delete_from inventOnhandExtractionTMPExt

        where inventOnhandExtractionTMPExt.ItemDataAreaId == curext();

    //inventOnhandExtractionTMP.clear();

    while select ItemId,Closed,ClosedQty,InventDimId,PhysicalInvent from inventSum

        join ItemId,NameAlias,dataAreaId,Product from inventTable

            where inventSum.ItemId      == inventTable.ItemId

            &&    inventSum.Closed      == NoYes::No

            &&    inventSum.ClosedQty   == NoYes::No

            &&    inventSum.PhysicalInvent > 0

            &&    inventTable.NameAlias != "Obsolete"

            &&    inventTable.NameAlias != "Use to Depletion"

    {

        ttsBegin;

        inventOnhandExtractionTMP.ItemId                = inventSum.ItemId;

        inventOnhandExtractionTMP.NameAlias             = inventTable.NameAlias;

        inventOnhandExtractionTMP.ItemName              = inventTable.itemName();

        inventOnhandExtractionTMP.ItemDataAreaId        = inventTable.dataAreaId;

        inventOnhandExtractionTMP.PhysicalInvent        = inventSum.PhysicalInvent;

        inventDim = InventDim::find(inventSum.InventDimId);

        inventOnhandExtractionTMP.InventSiteId          = inventDim.InventSiteId;

        inventOnhandExtractionTMP.InventLocationId      = inventDim.InventLocationId;

        inventOnhandExtractionTMP.wmsLocationId         = inventDim.wMSLocationId;

        inventOnhandExtractionTMP.InventBatchId         = inventDim.inventBatchId;

        this.initFromInventItemGroupItem(inventSum);

        this.initFromInventTableModule(inventSum);

        this.initFromInventBatch(inventSum, inventOnhandExtractionTMP.InventBatchId);

        inventOnhandExtractionTMP.insert();

        ttsCommit;

        //counter++;

    }

    //sysTableBrowser.run(tableNum(IDT_MaterialExtractionTMP));

}

4. Create a new Action type menu item IDT_InventOnhandExtractionService pointing to SysOperationServiceController.

5. Set the parameters of the action menu item to the service operation just created, IDT_InventOnhandExtractionService.processData.

6. Compile the service operation class and generate incremental CIL.

7.Click on the action menu item to run the batch job. Check the Batch processing checkbox to run the job in CLR runtime which is the batch server execution environment.

8. Click System administration > Inquiries > Batch jobs to view the status of the job. You may also click on the Log button to view the messages written to infolog during the job execution.

Happy DAXing...