Dealing with Crypto ENCRYPTION error in Dynamics 365 FINANCE and supply chain Tier 1 developer Machine

Recently I faced an issue related to crypto encryption on a DEV BOX (Tier 1) Cloud hosted machine.

Below is the full error message

Encryption error occured with exception: Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionException: Encryption error occured with exception: Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'EE19588CB6569148D75AB3096213505AEE312E85'. at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetCertificateFromLocalStore(X509FindType findType, String findValue) at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetFirstCertificateForId(String id) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) ---> Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'EE19588CB6569148D75AB3096213505AEE312E85'. at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetCertificateFromLocalStore(X509FindType findType, String findValue) at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetFirstCertificateForId(String id) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) --- End of inner exception stack trace --- at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.DecryptionInternal(Byte[] cipher, Boolean validateSignature, String purpose)

The error used to come on several forms like when editing customer address and customer groups. Upon initial research on internet, it looked like the certificates on the VM have expired and I did the rotate certificates from LCS but that did not helped.

Then we raise a case with Microsoft and we started tracing back recent changes done to environment and we encountered that we did a Database restore from another Tier 1 VM which had dual write mappings enabled on it.

Microsoft support team shared the below SQL script with us to clear the environment specific data so we ran the below script and then did IIS reset and restarted the VM. This solved the problem. Sharing it here in case it helps you. P.S –> Take your DB backups before running this script.

--Tidy up the batch server config from the previous environment
DELETE FROM SYSSERVERCONFIG

--Tidy up server sessions from the previous environment.
DELETE FROM SYSSERVERSESSIONS

--Tidy up printers from the previous environment
DELETE FROM SYSCORPNETPRINTERS

--Tidy up client sessions from the previous environment.
DELETE FROM SYSCLIENTSESSIONS

--Tidy up batch sessions from the previous environment.
DELETE FROM BATCHSERVERCONFIG

--Tidy up batch server to batch group relation table
DELETE FROM BATCHSERVERGROUP

--Set any waiting, executing, ready or canceling batches to withhold
UPDATE BatchJob
SET STATUS = 0
WHERE STATUS  IN (1,2,5,7)
GO

--Disable Change Data Capture / CDC from Microservices team as it isn't compatible on another server
IF(1=(SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()))
BEGIN
	EXEC sys.sp_cdc_disable_db
END

IF '$(EnvSku)' = 'prod'
BEGIN
  -- Batchjob History Cleanup from source environment
  BEGIN TRAN
  DELETE BATCHJOBHISTORY WHERE CREATEDDATETIME < GETDATE()
  COMMIT TRAN
  BEGIN TRAN
  DELETE BATCHHISTORY WHERE NOT EXISTS (SELECT RECID FROM BATCHJOBHISTORY JOB  WHERE JOB.RECID = BATCHJOBHISTORYID)
  COMMIT TRAN
  BEGIN TRAN
  DELETE BATCHCONSTRAINTSHISTORY WHERE NOT EXISTS (SELECT RECID FROM BATCHHISTORY WHERE BATCHHISTORY.RECID = BATCHCONSTRAINTSHISTORY.BATCHID)
  COMMIT TRAN 
  
  --Update SYSCONFIGURATION table 
  UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ( 'DATAAREAIDLITERAL', 'PARTITIONLITERAL ')
  --Tidy up printers from the previous environment
  UPDATE SYSGLOBALCONFIGURATION SET VALUE = 'SQLAZURE' WHERE NAME = 'BACKENDDB'

  --Tidy up printers from the previous environment
  UPDATE SYSGLOBALCONFIGURATION SET VALUE = 1 WHERE NAME = 'TEMPTABLEINAXDB' 
END

IF '$(EnvSku)' = 'sandbox'
BEGIN
  --disable all users except Admin, Microsoft account and Dynamics-alias users (they should go and re-enable as they see fit),
  DECLARE @sqlDisableUserCmd nvarchar(256) = 'UPDATE UserInfo
    SET ENABLE = 0
    WHERE ID <> ''Admin'' AND NETWORKALIAS not like ''%dynamics.com%'''

  IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'UserInfo' 
    AND COLUMN_NAME = 'IsMicrosoftAccount')
  BEGIN
    SET @sqlDisableUserCmd = @sqlDisableUserCmd + ' AND IsMicrosoftAccount <> 1'
  END

  exec sp_executesql @sqlDisableUserCmd

  --Proactively enable API Service Account.  This is optional account customer can create, and will not be enabled in the source.
  --Microsoft does not create this app user, but customers optionally can and they can choose which security role to give it.
  --Enabling it during database refresh ensures that their app user is able to run X++ cleanup scripts immediately after
  --the refresh is completed via Database Movement API from LCS.
  UPDATE UserInfo
  SET ENABLE = 1
  WHERE ID = 'DBMovementAPI'

  --Remove the SMTP server configuration to prevent sandbox from sending mails
  UPDATE SysEmailParameters
  SET SMTPRELAYSERVERNAME = '', MAILERNONINTERACTIVE = 'SMTP'

  --Blank out all email addresses - in the case someone adds back SMTP config this will prevent accidentally sending mails
  UPDATE LogisticsElectronicAddress
  SET LOCATOR = '' 
  WHERE Locator LIKE '%@%'
  
  --Remove all print management settings - there is email addresses stored in a container field here - again we want to prevent accidentally sending a vendor/customer a PO/SO email.
  DELETE FROM PrintMgmtSettings 

  DELETE FROM PrintMgmtDocInstance 
END

-- Change Maintenance Mode setting to disabled, in case it was enabled on the source environment
IF EXISTS (SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'SQLSYSTEMVARIABLES')
BEGIN
	IF EXISTS (SELECT * FROM SQLSYSTEMVARIABLES WHERE PARM = 'CONFIGURATIONMODE' AND VALUE = 1)
	BEGIN
		UPDATE SQLSYSTEMVARIABLES
		SET VALUE = 0
		WHERE PARM = 'CONFIGURATIONMODE'
	END
END

-- Clean up tables where data is encrypted.
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'SysEMailSMTPPassword')
BEGIN
	TRUNCATE TABLE SysEMailSMTPPassword
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'SYSOAUTHUSERTOKENS')
BEGIN
	TRUNCATE TABLE SYSOAUTHUSERTOKENS
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'B2BInvitationConfig')
BEGIN
	TRUNCATE TABLE B2BInvitationConfig
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'PersonnelIntegrationConfiguration')
BEGIN
	TRUNCATE TABLE PersonnelIntegrationConfiguration 
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'CatExternalCatalogProperties')
BEGIN
	TRUNCATE TABLE CatExternalCatalogProperties
END

--CDS Unlink Various Tables BEGIN
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectConfiguration')
BEGIN
  TRUNCATE TABLE DualWriteProjectConfiguration;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectFieldConfiguration')
BEGIN
  TRUNCATE TABLE DualWriteProjectFieldConfiguration;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteErrorLog')
BEGIN
  TRUNCATE TABLE DualWriteErrorLog;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectConfigurationStaging')
BEGIN
  TRUNCATE TABLE DualWriteProjectConfigurationStaging;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'BusinessEventsDefinition')
BEGIN
  TRUNCATE TABLE BusinessEventsDefinition;
END
--CDS Unlink Various Tables END


--Remove all attachment references as storage account is not copied. Approved by Tariq Bell.
UPDATE t1
SET t1.storageproviderid = 0
     , t1.accessinformation = ''
     , t1.modifiedby = 'NonProdRestore'
     , t1.modifieddatetime = getdate()
FROM docuvalue t1 
WHERE t1.storageproviderid = 1

-- RETAILTRANSACTIONSERVICEPROFILE
DECLARE @MIGRATIONVALUE NVARCHAR(50)
SET @MIGRATIONVALUE = N'https://MIGRATION_VALUE'

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'RETAILTRANSACTIONSERVICEPROFILE' 
  AND COLUMN_NAME = 'AzureResource')
BEGIN
  EXECUTE ('UPDATE dbo.[RETAILTRANSACTIONSERVICEPROFILE] SET ServiceHostUrl = ''' + @MIGRATIONVALUE + ''' , AzureResource = ''' + @MIGRATIONVALUE + ''' ')
END
ELSE
BEGIN
  EXECUTE ('UPDATE dbo.[RETAILTRANSACTIONSERVICEPROFILE] SET ServiceHostUrl = ''' + @MIGRATIONVALUE + '''  ')
END

-- RETAILCHANNELPROFILEPROPERTY
UPDATE 
  dbo.[RETAILCHANNELPROFILEPROPERTY]
SET 
  [VALUE] = N'https://MIGRATION_VALUE'
WHERE
  [VALUE] LIKE '%dynamics.com'


UPDATE 
  dbo.[RETAILCHANNELPROFILEPROPERTY]
SET 
  [VALUE] = N'https://MIGRATION_VALUE/Commerce'
WHERE
  [VALUE] LIKE '%dynamics.com/Commerce'


UPDATE 
  dbo.[RETAILCHANNELPROFILEPROPERTY]
SET 
  [VALUE] = N'https://MIGRATION_VALUE/MediaServer'
WHERE
  [VALUE] LIKE '%dynamics.com/MediaServer'


-- RETAILCONNDATABASEPROFILE (all rows)
UPDATE
  dbo.[RETAILCONNDATABASEPROFILE]
SET
  [CONNECTIONSTRING] = NULL

    -- RETAILIDENTITYPROVIDER 
UPDATE
    dbo.[RETAILIDENTITYPROVIDER]
SET
    [ISSUER] = N'https://sts.windows-ppe.net/MIGRATION_VALUE_' + SUBSTRING(CONVERT(nvarchar(50), NEWID()), 1, 8) + '/'
WHERE
    [NAME] = N'Azure AD'

UPDATE
    dbo.[RETAILIDENTITYPROVIDER]
SET
    [ISSUER] = N'https://MIGRATION_VALUE_' + SUBSTRING(CONVERT(nvarchar(50), NEWID()), 1, 8) + '/auth'
WHERE
    [NAME] = N'Commerce Identity Provider'

UPDATE 
  dbo.[RETAILHARDWAREPROFILE]
SET
  [SECUREMERCHANTPROPERTIES] = NULL

UPDATE 
  dbo.[CREDITCARDACCOUNTSETUP]
SET
  [SECUREMERCHANTPROPERTIES] = NULL

UPDATE 
  dbo.[RETAILCHANNELPAYMENTCONNECTORLINE]
SET
  [SECUREMERCHANTPROPERTIES] = NULL

--FULLTEXT STOP LIST REMOVAL
-------------------------------------------------------------------------------------

-- ALTER FULLTEXT INDEX ON [TableName] SET STOPLIST = SYSTEM'
DECLARE @_SQLFullTextStopList NVARCHAR(4000)
IF object_id('tempdb..#tmpsetstoplist') IS NOT NULL
DROP TABLE #tmpsetstoplist;

CREATE TABLE #tmpsetstoplist (
TableName [nvarchar] (250)
);

DECLARE cur CURSOR
FOR select object_NAME(sys.fulltext_indexes.object_id) as TableName from sys.fulltext_indexes where stoplist_id != 0

OPEN cur;
DECLARE @TableName [nvarchar](250);
FETCH NEXT FROM cur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #tmpsetstoplist (TableName)
	VALUES (@TableName);                
	FETCH NEXT
	FROM cur
	INTO @TableName;
END;
CLOSE cur;
DEALLOCATE cur;

DECLARE cur CURSOR
FOR SELECT TableName FROM #tmpsetstoplist;

OPEN cur;
FETCH NEXT FROM cur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @_SQLFullTextStopList = N'ALTER FULLTEXT INDEX ON ' + QUOTENAME(@TableName) + ' SET STOPLIST = SYSTEM'
	PRINT (@_SQLFullTextStopList)
	EXEC SP_EXECUTESQL @_SQLFullTextStopList
	FETCH NEXT
	FROM cur
	INTO @TableName;
END;
CLOSE cur;
DEALLOCATE cur;

-------------------------------------------------------------------------------------

-- DROP FULLTEXT STOPLIST [FullTextStopListName];

IF object_id('tempdb..#dropfulltextstoplist') IS NOT NULL
DROP TABLE #dropfulltextstoplist;

CREATE TABLE #dropfulltextstoplist (
StopListName [nvarchar] (250)
);

DECLARE cur CURSOR
FOR select name from sys.fulltext_stoplists

OPEN cur;
DECLARE @StopListName [nvarchar](250);
FETCH NEXT FROM cur INTO @StopListName;

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #dropfulltextstoplist (StopListName)
	VALUES (@StopListName);                

	FETCH NEXT
	FROM cur
	INTO @StopListName;
END;
CLOSE cur;
DEALLOCATE cur;

DECLARE cur CURSOR
FOR SELECT StopListName FROM #dropfulltextstoplist;

OPEN cur;
FETCH NEXT FROM cur INTO @StopListName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @_SQLFullTextStopList = N'DROP FULLTEXT STOPLIST ' + QUOTENAME(@StopListName) + ';'
	PRINT (@_SQLFullTextStopList)
	EXEC SP_EXECUTESQL @_SQLFullTextStopList
	FETCH NEXT
	FROM cur
	INTO @StopListName;
END;
CLOSE cur;
DEALLOCATE cur;
--END FULLTEXT STOPLIST REMOVAL



-- Start script: Turn off DMF change tracking for all entities

BEGIN TRY

-- 1. Find and drop triggers
DECLARE TriggerCursor CURSOR
FOR SELECT TriggerName FROM AifSqlCtTriggers

OPEN TriggerCursor

DECLARE @TriggerName NVARCHAR(200)
DECLARE @SqlStmt NVARCHAR(1000)

FETCH NEXT FROM TriggerCursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SqlStmt = 'DROP TRIGGER ' + @TriggerName
	PRINT @SqlStmt
	EXECUTE sp_executesql @SqlStmt
	FETCH NEXT FROM TriggerCursor INTO @TriggerName
END

CLOSE TriggerCursor
DEALLOCATE TriggerCursor

-- 2. Delete rows from AifSqlCtTriggers
DELETE AifSqlCtTriggers WHERE Scope LIKE '%Export'


-- 3. Turn off table level change tracking
DECLARE TableCursor CURSOR
FOR SELECT TableName FROM AifSqlChangeTrackingEnabledTables 

OPEN TableCursor

DECLARE @TableName1 NVARCHAR(162)

FETCH NEXT FROM TableCursor INTO @TableName1
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SqlStmt = 'ALTER TABLE ' + @TableName1 + ' DISABLE CHANGE_TRACKING'
	PRINT @SqlStmt
	EXECUTE sp_executesql @SqlStmt
	FETCH NEXT FROM TableCursor INTO @TableName1
END

CLOSE TableCursor
DEALLOCATE TableCursor

-- 4. Clean up tables
DELETE FROM AIFSqlChangeTrackingEnabledTables

DELETE FROM AIFSqlCdcEnabledTables WHERE Scope LIKE '%Export'

UPDATE DMFEntity SET DMFChangeTrackingType = 0

END TRY
BEGIN CATCH
	PRINT 'exception happened'
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage; 
END CATCH

-- End script: Turn off DMF change tracking for all entities

It is important to do IIS reset and restart the virtual machine after running the script.

Advertisement

Thats a wrap – Microsoft inspire 2021

Here鈥檚 overview of the biggest announcements from Microsoft Inspire 2021.

Microsoft Collaborative Apps with Dynamics 365

Going forward, Microsoft Teams and Dynamics 365 will work more seamlessly together to deliver new ways for everyone in an organisation to seamlessly capture and exchange ideas in the follow of the workday. Microsoft also removed the licensing tax that would otherwise make this integration harder to access.

Dynamics 365 users will now be able to invite anyone in their organisation to collaborate on customer records within a Teams channel or chat. Sales teams will even share information on different sales opportunities, customer relationship health, customer history, and more. Users of Dynamics 365 will also be able to add Teams meetings when creating appointments, allowing access to information within a Teams call.

Users will even be able to capture notes directly within the Teams call, which is saved automatically within the timeline of the Dynamics 365 record.

Connected Workflows and Collaborative Apps

Microsoft also used Inspire as a platform to announce a new host of independent software vendor apps from companies like SAP Sales Cloud, Atlassian Confluence, ServiceNow, Salesforce, and Workday. ISVs will also soon have the option to sell apps directly in Teams, offering new opportunities and providing simplified experiences for IT admins to purchase subscriptions and apps from the Teams admin centre directly.

Introducing Windows 365 Cloud PC

It鈥檚 not just workflows and collaborative apps making a major difference to the Microsoft ecosystem. According to the Microsoft workforce, PCs play a crucial role in keeping people connected while they work and learn. Microsoft recently introduced Windows 11 to update the PC experience. At Inspire, they revealed Windows 365, a new cloud service which changes the way users experience Windows.

Windows 365 will allow users to log into their solution from any device, with an instant-on booting experience from any environment. You can pick up from wherever you left off, no matter which device you鈥檙e using.

Windows 365 will also allow you to bring collaborative apps, Dynamics 365, Microsoft Teams, and security or identity credentials with you wherever you go.

Enhancing Microsoft Viva

Microsoft also introduced more of the amazing features of Microsoft Viva during Insight. This solution, powered by Microsoft 365, will work alongside the existing solutions in the Microsoft ecosystem. The new Viva upgrades include the arrival of Viva Partner Integrations, with a host of content providers to choose from, including Qualtrics, Workday, and many others.

Securing the Digital Transformation

Microsoft also revealed at Insight that it鈥檚 empowering partners to develop better levels of trust with customers through new security offerings to prioritise sustainability, accessibility, and inclusion. Microsoft 365 Lighthouse is now entering preview to provide managed service providers with a central location for security management. There are templates to help with securing data and devices and tools to help partners quickly identify and respond to threats, like device compliance alerts and anomalous sign-in.

Microsoft introduced app governance add-on functionality for the App Security environment. Partners will be able to use this to monitor and govern apps on Microsoft 365 more effectively and prevent risky behaviour.

Finally, new universal regulatory templates in Compliance Manager for non-Microsoft cloud will allow partners to use over 300 templates to manage customer compliance in different apps and cloud spaces.

Check out the on demand sessions on Inspire website below:

https://myinspire.microsoft.com/home

References:

https://www.uctoday.com/collaboration/the-biggest-announcements-from-microsoft-inspire-2021/

https://www.microsoft.com/en-us/microsoft-365/blog/2021/07/14/from-collaborative-apps-in-microsoft-teams-to-cloud-pc-heres-whats-new-in-microsoft-365-at-inspire/

Microsoft dynamics 365 finance and supply chain : SQL script to rename AXDB on tier 1 machines

Sometimes we have to rename AXDB in the SSMS on Tier 1 machines and we get error that the DB is locked by other processes.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to rename AxDB_1. (ObjectExplorer)

------------------------------
ADDITIONAL INFORMATION:

Rename failed for Database 'AxDB'.  (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.41011.9+(SqlManagementObjects-master-APPLOCAL)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5888&EvtSrc=MSSQLServer&EvtID=5030&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

The below SQL Script comeshandy to rename AX databases . The below script will rename database from AXDB to AXDB_Orig

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
ALTER DATABASE AXDB MODIFY NAME = AXDB_Orig 
GO 
ALTER DATABASE AXDB_Orig SET MULTI_USER 
GO

Keep it handy 馃槈

Dynamics 365 Finance and supply chain – X++ code to fetch open calendar days

One thing I like about coding in X++ is that we can reuse API’s and standard classes to perform a complex task. We do not need to reinvent the wheel and best way to do is to find any standard process and reuse the existing methods.

One such example is to find number of open days in a calendar setup between a date range. So you have a start date and end date and a calendar ID and you want to find which days are open in the calendar setup lines.

Instead of writing your own X++ SQL statement to understand table relations and loop through work calendar setup lines, there is a standard class which has this method built in and we can call it to find if a particular day is open in calendar or not.

This method also uses a global cache to lookup the setup and can be fast then writing SQL statements and pulling data directly from the database.

One example of using this method is shown below : Here we have a fromDate, toDate and Calendar ID. All we need to do is loop through the dates and call this method and we can then insert it into a map which can then be used in further logic.

   protected Days findOpenDates(
        TransDate   _fromDate,
        TransDate   _toDate)
    {
        CalendarId          calendarId;
        TransDate           calendarValidTo;

        TransDate           loopDate;

        Days                numOfOpenDays;

        WorkCalendarSched workCalendarSched = new WorkCalendarSched(true);

        for (loopDate = _fromDate; loopDate <= _toDate; loopDate++)
        {
            if ( ! calendarId
                || calendarValidTo < loopDate)
            {
                [calendarId, calendarValidTo] = this.calendar(loopDate);
            }

            if (workCalendarSched.isDateOpen(calendarId,loopDate))
            {
                if (mapAllDemand)
                {
                    mapAllDemand.insert(loopDate,0);
                }
                numOfOpenDays++;
            }
        }

        return numOfOpenDays;
    }

As X++ developers we should leverage standard API’s instead of reinventing the whole wheel. Thanks for reading the blog.

D365 Finance and supply chain ( F&O) : Adding custom fields on create product form via extension framework Approach

If you have added new fields in products table via extension, then their are high chances that you will also be asked to add them on the standard create product dialog.

Initially it may look like a simple task to add 2 fields on a form but when you will start developing and will have a look inside the form design you will notice that there is no data source on this form design and hence you cannot just add the fields by drag and drop feature.

No Datasource in the form design

Upon technical analysis , you will find that there is a method named createData2Controls() which basically populates a container with details of which controls on the form are mapped to which fields of the tables and then under the method writeMoreFields(), the container is read and the fields are assigned value based on the mappings present in the container.

Below is how we can use extension framework approach to add new fields to the form and add the mapping in the container.

  • Add new fields to Invent table. In my case I added 2 new fields.
  • Create extension of the EcoresProductCreate form and add unbounded controls on the form design. Set Auto declaration as True.
  • Create a new extension class and decorate it with attribute [ExtensionOf(formStr(EcoResProductCreate))].
  • In the new class , extend the method createdata2Controls() to add the mapping of your new fields on the table extension with the form unbounded controls . Below is the sample
[ExtensionOf(formStr(EcoResProductCreate))]
final class XYZ_EcoResProductCreate_Extension
{
    protected void createData2Controls()
    {
        next createData2Controls();

        data2Controls = conIns(data2Controls,
                                conLen(data2Controls) + 1,
                                [tableStr(InventTable), [[fieldNum(InventTable, YourFieldName1), formControlStr(EcoResProductCreate, YourControlName1)]]]);

        data2Controls = conIns(data2Controls,
                                conLen(data2Controls) + 1,
                                [tableStr(InventTable), [[fieldNum(InventTable, YourFieldName2), formControlStr(EcoResProductCreate, YourControlName2)]]]);

    }

}

After this build the project area and run the product create process and the values will flow to the inventTable.

Hope this helps if you are trying to figure out how to use extension framework to feed the value of new controls to the table.

Summary of what\’s new or changed in Dynamics 365 Finance and Supply Chain update 10.0.14 (November 2020)


Microsoft Dynamics 365 Finance & Operations update 10.0.14 became generally available in November 2020 and is loaded with another bag of goodies.聽

I wanted to share a summary from the analysis I did post installing it.聽Talking about numbers, there are 35 new features and 630 KB fixes across various modules. Quality update which got released has around 118 fixes across application.聽聽

Below is a quick summary snapshot of 10.0.14 update.聽



I want to briefly talk about few key features :


Dynamics 365 Guides for Manufacturing聽 –>聽聽This feature enables you to deliver mixed-reality, step-by-step instructions for production processes using Dynamics 365 Guides.聽
Basically once you setup the guides parameters and then define the guides, then the worker can use their HoloLens聽to scan the QR code and the guide will get launched.聽
Refer to this docs聽link聽for more details.聽 Below are some screenshots and steps from the same link, for quick reference:聽
To configure how Guides appear on the shop floor, go to聽Mixed Reality > Dynamics 365 Guides > Configure Guides integration.


You can attach guides to various aspects of production control like聽Resources ,Resource groups,聽Released products,聽Formulas,聽Routes,聽Route versions. etc.聽

When a worker opens a job list on the shop floor execution interface, Supply Chain Management finds the relevant guides for the jobs shown. Use the聽Guides聽button to view the relevant guides.


Then put on a HoloLens and access the respective guide by glancing at the QR code and activating the respective Guide.

Asset Leasing :
Asset leasing helps you adapt and be compliant to the ASC 842 and IFRS 16 accounting regulations related to lease management and its financial impact Asset leasing is integrated with fixed assets and the chart of accounts. Refer to Microsoft聽Tech talk on this topic having feature demonstration here.


Below is a summary of asset leasing capabilities from the tech talk presentation:聽



All the asset leasing objects are under a separate model in the application explorer

Enable Project Operations on Dynamics 365 Customer Engagement :聽

This feature allows to define default configurations can be defined to support Project Operations integration. It is dependent on the feature \”Enable multiple contract lines for a project\” .聽

The feature to Enable multiple contract lines for a project provides support for a project to have fixed price components and time and material components. Project type will be moved to the contract line. Project group will become optional and the setup related to tracking of work in process and revenue calculation will be moved to a new entity – Project revenue profiles. The project revenue profile rules will determine which project revenue profile to use. Internal projects will be projects without a contract and only costs will be tracked.聽

Test this in SBOX environment as these should be enabled only if using synchronous integration with the Common Data Service.


(Preview) Vendor invoice automation :

The automation processes can be used to perform these tasks:

聽聽聽聽聽聽聽 Automatically submit imported invoices to the workflow system.

聽聽聽聽聽聽聽 Match product receipts to pending vendor invoice lines.

聽聽聽聽聽聽聽 Simulate posting before a vendor invoice is posted.

聽聽聽聽聽聽聽 Quickly and efficiently view workflow history.

聽聽聽聽聽聽聽 View and analyze the results of automating vendor invoice processing.

Refer docs聽here聽for more details.聽

Below is a complete list of 35 new features shown in feature management post 1.0.0.14 installation聽




KB Fixes

Below is the breakup of KB Fixes across various modules.聽The complete list of聽 KB details can be found in LCS聽here


We can see most of the fixes are in Localization, PMA, Retail, Financial management聽and Warehouse and Transportation management modules.


Quality update got released with around聽118 fixes as well across聽various modules. The details can be seen from environment聽details page.聽


Platform features

In terms of platform features now we can have drop-down list on multi-line controls. Previously, only single-line input controls permitted drop-down lists.


An update to the (Preview) Grouping in grids feature is available now to expand/collapse groups and select or unselect all rows in a group.

I hope this article gives a summarized聽view of what\’s new in 10.0.14 and will enable readers to dive deep into the areas of their interest聽and related ongoing projects. Thanks for reading the blog. Have a great day and keep DAXing :).聽



New features at a glance for Microsoft Dynamics 365 Finance & Operations update 10.0.13

Microsoft Dynamics 365 Finance & Operations update 10.0.13 is generally available and the details of release can be found on docs site . This update marks end of 2020 wave 1 and as expected, this update is packed with lots of goodness and new features.聽

I wanted to share a summary from the analysis I did post installing it. Talking about numbers, there are 60 new features and 950 KB fixes across various modules.聽

Below is breakup of the new features per module. Most of the new features are released in Retail & Commerce, Project management & accounting and Accounts receivable module.

I did some more digging around the newly introduced featured and queried some tables under the hood and found:聽
Some features that cannot be disabled once you enable them:
Features which are force enabled:
Preview features:
Some of my favorite features from the release, which I feel shall catch a lot of attention going forward:聽

The complete list of the new features can be seen from feature management workspace after updating the environment

With regards to the platform capabilities, some cool and awaited features like saved views and new grid control have been made generally available. These will give great personalization capabilities to users and also allows them to use mathematical formulas in tabular grids and empowers them for fast data entry.

The tentative release plan for聽 wave 2 features can be accessed from聽聽https://docs.microsoft.com/en-us/dynamics365-release-plan/2020wave2/聽.聽
Microsoft business applications launch event is also planned where James Phillips, President of the Business Applications Group, will guide the community through what鈥檚 new in Dynamics 365 and Microsoft Power Platform 2020 release wave 2. Register here https://msft.it/6049TsJ1J
Keep sharing and stay safe.聽

Dynamics 365 Finance and Operations apps : SysExtensionSerializerMap – Your friend for adapting to normalization instead of extending tables when adding lot of fields

In every implementation of Dynamics 365 for Finance & Operation, there is need to add new fields in the core tables. With introduction of extension framework, Microsoft provided a clean way to add new fields without over layering the standard object hence making consumption of updates smooth.
However, Microsoft has introduced a best practice warning in the compiler when there are more than 10 fields added on a table via extension. The alternate recommended way is to create a new table and add a foreign key relation to standard table. One of the benefit of moving fields to separate tables is performance improvements. It makes sense because the performance cost associated with the retrieval of the custom fields should only be incurred when processes which needs these fields are executed.
This sounds good from database normalization perspective. But when you are working in a business application, then the expectation is that data in the custom table should be inserted, updated and deleted along with the core table. In order to make developers life easy there are maps 鈥 SysExtensionSerializerMap and SysExtensionSerializerExtensionMap available out of the box.
These were available in previous versions of the product AX 2012.
If used smartly developers can save efforts and reduce complexity to write custom logic of CRUD operations on the new table and in fact provide more flexibility when we need to access the table buffer. Below are key steps we should use when creating a new table to store custom fields:

聽Other best practices related to creation of tables like adding indexes, field groups, properties etc. still remain intact.


Below is how a table, for illustration, should look. In this example, I have created a table instead of adding fields via extension and have only added one field.

Mapping properties should look like :聽
Below is how the relation properties should look like:
Define the index as alternate index and use it as replacement key on the table properties
Index properties:聽
Table properties:聽
Once this mapping is added then developers don鈥檛 have to worry about things like calling insert and update methods on the new table whenever a record is created or updated in core table. This is how it works:
SysExtensionSerializerMap has methods like postInsert(), postUpdate() and more as shown below.






postInsert() Inserts the packed extension table record after the base table record is inserted, it uses SysExtensionSerializerFormRunHelper class to perform the logic.
Most of the tables where insert method is overridden, call methods of these maps as shown below
A nicely modeled data structure will enable developers to focus on writing the business logic and leverage the platform capabilities.聽
Thanks for reading the blog. I still love the old school tag line to end the blog – happy DAXing J

Dynamics 365 for Finance and Operations : Active periods in batch jobs

Friends,
In this blog post, I want to highlight the option of defining active period on a batch jobs. Microsoft released this feature it in platform update 21 (Link can be found聽Here)

When used properly this option helps to fine tune batch jobs so that the system resources can be used optimally.

Architects should identify business tasks running in batch jobs or system maintenance tasks which can be performed after hours and should configure batch jobs smartly. Some scenarios where this configuration is useful can be batch processes which are running consolidations at end of day or emailing out list of activities to be performed next day by field technicians or system maintenance tasks to clean up staging tables.

Active periods can be defined from System maintenance >> Setup >> Active period for batch jobs menu item聽

Multiple active periods can be defined as per the use case, it is very important for architects to discuss this with there clients and understand the need of batch jobs execution time frame:
Once active periods are defined they can be linked to a batch job as shown below :聽

Few important points:

1. Recurrence settings which are defined on the batch job are applied only within the active period define for the batch job.

2. This setting should not be used as an alternative for recurrence setting.

3. Active period does not go over midnight.聽

4. The batch jobs are put to waiting status once active time is crossed and then again put back to executing status when the active time is reached. The reference code can be found in below highlighted class.

5. There is a default period define from 12:00:00 AM to 11:59:59 PM. Do not modify time or delete it ,聽as system will check for a default active period and if this is deleted then system recreates it. The reference code can be found in below highlighted class.

It is always a good practice to:

  • Understand and discuss the need for defining active periods on batch jobs with your customers.
  • Define active periods and test batch jobs in tier 2 environments before applying to Production.聽
  • Have correct understanding of this setup and identify options to聽 reduce the load on system during business hours when most of the business users are logged on.聽

Thanks for reading the blog..please feel free to share your thoughts on the same topic.聽