Solutions for AX DEV: Your Go-To Resource for D365 F&O(AX) Development

Welcome to Solutions for AX DEV — a dedicated space for developers and consultants working with Microsoft Dynamics AX / D365 F&O (D365 F&O). This blog contains a collection of frequently used code snippets, development tips, and practical solutions related to AX (Axapta). Whether you're working with X++, you'll find helpful content tailored to your development needs. Have a question or need help with something Feel free to post your it.

Wednesday, 14 June 2023

Structured Query language (SQL)

1. Create database

create database sample2

2. Use the database

use sample2

3. Create table

create table customer

(

customerid int identity(1,1) primary key,

customernumber int not null unique check (customernumber>0),

lastname varchar(30) not null,

firstname varchar(30) not null,

areacode int default 71000,

address varchar(50),

country varchar(50) default 'Malaysia'

)

4. Insert values into table

insert into customer values

(100,'Fang Ying','Sham','418999','sdadasfdfd',default),

(200,'Mei Mei','Tan',default,'adssdsadsd','Thailand'),

(300,'Albert','John',default,'dfdsfsdf',default)

5. Display record from table

-- display all records

select * from customer

-- display particular columns

select customerid, customernumber, lastname, firstname

from customer

6. Add new column to table

alter table customer

add phonenumber varchar(20)

7. Add values to newly added column/ Update table

update customer set phonenumber='1234545346' where

customerid=1

update customer set phonenumber='45554654' where

customerid=2

8. Delete a column

alter table customer

drop column phonenumber

9. Delete record from table

--if not put ‘where’, will

delete all record

delete

from customer

where country='Thailand'

10. Delete table

drop table customer

11. Change data type

alter table customer

alter column phonenumber varchar(10)

12. Check what table inside

select * from information_schema.tables

13. View specific row

--top: show only the first two

select top 2 * from customer

--top 40 percent: also means show the first two

select top 40 percent * from customer

14. View specific column

--sort result (by default is ascending)

select customerfirstname, customerlastname from customer

order by customerlastname desc

select customerfirstname, customerlastname from customer

order by 4, 2, 3 desc -- Order By Based on column no. without typing column

name

--distinct: only show unique value

select distinct customerlastname from customer

order by customerlastname

15. Save table to another table

--into file_name: save result in another table (BASE TABLE)

select distinct customerlastname into temp

from customer

order by customerlastname

select * from temp --see the table (data type will remain)

16. Like (search something)

-- (underscore sign) _ is only specific for one character only

-- (percent sign) % represents zero, one, or multiple characters

select * from customer

where customerlastname like '_r%'

17. In (search something)

-- search multiple items

select * from customer

where customerlastname in ('Brown', ‘Michael’, ’Jim’)

18. > (search something)

select * from customer where customerlastname > 'Brown' or customerlastname>'Cross'

19. <> (Not Equal)

select * from customer

where customerlastname <> 'Brown'

20. IS NULL

-- check null values

select * from customer

where customerlastname IS NULL

21. IS NOT NULL

select * from customer

where customerlastname IS NOT NULL

22. between

select * from sale

where saleunitprice between 5 and 10 --not include 5 & 10

23. count

-- returns the number of rows in a table

-- AS means aliasing, temporary giving name to a column/ table

select count(*) as [Number of Records] from customer

where customerfirstname like 'B%'

24. sum

select sale.employeeid ,EmployeeFirstName, EmployeeLastName , count(*) as

[Number of order] ,

sum(salequantity) as [Total Quantity]

from sale,employee

where sale.employeeid = employee.employeeid

group by sale.employeeid ,EmployeeFirstName, EmployeeLastName

25. count month

select month(saledate) as [Month], count ( * ) as [Number of sale],

sum(salequantity*saleunitprice) as [Total Amount]

from sale

group by month(saledate)

26. Max/Min/Average

SELECT MAX(Salary)

FROM EmployeeSalary

27. having

SELECT JobTitle, COUNT(JobTitle)

FROM EmployeeDemographics ED

JOIN EmployeeSalary ES

ON ED.EmployeeID = ES.EmployeeID

GROUP BY JobTitle

HAVING COUNT(JobTitle) > 1

SELECT JobTitle, AVG(Salary)

FROM EmployeeDemographics ED

JOIN EmployeeSalary ES

ON ED.EmployeeID = ES.EmployeeID

GROUP BY JobTitle

HAVING AVG(Salary) > 45000

ORDER BY AVG(Salary)

28. Change data type temporary for use

-- CAST(expression AS datatype(length))

SELECT CAST('2017-08-25 00:00:00.000' AS date)

-- CONVERT(data_type(length), expression, style)

SELECT CONVERT(date,'2017-08-25 00:00:00.000')

29. CASE Statement

SELECT FirstName, LastName, Age,

CASE

WHEN Age > 30 THEN 'Old'

WHEN Age BETWEEN 27 AND 30 THEN 'Young'

ELSE 'Baby'

END

FROM EmployeeDemographics ED

WHERE Age IS NOT NULL

ORDER BY Age

SELECT FirstName, LastName, JobTitle, Salary,

CASE

WHEN JobTitle = 'Salesman' THEN Salary + (Salary *.10)

WHEN JobTitle = 'Accountant' THEN Salary + (Salary *.05)

WHEN JobTitle = 'HR' THEN Salary + (Salary *.000001)

ELSE Salary + (Salary *.03)

END AS SalaryAfterRaise

FROM EmployeeDemographics ED

JOIN EmployeeSalary ES

ON ED.EmployeeID = ES.EmployeeID

30. Partition By --returns a single value for each row

SELECT FirstName, LastName, Gender, Salary,

COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender

FROM EmployeeDemographics ED

JOIN EmployeeSalary ES

ON ED.EmployeeID = ES.EmployeeID

31. String Functions

-- Remove space

Select EmployeeID, TRIM(EmployeeID) AS IDTRIM

FROM EmployeeErrors

Select EmployeeID, RTRIM(EmployeeID) as IDRTRIM

FROM EmployeeErrors

Select EmployeeID, LTRIM(EmployeeID) as IDLTRIM

FROM EmployeeErrors

-- Replace

Select LastName, REPLACE(LastName, '- Fired', '') as

LastNameFixed

FROM EmployeeErrors

-- Substring

Select Substring(err.FirstName,1,3),

Substring(dem.FirstName,1,3), Substring(err.LastName,1,3),

Substring(dem.LastName,1,3)

FROM EmployeeErrors err

JOIN EmployeeDemographics dem

on Substring(err.FirstName,1,3) =

Substring(dem.FirstName,1,3)

and Substring(err.LastName,1,3) =

Substring(dem.LastName,1,3)

-- UPPER and LOWER CASE

Select firstname, LOWER(firstname)

from EmployeeErrors

Select Firstname, UPPER(FirstName)

from EmployeeErrors"

32. Stored Procedure

CREATE PROCEDURE Temp_Employee

@JobTitle nvarchar(100)

DROP TABLE IF EXISTS #temp_employee

Create table #temp_employee (

JobTitle varchar(100),

EmployeesPerJob int ,

AvgAge int,

AvgSalary int

)

Insert into #temp_employee

SELECT JobTitle, Count(JobTitle), Avg(Age), AVG(salary)

FROM EmployeeDemographics emp

JOIN EmployeeSalary sal

ON emp.EmployeeID = sal.EmployeeID

where JobTitle = @JobTitle --- make sure to change this in

this script from original above

group by JobTitle

Select *

From #temp_employee

GO;

By Fang Ying, Sham

--- only need to run this on next time

EXEC Temp_Employee @JobTitle = 'Salesman'

33. Subquery

-- Subquery in Select

SELECT EmployeeID, Salary, (SELECT AVG(Salary) FROM

EmployeeSalary) AS AllAvgSalary

FROM EmployeeSalary

-- with Partition By

SELECT EmployeeID, Salary, AVG(Salary) OVER () AS

AllAvgSalary

FROM EmployeeSalary

-- Subquery in From

SELECT a.EmployeeID, AllAvgSalary

FROM (SELECT EmployeeID, Salary, AVG(Salary) OVER () AS

AllAvgSalary

FROM EmployeeSalary) a

ORDER BY a.EmployeeID

-- Subquery in Where

SELECT EmployeeID, JobTitle, Salary

FROM EmployeeSalary

WHERE EmployeeID in (SELECT EmployeeID FROM

EmployeeDemographics

WHERE Age > 30)

SELECT EmployeeID, JobTitle, Salary

FROM EmployeeSalary

WHERE Salary in (SELECT Max(Salary) FROM EmployeeSalary)

1. getting data from multiple tables (explicit join - without using join command)

select * from inventory,sale

where sale.inventoryid=inventory.inventoryid

select

inventoryname,saledate,saleunitprice,salequantity,salequantity*saleunitprice

as [Total amount]

from sale,inventory

where sale.inventoryid=inventory.inventoryid

group by sale.inventoryid,inventoryname,saledate,salequantity,saleunitprice

order by inventoryname

2. getting data from multiple

tables

(implicit join - using join

command)

--inner join

select * from inventory

inner join sale

on sale.inventoryid=inventory.inventoryid

select

inventoryname,saledate,saleunitprice,salequantity,saleunitprice*salequantity

as [Total Amount]

from inventory inner join sale

on sale.inventoryid=inventory.inventoryid

order by inventoryname

--full outer join (shows everything)

select sale.inventoryid,inventoryname

from inventory

full outer join sale on

sale.inventoryid=inventory.inventoryid

where sale.inventoryid is NULL

--left join (might have NULL value, since some inventory might not have sales)

select inventory.inventoryid,inventoryname

from inventory left join sale on

sale.inventoryid=inventory.inventoryid

--left join

select inventory.inventoryid,inventoryname

from inventory left join sale on

sale.inventoryid=inventory.inventoryid

where sale.inventoryid is NULL

-- without join: use subquery

select inventoryid,inventoryname from inventory

where inventoryid not in (select inventoryid from sale)

--right join

select sale.inventoryid,inventoryname

from inventory right join sale on

sale.inventoryid=inventory.inventoryid

3. Self Join --commonly used in processing hierarchy

--inner join

Staff Table

employeeID employeefirstname employeelastname managerID

1001 Tan Mei Ling NULL

1002 Kelvin Koh 1001

1003 Amin Wong 1002

select E.employeeID, E.employeefirstname+' '+E.employeelastname as [Full

Name], E.managerID, , M.employeefirstname+' '+M.employeelastname as

[Manager Name]

from staff E

inner join staff M

on E.managerID = M.employeeID

--left outer join (list all the employees) select E.employeeID, E.employeefirstname+' '+E.employeelastname as [F Name], E.managerID, , M.employeefirstname+' '+M.employeelastname as [Manager Name] from staff E left outer join staff M on E.managerID = M.employeeID

4. Cross Join --generate all combination of records (all possibility

select * from inventory1

cross join inventory2

1. Union

--allow you to combine two tables

together (but the no. of columns &

each column’s data types for 2 tables

must be match)

--don't need common key, only need

common attributes

--merge, not showing duplicate record

select cust_lname,cust_fname from customer

union

select cust_lname,cust_fname from customer_2

2. Union all --merge, but show you everything, even the duplicate record

select cust_lname,cust_fname from customer

union all

select cust_lname,cust_fname from customer_2

3. Intersect

--keep only the rows in common to

both query

--not showing duplicate record

select cust_lname,cust_fname from customer

intersect

select cust_lname,cust_fname from customer_2

select c.cust_lname,c.cust_fname from customer c,customer_2 c2

where c.cust_lname=c2.cust_lname and c.cust_fname=c2.cust_fname

4. Except

--generate only the records that are

unique to

the CUSTOMER table

select cust_lname,cust_fname from customer

except

select cust_lname,cust_fname from customer_2

--use subquery

select cust_lname,cust_fname from customer

where(cust_lname) not in

(select cust_lname from customer_2) and

(cust_fname) not in

(select cust_fname from customer_2)

Table & View

1. view table

(view will be updated when

update base)

--view is a result set of SQL

statements, exists only for a

single query

create view CustomerView as

select customerfirstname+' '+customerlastname as [Customer Name] ,

customerphonenumber,

inventoryname,saledate,salequantity,saleunitprice,salequantity*saleunitprice

as [Total Amount]

from customer inner join sale on customer.customerid=sale.customerid inner

join inventory

on sale.inventoryid=inventory.inventoryid

2. Temp table (temp will NOT be updated when update base) --a single hashtag (#) sign must be added in front of their names --used to store data temporarily, physically created in the Tempdb database --can perform CRUD, join, and some other operations like the persistent database tables

DROP TABLE IF EXISTS #temp_Employee

Create table #temp_Employee (

JobTitle varchar(100),

EmployeesPerJob int,

AvgAge int,

AvgSalary int

)

Insert INTO #temp_Employee

SELECT JobTitle, Count(JobTitle), Avg(Age), AVG(salary)

FROM EmployeeDemographics emp

JOIN EmployeeSalary sal

ON emp.EmployeeID = sal.EmployeeID

group by JobTitle

SELECT * FROM #temp_Employee

3. CTE (Common Table Expression) --create temporary result set which is used to manipulate the complex sub-queries data --created in memory rather than Tempdb database, so cannot create any index on CTE.

WITH CTE_Employee AS

(

SELECT FirstName, LastName, Gender, Salary,

COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender

FROM EmployeeDemographics ED

JOIN EmployeeSalary ES

ON ED.EmployeeID = ES.EmployeeID

WHERE Salary > '45000'

)

SELECT FirstName, LastName, Gender, TotalGender

FROM CTE_Employee

WHERE TotalGender = (SELECT MIN(TotalGender) FROM CTE_Employee)

4. Duplicate Table

select customerfirstname+' '+customerlastname as [Customer Name] ,

customerphonenumber,

inventoryname,saledate,salequantity,saleunitprice,salequantity*saleunitprice

as [Total Amount] into customerRec

from customer inner join sale on customer.customerid=sale.customerid inner

join inventory

on sale.inventoryid=inventory.inventoryid

order by customerfirstname +' '+ customerlastname,inventoryname

SQL RANKS

1. ROW_NUMBER()

--get a unique sequential number for each row

--get different ranks for the row having similar values

SELECT *,

ROW_NUMBER() OVER(ORDER BY Salary DESC) SalaryRank

FROM EmployeeSalary

2. RANK()

--specify rank for each row in the result set

--use PARTITION BY to performs calculation on each group

--each subset get rank as per Salary in descending order

USING PARTITION BY

SELECT *,

RANK() OVER(PARTITION BY JobTitle ORDER BY Salary DESC)

SalaryRank

FROM EmployeeSalary

ORDER BY JobTitle, SalaryRank

NOT USING PARTITION BY

-- get SAME ranks for the row having similar values

SELECT *,

RANK() OVER(ORDER BY Salary DESC) SalaryRank

FROM EmployeeSalary

ORDER BY SalaryRank

3. DENSE_RANK()

-- if have duplicate values, SQL assigns different ranks to those rows.

-- will get the same rank for duplicate or similar values

SELECT *,

DENSE_RANK() OVER(ORDER BY Salary DESC) SalaryRank

FROM EmployeeSalary

ORDER BY SalaryRank

3. Split by delimiter ❖ SUBSTRING(string, start, length) ❖ CHARINDEX(substring, string, start) ❖ LEN(string)

SELECT PropertyAddress,

SUBSTRING(PropertyAddress, 1, CHARINDEX(',',

PropertyAddress) -1 ) as Address

, SUBSTRING(PropertyAddress, CHARINDEX(',',

PropertyAddress) + 1 , LEN(PropertyAddress)) as City

From NashvilleHousing

ALTER TABLE NashvilleHousing

Add PropertySplitAddress Nvarchar(255);

ALTER TABLE NashvilleHousing

Add PropertySplitCity Nvarchar(255);

Update NashvilleHousing

SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1,

CHARINDEX(',', PropertyAddress) -1 )

Update NashvilleHousing

SET PropertySplitCity = SUBSTRING(PropertyAddress,

CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress))

PARSENAME('object_name'

, object_piece)

--numbering works from

right to left

❖ REPLACE(string, old_string,

new_string)

Select OwnerAddress,

PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3)

,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2)

,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1)

From NashvilleHousing

ALTER TABLE NashvilleHousing

Add OwnerSplitAddress Nvarchar(255);

ALTER TABLE NashvilleHousing

Add OwnerSplitCity Nvarchar(255);

ALTER TABLE NashvilleHousing

Add OwnerSplitState Nvarchar(255);

Update NashvilleHousing

SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress,

',', '.') , 3)

Update NashvilleHousing

SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',',

'.') , 2)

Update NashvilleHousing

SET OwnerSplitState = PARSENAME(REPLACE(OwnerAddress, ',',

'.') , 1)

5. Remove duplicate records

WITH RowNumCTE AS(

Select *,

ROW_NUMBER() OVER (

PARTITION BY ParcelID,

PropertyAddress,

SalePrice,

SaleDate,

LegalReference

ORDER BY UniqueID) as row_num

From NashvilleHousing

order by ParcelID

)

--DELETE

Select * From RowNumCTE

Where row_num > 1

Order by PropertyAddress

Thursday, 9 March 2023

D365 FO: ER Diagrams (Entity Relationship Diagrams)

D365 FO: ER Diagrams (Entity Relationship Diagrams)

Today we went to learn about the Visual Studio extension tool which is used to Visualize the D365FO data models.

The tool is called dbdiagram.io, the Database Markup Language (DBML), and the Generate Entity Relation Schema extension for Visual Studio, we can interrogate and transform the metamodel into a visual representation of our entity relationships.

We can download the tool from github.

Process of installation:

1. Download the Waywo.DbSchema.Addin.dll from Releases.

2. Unblock the dll. Once the download is done, right-click and view the properties and check Unblock and click ok.

4. In your Documents\Visual Studio Dynamics 365 folder there is a DynamicsDevConfig.xml XML file. like this:

<?xml version="1.0" encoding="utf-8"?>

<DynamicsDevConfig xmlns:i="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://schemas.microsoft.com/dynamics/2012/03/development/configuration">

<d2p1:string>C:\D365CustomAddins</d2p1:string>

</AddInPaths>

</DynamicsDevConfig>

5. Edit the XML in note pad and find the tag “AddInPaths”

6. Copy the Waywo.DBSchema.AddIn.dll to the AddInPath folder (create the folder or change the path to where the "Waywo.DbSchema.AddIn.dll" is downloaded.) and then restart Visual Studio.

7. In the Visual studio from the table designer, right-click àAddinsàGenerate entity relation schema.

Data Entity: PostTargetProcess and PostGetStagingData Methods to Update Target Table D365 FO X++

Data Entity: Methods to Update Target Table D365 FO X++

Recently we got a requirement to update the target table with the execution id and show the status of the Entity status on the Dynamics AX Form.

In this case, we can use the method “PostTargetProcess” method, which allows you to use DMFDefinitionGroupExecution from where we can get the Entity DefinitionGroup and Execution Id.

This method is automatically called by the Framework class DMFEntityWriter at the end of processRecords() method.

ProcessRecords() method will process all the records transferred from staging to target.

In the below example, we are updating the LedgerJournalTable with ExecutionID, which is used to display the status (error/ completed, etc ) based on execution id.

[ExtensionOf(tableStr(DataEntity))]
final public class DataEntity_Extension
{

public static void postTargetProcess(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)

{

LedgerJournalEntityStaging staging;

LedgerJournalTable ledgerJournalTableloc;

select firstonly JournalBatchNumber,ExecutionId from staging

where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

&& staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId;

if (staging.JournalBatchNumber)

{

ledgerJournalTableloc = LedgerJournalTable::find(staging.JournalBatchNumber);

if (ledgerJournalTableloc.RecId && ledgerJournalTableloc.MessageId != staging.ExecutionId)

{

ttsbegin;

ledgerJournalTableloc.selectForUpdate(true);

ledgerJournalTableloc.MessageId = _dmfDefinitionGroupExecution.ExecutionId;

ledgerJournalTableloc.update();

ttscommit;

}

Like postTargetProcess, we have a method called “PostGetStagingData” this method is used to update the data before it gets inserted into the staging table.

public static void postGetStagingData(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)

{

//logic here

}

Thursday, 12 January 2023

D365 FO DB restore in to cloud hosted Development environment using backup (.bacpac) file

DB restore in to cloud hosted environment.

Import Database from UAT/Production to cloud-hosted or dev environment | D365 Finance and operations

To import a database to a developer environment after you've downloaded a database backup (.bacpac) file, you can begin the manual import operation on your Tier 1 environment. When you import the database, we recommend that you follow these guidelines:

· Keep a copy of the existing AxDB database, so that you can revert to it later if needed.

· Import the new database under a new name, such as AxDB_fromProd.

To ensure the best performance, copy the *.bacpac file to the local computer that you're importing from. Download sqlpackage .NET Core for Windows from Get sqlpackage .NET Core for Windows. Open a Command Prompt window, and run the following commands from the sqlpackage .NET Core folder.

Note:

Stop all the related dynamics services and w3 services.

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200 (old)

SqlPackage /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"AdventureWorksLT" /TargetUser:"your_username" /TargetPassword:"your_password" /TargetTrustServerCertificate:True /SourceFile:"C:\AdventureWorksLT.bacpac" (new)

Eg:

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\ERPREPROD.bacpac" /p:CommandTimeout=6200

· tsn (target server name) – The name of the Microsoft SQL Server instance to import into.

· tdn (target database name) – The name of the database to import into. The database should not already exist.

· sf (source file) – The path and name of the file to import from.

Eg:

SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"NDPC" /TargetTrustServerCertificate:True /SourceFile:"J:\MSSQL_BACKUP\DFDSPRODCOPY.bacpac" /p:CommandTimeout=6200

(https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15)

Update the database

Run the following SQL script against the imported database. This script adds back the users that you deleted from the source database and correctly links them to the SQL logins for this SQL Server instance. The script also turns change tracking back on. Remember to edit the final ALTER DATABASE statement so that it uses the name of your database.

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

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

CREATE USER axdeployextuser FROM LOGIN 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

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

-- End Refresh Retail FullText Catalogs

--Begin create retail channel database record--

declare @ExpectedDatabaseName nvarchar(64) = 'Default';

declare @DefaultDataGroupRecId BIGINT;

declare @ExpectedDatabaseRecId BIGINT;

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)

BEGIN

select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default';

insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)

values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0);

select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName;

insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)

select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT

inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID

update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0

END;

--End create retail channel database record

Turn on change tracking

If change tracking was turned on in the source database, be sure to turn it on in the newly provisioned database in the target environment. To turn on change tracking, use the ALTER DATABASE command.

ALTER DATABASE [your database name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);

Start to use the new database

To switch environments and use the new database, first stop the following services:

· World Wide Web Publishing Service

· Microsoft Dynamics 365 Unified Operations: Batch Management Service

· Management Reporter 2012 Process Service

After these services have been stopped, rename the AxDB database AxDB_orig, rename your newly imported database AxDB, and then restart the three services.

To switch back to the original database, reverse this process. In other words, stop the services, rename the databases, and then restart the services.

Example

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE AxDB MODIFY NAME = AxDB_orig

ALTER DATABASE AxDB_orig SET MULTI_USER;

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

USE master;

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AXDB MODIFY NAME = AXDB_Orig;

ALTER DATABASE AXDB_Orig SET MULTI_USER;

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

MS reference :

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#import-the-database

Wednesday, 4 January 2023

Override An Existing Lookup Method: Chain Of Command D365 X++

Override An Existing Lookup Method: Chain Of Command

Here in this example, we will see, how we can override the form control lookup method using a chain of commands.

Let's take an example of the Purch order form and we will try to override the punchline item ID control lookup.

1. Step one create new Classes as shown below.

[ExtensionOf(FormStr(PurchTable))]

final class PurchTableItemIdLookup_Extension

{

}

2. Step two, create a new method with customized logic to override the standard lookup. I call this function "OverrideItemIdLookup" as shown below.

public void overrideItemIdLookup(FormStringControl _formControl)

{

Query qr = new Query();

QueryBuildDataSource qbdsInventTableExpanded;

QueryBuildDataSource qbdEcoResLS;

SysTableLookup systablelookup = SysTableLookup::newParameters(tableNum(InventTable),_formControl);

qbdsInventTableExpanded = qr.addDataSource(tableNum(InventTable));

qbdEcoResLS = qr.dataSourceTable(tableNum(InventTable)).addDataSource(tableNum( EcoResProductLifecycleState)) ;

qbdEcoResLS.addLink(fieldNum(InventTable, ProductLifeCycleStateId), fieldNum(EcoResProductLifecycleState, StateId));

qbdEcoResLS.addRange(fieldNum(EcoResProductLifecycleState, ItemsHideDropDownPurch)).value(queryValue(NoYes::No));

systablelookup.parmQuery(qr);

systablelookup.addLookupfield(fieldNum(InventTable,ItemId));

systablelookup.addLookupfield(fieldNum(InventTable,NameAlias));

systablelookup.performFormLookup();

}

3. step three, this is the Main point, as we have created a new method, the same is to be registered. for doing this, we need to override the Init method as shown below.

public void init()

{

next init();

//<<controlname. -->PurchLine_ItemId

PurchLine_ItemId.registerOverrideMethod(methodStr(FormDataObject,lookup), FormMethodStr(PurchTable, overrideItemIdLookup));

}

Now build and run..