Open Forum

Expand all | Collapse all

# GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

  • 1.  # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 03, 2019 01:44 PM
      |   view attached
    Hello all,

    I will explain how I arrived at this issue.
    I was using Microsoft Dynamics GP> Tools> Utilities> Sales> Customer Combiner and Modifier> Customer Modifier to modify a group of customer names. (2) of the customers returned an error on the same item number. (I will attach the error)

    Through some trial and error within Test Company, we deleted the item number from Cards> Sales> Customer Items for one of the customers with the issue.
    We ran the Customer Modifier on the customer again in Test Company. We received the same error on a different item number.

    The (2) customers with the issues have many Customer Items assigned to their accounts.
    We are looking for a way to convert Customer Items assigned to a customer to remove the error of the duplicate key.
    Is there a way to convert those items or can you provide the best process to handle the items for these customers?

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------


  • 2.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 03, 2019 08:14 PM

    Hi Mark,
    unfortunately, these excellent tools have their limits.  In some cases, it has to merge the information from the old customer into the target customer, keeping most of the target, and in some other cases, it will try to keep both the source and the target information.

    In this case, it tries to keep both item/customer records from the source and target, resulting in duplicates.

    What you will have to do is delete all records from customer/item table for the source customer (assuming the source customer is the one you do not consider as the one to keep in case of duplicate).

    But before proceeding, you can, if desired, keep the records from the source customer if they do not already exists in the target customer (the one to be kept).

    ** Continue reading only if you are familiar and comfortable with SQL :
    Here is an sql script you could run in order to keep non-existing records in the target, from the source :
    -- Script to keep records from source customer and move them to target customer :
    update sop60300
    set custnmbr='**TargetCustNo**'
    where custnmbr='**SourceCustNo**'
    and not exists(select 1 from sop60300 t where t.custnmbr='**TargetCustNo**' and t.itemnmbr=sop60300.itemnmbr)

    And then after, the following script will delete all remaining records for your source customer :
    -- Script to delete all remaining source records from table Customer/Item :
    delete sop60300 where custnmbr='**SourceCustNo**'

    Make sure you change the **SourceCustNo** with the source customer number that you dont want to keep, and the **TargetCustNo** with the customer number you want to keep information at the end of the merge.

    Hope this helps !

    Cheers



    ------------------------------
    Luc St-Yves
    Business Analyst
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 3.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 02:47 PM
    I ran the first script as directed and opened GP2016 and the new customer did not show up in the customer look up.
    There are records in the table sop60300 for the new customer.
    Do I have to run the delete statement before new customer will show up?

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 4.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:05 PM
    Hi,
    the script will not operate the merge.  These script were meant to prepare before the merge you were trying to do.

    But still, if you are saying that the target customer number does not exist in your GP, why are you trying to merge ?  You should merge if both customer exists ?

    ------------------------------
    Luc St-Yves
    Business Analyst
    Co-Leader GPUG - Montreal chapter
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 5.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:17 PM
    I am modifying one customer number to a new (Non existant) customer number.
    Not merging. I really don't know where merge came from.
    I suppose I could reverse the merge to send the items back were they came from.

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 6.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:36 PM
    Get it.

    Then yes, you will have to execute the second sql statement since it is more than probably duplicates staying there.
    You have to delete them before merging to eliminate duplicates.

    Then merge, it should work ;)

    ------------------------------
    Luc St-Yves
    Business Analyst
    Co-Leader GPUG - Montreal chapter
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 7.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:42 PM
    So, to be clear, I am going to use the delete script to delete the original customer?
    The original customer does not have any records in SOP60300.

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 8.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:48 PM
    Maybe it would be a good thing if we start all this over.

    So from the beginning, you were not merging but rather wanted to rename the customer number, right ?

    If that is the case, the first script renamed all SOP60300 records from the source to the target customer, which in reality, does not exist yet.
    Which is very strange since you had duplicates in this table.

    Even stranger is that you are saying there is no remaining records from the source customer in SOP60300 ?
    So you ran a select * from SOP60300 where CUSTNMBR='*SourceCustNo*' and there were no records ?

    ------------------------------
    Luc St-Yves
    Business Analyst
    Co-Leader GPUG - Montreal chapter
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 9.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:59 PM
    Correct. I ran the select statement. I just received the columns. No data.

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 10.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 03:53 PM
    I am going to restore the test company with fresh data.
    Do you have any other scripts that might work to resolve my modifying one customer number to a new (Non-existent) customer number?
    If not. Thank you for your help and have a great day.

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 11.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 04:01 PM
    I would have expected remaining records from your source customer number after you did the first script.  If not, that would mean there are no duplicates.

    Since you are in a test company, it should be easy to find.  So refresh your test company and try your rename again.  If you still have duplicates, it should be easy to find.

    ------------------------------
    Luc St-Yves
    Business Analyst
    Co-Leader GPUG - Montreal chapter
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 12.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 04:07 PM
    Just a guess...  I looked back at your original post and you are saying you are doing a series of rename.  Is it possible that you rename, let's say X to Y, then later on, renaming Z to Y also ?  Maybe I would take a look at the renaming list before you run it again.  It may be your answer ;)

    ------------------------------
    Luc St-Yves
    Business Analyst
    Co-Leader GPUG - Montreal chapter
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 13.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 04:18 PM
    I checked the list over several times. I was thinking the same thing.
    I believe our issue is the Customer Item numbers that are creating the issue.
    On one of the failed customers, we removed one of the customer items and received the same error but for a different item number.
    We were thinking we should remove all the Customer Items assigned to these (2) customers and then try to Modify them.
    Your thoughts?

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 14.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    Posted Jan 04, 2019 04:25 PM
    Edited by Shawn Dorward Jan 04, 2019 04:32 PM
    Jumping in late, but I feel like you are on the right path - I had this happen to me in the past and it was customer item numbers.

    I don't have the specific error I received back then but it was similar circumstances - curious to see what you come up with.

    ------------------------------
    Shawn Dorward
    Dynamics GP Practice Lead
    Microsoft MVP, Business Applications
    shawn.dorward@interdynartis.com
    Twitter: @ShawnMDorward
    InterDyn Artis
    Blog: http://www.GPLifeHacks.com

    ------------------------------



  • 15.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    Posted Jan 04, 2019 04:28 PM
    Edited by Shawn Dorward Jan 04, 2019 04:31 PM
    Original Message:
    Sent: 01-04-2019 04:24 PM
    From: Shawn Dorward
    Subject: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    Jumping in late, but I feel like you are on the right path - I had this happen to me in the past and it was customer item numbers.

    I don't have the specific error I received back then but it was similar circumstances - curious to see what you come up with.

    ------------------------------
    Shawn Dorward
    Dynamics GP Practice Lead
    Microsoft MVP, Business Applications
    shawn.dorward@interdynartis.com
    Twitter: @ShawnMDorward
    InterDyn Artis
    Blog: http://www.GPLifeHacks.com





  • 16.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 04:35 PM
    If it does not bother you to lose the customer/items, then yes, you could delete these records before proceeding with the rename.
    Or, you could also rename them in advance, before proceeding your list of combine/modify, by updating them and changing their customers to the target one, before you proceed ?  Since you are in test company, it could worth the try.

    It is not like if you were deleting records from a transactional table.  This table is pretty much stand alone.
     Let us know, I am curious of what you find.

    ------------------------------
    Luc St-Yves
    Business Analyst
    Co-Leader GPUG - Montreal chapter
    Humanware Technologies
    Drummondville QC
    ------------------------------



  • 17.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 04, 2019 04:40 PM
    Thank you Luc and Shawn. I will let you know the outcome.

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------



  • 18.  RE: # GP2016 r2 - Customer Combiner/modifier - dbo.tcsINVTB00063_AIC - Duplicate Key on item 'item number'

    SILVER CONTRIBUTOR
    Posted Jan 31, 2019 02:31 PM
    We ended up removing the customer items and was able to modify the customers without any errors. Thank you all for your input.

    ------------------------------
    Mark Roark
    Infrastructure Supervisor
    Global Products, Inc.
    ------------------------------