How to read records from different company accounts with one select statement

A Dynamics Ax installation can hold information for different company accounts, all in one database. If you wanna share information between different company accounts, you can use Table Collections and Virtual Companies. Maybe you can use the InterCompany module of Ax, in order to automate the information exchange between different company accounts in your setup.

But even if the data is not shared and you don't use InterCompany, you can retrieve data from different company accounts with a simple select statement. In Ax 2009, you can use the crossCompany keyword for this.
All you have to do is add this keyword to your select statement.
Code sample 1: Select records from all company accounts in the database.

static void CrossCompanyTest(Args _args)
{  CustTable CustTable;
;

while select crossCompany CustTable
order by AccountNum asc
{
info(strfmt('%3 - %1 %2',CustTable.AccountNum,CustTable.Name,CustTable.dataAreaId));
}
}

In code sample 2, we limit the company accounts we want to read from.
For this, we can add a container after the crossCompany keyword, that limits the company accounts. The container holds the different wanted company identifiers.

static void CrossCompanyTest(Args _args)
{  CustTable CustTable;
container companyAccountcontainer = ['ca1','ca2'];
;
while select crossCompany : companyAccountcontainer CustTable
order by AccountNum asc
{
info(strfmt('%3 - %1 %2',CustTable.AccountNum,CustTable.Name,CustTable.dataAreaId));
}
}

Note that you cannot use the crossCompany keyword with data modification commands like insert_recordset or delete_from. But with a little workaround and the help of the changeCompany statement, you can still use the crossCompany keyword. Together, they can perform the data updates. More of that later on...

Happy New Year!

Hi there,

A Happy New Year to all my blog readers!
May 2011 bring you, your family and your friends good health and prosperity. I hope all your wishes may come true in this new year.
Let's make it a year to remember!

Lots of new Ax stuff coming up, with the Technical Conference just ahead of us and a new release of Dynamics Ax planned later in 2011. You can expect more blog posts as well over here, so stay tuned!

How to read group membership information from Active Directory

Last post I talked about reading from Active Directory. This post takes it a little further. We gonna read the user list for a specific group from a given domain.

With this group membership list, you can for example

• add new users to Dynamics Ax according to an AD group (called 'Ax Users' fe)


• add users from the Administrators group (AD) to the 'Admin' group in Dynamics Ax
  

Plenty of applications, for which you can use following job as a base. Again we'll use CLR Interop with the System.DirectoryServices namespace.

static void ReadMoreFromAD(Args _args)

{  System.DirectoryServices.DirectorySearcher DirectorySearcher;

   System.DirectoryServices.SearchScope SearchScope;

   System.DirectoryServices.DirectoryEntry DirectoryEntry;

   System.DirectoryServices.SearchResultCollection SearchResultCollection;

   System.DirectoryServices.SearchResult SearchResult;

   System.DirectoryServices.PropertyCollection PropertyCollection;

   System.DirectoryServices.PropertyValueCollection PropertyValueCollection;



   str    networkDomain="yourdomainnamehere";

   str    prefix = 'LDAP://';

   int    totalCount;

   int    counter;



   str    groupName="Administrators";

   str    groupCrit;



   int    usercount;

   int    ucount;

   str    userinfo;


   ;

   try

   {

      DirectoryEntry = new System.DirectoryServices.DirectoryEntry(prefix + networkDomain);

      SearchScope =CLRInterop::parseClrEnum('System.DirectoryServices.SearchScope', 'Subtree');



      DirectorySearcher = new System.DirectoryServices.DirectorySearcher(DirectoryEntry);

      DirectorySearcher.set_SearchScope(searchScope);

      groupCrit = strfmt('(samaccountname=%1)', groupName) ;

      DirectorySearcher.set_Filter(strfmt('(&(objectClass=group)%1)', groupCrit));



      SearchResultCollection = DirectorySearcher.FindAll();

      totalCount = SearchResultCollection.get_Count();

        for (counter=0; counter < totalcount; counter++)
        {
            SearchResult = SearchResultCollection.get_Item(counter);
            DirectoryEntry = SearchResult.GetDirectoryEntry();
            if (DirectoryEntry)
            {
                PropertyCollection = DirectoryEntry.get_Properties();
                if (PropertyCollection)
                {
                    PropertyValueCollection = propertyCollection.get_Item('member');
                    usercount = PropertyValueCollection.get_Count();

                    for (ucount=0; ucount < usercount; ucount++)
                    {
                        userinfo = PropertyValueCollection.get_Item(ucount);
                        if(userinfo)
                            info(userinfo);
                    }
                 }
             }
        }
       
        DirectorySearcher.Dispose();
        SearchResultCollection.Dispose();
       
    } catch (Exception::CLRError)
    {
        error("Error reading AD");
        return;
    }
}

How to read in Active Directory

Dynamics Ax relies on Active Directory for user authentication. And thanx to CLR Interop, you too can use Active Directory and all it's objects and properties from within Ax. You can use AD for what it is designed for: a central storage location for application data.


But how does one get to read information from the AD? In following code snippet, I'll show you how to collect a list of all users from a specific domain, with some basic information about those users.
For this, we'll use the System.DirectoryServices namespace, an easy way of getting access to Active Directory from managed code.

In order for your code to work, don't forget to edit the networkDomain variable!

static void ReadFromAD(Args _args)
{
System.DirectoryServices.DirectorySearcher DirectorySearcher;
System.DirectoryServices.SearchScope SearchScope;
System.DirectoryServices.DirectoryEntry DirectoryEntry;

System.DirectoryServices.SearchResultCollection SearchResultCollection;
System.DirectoryServices.SearchResult SearchResult;

System.DirectoryServices.PropertyCollection PropertyCollection;
System.DirectoryServices.PropertyValueCollection PropertyValueCollection;

str networkDomain="yourdomainhere.com";
str prefix = 'LDAP://';

int totalCount;
int counter;

str mysamaccountname;
str myusername;
;

try
{
DirectoryEntry = new System.DirectoryServices.DirectoryEntry(prefix + networkDomain);
SearchScope = CLRInterop::parseClrEnum('System.DirectoryServices.SearchScope', 'Subtree');

DirectorySearcher = new System.DirectoryServices.DirectorySearcher(DirectoryEntry);
DirectorySearcher.set_SearchScope(searchScope);
DirectorySearcher.set_Filter(strfmt('(&(objectClass=user))'));

SearchResultCollection = DirectorySearcher.FindAll();

totalCount = SearchResultCollection.get_Count();
for (counter=0; counter < totalcount; counter++)
{
SearchResult = SearchResultCollection.get_Item(counter);
DirectoryEntry = SearchResult.GetDirectoryEntry();

if (DirectoryEntry)
{
PropertyCollection = DirectoryEntry.get_Properties();

if (PropertyCollection)
{
PropertyValueCollection = PropertyCollection.get_Item('samaccountname');
mysamaccountname=PropertyValueCollection.get_Value();

PropertyValueCollection = PropertyCollection.get_Item('name');
myusername=PropertyValueCollection.get_Value();

info(strfmt('%1 - %2',mysamaccountname,myusername));
}
}
}

DirectorySearcher.Dispose();
SearchResultCollection.Dispose();
}
catch (Exception::CLRError)
{
error("Error reading AD");
return;
}

}