Sage 100

I know this was just discussed and what I need to know is what are his options now that he is in the middle of running it. Client decided to delete 17,000 customers that have no activity (never had activity). They are on 4.30 and it is still running from last night. He is asking me what should he do - can he just kill the process? Asked him if he had a back up before he decided to do this and you know his answer - no. any thoughts or suggestions ?

I'm not sure they can do much except wait it out. I think there may be some backup files that are created as part of this process - seem to think @AlnoorCassim or someone mentioned that one time.

Thanks @WayneSchulz that is what I thought but will looks and see if I see any temp files.

Generally I tell the customer that they're on their own if they decide to stop the utility mid-way and since so many data files are touched we cannot predict what may occur.

I agree with you @WayneSchulz and that is basically what I already told him .. but I also understand my client that all he was doing is deleting customer that he could delete in AR Customer Main... and thought the utility would do it faster...

Agreed, Whatever you do don't put yourself in the position of the unpaid cleanup person. If the customer decides to stop and there is damage they should be aware there's a fee to fix it (if possible). Do they have any backups? Even from the day before?

Yes and that is what we discussed, that if he does kill it and things look bad he can restore from day before and enter everything from yesterday. Already prepared them for that. And there is always a clean up\fix up fee for things like this !!!!!

You said they never had activity so even if he does kill it, there shouldn't be any other files involved just the masterfile and they will either still be there or they won't. Problem would be if there was activity but since there isn't then it should be ok I would think.

Thanks @RobertaFerguson your right, but I am worried about what files are open the time we kill the process???? The reason it is taking so long is because it has to check all the files to make sure there is no history ( I think). You would think Sage could have put something in so that is would know that these customers have never had history/activity...

Even in the customer has never had activity, there is more than one file in play. When you load a customer via VI, how many tables do you impact? There is the customer-master, contacts, Items, Ship-to address, Sales Tax Exemptions, and credit cards that I can think of off the top of my head. At best, you might orphan a record in a file. I agree with Wayne, they need to ride the horse they picked to the end of the trail. Otherwise, restore the backup, re-key the activity, and don't take chances.

LOL thanks @ShawnSlavin that is one way of putting it. I did just get good news that they do have a full back up from last night before he started this. So going to cancel the process shortly and see what issues we have and if need be restore the back up. Will let you know the outcome once we are done. Thanks everyone for your help....

I agree that stopping it now is not a good idea, wait it out. Purging 17,000 customers at one time is a big swipe at clean up. I find these programs do finish though I like to use a smaller hammer if you will, so I can see results faster.Good luck!

Well we did it - we stopped the process - it deleted just under 3,000. Lucky for them we have no issues ... Check most of the files and they all look good. I think it was like @RobertaFerguson said since there was no activity in any of the other files we could be ok. I think they just got lucky . Thanks all !!

We should all be dropping tips into our newsletters to customers reminding them not to run this utility on large sets of data - especially on 4.50

I second that @WayneSchulz. While not the customer files, I recently ran a renumber of items for a client. I calculated 11-15 seconds per item from a test run at the server. Based on @DianeRuth results, it seems that customer deletes are just as time intensive (or more) as item renumbering perhaps more so if run from a workstaion. It seems depending on the number of modules and years of history these utilities may be only able to change/delete 4 or fewer records per minute or 240 per hour. Include in that, while the utiltiy runs, the related modules are locked out from use, for obvious reasons.

I need to perform a mass-delete of about 14k customers. Client is running 4.5.4. So I did a test and imported 500 customers into D/R/M, and the deletion process took 5min. What am I missing? I was really expecting really poor performance.

Did it delete the invoice history or just the customer records?

Not sure. Whatever it does. I had told it ""all files"". The client did purge a ton of A/R Invoice History last week though.

I was deleting only customers and it took almost 11 hours before we stopped it. Once we stopped it - less than 3,000 customers were deleted from the 17,000 on 4.30. Did you do a range or 500 individual customers? Maybe that has something to do with it????

It was 500 individual customers. I'm test-deleting the rest right now (13,887), so we'll see how that goes.

@BrettZimmerman can you update us on the status of deleting the 13,887 customers?

Just wanted to give an update - my client tried a range of 20 customers and then did 20 individual Customers had no activity and he wanted to delete them - it took the same time 8 min. He also said that he original thought 3,000 customers were deleted but was wrong it was 1,800 in 11 hours... just a FYI

My testing of those Customer deletes (13,887) took 2hr 22min in the Test company. HOWEVER, doing it in the Live company took roughly 8hr. Even my first test run of 500 customers took longer. The test of those 500 took 5min in the Test company, but then in the Live company it took 17min. It made no sense to me since my Test company was just a copy of the Live company and no one was in MAS. All variables were the same as far as I could tell. But then another Consultant in our office mentioned that Sage said it's due to the Sage CRM Integration Engine, which came to the surface during a separate data-cleanup effort he had performed previously. And I could see that making sense since the Engine is only being used for the Live company and not the Test company. At which point I exclaimed, ""That could have been brought to my attention YESTERDAY!"" :)) http://www.youtube.com/watch?v=yMQhXc1dHIQ HOWEVER, why would my Vendor delete process also take longer in the Live company vs. the Test company? That has nothing to do with the Integration Engine, correct?

@DianeRuth, was he performing that at the server or a workstation? Was the Sage CRM Integration Engine running during that process?

@BrettZimmerman this just makes no sense. He did do it on the server and they don't have CRM. We actually did the 20 customer test on a test company so 8 min will probably take longer on the live company based on what you are saying.

Yes, very wacky. You're on 4.30 (I'm 4.5) so I'm wondering if maybe you're dealing with a slightly different issue - because my performance in the Test company seemed somewhat reasonable based on the volume of data. I'm just baffled by how it could take so much longer in the Test company vs. the Live company if both companies are the same. Is it possible that the ""old"" files in the Live company's data folders (from the recent Rebuilds) have an impact? Because those ""old"" files don't exist in the Test company's data folders after doing a company copy via Company Maint.

Any chance the live company files have become highly fragmented over time and the files in the new test company are still contiguous? It would have been interesting to watch the processes in task manager to see what percentage of CPU time they were taking.

I suppose that's possible, although I had performed Rebuilds in the Live company prior to running the Delete Utility, although I guess that may not have had an impact on the condition of where those files reside on the drive.

We did this mass-delete (4.5) for any customers with a Last Activity Date prior to 1-1-10. However, we noticed that it removed those customers even if an open Sales Order existed, and it removed the open Order from S/O Entry and set it as ""deleted"" in S/O History. Not good! Has the DRM function always done that? And why wouldn't a Sales Order constitute an ""activity"" and populate the Last Activity Date field?

Next time we'll need to also analyze the Open Order Amount field in the Customer masterfile.

@BrettZimmerman Delete customer should not delete active customers. Only customers that have a zero balance and based on what I thought no open sales orders. I think you need to call Sage and report this.

Same thing in 4.30.

So let me understand what you are saying. If a customer has a S/O open with a date after the "" delete date"" and no balance in the customers account it will delete it? Or it just doesn't matter what the date is in open S\O?

I could arge that's a WAD -- if you want to delete a customer and they have an order - then if the customer goes then the order goes since it's not yet an invoice. I'm assuming the issue here is the definition of ""last activity"" ?

@DianeRuth, the dates don't matter. We were just using the Last Activity Date as criteria for creating the Excel file of Customers to import into the Delete Utility. If you create a brand new customer right now, then enter a sales order for that customer, then process the Delete Utility for that customer, it'll delete the customer and it'll remove the sales order from S/O Entry and mark it as ""deleted"" in S/O History. Agreed, @WayneSchulz, but wouldn't it make more sense if the system saw that you're trying to delete a Customer who has an open order, and says, ""Wait, are you sure you want to delete this Customer? They have an open order.

Yes - totally agree - there should be some type of option to say whether you want to remove customers with orders or not.

I agree it should give you the option on whether or not to remove customers with open orders. I thought it didn't delete those customers with open orders. So I learned something new today - thanks @BrettZimmerman for making me aware of this.