Hi, I have a problem. One of our clients has by accident duplicate customers. I want to get rid of the duplicates and bring all the related entries back to the primary customer. Has anyone did this before ore maybe someone wrote a spinfix? The version is 3.60. Thanks. Roelof de Jong.
Hi, You can do that by using standart rename function: Code1 := ‘XXXXX’; //First customer code Code2 := ‘YYYYY’; //Second customer code recCustomer1.GET(Code1); recCustomer1.DELETE; recCustomer2.GET(Code2); recCustomer2.RENAME(Code1); recCustomer2.DELETE; recCustomer1.INSERT;
This is OK providing there is no Sales History for the Same Customer with the Second Code, if there is you have a problem. You cannot delete a Customer with Ledger Entries. with regards recCustomer1.GET(Code1); recCustomer1.DELETE; Always use recCustomer1.DELETE(TRUE); This will error if there are Open Sales Orders or Ledger Entries also will delete any Child records Comments etc: recCustomer2.GET(Code2); recCustomer2.RENAME(Code1); Why Delete the Customer we have Just Renamed ?? recCustomer2.DELETE; What is this Insert for ?? recCustomer1.INSERT;
David, You are not right, that code was OK, I used it few times. recCustomer1.DELETE; - without TRUE, because I don’t want to delete all history. This deletion is temporary, because without that I can’t rename. After renaming this entry is inserted again. Rename function brings all customer2 entries to customer1.
Now we need to look at this again First we need to find Customer1 then Duplicate Customer2 Now look at the Child records and transfer then to the Main Customer CommentLine.SETRANGE(“Table Name”,CommentLine.“Table Name”::Customer); CommentLine.SETRANGE(“No.”,Customer2.“No.”); If you find any transfer then to customer1 CustBankAcc.SETRANGE(“Customer No.”,Customer2.“No.”); If you find any transfer then to customer1 ShipToAddr.SETRANGE(“Customer No.”,customer2.“No.”); If you find any transfer then to customer1 SalesPrice.SETRANGE(“Sales Type”,SalesPrice.“Sales Type”::Customer); SalesPrice.SETRANGE(“Sales Code”,customer2.“No.”); If you find any transfer then to customer1 SalesLineDisc.SETRANGE(“Sales Type”,SalesLineDisc.“Sales Type”::Customer); SalesLineDisc.SETRANGE(“Sales Code”,customer2.“No.”); If you find any transfer then to customer1 ItemCrossReference.SETCURRENTKEY(“Cross-Reference Type”,“Cross-Reference Type No.”); ItemCrossReference.SETRANGE(“Cross-Reference Type”,ItemCrossReference.“Cross-Reference Type”::Customer); ItemCrossReference.SETRANGE(“Cross-Reference Type No.”,customer2.“No.”); If you find any transfer then to customer1 SalesHeader.SETCURRENTKEY(“Document Type”,“Sell-to Customer No.”); SalesHeader.SETRANGE(“Sell-to Customer No.”,Customer2.“No.”); If you find any check if lines to shipped or invoice Then if ok validate “Sell-to Customer No.” with Customer1 If Customer2 has a balance then you will need to create General Journal and Transfer the balance via a suspense Account to Customer 1 Nearly There If Customer2 has Ledger Entries then Block the Customer else you can customer2.DELETE(TRUE); If the second set of Customer records has no transaction or sales orders you can just find the second customer and delete(TRUE) the record then rename Customer1 The quick solution is often not thought through enough but a quick reaction to the question.
quote:
Originally posted by Viktoras
David, You are not right, that code was OK, I used it few times. recCustomer1.DELETE; - without TRUE, because I don’t want to delete all history. This deletion is temporary, because without that I can’t rename. After renaming this entry is inserted again. Rename function brings all customer2 entries to customer1.
I can see that you solution would work, but I would have reservations as each Customer could have records with Different Values What if I have Customer Comments sales prices etc: what will happen on rename if both have a line 10000 with different values?
quote:
Originally posted by David Cox
Why do we need to rename to transfer entries ?
As I know, rename function goes throw all entries related to customer2 (in our case) and changes customer code to new code. This function is slow, but it do the job.
quote:
Originally posted by David Cox
What if I have Customer Comments sales prices etc: what will happen on rename if both have a line 10000 with different values?
I think problem is to transfer ledger entries, because user can’t change them manualy. With comments, or prices - rename function will stop with error (i think so), but user then can delete such entries related to customer2 and run function again. This solution is not ideal, but is simple and really helped.
quote:
Originally posted by Viktoras
quote:
Originally posted by David Cox
What if I have Customer Comments sales prices etc: what will happen on rename if both have a line 10000 with different values?
I think problem is to transfer ledger entries, because user can’t change them manualy. With comments, or prices - rename function will stop with error (i think so), but user then can delete such entries related to customer2 and run function again. This solution is not ideal, but is simple and really helped.
Ok: But I would Never rename a Customer which had Transaction History. Why?: Simple Audit Trail, The Customer will have Printed Documents with the old Customer Number, if they have this on thier IT system as a point of reference and make an enquiry we would not be able to find thier account, if we keep the Customers Entries and Transfer any balance and Open Orders then block the Customer, change one of the Address fields to say Now Customer #YYYY then we have a good Audit Trail, I know that we would not have the full History on Customer1 so we have one problem with two workable solutions. [:D]
Lot’s of stuff to think about. Let me ask Viktoras a question: does that mean that, if you first delete your customer record which is probably still in the buffer, can you can do an insert again? I know that 370 is fast in renaming, but this issue is related to a 2.60 version. I have a whole bunch of customer to rename (hardly any history though). Any thoughts about this? Thanks. Roelof de Jong.
quote:
Originally posted by Roelof
Lot’s of stuff to think about. Let me ask Viktoras a question: does that mean that, if you first delete your customer record which is probably still in the buffer, can you can do an insert again? I know that 370 is fast in renaming, but this issue is related to a 2.60 version. I have a whole bunch of customer to rename (hardly any history though). Any thoughts about this? Thanks. Roelof de Jong.
Command recCustomer1.DELETE deletes customer entry from database, but varriable recCustomer1 has all info unchanged. What about speed of renaming, you should try on copy of db. If it will be very very very slow, then this solution isn’t ok for you[:(]