Vendor master data sync fails in accounts payable - duplicate records created during batch import

[samantha_solver]

We’re experiencing critical issues with vendor master data synchronization in our AP module after upgrading to 10.0.41. Our batch import process through the Data Management Framework is creating duplicate vendor records despite having deduplication keys configured.

The problem occurs during nightly batch imports from our legacy ERP system. We’ve configured the Vendor entity with Tax ID as the primary deduplication key, but the system still creates new vendor records instead of updating existing ones. This is causing payment matching failures and creating significant reconciliation work.

Our current entity configuration:

Entity: VendorV2Entity
Key fields: VendorAccount, TaxID
Skip staging: No
Validation: Enabled

We’ve verified that Tax IDs in the source data are properly formatted and match existing records. The staging table shows correct data before import, but the final import step creates duplicates. Has anyone encountered similar issues with vendor master data deduplication in the Data Management Framework?

[nancy_admin]

Building on the previous suggestions, you should also review your staging validation configuration. The Data Management Framework has a specific validation phase that occurs before the import step, and this is where you can catch data quality issues that lead to duplicates.

In your case, add a staging table validation rule that checks for existing vendors by normalized Tax ID before allowing the record to proceed to import. This gives you a chance to flag potential duplicates and handle them through an exception workflow rather than blindly creating new records. The validation can query the VendTable directly and mark staging records accordingly.

[nancy_admin]

Let me provide a comprehensive solution that addresses all the key points we’ve discussed:

1. Data Management Framework Entity Configuration: First, reconfigure your VendorV2Entity to properly handle alternate keys. Navigate to Data Management > Entities > VendorV2Entity and add TaxID as an alternate key in the entity properties. This is separate from the field mapping - you need both configured.

Entity settings:
- Primary key: VendorAccount
- Alternate keys: TaxRegistrationNumber (Tax ID field)
- Update mode: Update existing records

2. Deduplication Key Mapping for Vendor Master: The critical issue is your Tax ID format mismatch. Implement a transformation in your source data extraction that normalizes Tax IDs before they reach D365. If you’re using SQL Server as your source, apply this transformation:

SELECT
  VendorID,
  REPLACE(REPLACE(TaxID, '-', ''), ' ', '') AS TaxRegistrationNumber,
  VendorName,
  ...
FROM SourceVendors

This strips all dashes and spaces, ensuring exact matching with D365’s stored format.

3. Tax ID Validation and Normalization: Add a pre-processing validation step in your DMF project. Create a custom staging table validation that:

• Normalizes incoming Tax IDs to remove formatting
• Queries VendTable to check for existing vendors with matching normalized Tax IDs
• Marks staging records with a validation status (NEW/UPDATE/DUPLICATE)
• Prevents import of records marked as DUPLICATE until manually reviewed

You can implement this as a staging table method or a separate validation query that runs before the import step.

4. Batch Import Staging Validation: Review your batch job configuration and enable these critical settings:

• Enable “Validate on import” in the entity properties
• Set “Error handling” to “Stop on first error” during testing
• Add a post-import validation report that identifies any duplicate Tax IDs created
• Configure the batch job to run vendor imports sequentially, not in parallel

Additional Recommendations:

• Create a cleanup script to identify and merge existing duplicates before implementing the fix
• Set up a recurring batch job that scans for duplicate Tax IDs and alerts your team
• Document the Tax ID normalization rules in your data governance procedures
• Test the new configuration with a small subset of vendors in a non-production environment first

Root Cause Summary: Your issue stems from three compounding factors: (1) alternate keys not properly configured at the entity level, (2) Tax ID format inconsistency between systems preventing successful matching, and (3) missing staging validation that would catch duplicates before import. Addressing all three is essential - fixing just one or two won’t fully resolve the problem.

The normalization transformation is your quickest win and should immediately reduce duplicate creation. The alternate key configuration ensures the DMF uses Tax ID for matching. The staging validation provides a safety net for edge cases.

Let me know if you need help with the specific validation query syntax or if you encounter any issues during implementation.

[guptaarch]

Thanks for the response. I checked the alternate key configuration and you’re right - we only had VendorAccount defined. However, I’m confused about the Tax ID normalization aspect. Our source system stores Tax IDs with dashes (XX-XXXXXXX format) while D365 stores them without formatting. Could this be causing the matching to fail even if we add TaxID as an alternate key?

[vikrambuilder]

VendorV2Entity is the correct choice for 10.0.41. VendorV3Entity isn’t available in your version - it’s still in preview for later releases. Stick with V2 but make sure you’re using the latest entity definition from your version’s metadata.

For your immediate problem, here’s what I recommend checking in your batch job configuration as well - the execution order matters. If you’re importing vendor groups, payment terms, or other related entities in the same batch, make sure vendors are imported after all their dependencies. I’ve seen cases where timing issues in parallel execution cause the system to fail key lookups and default to creating new records.

[jacob_master]

Absolutely, that’s your smoking gun. The alternate key matching in DMF is extremely literal - it won’t normalize or transform values during the comparison. If your source has “12-3456789” and D365 has “123456789”, they won’t match.

You have two options: implement a transformation in your source query to strip formatting before import, or add a custom validation script in the staging table that normalizes the Tax ID before the import step executes. I’d recommend the source transformation approach as it’s cleaner and doesn’t add processing overhead to the import job. You’ll also want to ensure your batch import validation rules check for this formatting consistency.

[patricia_arch]

I’ve seen this behavior before. The issue typically stems from how the Data Management Framework handles alternate keys versus natural keys. Even though you’ve set TaxID as a key field, the system primarily uses VendorAccount for matching during updates.

Check your entity mapping configuration - specifically the “Alternate key” setting in the entity properties. You need to explicitly define TaxID as an alternate key at the entity level, not just as a key field in the mapping. Also verify that your Tax ID normalization is consistent (removing spaces, dashes, etc.) between source and target systems.

[nancy_admin]

This is really helpful. One more question - should we be using the VendorV2Entity or is there a newer entity that handles deduplication better? I’ve seen references to VendorV3Entity in some documentation but I’m not sure if it’s available in our version or if it would solve this specific issue with Tax ID matching.