Data entity error "Change Tracking Query error in D365 Finance and Operations Data Export" D365FO

Change Tracking Query error in D365 Finance and Operations Data Export

Recently there was an issue with Change Tracking for BYOD. We keep getting the following error even after Change Tracking is enabled for the data entity.

"Incremental push is not supported for entity X, as change tracking query is not enabled on it."

Solution :

After debugging I found that the system will check if the record exists in the table "AifSqlCdcEnabledTables" with the field TableName and return int (1/0).

Run the below query in SQL to find if the list contains your table.

 select RecId, * from AifSqlCdcEnabledTables       where AifSqlCdcEnabledTables.TableName = 'XXXXX'

In my case, the Primary table was missing and causing the issue, whereas the secondary tables are present in the list. So I have changed the Entity to make the Primary Data source as secondary and secondary as Primary and the issue is fixed.

SQL Command to Enable and Disable Change Tracking (SQL Server) + D365 FO

 Below are the commands used to enable/disable change tracking in the SQL. 

1. Enable change tracking on the database

ALTER DATABASE AXDBNAME  

SET CHANGE_TRACKING = ON  

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

2. Enable Change Tracking for a Table

ALTER TABLE TableName

ENABLE CHANGE_TRACKING  

WITH (TRACK_COLUMNS_UPDATED = ON)

3. Disable Change Tracking for a Database 

ALTER DATABASE AdventureWorks2012  

SET CHANGE_TRACKING = OFF

4. Disable Change Tracking for a Table

ALTER TABLE TableName  

DISABLE CHANGE_TRACKING;

Add address to Customer AX 2012 X ++

 //How to create a new customer address using x++ code

static void MK_createCustomerAddress(Args _args)

{

    LogisticsPostalAddress address;

    LogisticsLocation       location;

    LogisticsAddressCountryRegion conuntry;

    LogisticsAddressState           state;

    DirPartyPostalAddressView   addressView;

    DirPartyLocation    partylocation;

    DirParty dirParty;

    DirPartyTable   dirPartyTable;

    CustTable   custTable;

    container  roles;

    LogisticsPostalAddressEntity    postalAddressEntity;

    LogisticsPostalAddressView      paddressView;

    date dVF,dvt;

    select custTable

          join  dirPartyTable where custtable.Party ==   dirPartyTable.RecId

          //join  location where dirPartyTable.PrimaryAddressLocation == location.RecId

                                //&& location. == "Mrs Carol Davis"

           //join  address  where address.Location == location.RecId

        && custtable.AccountNum == "C012436877";

    if (dirPartytable.RecId)

    {

        conuntry = LogisticsAddressCountryRegion::findByISOCode('GB');

        //state = LogisticsAddressState::find(conuntry

        location.Description = 'Mr paddressView';

        location.IsPostalAddress = true;

        location.insert();

        address.Street = "Mr paddressView 909 Market St 3";

        address.ZipCode = "LE65 1AH";

        address.City = "Ashby";

        //address.State = 

        address.CountryRegionId = "GBR";

        address.Location = location.RecId;

        dvf = str2Date("08/9/2022", 213);

        dvt = str2Date("08/9/2029", 213);

        address.ValidFrom = DateTimeUtil::newDateTime(dvf,0);

        address.ValidTo = DateTimeUtil::newDateTime(dateMax(),0);

        //partylocation.Party = CustTable.Party;

        addressView.initFromPostalAddress(address);

        addressView.Location = address.Location;

        addressView.State = 'Derbyshire';

        //addressView.LocationName = logisticsLocation.Description;

        addressView.IsPrimary = true;

        addressView.LocationName = location.Description;

        addressView.Party = CustTable.Party;

       // addressview.initFromPostalAddress(address);

        

        paddressView.initFromPartyPostalAddressView(addressView);

        //paddressView.insert();

        postalAddressEntity = LogisticsPostalAddressEntity::construct();

        postalAddressEntity.createPostalAddress(paddressView);

        

        DirParty = DirParty::constructFromPartyRecId(CustTable.Party);

        roles = [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Business).RecId];

        DirParty.createOrUpdatePostalAddress(addressView,roles);

        //

        info(strFmt("Customer %1 Description %2 Address %3",custtable.AccountNum,addressView.LocationName,address.Street));

    }

         info("done");

 } 

Custom lookup on Form in D365

 [Form]

public class MKCustomLookupForm extends FormRun

{

    [DataSource]

    class MKCustomDSTable

    {

        [DataField]

        class FieldIdCust 

        {

            /// <summary>

            /// </summary>

            /// <param name = "_formControl">Form control</param>

            /// <param name = "_filterStr">Fileter condition, if any</param>

            public void lookup(FormControl _formControl, str _filterStr)

            {

                CustTable   custTable;

                 SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(MKTable), _formControl);

        Query query = new Query();

        QueryBuildDataSource queryBuildDataSource;

        queryBuildDataSource = query.addDataSource(tablenum(MKTable));

        queryBuildDataSource.addSortField(fieldNum(MKTable, FieldId), SortOrder::Ascending);

        sysTableLookup.addLookupfield(fieldnum(MKTable, FieldId), true);

        sysTableLookup.addLookupfield(fieldnum(MKTable, FieldName));

        sysTableLookup.parmQuery(query);

        //logic

        sysTableLookup.parmTmpBuffer(fieldList);

        sysTableLookup.performFormLookup();

            }

        }

    }

}

Validate field method for Form DataSource FormDataFieldEventType::Validating

   /// <summary>

    ///

    /// </summary>

    /// <param name="sender"></param>

    /// <param name="e"></param>

    [FormDataFieldEventHandler(formDataFieldStr(VendTable, VendTable, ProcessToProphet), FormDataFieldEventType::Validated)]

    public static void ProcessToProphet_OnValidated(FormDataObject sender, FormDataFieldEventArgs e)

    {

        var dataObject = sender as FormDataObject;

        var args = e as FormDataFieldCancelEventArgs;

        DirPartyBaseType        dirPartyBaseTypeOrg;

        FormDataSource         dataSource = sender.datasource();

        FormRun    element = dataSource.formRun();

        FormControl     dirtype = element.design(0).controlName("type");

        str  dirPartyBaseTypestr =   dirtype.valueStr();

        dirPartyBaseTypeOrg = str2Enum(dirPartyBaseTypeOrg , dirPartyBaseTypestr);

       // info (strFmt ("%1 ", dirtype.valueStr()) )    ;

      //  DirPartyBaseType selected = any2Enum(dirtype.valueStr());

        if (args != null && dataObject != null)

        {

            var vendTable_ds = dataObject.datasource() as FormDataSource;

            if (vendTable_ds != null)

            {

                VendTable vendRec = vendTable_ds.cursor() as VendTable;

                if (vendRec.RecId > 0)

                {

                    if (dirPartyBaseTypeOrg != DirPartyBaseType::Organization)

                    {

                        boolean doCancel = !checkFailed("Not Organization");

                        args.cancel(doCancel);

                    }

                }

            }

        }

    }

FormDataSourceEventHandler to enable field

 class IPLVendTableTriggerForm_EventHandler

{

    /// <summary>

    ///

    /// </summary>

    /// <param name="sender"></param>

    /// <param name="e"></param>

    [FormDataSourceEventHandler(formDataSourceStr(VendTable, VendTable), FormDataSourceEventType::Activated)]

    public static void VendTable_OnActivated(FormDataSource sender, FormDataSourceEventArgs e)

    {

        VendTable       vendTable = sender.cursor();

        FormDataSource  vendTable_ds = sender.formRun().dataSource("VendTable");

        FormRun         element = sender.formRun();

        FormControl     processTo = element.design(0).controlName("BISTrigger_ProcessTo");

        if(vendTable.ProcessTo == NoYes::Yes)

        {

            processTo.enabled(false);

        }

        else

        {

            processTo.enabled(true);

        }

    }

}

Find Missing Indexes SQL query

 USE YourDatabase

GO

 

SELECT db.[name] AS [DatabaseName]

    ,id.[object_id] AS [ObjectID]

,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]

    ,id.[statement] AS [FullyQualifiedObjectName]

    ,id.[equality_columns] AS [EqualityColumns]

    ,id.[inequality_columns] AS [InEqualityColumns]

    ,id.[included_columns] AS [IncludedColumns]

    ,gs.[unique_compiles] AS [UniqueCompiles]

    ,gs.[user_seeks] AS [UserSeeks]

    ,gs.[user_scans] AS [UserScans]

    ,gs.[last_user_seek] AS [LastUserSeekTime]

    ,gs.[last_user_scan] AS [LastUserScanTime]

    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.

    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

    ,gs.[system_seeks] AS [SystemSeeks]

    ,gs.[system_scans] AS [SystemScans]

    ,gs.[last_system_seek] AS [LastSystemSeekTime]

    ,gs.[last_system_scan] AS [LastSystemScanTime]

    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]

    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.

    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]

    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE

        WHEN id.[equality_columns] IS NOT NULL

            AND id.[inequality_columns] IS NOT NULL

            THEN '_'

        ELSE ''

        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE

        WHEN id.[equality_columns] IS NOT NULL

            AND id.[inequality_columns] IS NOT NULL

            THEN ','

        ELSE ''

        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]

    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]

FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)

INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]

INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]

INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]

WHERE  db.[database_id] = DB_ID()

--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'

ORDER BY ObjectName, [IndexAdvantage] DESC

OPTION (RECOMPILE);