Purpose:
The purpose of this document is to describe how Dynamics AX technically stores the data of virtual company accounts in database tables. Specifically, what are the tables involved in storing the relationships between a virtual company account and its company accounts and table collections?
Assumptions:
Reader has the functional knowledge of how virtual company accounts work in Dynamics AX.
Description:
Virtual company accounts are maintained at System administration > Setup > Virtual company accounts.
Schema:
The following system tables are used to store the mappings of virtual company account, company accounts and its table collections. Note you can’t find them in the AOT. As they are system tables.
Where,
DataArea:
The DataArea table contains a list of companies that have been created in the database.
VirtualDataAreaList:
The VirtualDataAreaList table stores the mapping between real companies and virtual companies.
TableCollectionList:
The TableCollectionList table stores the mapping between table collections and virtual companies.
UtilElements:
The UtilElements table contains the application that is shown in the AOT.
Since the data model is clear now, the following code can be used to lookup the virtual data area id for a given company id:
// Developed on 28 Dec 2015 by Muhammad Anas Khan
// Blog: dynamicsaxinsight.wordpress.com
// LinkedIn: pk.linkedin.com/in/muhammadanaskhan
// Description: Ability to confirm purchase order
public DataAreaId getVirtualDataArea(DataAreaId _companyId)
{
#define.Vendor("Vendors")
VirtualDataAreaList dataAreaList;
TableCollectionList tableCollectionList;
DataAreaId virtualdataArea;
select ID, VirtualDataArea from dataAreaList
join VirtualDataArea, TableCollection from tableCollectionList
where dataAreaList.id == _companyId
&& tableCollectionList.virtualDataArea == dataAreaList.virtualDataArea
&& tableCollectionList.tableCollection == #Vendor;
virtualdataArea = tableCollectionList.virtualDataArea;
return virtualdataArea;
}