Purge specific records from AR Invoice History-VI or SQL?

Running Sage 100 Premium 2021: need to reduce the size of the AR Invoice History tables. The purge invoice history utility is good as far as it goes, but I need to remove invoices for only one division that creates thousands of repetitive invoices monthly--while retaining invoices in other divisions that contain equipment sales. I know I can use perform logic to delete records via Visual Integrator, but now that we're on Premium it would be SO much easier simply to remove them via SQL query.

I believe the only tables I need to touch are AR_InvoiceHistoryHeader, AR_InvoiceHistoryDetail, and AR_InvoiceHistoryTaxSummary... other tables starting with AR_InvoiceHistory... do not contain data for the invoices in question.

I know Visual Integrator uses validation logic so for imports is highly preferable, but for this specific task, removing invoice history for invoices that are over 5 years old and thus not in sales tax or open invoice tables, is there a valid reason to go that route, given the additional steps it will require?

Parents
  • If you are on the SQL version why would you need to purge records?  Also VI is not used to remove records, only add. And you should have a utility to purge history. Look until the Utilities tab in AR.

  • in reply to BigLouie

    Because Sage is not so speedy as SQL... I spent many hours this weekend waiting for the Sage Delete and Change Items Utility to merge less than 300 items, and I have many thousands to go. I understand that removing transaction data is the only way to speed the process. And I absolutely, positively, will NEVER need the invoice history in question.

    While the Purge AR Invoice History Utility is helpful to a point, it doesn't offer the ability to delete invoices from only one division, while retaining the invoices from another division that needs equipment sales history. I will never need these 200,000 invoices with over 500,000 details lines but I do need equipment sales history for another division, and targeted removal of invoice history will save me the inconvenience of having to go to a backup company to get it.

    Big Louie, I guess do not appreciate why GAAP rules would prohibit removal of invoice history this old, especially when I could remove it via the Purge AR History utility. But I welcome your input.

  • in reply to jcbaker

    Why?  Audits.  5 years is not that old, and companies are legally required to keep years of historical data (with the exact requirements varying by jurisdiction).

    The question is not "why keep it?".  The question is "why delete it?".  After the move to Premium, the technical issues related to large table sizes are gone, since SQL is so much better at handling large data sets.

  • in reply to Kevin M

    We have had Sage 100 for over 20 years. Keeping everything is not reasonable nor necessary. We have already been through a sales tax audit, these invoices truly can go (plus I do have backup companies each year with the data so it's not truly "gone").

    If you work only in SQL yes table size doesn't matter, but it sure matters when using Sage 100 utilities! I do not have a free month to spend with no open batches etc. to merge thousands of items that have not been used for 15 years, since Sage must churn through all this transaction history that I'll never use. Even Sage says to remove transaction records to speed the process. It's just that I want to do it in a targeted fashion. Since I will retain access to the data in backup I'm not concerned about GAAP rules or potential audits, I was just asking within Sage itself would there be some hidden index or table that would be upset if I remove the data using a SQL query rather than the front-door purge utility. 

  • FormerMember
    FormerMember in reply to jcbaker

    Make a backup and  follow your plan. Worse case is a restore if something unexpected happens.

    Large table selects will be slower and resource intensive. SQL makes a copy of the table(s) as a record set.

  • in reply to jcbaker

    Yes, those utilities are super agonizingly slow.  I've had requests that would literally take weeks / months running 24/7 to accomplish.

    Why keep partial history in one place, and partial history in another?  Inconvenience?  I'd say your efforts to do the partial purge is more than inconvenient.  Our general approach is to keep archive company copies before a big purge, and use the regular period end functions to clear history properly.

    To answer your question, there are some reports that may not work properly if you remove items from CI_Item but leave data behind... (like running a by-period report with data in a summary table, where the report looks to the item code for things like ItemCodeDesc and ProductLine...).

    The IM utility to recalculate item history (sometimes required as part of data fixes) can also be thrown off by missing / partial history.

    (There are probably many more potential issues I'm not thinking of).

  • in reply to Kevin M

    I think there is a misunderstanding here: I DO plan to use the Sage Delete and Change Item Utility to properly clean up Inventory: I would never attempt to modify IM manually. And I DO use the built-in purge functions within Sage as well as the history retention settings to clear history at month end. The Sage Delete and Change Item Utility still takes huge amounts of time to churn through the history we do want to keep, so I was looking for a way to reduce the number of transactions it must review, that wouldn't impact the rest of our system.

    The ONLY records I wanted to remove via SQL are in AR Invoice History tables: these were AR invoices so do not contain inventory items. They have no balance so are not in AR_OpenInvoice. As far as I can tell the only place they exist are in the AR_InvoiceHistoryxxx tables, so I would not think removal from there should cause issues.

    I realize this proposal is outside the norm, not the recommended approach, and perhaps I didn't phrase it well originally but I simply wanted to know if these particular tables touch anything else in Sage that I had not considered.

    Thank you both for your input.

Reply
  • in reply to Kevin M

    I think there is a misunderstanding here: I DO plan to use the Sage Delete and Change Item Utility to properly clean up Inventory: I would never attempt to modify IM manually. And I DO use the built-in purge functions within Sage as well as the history retention settings to clear history at month end. The Sage Delete and Change Item Utility still takes huge amounts of time to churn through the history we do want to keep, so I was looking for a way to reduce the number of transactions it must review, that wouldn't impact the rest of our system.

    The ONLY records I wanted to remove via SQL are in AR Invoice History tables: these were AR invoices so do not contain inventory items. They have no balance so are not in AR_OpenInvoice. As far as I can tell the only place they exist are in the AR_InvoiceHistoryxxx tables, so I would not think removal from there should cause issues.

    I realize this proposal is outside the norm, not the recommended approach, and perhaps I didn't phrase it well originally but I simply wanted to know if these particular tables touch anything else in Sage that I had not considered.

    Thank you both for your input.

Children
No Data