Truncate Entity Data Functionality in Dynamics 365 Finance & Operations

When managing data in your Dynamics 365 Finance & Operations system, it’s crucial to understand both the benefits and potential risks associated with the truncate entity data functionality.

This functionality is incredibly valuable for updating large volumes of data, such as hundreds or thousands of records. However, it must be used with great caution.

While you might already be familiar with data management‘s capability to import data within Dynamics 365, the truncate entity data functionality goes a step further. It allows you to upload and replace existing data within a data entity, rather than merely adding new records.

This feature is particularly beneficial during the initial configuration of Dynamics 365, enabling the replacement of specific setup data. It’s also useful for performing mass deletions and replacements of records when an Excel add-in data connector for import is not available.

Below is an example demonstrating how to use this functionality, along with some important caveats to consider.

Table of Contents:

• Using Truncate Entity Data Functionality During a Data Management Import
• Truncate Entity Data Functionality – Duplicate Values
• What Does the Truncate Entity Data Functionality Do?
• Risks and Warnings With the Truncate Feature

Note: Even though most customers, commentators, and consultants still call it Dynamics 365 Finance & Operations or D365FO, Microsoft now technically licenses it as Dynamics 365 Finance and Supply Chain Management.

Using Truncate Entity Data Functionality During a Data Management Import

Consider a situation where you need to add new frequently used terms of payment to your system while simultaneously removing outdated terms of payment no longer used.

Due to the large number of records involved, it’s preferable not to handle this manually. Instead, you can use a programmatic method to ensure a consistent and efficient process.

To accomplish this, we will use the truncate entity data functionality. In the example below, we will export the current terms of payment, modify our source file, and then import the updated data back into the system.

Important Note:

The truncate entity data functionality removes all existing data within the data entity, so it should be used with caution and thoroughly tested first in a non-production environment.

Step 1 – Export the Current Terms of Payment Data

To begin, we will use the data management export functionality to export the current terms of payment data. This process will generate a correctly formatted template file that we can use to record our changes for import.

To export the terms of payment data, follow these steps:

1. Navigate to workspaces
2. Select “Data Management”
3. Choose “Export”

In the data management export form, create a new export project for the terms of payment data entity.

1. Select “New”
2. Enter a group name
3. Enter a description
4. Select “Save” when done
5. Click “Add entity”
6. Select terms of payment as the entity name
7. Select Excel as the target data format
8. Click “Add”

Once created, select “Export” along the top row.

Once the export execution status has changed to succeeded, click “Download File.”

We now have a correctly formatted terms of payment data entity source file.

We can now proceed with making the desired additions, deletions, and modifications to this file in preparation for importing.

Step 2 – Import the Updated Terms of Payment Data

After updating the terms of payment source file, we’ll use the data management import functionality to import our source file changes.

To import the terms of payment data, follow these steps:

1. Navigate to workspaces
2. Select “Data Management”
3. Click “Import”

In the data management import form, create a new import project for the terms of payment data entity.

1. Select “New”
2. Enter a group name
3. Enter a description
4. Select “Yes” to truncate entity data
5. Select “Save” when done
6. Select “Add File”
7. Select Excel as the source data format
8. Select terms of payment as the entity name
9. Select the updated terms of payment source file in the upload data file
10. Select “Close”

Once created, select “Import.”

Once the import execution status has changed to succeeded, you are ready to validate the data imported.

Important Note:

Using the truncate entity data setting during the import process will remove all existing data within the data entity. It should be used with caution and thoroughly tested first in a non-production environment.

Warning:

Note the system warning provided when this functionality is selected: “Choosing to truncate entity data will result in deletion of data in the selected data entities before new data is imported.”

Step 3 – Validate the Terms of Payment Data Imported

To validate the results of the terms of payment import process, navigate to the terms of payment setup form in the payment setup section of the Accounts Payable or Accounts Receivable modules.

Review to confirm your additions, deletions, and changes that have been applied as desired.

Important Note:

If the truncate entity data functionality is used for setup values that could be linked to or used by other master records, such as customers, vendors, or items, a thorough review of those records should be completed and updated accordingly.

Truncate Entity Data Functionality – Duplicate Values

Certain data entities within Dynamics 365 Finance & Operations, such as the address setup entity with zip/postal codes, may generate duplicate values if the truncate entity data functionality is not utilized.

To see this, navigate to organization administration, select global address book, click on addresses, and navigate to address setup.

You see we have four zip codes – 98052, 98053, 98073, and 98074 – in the ZIP/postal codes address setup. These ZIP/postal codes do not have values for city, state, or county.

If we use data management to import records with correctly defined city, state, and county values, but do not select the truncate entity data option during the import process, we will create duplicate values within the data entity.

Here’s the ZIP/postal codes import source file:

The following screenshot shows the ZIP/postal codes import project with truncate entity data set to “No.”

Import Result:

Since we did not use the truncate entity data functionality to remove existing data during the import process, our result was duplicated values for the 98052, 98053, 98073, and 98074 in the ZIP/postal codes address data entity.

Important Note:

In this example, if other values existed in the ZIP/postal codes data entity, they would also be removed when using the truncate entity data feature, unless they are included in the import source file. As noted, using the truncate entity data feature during the import process will remove all existing data within the data entity. It should be used with caution and thoroughly tested first in a non-production environment.

What Does the Truncate Entity Data Functionality Do?

The truncate entity data functionality during the data management import process in Dynamics 365 Finance & Operations is a powerful feature used for replacing existing data within data entities.

The truncate entity data functionality is particularly useful during the initial configuration of Dynamics 365 Finance & Operations, as it allows for the replacement of specific setup data when there is no Excel add-in data connector available for importing.

• Data replacement: The truncate entity data functionality allows you to overwrite existing data in a table with new data from an import file. This is particularly useful when you need to clean out existing tables and replace them with updated information.
• Managing setup data: The truncate entity data functionality is ideal for managing setup data, where you might need to replace large volumes of data and facilitate a consistent and efficient approach to data import.
• Avoiding duplicate entries: By using the truncate entity data functionality, you can prevent the creation of duplicate entries in your tables. Instead of adding new lines, the truncate entity data functionality removes all existing data and then inserts the new data from your file.

Risks and Warnings With the Truncate Feature

In general, I would advise that only your system administrator or a similar power user – or a specialist from your Dynamics 365 Finance & Operations Partner – should use it.

• Non-production environment: We recommend you perform a test of the truncate entity data functionality in a non-production environment before applying it in a live system to avoid unintended data loss.
• Transactional data: Exercise extreme caution when using the truncate entity data functionality with data entities that have transactional data linked to master records, such as customers, vendors, or items. Altering this data could lead to data integrity issues if transactions are recorded against the data being modified.

As a Dynamics 365 Finance & Operations Partner, Encore provides training, support, and integrations to help companies leverage the benefits of their enterprise software systems. Learn more about our Finance & Operations support service.