Table collections in AX 2012

Dynamics Ax Table collections & Virtual company

If using more than one company, sometimes, it will be useful to share data from tables with general information, like, tables storing data like zip codes and country codes. The most basic way to share data from a table among all companies is to set the table property SaveDataPerCompany to No.
This will merge data for the table and make the data accessible from all companies. In practice, the kernel will delete the system field dataAreaId for the table.

Another way to sharing data without having to change any settings in the existing tables is by using Table Collections. A table collection is just a template for tables to be shared by any number of companies and Table collections shared using a virtual company.

The form SysDataAreaVirtual is used to define virtual companies. Virtual company is a term used for sharing table collections among a set of companies and does not exist in reality, and therefore, you cannot switch to a virtual company like any normal company.

When using TC for setup tables (like customer groups), setting up a VC will be easy. But, if you are going to share data from main tables (like the inventory table), you should do more investigation as you cannot only share the table InventTable. You must include all tables which are related to InventTable.

Note: Before creating a VC you should export data for the tables used in the table collection, as existing data will be deleted from these tables when added to a virtual company.

Virtual Company setup:

Step 1: Create Table Collection:
Decide which tables you want to share and create a Table collection for these functionally related tables. For example; if you want to share 'Global Address Book' across companies then you can utilize the existing table collection "DirPartyCollection".

To create a table collection, go to AOT\Data Dictionary\Table Collections and on right click select "New Table Collection", then just drag your required tables in this collection.

Step 2: Create Virtual Company, configure/attach normal companies and table collection:
Create a virtual company that will hold the shared data for normal companies.
Note: Before doing the below steps, make sure you are the Ax administrator and the only user online.

1.       Go to Administration > Setup > Virtual company accounts, and create a virtual company.

2.      Decide which companies needs to share data and attach those normal companies with this virtual company.

3.      Attach the table collection with this virtual company and save form.

Your Ax client will re-start and you are done with setting up the virtual company account.

Now, when you have virtual company in place, all new data will be saved in this virtual company. Only companies attached to the virtual company can use this shared data. All other companies which are not attached will work normally, these companies will continue to read/write data as per company bases.

How to move existing data to virtual company?
When you setup a new virtual company, Ax does not move data automatically from normal company to virtual company. This is done by system administrator manually.

There are many ways to do this data move, let’s see only two such approaches here.
Approach 1: Ax Import / Export
This is standard Ax approach.

1.        Manually export existing normal company data from Ax.

2.        Remove duplicate records from this exported data set.

3.        Delete exported data from normal companies.

4.        Import the exported data back in Ax, while logged into one of the participating companies.

5.        Create records deleted in point 2 again in Ax using your logic. How you want to handle duplicate? For example, if you have customer 'Customer' in more than one normal company, what you want to do with this?

Approach 2: Direct SQL
Use this approach if you have good knowledge about SQL queries and Ax table structures/relationships. Below are steps you can follow.

1.        All Ax tables store data as per company unless otherwise specified. For this, Ax uses a special field called DataAreaId. In case of virtual company, it does not matter from which normal company you log-in, it is always the virtual company id which is stored in DataAreaId field of shared tables.

2.        Ax also assigns a unique 64bit number to each record in table. For this, Ax uses a special field called RecId. This RecId is unique in the table and is generated by Ax when you insert a new record in Ax. It is not related to DataAreaId / Company.

3.        For unique records between all participating normal companies, update the DataAreaId to the virtual company id.

For duplicate records, create them again in Ax using some Ax job or Ax import/export technique.