In the course of a Dynamics 365 for Finance / SCM (aka Operations aka Dynamics AX) implementation the sales department often asks for a quick sales order totals display, right in the sales order grid. A mature consultant instantly refuses with an argument a ‘display column’ is heavy on the database, the screen refresh is going to be slow, the calculation is oh so complex and so on.

Interestingly, for about a year there has been an out-of-the box solution by Microsoft. It apparently facilitates the two-way synchronisation through the Common Data Service (aka CDS, aka Microsoft Dataflex Pro) between Dynamics 365 SCM and Dynamics 365 for Sales (aka Microsoft CRM; I should have used my sarcasm tags).

The solution is deadlock-proof and safe in the terms of performance: a separate batch job populates two tables SalesOrderTotals and SalesOrderLineTotals related to the sales order headers and lines, respectively. Only the recently updated sales orders are considered. Contrary to the usual SO totals form, the total amount includes both the invoiced and backorder lines. 1000 sales orders ~5 lines each get processed in around 20 minutes in a production environment. The drawback is of course that the totals display is not immediate.

1. Run Sales and marketing / Periodic tasks / Calculate sales totals, specify how many days should the routine look back for the “touched” orders:
2. Check the table browser for results https://xxx.operations.dynamics.com/?mi=SysTableBrowser&tablename=SalesOrderTotals
   
3. Extend the sales order screen to OUTER-JOIN the Sales order totals table and display the totals,
   or use the joined table in query conditions in complex scenarios of the automatic confirmation, invoicing, etc: