Wednesday, 14 October 2015

CompanyMask for Data Sharing Between Tenats in Acumatica

Hi All,

CompanyMask – is a special column that stores binary mask that defines where this record should be visible and where you can update this record. 
Acumatica stores data in database separated by different tenants. All tenants are isolated, but you have some options how you can share data between them. To do it, your database and table should meet some conditions:
  1. Your table should have CompanyID and CompanyMask columns. Without CompanyMask data will be completely separated with no way to read it from other tenant from code.
  2. CompanyMask should be configured to allow other tenants select and update this record. Mask contains two flags for all companies. First flag –  s it possible to select this record from particular company or not. Second flag – is it possible to update this record from particular company or not. So as more companies you have in database, as longer will be a company mask value.
  3. Companyes should be arranged in proper tries. In company table we have ParentID column, that stores hierarchy of companies. Only child companies can see records of parents companies. Parent companies not able to select date of child companies. Also companies from different chains not able to so get data of each other.
Lets discuss, how does it works base on example.

Initial setup of database with 2 companies

Table Company
CompanyID
Name
ParentCompanyID
IsReadOnly
CompanyKey
1
System

1

2
Demo
1
0
Demo
3
Shared
1
1

4
Production
3
0
Production
5
Testing
3
0
Testing

Table Users (Default Company mask is 0x0000)
CompanyID
Username
Password
PasswordChangeOnNexLogin
CompanyMask
1
Admin
Setup
1
0xAAAA







We are going to login into the company 2 for the first time. We don't have users in the second company but there is a user in the first company. Also we have company 1 as a parent for the second company.
In this case, when you log into the second company, system will select data from companies 1 and 2, so system will get record from the first company. We have flag PasswordChangeOnNexLogin, so user should change password on next login.
What will happen when we will change the password?

CompanyMask
Let's take a look on companymask column. Companymask is a binary mask that represents visibility and updateability of current record in different companies.
All companies in this mask have 2 bits: visibility and updateability.
Visibility - means that this record is visible in related company
Updateability - means that this record is updatable from related company.
Acumatica Company Masks
In this example record is visible in all companies but is not updateable.

Let’s return to the record from Users table. This record is visible but not updatable from the second company.
In this case, when user has changed password, system will create a copy of this record and place it into the second company (the company you are logged into). New record will be created with default company mask (it was 0x0000).
Table Users (Default Company mask is 0x0000)
CompanyID
Username
Password
PasswordChangeOnNexLogin
CompanyMask
1
Admin
setup
1
0xAAAA
2
Admin
123
0
0x0000
After that system will update this new record to make it visible and updateable in current company. New mask will be 0x0C00 (HEX) = 0000 1100 0000 0000 (BIN), so new record will be visible only in the second company.
System will also change visibility of parent record, so it will not be visible in the child company any more. New mask for record from the first company will be 0xA2AA = 1010 0010 1010 1010
CompanyID
Username
Password
PasswordChangeOnNexLogin
CompanyMask
1
Admin
setup
1
0xA2AA
2
Admin
123
0
0x0C00

  
Chain of companies
If we have a chain of companies (companies 1, 3, 4 for example) then system will select data from all companies in the chain. But you should pay attention to the company mask, because some records can be invisible in destination company.
CompanyID
Username
Password
PasswordChangeOnNexLogin
CompanyMask
1
Admin
setup
1
0xA2AA
3
Admin
123
0
0x0C00
3
Bob
123
0
0x3000
3
Alise
123
0
0xAAAA
4
Admin
12345
0
0xC000
In this example we will get users alise with password 123 and admin with password 12345 from the forth company.

Starting from version 3.0 you can't login into company if it has at least one child company.

Sharing
Sometimes you will need to share one record between two or more companies. For this task you will need an updatable flag of the company mask. 
CompanyID
Username
Password
PasswordChangeOnNexLogin
CompanyMask
3
Admin
123
0
0xFFFF
4
Bob
123
0
0xAAAA
5
Alise
123
0
0xAAAA
In this example you will see 2 users (admin, bob) from company 4 and also 2 users (admin, alise) from company 5.
“admin” is shared between companies that have company 3 as a parent, and admin has the same password in all child companies.
If you login into company 4 and update users password to 12345 it will affect company 5 too.
CompanyID
Username
Password
PasswordChangeOnNexLogin
CompanyMask
3
Admin
12345
0
0xFFFF
4
Bob
12345
0
0xAAAA
5
Alise
123
0
0xAAAA
so now admin has the same password in companies 4 and 5 too.

Note, that new records will be inserted into the system with default mask. If you want to share new records, then you should change default mask to 0xFFFF.

Tables modes
All tables in acumatica database can be in 3 different modes:
1)Separate - Table does not contain CompanyMask column or default mask is 0x0000.
2)Split - Table contains CompanyMask column and default mask is 0xAAAA
3)Shared - Table contains CompanyMask column and default mask is 0xFFFF

You can use the following stored procedure to change table modes:
pp_splittable @tablename, @mode
@tablename - name of the table
@mode - 0 (Separate), 1(Split), 2(Shared)


As you understand now Acumatica uses sharing functionality to provide system data to all tenants inside one instance. All tables with system data that should be shared have CompanyMask column and it is configured for split mode.
During upgrade Acumatica just removes system company and inserts new one, thats available only because of storing system data separately. If you developing add-solution to acumatica, you also able to do the same and reserve one company for your system data.

Have a nice development!

No comments: