Monday, October 31, 2022

How to connect UAT DB with Dev instance

Request for DB access from LCS from the following page

You will get the DataBase name, Server name, User ID, and Password from there.

Now enable access to your dev machine from the following

When you click on "Enable access" a new form will popup like this

Now click on the add sign and a new form will pop up like this one, add any name in the name section, and in the source address prefix put in the IP address of the Dev machine.

Now open the "web.config" file from the following path

K:\AosService\WebRoot folder

Now change the DataBase nameServer nameUser ID, and Password from the UAT to the file.

Now do an IIS reset.

Friday, October 28, 2022

Calling modified field from code x++

SalesLine.modifiedField(fieldNum(SalesLine, ShippingDateConfirmed ));

Friday, October 21, 2022

"Set based processing" in Data entity in Dynamics 365 F&O

Set-based operations are used to perform bulk operations more efficiently using record set operations. Data entity must Support set-based SQL operation.

Set-based operations cannot be used on entities using the following -

a. Views

b. Unions

c. Time-State Enabled

d. Temporary or TempDB

e. Mixed SaveDataPerCompany

f. Fields in AK(Alternate Key) not mapped in entity

g. Aggregations

h. Composite Data Sources

i. Joins other than inner and outer join

Now if you want to turn on the set based operation but want to run the data methods or perform some operations while data is being imported with the help of data entity, then have a look at the below code

public static container copyCustomStagingToTarget(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)


        MCOEmployeeDataTableStaging     staging;

        MCOEmployeeDataTable            EmployeeDataExisting;

        MCOEmployeeDataTable            EmployeeDataInserted;

        MCOEmployeeDataTable            EmployeeDataUpdated;

        Int64 insertedCount, updatedCount;

        boolean successful;





            // Validate




            // Update records

            update_recordset EmployeeDataUpdated

                setting FirstName = staging.FirstName,

                    LastName = staging.LastName,

                    PersonnelNumber = staging.PersonnelNumber,

                    EmploymentStartDate = staging.EmploymentStartDate,

                    EmploymentEndDate = staging.EmploymentEndDate,

                    Position = staging.Position,

                    PositionDescription = staging.PositionDescription,

                    AssignmentStartDate = staging.AssignmentStartDate,

                    Email = staging.Email,

                    Status = staging.Status,

                    Log = staging.Log,

                    AddressBook = staging.AddressBook,

                    PayrollCompanyCode = staging.PayrollCompanyCode,

                    PositionStatus = staging.PositionStatus,

                    RehireDate = staging.RehireDate

                    join staging

                        where staging.PersonnelNumber == EmployeeDataUpdated.PersonnelNumber

                            && staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

                            && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId

                            && staging.TransferStatus== DMFTransferStatus::NotStarted;

            if (EmployeeDataUpdated.RowCount() > 0)


                // Only add records updated instead of -1 when no records returned.

                updatedCount = EmployeeDataUpdated.RowCount();


            // Insert the staging records into the physical tables.


            insert_recordset EmployeeDataInserted (FirstName,LastName,PersonnelNumber,EmploymentStartDate,EmploymentEndDate,Position,PositionDescription,AssignmentStartDate,Email,Status,Log,AddressBook,PayrollCompanyCode,PositionStatus,RehireDate)

                select FirstName,LastName,PersonnelNumber,EmploymentStartDate,EmploymentEndDate,Position,PositionDescription,AssignmentStartDate,Email,Status,Log,AddressBook,PayrollCompanyCode,PositionStatus,RehireDate from staging

                    where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

                        && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId

                        && staging.TransferStatus == DMFTransferStatus::NotStarted

                notexists join EmployeeDataExisting

                    where EmployeeDataExisting.PersonnelNumber == staging.PersonnelNumber

                        && EmployeeDataExisting.FirstName == staging.FirstName

                        && EmployeeDataExisting.LastName == staging.LastName;

            if (EmployeeDataInserted.RowCount() > 0)


                // Only add records inserted instead of -1 when no records returned.

                insertedCount = EmployeeDataInserted.RowCount();


            // Update the staging table to specify records were successfully processed.

            update_recordset staging

                    setting TransferStatus = DMFTransferStatus::Completed

                    where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

                        && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId

                        && staging.TransferStatus == DMFTransferStatus::NotStarted;

            successful = true;





            if (!successful)


                // An error occurred, move all rows to error state. Note this is done in a finally

                // block instead of a catch block so that the exception will automatically propegate

                // to the calling code.

                update_recordset staging

                    setting TransferStatus = DMFTransferStatus::Error

                    where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

                        && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId

                        && staging.TransferStatus == DMFTransferStatus::NotStarted;



        return [insertedCount, updatedCount];


    private static void resetTransferStatus(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)


            MCOEmployeeDataTableStaging staging;

            update_recordset staging

                setting TransferStatus = DMFTransferStatus::NotStarted

                where staging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup

                    && staging.ExecutionId == _dmfDefinitionGroupExecution.ExecutionId

                    && staging.TransferStatus != DMFTransferStatus::NotStarted

                    && staging.TransferStatus != DMFTransferStatus::Completed;


Saturday, October 8, 2022

How to connect to SFTP using FileZilla

SFTP (SSH File Transfer Protocol) is a secure file transfer protocol. It runs over the SSH protocol. It supports the complete security and authentication functionality of SSH.

Download and install FileZilla from the following Link

Once installed open FileZilla

Now fill in the Host, username, password, and port

Once done click on "Quickconnect"

Now you will see all the files on the server in the below-mentioned area