Browsing Folders for Files on 64-bit Server Side Implementation (Batch Run)

I am sure all of us have had a requirement where we were supposed to process a folder for specific file pattern / all file pattern and process these files.

This scenario is very prominent during integration of different applications with AX.

I scenarios where this process is run manually or on client side, we use the standard methods provided by Microsoft in WinApi class (FindFirstFile, FindNextFile, FindClose).

These methods in turn use the FindFirstFile, FindNextFile and FindClose methods defined in Kernel32 dll.

Now these methods are not provided on Server Side implementation of WinApi namely WinApiServer. But you can copy  these methods from WinApi to WinApiServer and use them as required.

These methods work fine on any 32-bit OS on both client side and server side.

But the real issue comes when you are using these methods on 64-bit systems and that too on server side.

The reason being we need some redirection that needs to be done. I will speak about this re-direction in a separate blog post as I am still working on making this work inside Ax.

But other method that I found was to use System.IO class.

Here is a sample code with proper comments to make you understand.

server public static void getFiles()

{

    System.IO.DirectoryInfo directoryInfo;

    System.IO.FileInfo[]    fileList;

    System.IO.FileInfo      file;

    System.Exception        e;

    System.String           moveName, fromName;

 

    int i;

    int fileCount;

    str axMoveName, axFromName;

 

    InterOpPermission   perm;

    FileIOPermission    filePerm;

    FilePath            searchFilePath;

    FileName            moveFileName;

    ;

 

    //Check if the folder exists

    filePerm = new FileIOPermission(@"<Server Unc Path>\AX_InBound\", ‘r’);

    filePerm.assert();

 

    if (!WinApiServer::fileExists(@"<Server Unc Path>\AX_InBound\", true))

    {

        throw error("Path not found");

    }

 

    CodeAccessPermission::revertAssert();

 

    //Check if the archive folder exists

    filePerm = new FileIOPermission(@"<Server Unc Path>\AX_Archive\", ‘r’);

    filePerm.assert();

 

    if (!WinApiServer::fileExists(@"<Server Unc Path>\AX_Archive\", true))

    {

        throw error("Archive Path not found");

    }

 

    CodeAccessPermission::revertAssert();

   

    //Check the permission for CLR InterOp

    perm = new InterOpPermission(InteropKind::ClrInterop);

    perm.assert();

 

    try

    {

        //Traverse to the inbound folder and search file

        searchFilePath = @"<Server Unc Path>\AX_InBound\";

        directoryInfo = new System.IO.DirectoryInfo(searchFilePath);

 

        //Get list of only text files

        fileList = directoryInfo.GetFiles("*.txt");

 

        //Get the file count

        fileCount = fileList.get_Length();

 

        //One by one get each file

        for (i = 0; i < fileCount; i++)

        {

            file = fileList.GetValue(i);

 

            //.Net method returns System.String need to marshal it to AX str data type

            fromName = file.get_FullName();

            axFromName = fromName;

 

            //Display the name of the fetched file

            info(fromName);

 

            //.Net method returns System.String need to marshal it to AX str data type

            moveName = file.get_Name();

            axMoveName = moveName;

 

            //Move to archive folder

            moveFileName = @"<Server Unc Path>\AX_Archive\" + axMoveName;

            file.MoveTo(moveFileName);

 

            info("File moved to " + moveFileName);

        }

    }

    catch(Exception::CLRError)

    {

        e = ClrInterOp::getLastException();

        while (e)

        {

            error(e.get_Message());

            e = e.get_InnerException();

        }

    }

    catch

    {

        error("An unknown exception has occurred");

    }

   

    CodeAccessPermission::revertAssert();

}

Creating a custom compiler output type and displaying in the compiler output in Dynamics Ax 2009

We have all seen the tabs and messages on the compiler form. But has anybody given a thought to change this so that we can also add our custom or tailor made messages to it?

Well if you are thinking how this can be achieved, here is a link to one such article on my friend Kamal’s blog that explains the method to modify the compiler form to make it display messages as desired by us

Really cool one Kamal.

Click here to read the article.

Info on Indexes

Important notes to remember on Indexes:

 

1.       Whenever you are using an index or index hint in the query, specify the fields in the where clause as defined in index otherwise SQL doesn’t use the index specified

2.       If a table index has been disabled by setting the index’s Enabled property to No, the select statement that references the index is still valid. However, the database can’t use the index as a hint for how to sort the data, because the index doesn’t exist in the database.

 

Unique and Non-Unique Indexes

There are two types of indexes: unique and non-unique. Whether an index is unique is defined by the index’s AllowDuplicates property. When this property is set to No, a unique index is created. The database uses the unique index to ensure that no duplicate key values occur. The database prevents you from inserting records with duplicate key values by rejecting the insert

Setting the index’s AllowDuplicates property to Yes creates a non-unique index. These indexes allow you to enter duplicate values for the indexed fields and are used for performance reasons.

 

Note

A field of data type memo or container cannot be used in an index.

 

System Index

Microsoft Dynamics AX requires a unique index on each table so if there are no indexes on a table or all the indexes are disabled, a system index is automatically created. The system index is created on the RecId and DataAreaId fields if the DataAreaId field exists. Otherwise the system index is created on the RecId field. You can see system indexes in the database but they aren’t visible in the AOT.

If there are indexes on a table but none of them are unique, the runtime estimates the average key length of the existing indexes, chooses the index with the smallest key length and appends the RecId column to create a unique index.

 

Using Index Hints

To use index hints in queries you must first specify the use of hints on the server using the following procedure.

1. Open Start > Administrative Tools > Microsoft Dynamics AX Server Configuration Utility and select the Database Tuning tab.
2. Select Allow INDEX hints in queries and click OK.
3. A message box prompting you to restart the AOS service appears. Click Yes to restart the AOS service. Index hints won’t be enabled until the service is restarted.

Note

A wrong index hint can have a big performance impact. Index hints should only be applied to SQL statements that do not have dynamic where clauses or order by clauses, and where the effect of the hint can be verified.

When an index hint in a select statement refers to a non-clustered index and the WHERE clause contains only the fields that are found in a clustered index on the same table, the clustered index is used instead of the index specified in the hint.

 

For example, if you run sp_helpindex InventTable in SQL Server Management Studio, you see that the InventTable has a clustered index on the DataAreaId and ItemId columns and a non-clustered index on the DataAreaId, ItemGroupId, and ItemId columns.

 

Index name	Description	Key columns
I_175ITEMIDX	Clustered, unique, primary key located on PRIMARY	DATAAREAID, ITEMID
I_175GROUPITEMIDX	Nonclustered located on PRIMARY	DATAAREAID, ITEMGROUPID, ITEMID

 

In the following code the clustered index will be used instead of the non-clustered index specified in the index hint.

 

static void IndexHint(Args _args) {     InventTable inv;     ;     select * from inv index hint GroupItemIdx         where inv.ItemId == ‘B-R14’; }

Presence Information in DAX

Microsoft Dynamics AX 2009 now has integration to Office Communicator. This allows a user to see presence information of a contact & contact him instantly. Office Communicator is required for this purpose.

We can enable the presence information in a AX Form Control of type StringEdit by setting up the following properties:

1. PresenceIndicatorAllowed: If set to Yes then it will show the presence indicator sign in same control.
2. PresenceDataSource: Data source which will have the PresenceDataField information.
3. PresenceDataField: It should be the field for address book identification such as DirPartyId, ContactPersonId etc. These are the extended data types.

These extended data types are having the properties to call the Presence Class (DirPresenceInfo) & the respective method. These Properties are PresenceClass & PresenceMethod.

For ex: The DirPartyId field the Presence Class property is set to DirPresenceClass & the PresenceMethod property is set to "partyInfo".

The contact information should be available in the respective table. For ex: for an employee the contact information should be available & the Communicator Sign In address check box should be checked to use the feature.

AOSAuthorization property on tables

This property is part of Tables Permission Framework (TPF). The Table Permissions Framework (TPF) enables administrators to add an additional level of security to tables that store sensitive data. TPF adds table-level security that verifies access rights no matter the origin of the request.

To enable TPF, an administrator specifies a value for the AOSAuthorizationProperty on a specific table in the AOT. The AOSAuthorizationProperty authorizes Create, Read, Update, and Delete operations. For some tables, it is important to authorize all operations because the data is sensitive. For other tables, you might find it suitable to specify a subset of operations, such as Create, Update, and Delete. In the case when you have specified a subset, the AOS authorizes the Create, Update, and Delete operations, but allows users to perform View operations if they have access to Microsoft Dynamics AX.

TPF can be enabled on any table in the Microsoft Dynamics AX database. For the sake of time and efficiency, however, administrators assign TPF to tables that are considered to be sensitive or to be of critical business value.

For example, consider the following scenario:

1. Microsoft Dynamics AX and allows users to access data by using the Microsoft Dynamics AX client, Enterprise Portal, the Application Integration Framework, and a third-party application that connects to Microsoft Dynamics AX by using the .NET Business Connector.
2. The administrator configured a Microsoft Dynamics AX user group called Senior Accountants, and members of this group have access to sensitive data about financial information and trade secrets. One of the database tables that stores this sensitive information is called FinancialResults. This table was added as part of a customization done by a partner after Microsoft Dynamics AX was installed.
3. In the Application Object Tree (AOT), the administrator configures the FinancialResults table so that the Application Object Server (AOS) must authorize all operations for that table. The administrator specifies the value CreateReadUpdateDelete for the AOSAuthorizationProperty.
4. Soon thereafter, a malicious user discovers a vulnerability in Contoso’s third-party application that connects to Microsoft Dynamics AX by using the .NET Business Connector. The malicious user connects to the database as a member of the CRM_users group and attempts to read the data in the FinancialResults table.
5. Before allowing the read operation, the AOS checks to see if the user is a member of the Senior Leadership user group and if members of the group have permission to read the data. The malicious user is not a member of the Senior Leadership group, so the AOS denies the read operation.

You can change or add TPF for a table, but its is recommended that you perform TPF changes in a test environment so that you can study the impact of TPF changes on user groups that access that table.

To enable TPF on database table:

1. In the AOT, expand Data Dictionary > Tables.
2. Right-click a table, and then click Properties.
3. Click AOSAuthorizationProperty and select a new value by using the drop-down list.
4. Click Save All.

If you added TPF to a table, you might need to specify or expand permissions for user groups that access that table. You can view which objects access a table by using the Used-by command in the AOT:

1. In the AOT, expand Data Dictionary > Tables.
2. Right-click a table, and then click Add-ins > Cross-reference > Update.
3. Right-click a table, and then click Add-ins > Cross-reference > Used by. The Used by form is displayed. This form shows all objects that access the selected table and what permissions (the Reference column) are required when accessing the table. You might need to adjust user group permissions if you set tighter restrictions on a table.

Iterators Vs. Enumerators

We can traverse our collections by using either an enumerator or an iterator.But there is no clarity why sometimes iterator fails while on other occasions it is flawless.Simply what we do is that just replace iterator with the enumerator.

First theoretically what happens behind the scenes is as follows:

When collection classes were first introduced in DAX, the iterator was the only option.But because of a few unwarranted drawbacks that appear as hard-to-find errors, enumerators were added, and iterators were kept for the backward compatibility. Just see the below listed code snippet

List list   = new List(Types::Integer);
ListIterator  iterator;
ListEnumerator  enumerator;
;
//Populate List
…..
…..

//Traverse using an iterator.
iterator = new ListIterator(list);

while(Iterator.more())
{
print iterator.value());
iterator.next();
}

//Traverse using an enumerator
enumerator = list.getEnumerator();

while(enumerator.moveNext())
{
print enumerator.current();
}

The 1^st difference is the way iterator and enumerator instances are created.For the iterator,you call new,and for the enumerator,you get an instance from the collection class by calling the getEnumerator method.

In most cases, both approaches will work equally well. However, when the collection class resides on the opposite tier from the tier on which it is traversed,the situation is quite different.

For example, if the collection resides on the client tier and is traversed on the server tier, the iterator approach fails because the iterator does not support cross-tier referencing.

The enumerator does not support cross-referencing either, but it doesn’t have to because it is instantiated on the same tier as the collection class. Traversing on the server tier using the client tier enumerator is quite network intensive, but the result is logically correct because some code is marked as “Called From”, meaning that it can run on either tier,depending on where it is called from. You could have broken logic if you use iterators, even if you test one execution path.In many cases, hard-to-track bugs such as this surface only when an operation is executed in batch mode.

The 2^nd difference is the way traversing happens which is another potential threat as the onus lies on the developer to ensure that he moves the pointer by using .next() method else the code can land into endless loop.So again enumerator is clear winner here

But there is still one scenario while iterator holds edge over enumerator, if you want to delete/insert items from list.See the code snippet below:

List   list = new List(Types::Integer);
ListIterator iterator;
;

list.addEnd(100);
list.addEnd(200);
list.addEnd(300);

iterator = new  ListIterator(list);
while(iterator.more())
{
if(iterator.value() == 200)
  iterator.delete();
iterator.next();
}
print list.toString();   //{100,300}
pause;
}

Using and Interacting with CLR Data Types

It is possible to use CLR data types inside AX. The requirement arises when we use CLR classes and framework inside AX for any of our use.

One thing to note regarding them is marshaling. When you use CLR data types, you cannot directly use them in AX constructs you need to marshal them to AX data type and use them.

Below is an example of once such job:

static void xppCLRDataTypeInterOp(Args _args)

{

    System.String[] retString; //.Net Data Type

    System.Int32    netI;      //.Net Data Type

    int len;

    int i;

   

    //Test function to initialize and return a .Net string array

    System.String [] getStringArray()

    {

        System.String[] myString;

        ;

 

        myString = new System.String[2]();

        myString.SetValue("Test", 0);

        myString.SetValue("Hello", 1);

        return myString;

 

    }

    ;

 

    //Get and store the array in a .Net Data Type string array

    retString = getStringArray();

 

    if (retString)

    {

        //Get the length and store it in .Net Int32

        netI = retString.get_Length();

       

        //Marshal .Net to X++

        len = netI;

       

        //Loop and display the string

        for( i = 0 ; i < len ; i++)

        {

            info(retString.GetValue(i));

        }

    }

}

Using Methods Inside Query Range

This article may be a little late. I had discovered this long time back for one of the usages but writing it down now.

In class SysQueryRangeUtil you will find many static methods that can be used in a Range of a query.

Following are the methods that be used:

• currentBusinessRelationsAccount
• currentContactPersonId
• currentCustomerAccount
• currentEmployeeId
• currentSessionDate
• currentSessionDateTime
• currentStaticMasterPlan
• currentUserId
• currentVendorAccount
• dateRange
• day
• dayRange
• greaterThanDate
• lessThanDate
• monthRange
• yearRange

It is often useful to have these methods specially the methods involving dates. Think you have to set up a recurring batch job where you need to filter some records based on date criteria say all transactions having transactions today – 10. During such times these methods can be useful.

Here is one such example:

static void methodsInsideQueryRange(Args _args)
{
    Query                   q = new Query();
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    str     rangestr;
    ProjTable   p;
    ;

    rangestr = ‘((Responsible == currentEmployeeId()) && (Responsible != ""))’;
    qbds = q.addDataSource(tableNUm(ProjTable));
    qbds.addRange(fieldNum(ProjTable, Responsible)).value(rangestr);

    qr = new QueryRun(q);
    while (qr.next())
    {
        p = qr.getNo(1);
        info(p.ProjId);
    }

    info(q.dataSourceNo(1).toString());
}

This is how you use them in code. Now how is it useful in daily tasks?

Think of a scenario where you have 100’s of transactions posted daily and say you want to filter out those transactions that have been posted in last two months? How will you filter them?

Take all the transactions to excel and filter there?

Hold on there is a better way to do it in AX 2009.

On the required form press Ctrl + G and then in the filter use these methods:

Ex: (monthRange(-2,0)). This means we need to filter all those transactions starting from 2 months behind and till today. If we specify (monthRange(-2,2)), this means all the transactions between 2 months backward and 2 months forward.

Check  the screen shots below:

Here is a screen shot of Item transactions form before applying monthRange:

After we apply the monthRange method filter:

Generating Next Number Sequence in SQL

In my last article, I explained how to Generate RecIds in SQL Server.

In this article, I will provide a SQL Job to automatically generate next number sequence if a field is getting populated using Number sequences. I created this job recently for InventTransId.

So here is the job that will help you generate next number sequence in SQL. I have tried to put in as many comments as possible:

DECLARE @FORMAT AS NVARCHAR(20), @TMPFORMAT1 AS NVARCHAR(20), @TMPFORMAT2 AS NVARCHAR(20), @NEXTREC AS INT

DECLARE @FINALID AS NVARCHAR(20)

 

–FETCH THE NUMBERSEQUENCE FORMAT AND VALUES FOR INVENTTRANSID (Lot ID)

–NOTE THAT 582 is the Extended Type Id for InventTransId

SELECT @FORMAT =FORMAT, @NEXTREC =  NEXTREC FROM NUMBERSEQUENCETABLE

WITH(UPDLOCK, HOLDLOCK)

JOIN NUMBERSEQUENCEREFERENCE ON NUMBERSEQUENCEREFERENCE.NUMBERSEQUENCE = NUMBERSEQUENCETABLE.NUMBERSEQUENCE

AND NUMBERSEQUENCEREFERENCE.DATATYPEID = 582 AND NUMBERSEQUENCETABLE.DATAAREAID = ‘CEU’

 

–GET THE # CHARACTERS IN ONE VARIABLE AND FIXED CHARACTERS (IF ANY) IN ANOTHER

–EX: ######_068 WILL BE BROKEN INTO "######" AND "_068"

SET @TMPFORMAT1=SUBSTRING(@FORMAT, 1, LEN(@FORMAT)-CHARINDEX(‘#’,REVERSE(@FORMAT))+1)

SET @TMPFORMAT2=SUBSTRING(@FORMAT, LEN(@TMPFORMAT1)+1, LEN(@FORMAT)-LEN(@TMPFORMAT1))

 

–NOW REPLACE # WITH REQUIRED ZEROS AND THE NEXT RECORD NUMBER THEN APPEND THE FIXED CHARACTERS AND DISPLAY OUTPUT

SET @FINALID = (REPLICATE(‘0’, LEN(@TMPFORMAT1)-LEN(CAST(@NEXTREC AS NVARCHAR))) + CAST(@NEXTREC AS NVARCHAR)) + @TMPFORMAT2

SELECT @FINALID as INVENTTRANSID

Listing out Published SQL Servers in AX

Recently, I had a requirement where I had to populate list of SQL Servers in a table in AX and all this had to be done from within AX.

I couldn’t find any support directly in AX, so I decided to use .Net classes for this purpose.

Here is a job that can help you achieve the same.

 

static void getListOfSQLServers(Args _args)
{
    System.Data.Sql.SqlDataSourceEnumerator dataEnum;
    System.Data.DataTable                   dataTable;
    System.Data.DataRowCollection           collection;
    System.Data.DataRow                     row;
    System.Object                           serverObj, instanceObj;

    int sqlCount, i;
    str   serverName, instanceName;
    ;

    dataEnum = System.Data.Sql.SqlDataSourceEnumerator::get_Instance();
    dataTable = dataEnum.GetDataSources();
    collection = dataTable.get_Rows();
    sqlCount = collection.get_Count();

    for (i = 0; i < sqlCount; i++)
    {
        row = collection.get_Item(i);
        serverObj = row.get_Item("ServerName");
        instanceObj = row.get_Item("InstanceName");

        serverName = serverObj.ToString();
        instanceName = instanceObj.ToString();

        if (instanceName)
            info(strfmt("%1\\%2", serverName, instanceName));
        else
            info(serverName);
    }
}