Duplicate Customer (TradingPartner) Name error

#1

Hello,

I am encountering an error that on the surface is puzzling to me, but it may just need some clarification.

When we run our integration of Customers from our Accounting ERP to Quantify, we occasionally run into this error:

DataPortal.Update failed (System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object ‘dbo.TradingPartner’ with unique index ‘UQ_TradingPartner_Name’. The duplicate key value is (SOWLES STEEL ERECTORS, 6).
The statement has been terminated.
at Avontus.Rental.Library.BusinessPartner.DataPortal_Update()
at dm(Object , Object[] )
at Avontus.Core.Reflection.MethodCaller.CallMethod(Object obj, DynamicMethodHandle methodHandle, Object[] parameters)
ClientConnectionId:0e4d9f7f-5764-45d4-a564-2e6dab529151
Error Number:2601,State:1,Class:14)

This seems to imply that you cannot have two Customers of the same name - is that really the case? If so, how do we handle instances where there are two unique people named ‘John Smith’ that we are selling products to?

If that is not actually the case, how does Quantify actually evaluate if a Customer is a duplicate? I can provide the exact code we are using for the Customer upserts if needed.

Thanks so much,
Alex

#2

Hello,

Just looking for an update on this item. This also (expectedly) happens with Vendors, since they are also a Trading Partner. We will need to know exactly how the duplicate/unique key check(s) work for these entities, so that we can plan accordingly in our code to ensure all of our Customers and Vendors make it into Quantify successfully.

Thank you,
Alex

#3

Hi Alex.

In Quantify, Customer and Vendor names are expected to be unique texts per type, by design ↓

image
image.png1032×570 25.7 KB

Thanks for posting.

#4

Hello,

Thank you for the info/update on this question.

This is very interesting. What is the recommended approach for handling Customers/Vendors of the exact same name, e.g. a person with a very common first and last name (e.g. John Smith, Tom Johnson, etc.)?

We have numerous instances of these in our accounting ERP, and typically de-dupe based on a combination of Name and Address. It seems that will clash with the way Quantify is architected.

#5

Hi Alex.

Technically, a Customer name is unique, however you can add any number of people under a Customer with same first and last names (like Paul John under ABC Corp in the example ↓). Would you like sample code to do this?

image
image.png693×534 16.2 KB

Thanks for posting.

#6

This would definitely be useful, as that may solve some of our scenarios. If you could provide sample code to do that I would greatly appreciate it.

There is a subset that I think may not be solved with this, though, for example if the Customer or Vendor is literally just a person, not a company, and there are multiple person-customers/vendors of the same name. I am not sure how often this happens in our accounting system, I will have to check. I imagine a lot of the Vendors would be employees that are getting reimbursed, so that likely wouldn’t apply in Quantify. I’ll do some research on that side in the meantime.

Thanks so much for the help with this,
Alex

#7

Hi Alex.

Sample code below adds a customer with multiple customer-users.

        BusinessPartner customer = BusinessPartner.NewBusinessPartner(PartnerTypes.Customer);
        customer.Name = "Test Corp";
        customer.EmailAddress = "testcorp@avontus.com";
        customer.IsActive = true;

        AvontusUser contact1 = customer.Contacts.AddNew();
        contact1.UserType = UserTypes.Customer;
        contact1.FirstName = "Paul";
        contact1.LastName = "John";
        contact1.Username = "pauljohn1";

        RoleList roles = RoleList.GetRoles(false, true);
        contact1.RoleID = roles.GetRoleByType(AvontusRoleType.Customer).RoleID;
        
        AvontusUser contact2 = customer.Contacts.AddNew();
        contact2.UserType = UserTypes.Customer;
        contact2.FirstName = "Paul";
        contact2.LastName = "John";
        contact2.Username = "pauljohn2";
        contact2.RoleID = contact1.RoleID;

        customer.Save();

Thanks for posting.