Change tracking through AIFChangeTrackingTable in X++

 

Change tracking through AIFChangeTrackingTable in x++ 

This document demonstrates how to get changes done in tables through data entity. You need changed records from multiple tables and save those records in our custom table.

For this, you need to create a data entity containing the tables, views or query to include required tables.

Then,

Navigate to Modules -> System Administration -> Data Management.



Click on Data Entities




Search for your entity. Then click the three dots on action pane and select Change tracking. It will show three options, select enable custom query.








After selecting the option, you will get these Infolog messages and change tracking will be changed to custom.










This is needed to functionally enable the change tracking for required entity.

The requirement is to get the  records that are created or modified in actual tables. For this you have to create a query same as data entity and with calling AIF change tracking code, you have to add ranges inside your query to get only those Recid(s) which are created or modified in original tables.

Now, the code required to get the changes:

         AifChangeTracking       aifChangeTracking;

        AifChangeTrackingTable  changeTrackingTable;

        new AifChangeTrackingPermission().assert();

        // entity name is specified in dictEntity.

        DictDataEntity dictEntity   = new DictDataEntity(tableNum(yourEntityName));

 

        aifChangeTracking = AifChangeTracking::constructFromDataEntityV2(dictEntity, DMFChangeTrackingType:        :CustomQuery, '', AifChangeTrackingType::SqlChangeTracking);

        changeTrackingTable = aifChangeTracking.getChanges(DateTimeUtil::utcNow());

 

        while select changeTrackingTable

        {

            hasRecord = true;

            if (changeTrackingTable.ChangedTableId == tableNum(VendTrans))

            {

                qbdsVendTrans.addRange(fieldNum(VendTrans, RecId)).value(queryValue(changeTrackingTable.Key                Field_RecId));

            }

            else  if (changeTrackingTable.ChangedTableId == tableNum(Vendsettlement))

            {

                qbdsVendSettlement.addRange(fieldNum(vendSettlement, RecId)).value(queryValue(changeTrackin                gTable.KeyField_RecId));

            }

            else  if (changeTrackingTable.ChangedTableId == tableNum(bankChequeTable))

            {

                qbdsBankChequeTable.addRange(fieldNum(BankChequeTable, RecId)).value(queryValue(changeTrack                ingTable.KeyField_RecId));

            }

           

        }

Now, running the query through QueryRun class and saving the list of records from AIF Change tracking.

        new AifChangeTrackingPermission().assert();

        // entity name is specified in dictEntity.

        DictDataEntity dictEntity   = new DictDataEntity(tableNum(yourEntityName));

 

        aifChangeTracking = AifChangeTracking::constructFromDataEntityV2(dictEntity, DMFChangeTrackingType:        :CustomQuery, '', AifChangeTrackingType::SqlChangeTracking);

        changeTrackingTable = aifChangeTracking.getChanges(DateTimeUtil::utcNow());

 

        while select changeTrackingTable

        {

            hasRecord = true;

            if (changeTrackingTable.ChangedTableId == tableNum(VendTrans))

            {

                qbdsVendTrans.addRange(fieldNum(VendTrans, RecId)).value(queryValue(changeTrackingTable.Key                Field_RecId));

            }

            else  if (changeTrackingTable.ChangedTableId == tableNum(Vendsettlement))

            {

                qbdsVendSettlement.addRange(fieldNum(vendSettlement, RecId)).value(queryValue(changeTrackin                gTable.KeyField_RecId));

            }

            else  if (changeTrackingTable.ChangedTableId == tableNum(bankChequeTable))

            {

                qbdsBankChequeTable.addRange(fieldNum(BankChequeTable, RecId)).value(queryValue(changeTrack                ingTable.KeyField_RecId));

            }

           

        }

In the end your method would be like:

        Query                       query;

        QueryRun                    qr;

        QueryBuildDataSource        qbdsVendTrans;

        QueryBuildDataSource        qbdsVendSettlement;

        QueryBuildDataSource        qbdsBankChequeTable;

        VendTrans                   vendTrans;

        VendSettlement              vendSettlement;

        BankChequeTable             bankChequeTable;

        FromDateTime                today;

        boolean                     hasRecord = false;

        BYRPaymentInformation       paymentInformation;

        vendTrans                   invoiceNumber;

       

        query = new Query(queryStr(BYRHylandPaymentInformationQuery));

        qbdsVendTrans = query.dataSourceTable(tablenum(VendTrans));

        qbdsVendSettlement = query.dataSourceTable(tablenum(VendSettlement));

        qbdsBankChequeTable = query.dataSourceTable(tablenum(BankChequeTable));

 

        today = DateTimeUtil::getSystemDateTime();

  

        DictDataEntity dictEntity   = new DictDataEntity(tableNum(BYRHylandPaymentInformationExportEntity))        ;

 

        AifChangeTracking       aifChangeTracking;

        AifChangeTrackingTable  changeTrackingTable;

 

        new AifChangeTrackingPermission().assert();

 

        aifChangeTracking = AifChangeTracking::constructFromDataEntityV2(dictEntity, DMFChangeTrackingType:        :CustomQuery, '', AifChangeTrackingType::SqlChangeTracking);

        changeTrackingTable = aifChangeTracking.getChanges(DateTimeUtil::utcNow());

 

        while select changeTrackingTable

        {

            hasRecord = true;

            if (changeTrackingTable.ChangedTableId == tableNum(VendTrans))

            {

                qbdsVendTrans.addRange(fieldNum(VendTrans, RecId)).value(queryValue(changeTrackingTable.Key                Field_RecId));

            }

            else  if (changeTrackingTable.ChangedTableId == tableNum(Vendsettlement))

            {

                qbdsVendSettlement.addRange(fieldNum(vendSettlement, RecId)).value(queryValue(changeTrackin                gTable.KeyField_RecId));

            }

            else  if (changeTrackingTable.ChangedTableId == tableNum(bankChequeTable))

            {

                qbdsBankChequeTable.addRange(fieldNum(BankChequeTable, RecId)).value(queryValue(changeTrack                ingTable.KeyField_RecId));

            }

           

        }

 

        qr = new QueryRun(query);

     

        if (hasRecord)

        {

            RecordInsertList recordList = new RecordInsertList(tableNum(CustomTableName));

 

            while (qr.next())

            {

                vendTrans = qr.get(tableNum(VendTrans));

                vendSettlement = qr.get(tableNum(VendSettlement));

                bankChequeTable = qr.get(tableNum(BankChequeTable));

                BYRHylVendTransInvoiceView vendTransInvoiceView = qr.get(tableNum(BYRHylVendTransInvoiceVie                w));

 

                paymentInformation.clear();

                paymentInformation.AccountNum = vendTrans.AccountNum;

                paymentInformation.Invoice = vendTransInvoiceView.Invoice;

                paymentInformation.ChequeNum = bankChequeTable.ChequeNum;

                paymentInformation.SettlementAmount = (vendSettlement.SettleAmountCur) * -1;

                paymentInformation.PaymMode = vendTrans.PaymMode;

                paymentInformation.PaymentDate = vendTrans.TransDate;

                paymentInformation.ChequeStatus = vendTrans.PaymMode == 'CHECK' ? enum2Value(bankChequeTabl                e.ChequeStatus) : 'Paid';

                paymentInformation.ExportStatus = NoYes::No;

                recordList.add(paymentInformation);

            }

            ttsbegin;

            recordList.insertDatabase();

            ttscommit;

        }


Comments

Post a Comment

Popular posts from this blog

How to customize segmented entry control

Image
 Customizing Segmented entry Lookup in out of the box form When you have a requirement to customize the lookup in ledger dimension segmented entry, following are the steps needed to perform to achieve this requirement. Step 1: Create field account type that contains multiple accounts like Customer, Vendor, Bank etc. Create field account number that will save the account number related to account type selected. for account number take EDT ledger dimension that would save the RecId of account number. Step 2:  Now paste your field along with account type in extension of out of the box form. And set the following properties of segmented control with auto declaration set to yes. Step 3:  Create a class to extend from dimension hierarchy class. Step 4:  Create condition based on your account type. Step 5:  Create extension class of SegmentedEntryControl class. Step 6:  Override the checkUseCutomLookup method. and specify for what account types custom lookup is re...
Read more

Integration from azure blob through x++

Image
IMPORT INTEGRATION FROM AZURE BLOB THROUGH X++   This document will contain all the steps needed to fetch file(s) from azure blob storage account container and process the data as per requirement. Azure Blob Storage is Microsoft's object storage solution for the cloud. Blob storage is optimized for storing massive amounts of unstructured data. Blob storage is designed for: ·        Serving images or documents directly to a browser. ·        Storing files for distributed access. ·        Streaming video and audio. ·        Writing to log files. ·        Storing data for backup and restore, disaster recovery, and archiving. ·        Storing data for analysis by an on-premises or Azure-hosted service.   Here is how to configure storage accounts: Step# 01: Navigate to URL: https...
Read more