JavaScript Required

We're sorry, but we doesn't work properly without JavaScript enabled.

Looking for an Expert Development Team? Take two weeks Trial! Try Now

Efficient Cross Company Data Sharing in D365

D365 Finance and operations

With greater number of transactions piling up in the system the inevitable challenge that come are a. speed of data fetching, b. calculations that are based on tables that have enormous data in them c. challenges get all the more serious when you plan to send the end result as an oData payload result.

Imagine a condition when you are creating a report of payment buckets of customers (customer ageing) and you have to link CUSTTABLE with CUSTTRANS. Conditions worsens when you have for each customer itself a huge number of transactions. And then you need to pull customer results from just not from one Legal entity, but from multiple companies.

Your query ideally looks like this:

Select CrossCompany sum(AmountMST) from custTrans exists join custTable where custTable.AccountNum ==custTrans.AccountNum && custTrans.TransDate >= <_some_Date_Range>

Also you can specify from which companies you want to query the data:

Container companyRange = [‘LE1’, ‘LE2’, ‘LE3’]; Select CrossCompany:companyRange sum(AmountMST) from custTrans exists join custTable where custTable.AccountNum ==custTrans.AccountNum && custTrans.TransDate >= <_some_Date_Range>

In either case when the number of transactions rise very high, inevitably the compiler is sure to fall under the clasps of performance issues.

Luckily there are efficient ways to handle such kind of situations.

Using views

Views can offer you a pre-calculated tool of data fetching/mining that involves a high degree of SQL performance and concepts. Essentially when you build your solution( and provided if you have kept your project area of VS → Synchronize database on build → true), the view gets reflected/created in SQL too.

Steps

Step 1:

Save the Project. Do a build.

Making the ‘All Cross-company’ makes it to fetch the data from across all the companies.

Step 2:

This will create the view at the backend. Refresh your SQL Server → the SQL_DB (ideally it’s located as AxDB) → open the views node → you can see the view being created as:

Create or alter view TestView1 As Select T1.AccountNum as Accountnum, T1.DataAreaId as LegalEntity, SUM(T1.AmountMST) as Balance from CustTrans T1 where exists(select T2. AccountNum from CustTable T2 where T2.AccountNum == T1.AccountNum) group by T1.AccountNum, Order by T1.DataAreaId

Please note that:

Issues:

Views do have certain drawback too while considering the cross company share

Using entities as an alternate procedure

A data entity is a tantamount of what it used to be as the DIXF in Ax 2012, though with much larger and far reaching offerings:

Entities to support cross company data

Step 1

Step 2

Select/Enter/review your table → staging table → target entity mapping. You can specify the various labels of the fields you will be using. You can also tick on/off any field in order to enable/disable its availability. You can also select if a particular field is mandatory/not.

Also this step helps to select/enter your own custom help texts.

When you Click finish: it will create the entity.

A lot many things you need to change/alter after this.

Step 3

Note the ‘Primary company context’. By default it is set as: DataAreaId. When you say this, it by default restricts the data to be exposed as per company only.

TestEntity 1 ent1; While select * from ent1 { info(ent1.AccountNum); } This returns data from the logged on company only. While select: crossCompany from ent1 { info(ent1.AccountNum); }

This returns data from all the companies.

If I am at ‘USMF’ company, and I order this entity from the browser: https://yourD365Client.com/data/TestEntity1, I will get the following JSON response:

“Values”: [ { “DataAreaId”: “USMF”, “AccountNum”: “Cust1”, “Balance”: 122 } , { “DataAreaId”: “USMF”, “AccountNum”: “Cust2”, “Balance”: 150 } , { “DataAreaId”: “USMF”, “AccountNum”: “Cust3”, “Balance”: 180 } ]

Data to share as cross company:

Step 1:

In your underlying query, you need insert a new field: DataAreaId

Step 2:

Add the same in the view. Rename the field as ‘LegalEntity’. Build the visual studio project → in order to reproduce the view in SQL.

Step 3:

In your entity, remove the ‘PrimaryCompanyContext’. Add the newly created view in your entity. Build/synch the project now.

Step :

Next time when you call the entity from the browser it will get:

“Values” : [ { “DataAreaId” : “USMF”, “AccountNum”: “Cust1”, “Balance”: 122 }, { “DataAreaId” : “USMF”, “AccountNum”: “Cust2”, “Balance”: 150 }, { “DataAreaId” : “USMF”, “AccountNum”: “Cust3”, “Balance”: 180 }, { “DataAreaId” : “INMF”, “AccountNum”: “Cust4”, “Balance”: 200 }, { “DataAreaId” : “DMF”, “AccountNum”: “Cust5”, “Balance”: 600 } ]

Conclusion

As we mention above discussed about cross company data sharing between companies in Dynamics 365 for finance and operations. These data sharing platform helps increase the skill levels for users and get the most of their business application, opening the door to development and automation for every user.

NSS Note

Some of our clients

team