Number of records in D365

Number of records in D365

In Dynamics AX2012 there used to be a development tool to quickly count the number of records in all DAX tables: Ctrl-Shift-W, then Tools / Number of records:
Number of records Ax2012

This can be an interesting figure for data migration, measurement of the transaction volume, performance evaluation etc. A similar inquiry in Dynamics 365 for Finance / SCM can be executed in a multitude of ways:

The new grid

At the moment of this publication the grid is still in a preview and slightly defunct, but you can add a grouping into any D365 list and it is going to draw the number of rows inside: right-click a column, say Group by this column and get a counter in the group header:
Count by group in the New grid
The calculation is slow as all records must be loaded from the server into the form data source first. On 10k+ records this method becomes slow to the grade of full impracticability.

Export to Excel

This method works faster than any Data management entity export: press Ctrl-Shift-M to select all records (including invisible / not loaded), then Office button / Export to excel. The success message already gives a clear indication of the total of all records exported: “We finished your export of 6265 rows. The full export took 1.6 minutes.
Export to Excel
This works a way faster, but not the fastest.

Workspace tile

On a screen of your choice, apply a query if needed, then use Options / Add to workspace, then ConfigureShow count on the tile, then check the destination workspace:
Tile counter
This works fast and even provides a drill-down capability, but not techy enough.

Get entity record count

Navigate to Common / Common / Office integration / Excel workbook designer. This little known cockpit let you make magic self-refreshing Excels you can give out to the users. The tailored Excel templates have pre-configured columns and a Microsoft Dynamics Office Add-in conditioned to the current environment. Now look for the entity of your interest and press the sleek button Get entity record count:
Get entity record count
This is a super fast, smart and satisfying method, handicapped by the fact, that not every table in the system is an entity, but it comes the closest to the good old Number of records list.

What Item requirements can’t do

What Item requirements can’t do

This is kind of a recurring question: people have illusions about the Item requirements sales order type in Dynamics 365 for Finance and SCM.

First of all, in connection with a Fixed-fee project in D365 you may only open an Item requirement sales order. In one of the D365 versions they shortly enabled the classic Sales order but then quickly closed the ‘exploit’ (“Sales Orders may not be created for project type Fixed-price“), because a “classic” delivery note (en-us: Picking list) does not produce project transactions; the sales order would forever remain in the status Delivered and would never impact the project revenue. A project Sales order is only available on T&M projects where it can be billed directly.

Project management and accounting/Item tasks/Item requirements

Item requirements form

Pros and contras

A project sales order of the Item requirement type cannot be updated with an ordinary Delivery note but the special “Project – Delivery note” only. Then it produces a cost transaction to drive the project revenue recognition. Once delivered, the line immediately becomes “Invoiced” as if the sales price was zero. The support of Warehouse management had been added some time ago, and the shipment knows exactly which type of the delivery note to trigger upon confirmation of the load. This is what can be considered ‘good’ about the sales order type.

The list of the Item requirement sales order disadvantages is much longer:

  1. One cannot confirm an Item requirement sales order, I seriously can’t comprehend the reason. Order confirmations are not supported, you cannot liaise the delivery to your customer in a structured way.
  2. The user interface differs radically from the normal sales order, which is not well received by users and business owners.
  3. One cannot activate the Direct delivery mode at the IR sales order line level. Instead, you use the classic marking and open a purchase order through the Create purchase order button. Then you have to manually update the purchase order, choosing the customer’s address from the global address book and propagating it to the lines.
  4. Subsequently, a PO delivery note cannot update the sales order unless you affirm the “Consume items for a project…” prompt on posting. This however requires a 1:1 relation between a PO and SO which is not always given if the MRP planning is used: one purchase order may cover both project and non-project demands, and won’t become a Project purchase order. In such a case, the IR sales order delivery note must be posted manually.
  5. Similarly, Intercompany order chains are not supported.
  6. In the Warehouse management module, item requirements should not be consolidated in a load with “classic” sales orders as they may not be on the same delivery note. This is a subtle, nasty trap: in standard D365, a delivery note is posted per load = truck, not per shipment = order. There is an attribute, a flavour of the load whether it’s a ‘project’ load or a ‘normal’ load. In a mix of different sales order types this flavour cannot be right of course (see \Classes\WHSSalesPackingSlipPost): either one shipment or another is going to be posted wrongly or throw an error. Post the 2 delivery notes separately from the sales order screen, neither the load nor the shipment Delivery note button is going to work.
  7. Item requirements do not support multiple funding sources, i.e. multiple billing customers or changes of the customer account in the project contract. And no, the latest feature Allow sales orders for projects with multiple funding sources does not apply to the fixed-price project, try it: “Item requirement may not be created for a project associated to a contract with multiple funding sources“.
    The validation is dumb stupid, the 100% allocation in the funding rules to one or another funding source doesn’t bring any relief. You make this mistake only once; you may still gain access to your IR sales order through the normal sales list page or the Item tasks/Sales orders button, but processing the delivery of this sales order poses a serious challenge (unless you use Warehouse management). And no, you can’t remove the 2nd funding source anymore: Catch-22. You better delete the sales order and start all over.
  8. For the migration of historical orders, there is a special entity “Project item requirements“, and it is weak. It works at the line level and creates one SO header per project, i.e. you have no control over the Sales ID. Most of the sales order header attributes – delivery reason, sales taker, sales responsible etc. – are not available.
  9. Last but not least, non-stockable services are not updated properly by the “Project – Delivery note” and such a line remains in the Delivered status forever, the sales order as a whole will never be “Invoiced”.
    Update 28.07.2021: the issue 9 will go away if you activate the feature Enable creation of item requirement for non-stocked items: “Enabling this and turning on the ‘Create item requirement’ parameter will create an item requirement when creating a PO with non-stocked items.”

What can we do?

Not much:

  • Live with it.
  • Complain about it, but Microsoft has been aware for a long time.
  • Re-enable “classic”project sales orders on Fixed fee projects and programmatically enforce the “Item requirement-style” delivery note on those sales orders. This is not an easy development, but feasible in D365.

Sales order totals

Sales order totals

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
  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: