The post shows how to create a new custom
table across both AX and the channel and setup sync(CDX) in Dynamics 365 Retail.
Changes are in AX tables, CDX, Channel DB
Setup steps:-
1. AX Customization:
- Create a new Table
called ISVRetailStoreHoursTable
- Enum field: Day, enum type: WeekDays, mandatory,
- Int field: OpenTime, extended data
type: TimeOfDay, mandatory,
- Int field: ClosingTime, extended data
type: TimeOfDay, mandatory,
- Int64 field: RetailStoreTable,
extended data type: RefRecId, mandatory
- Create a Foreign Key
Relation to RetailStoreTable
- Name: RetailStoreTable
- Cardinality: ZeroOne
- RelatedTable: RetailStoreTable
- Related table cardinality: ExactlyOne
- Relationship type: Association
- Contraint: Normal, name:
RetailStoreTable, Field: RetailStoreTable, Related field: RecId
- Populate some data by
running below job or manually enter some data.
class Temp_InsertData
{
/// <summary>
/// Runs the class with the specified
arguments.
/// </summary>
/// <param name =
"_args">The specified arguments.</param>
public static void main(Args _args)
{
ISVRetailStoreHoursTable
storeDayHoursTable;
RetailStoreTable storeTable;
Int64 numberOfDeletedRows;
// insert data for houston
select * from storeTable
where storeTable.StoreNumber == "HOUSTON";
print storeTable.Recid;
ttsBegin;
delete_from storeDayHoursTable
where storeDayHoursTable.RetailStoreTable == storeTable.RecId;
numberOfDeletedRows = storeDayHoursTable.RowCount();
print numberOfDeletedRows;
// yyyy-mm-ddThh:mm:ss
storeDayHoursTable.RetailStoreTable =
storeTable.RecId;
storeDayHoursTable.Day = WeekDays::Monday;
storeDayHoursTable.OpenTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T08:00:00"));
storeDayHoursTable.ClosingTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T20:00:00"));
storeDayHoursTable.insert();
storeDayHoursTable.RetailStoreTable =
storeTable.RecId;
storeDayHoursTable.Day = WeekDays::Tuesday;
storeDayHoursTable.OpenTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T08:00:00"));
storeDayHoursTable.ClosingTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T20:00:00"));
storeDayHoursTable.insert();
storeDayHoursTable.RetailStoreTable =
storeTable.RecId;
storeDayHoursTable.Day = WeekDays::Wednesday;
storeDayHoursTable.OpenTime
= DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T11:00:00"));
storeDayHoursTable.ClosingTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T21:00:00"));
storeDayHoursTable.insert();
storeDayHoursTable.RetailStoreTable =
storeTable.RecId;
storeDayHoursTable.Day = WeekDays::Thursday;
storeDayHoursTable.OpenTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T09:00:00"));
storeDayHoursTable.ClosingTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T20:00:00"));
storeDayHoursTable.insert();
storeDayHoursTable.RetailStoreTable =
storeTable.RecId;
storeDayHoursTable.Day = WeekDays::Friday;
storeDayHoursTable.OpenTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T08:00:00"));
storeDayHoursTable.ClosingTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T20:00:00"));
storeDayHoursTable.insert();
storeDayHoursTable.RetailStoreTable =
storeTable.RecId;
storeDayHoursTable.Day = WeekDays::Saturday;
storeDayHoursTable.OpenTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T10:00:00"));
storeDayHoursTable.ClosingTime = DateTimeUtil::time(DateTimeUtil::parse("2015-05-18T18:00:00"));
storeDayHoursTable.insert();
ttsCommit;
}
}
2. CDX CHANGE:-
- -
Create a new resource file with
all job, sub jobs and table information as below and save it as ISVRetailStoreHoursTable.xml file.
<RetailCdxSeedData ChannelDBMajorVersion="7" ChannelDBSchema="ext" Name="AX7">
<Jobs>
<!-- <Job
DescriptionLabelId="REX4520710" Description="Custom job"
Id="1070"/> -->
</Jobs>
<Subjobs>
<Subjob Id="ISVRetailStoreHoursTable" TargetTableSchema="ext" AxTableName="ISVRetailStoreHoursTable">
<ScheduledByJobs>
<ScheduledByJob>1070</ScheduledByJob>
</ScheduledByJobs>
<AxFields>
<Field Name="Day"/>
<Field Name="RetailStoreTable"/>
<Field Name="OpenTime"/>
<Field Name="ClosingTime"/>
<Field Name="RecId"/>
</AxFields>
</Subjob>
</Subjobs>
</RetailCdxSeedData>
-
Right-click the
project, and then select Add > New Item
-
In the Add New item dialog box,
select Resources, name the resource file RetailCDXSeedDataAX7_Demo,
and then select Add.
- In the Select a Resource file
dialog box, find the resource file that you created in step 2, and then select
Open.
-
Add a new class that should be
used to handle the registerCDXSeedDataExtension event. Search for the
RetailCDXSeedDataBase class in AOT, and then open it in the designer.
Right-click the registerCDXSeedDataExtension delegate, and then select Copy
event handler.
-
Go to the event handler class
that you created, and paste the following event handler code into it.
class RetailCDXSeedDataAX7EventHandler
{
/// <summary>
///
/// </summary>
/// <param
name="originalCDXSeedDataResource"></param>
/// <param
name="resources"></param>
[SubscribesTo(classStr(RetailCDXSeedDataBase), delegateStr(RetailCDXSeedDataBase, registerCDXSeedDataExtension))]
public static void RetailCDXSeedDataBase_registerCDXSeedDataExtension(str
originalCDXSeedDataResource, List resources)
{
if
(originalCDXSeedDataResource == resourceStr(RetailCDXSeedDataAX7))
{
resources.addEnd(resourceStr(RetailCDXSeedDataAX7_Demo));
}
}
}
-
Whenever the Retail
initialization class runs, it looks for any extension that implements this
handler. If an extension is found, the runtime will also initialize the custom
information that is found in the resource file.
-
Before you add your custom
resource to the list, you must verify that the originalCDXSeedDataResource
resource that is being processed is RetailCDXSeedDataAX7. Otherwise, you might
cause unintended results.
-
Navigate to Retail >
Headquarters setup > Retail scheduler and click on Initialize retail
scheduler.
-
In the dialog box that appears,
select Delete existing configuration.
-
Select OK to start the
initialization.
-
When the initialization is
completed, the CDX scheduler jobs, subjob definitions, and distribution
schedules are updated by using the original RetailCDXSeedDataAX7 resource
and the customized RetailCDXSeedDataAX7_Demo resource.
3. Verify Changes:
-
Navigate to Retail > Headquarters setup > Retail
scheduler > Scheduler subjobs , here we can see ISVRetailStoreHoursTable is added under Scheduler subjobs and
scheduled by 1070 as specified in
resource file.
4. Channel DB:
-
Run the below query to create
table in Channel database, here we should create custom tables in ext schema as
we are not allowed to override the standard.
-
The advantage of using ext
schema , while DB upgrade we will not face any conflict .
-- Create the
extension table to store the custom fields.
IF (SELECT OBJECT_ID('[ext].[ISVRETAILSTOREHOURSTABLE]')) IS NULL
BEGIN
CREATE TABLE [ext].[ISVRETAILSTOREHOURSTABLE](
[RECID] [bigint] NOT NULL,
[DAY] [int] NOT NULL DEFAULT ((0)),
[OPENTIME] [int] NOT NULL DEFAULT ((0)),
[CLOSINGTIME] [int] NOT NULL DEFAULT ((0)),
[RETAILSTORETABLE] [bigint] NOT NULL DEFAULT ((0)),
CONSTRAINT [I_ISVRETAILSTOREHOURSTABLE_RECID] PRIMARY KEY CLUSTERED
(
[RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [ext].[ISVRETAILSTOREHOURSTABLE] WITH CHECK ADD CHECK (([RECID]<>(0)))
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::[ext].[ISVRETAILSTOREHOURSTABLE] TO [DataSyncUsersRole]
- Create
view for the table we created that joins the original table on the channel side
and the extension table on the channel side is created. This view is required
so that CDX can correctly download and push the records from Retail HQ tables
to the channel extension table back.
-- Create the customer
extension view that is accessed by CRT to query the custom fields.
IF (SELECT OBJECT_ID('[ext].[ISVRETAILSTOREHOURSVIEW]')) IS NOT NULL
DROP VIEW [ext].[ISVRETAILSTOREHOURSVIEW]
GO
CREATE VIEW [ext].[ISVRETAILSTOREHOURSVIEW] AS
(
SELECT
sdht.DAY,
sdht.OPENTIME,
sdht.CLOSINGTIME,
sdht.RECID,
rst.STORENUMBER
FROM [ext].[ISVRETAILSTOREHOURSTABLE] sdht
INNER JOIN [ax].RETAILSTORETABLE rst ON rst.RECID = sdht.RETAILSTORETABLE
)
GO
GRANT SELECT ON OBJECT::[ext].[ISVRETAILSTOREHOURSVIEW]
TO [UsersRole];
GO
5. Verify CDX:
-
run 1070 job full sync (channel data group)
-
check Download sessions and channel DB that the data arrived
