Dynamics 365 Commerce: How to use Excel Add-In with Edit and Audit to update Business Date at Commerce Statement Level

Please see Documentation on the Edit and Audit feature, which includes installing the Microsoft Dynamics Office Add In

Edit and audit cash and carry and cash management transactions - Commerce | Dynamics 365 | Microsoft Learn

In this example, I have Tricklefeed enabled and the transactions are in a closed fiscal period.

If you manually invoice a sales order with a date different from the Business date, you will also need to use Edit and Audit to update the Business Date to match the date used when manually invoicing the Sales order.

Note:  I have seen instances where extensions need the Date field in the Excel file also updated to the same date as the Business Date.  The statement will post with only the Business Date updated, but this is something to be aware of.

I have used this process to update invalid Customer accounts and to Void transactions.

First Calculate Transactional statement for a period that is closed.

When in the Statement form, Select Office icon in the upper right corner

Select Edit cash and carry transactions

Click Download

Save the file and open

Click Enable editing

You may get a message to “Trust this add-in”

Sign in

Data will refresh

Transaction tab

Validation Status = None

**Note:  Before you can edit a transaction, the Validation Status must be set to none and published

On the Excel file you can select all and fill down.

Publish

You can then edit the field you want to change

In this case I am changing the Business date to a date within the current period

Update the first transaction, then select all lines and Fill Down

Select Sales transaction worksheet and update business date

Select Payment transactions worksheet and update business date

Select Aggregation worksheet and update business date.

**Note: All worksheets that have the business date available should be updated or you will continue to receive errors on a closed fiscal period or Inventory is closed.  Don't forget the Aggregation worksheet.

Click Publish

Go back into the Retail Statement, select Revalidate Transactions to set the Validation Status back to Validated.

You may also need to go into Aggregated transactions and select Re-generate aggregation data.

You should be able to post the statement

Please visit the following for other resources for troubleshooting statement posting and validation errors:

https://learn.microsoft.com/en-us/dynamics365/commerce/troubleshoot/fix-common-posting-issues#transaction-validation-failed

https://learn.microsoft.com/en-us/dynamics365/commerce/troubleshoot/fix-common-posting-issues#issues-during-order-creation

https://learn.microsoft.com/en-us/dynamics365/commerce/troubleshoot/fix-common-posting-issues#issues-during-customer-order-invoicing