Changing Company Id in D365 FO

 Changing the company id in D365 FO is not directly possible and also not a recommended approach. But there still is a way through which we can change the Id in rare cases. Below are the list of technical steps to be followed for changing the company id (DataAreaId).

Note: Please take the database back up before any changes to revert back in case of any issues during the process.

There are three scenarios of tables where the company Id is present.  They all need to be updated in order to bring the new Id all over the application.

1. Company Specific tables where there is a DataAreaId filed available.

• Use the below stored procedure command in SQL to update the field in all tables with the values required.

• Use the below SQL query to update the table "DataArea"

• Get the list of tables using the below query

        EXEC sp_MSforeachtable 'update ? set DataAreaID = "XNEW" where ?.DataAreaID = "XOLD"

2. DataArea Table

        UPDATE DataArea SET ID = 'XNEW' WHERE DataArea.ID = 'XOLD'

3. Global tables which do not have a default framework maintained "DataAreaId" field but has a physical ordinary field called "DataArea".

• Use the below SQL query to get the list of table having the field as DataArea

        SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 

            WHERE TABLE_TYPE = 'BASE TABLE' 

            AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME    

            FROM INFORMATION_SCHEMA.COLUMNS 

            WHERE COLUMN_NAME = 'DataArea');

• Update the list of tables with the new companyId value in the fields.

        UPDATE TableName SET DataArea = 'XNEW' WHERE DataArea.ID = 'XOLD'

Multi select lookup in D365 FO

 We come across many scenarios where a multiple records need to be selected in a drop down. The usual approach of bringing up the lookup using the "SysLookup" will not work in this case. We have separate classes for bringing up multi select lookup.

Below is one example scenario for multi select lookup.

Let us say we have a field called manufacturing Unit in Sales order (Sales table) where we need to bring up a multiselect lookup. This lookup should bring the "Site" (InventSite Table) in the legal entity.

Below is the sample code for the mentioned scenario.

1. Create an extension class for the for the sales table form

2. make CoC for the form init method

3. Create a parm method to hold the multilookupSelectCtrl object

4. Create an Event Handler class to take the onLookup Event of the form control where the multi select lookup is required.

5. Write the below code to set the selected value to the field.

Below is the result of the code.

Deploy SSRS reports through command line in D365 FO

Deployment of reports in D365 FO can be done using Visual studio. But there is also another way through which we can deploy report faster.

We can use the Windows Powershell in the environment to deploy the reports faster and easier. Below are the steps to deploy the reports using Windows Powershell in D365 FO 

1. Open Windows Powershell as Administrator

2. Use the below command to navigate to the folder

• CD K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask

3. Use the below command to deploy the reports.

• All Reports. 
• DeployAllReportsToSSRS.ps1 -PackageInstallLocation “K:\AosService\PackagesLocalDirectory”

• Specific Reports
• DeployAllReportsToSSRS.ps1 -Module ApplicationSuite -ReportName AssetDep*,TaxVatRegister.Report -PackageInstallLocation “K:\AosService\PackagesLocalDirectory”

Database restore from bacpac file

We may have to restore the database in D365 environment in order to get the latest data and configurations in the system. To get the latest dat we should have a backup file of the database taken from other environment. 

The back up files can of two types

• .bak 
• .bacpac

Restoring a ".bak" file in to SQL server involves the direct process to get the latest data base. But if the file type is of ".bacpac" then it is not the same way as a ".bak" file.

Restoring a ".bacpac" file needs a different approach than the regular database back up file Below are the steps to be followed to restore the bacpac file in to SQL Server.

1. Open command prompt with Admin rights

2. Use the below command to navigate to the folder 

• Cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

      Note : If 140 or 150 not available then install update for dac framework. Install DAC

3. Execute the below command

• SqlPackage.exe /a:import /sf:Your file path /tsn:localhost /tdn:AXDB_New /p:CommandTimeout=1800
• File path example : C:\Temp\YourbacpacName.bacpac

4. Login to SQL Server with Admin user

5. Select the newly created DB (AXDB_New) in previous steps and execute the following SQL command in it.

6. Rename AXDB to AXDB_Old.

7. Rename AXDB_New to AXDB.

SQL Script to be executed

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdbadmin FROM LOGIN axdbadmin

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser

EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'

EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axdeployextuser FROM LOGIN axdeployextuser

EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]

EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

UPDATE T1

SET T1.storageproviderid = 0

    , T1.accessinformation = ''

    , T1.modifiedby = 'Admin'

    , T1.modifieddatetime = getdate()

FROM docuvalue T1

WHERE T1.storageproviderid = 1 --Azure storage

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

GO

-- Begin Refresh Retail FullText Catalogs

DECLARE @RFTXNAME NVARCHAR(MAX);

DECLARE @RFTXSQL NVARCHAR(MAX);

DECLARE retail_ftx CURSOR FOR

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES

    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');

OPEN retail_ftx;

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

BEGIN TRY

    WHILE @@FETCH_STATUS = 0 

    BEGIN 

        PRINT 'Refreshing Full Text Index ' + @RFTXNAME;

        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';

        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';

        EXEC SP_EXECUTESQL @RFTXSQL;

        FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

    END

END TRY

BEGIN CATCH

    PRINT error_message()

END CATCH

CLOSE retail_ftx; 

DEALLOCATE retail_ftx; 

SQL server log database size shrink

1. Log in to SQL server wit Admin rights 

2. Use the below SQL commands in sequence

Note: Replace DBName with your log DB Name. EX(AXDB_Log)

• Set the Database recovery mode to simple

            ALTER DATABASE DBName

            SET RECOVERY SIMPLE;

            GO

• Shrink the DB

            DBCC SHRINKFILE (DBName, 1);

            GO

• Set the database recover mode back to full

            ALTER DATABASE DBName

            SET RECOVERY FULL;

Add third party packages in to Source control in TFS

Below are the steps to Add a third party packages or ISV solutions received from different team in to the TFS t make it available in the Branch that can be accessed by the entire team. 

1.     Open Source Control Explorer by clicking View > Other Windows > Source Control Explorer.

2.     Navigate to the metadata folder that is mapped on this development VM, such as MyProject/Trunk/Main/Metadata.

3.     Right-click the Metadata folder, and then click Add Items to Folder.

4.     In the Add to Source Control dialog box, double-click the folder that has the package name that you want to add to source control.

5.     Select all the folders except XppMetadata and Descriptor, if they exist, and then click Next.

6.     On the next page, on the Excluded items tab, select all files by clicking one of the files and then pressing Ctrl+A. At the bottom of the selection window, click Include item(s). When you're ready, click Finish.

7.     Open the Pending Changes window from the Team Explorer pane or by clicking View > Other Windows > Pending Changes.

8.     Review the changes, enter a check-in comment, and then click Check In.

Install third party packages in D365 F&O development environment

1.     On the virtual machine (VM), download the zip file for the deployable package. Make sure that the zip file is stored in a non-user folder.

Note : After you download the zip file, right-click it, and then select Properties. Then, in the Properties dialog box, on the General tab, select Unblock to unlock the files.

2.     Extract the files.

3.     Open a Command Prompt window, and go to the folder where you extracted the deployable package.

4.     Run the following command.

AXUpdateInstaller.exe devinstall

The devinstall option installs the AOT deployable package on the VM.

 Note

This command doesn't run database synchronization. You must run database synchronization from Microsoft Visual Studio after you install the deployable package.