RMS 2.0 - Purging old transactions

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Make a backup of the database first to use as an archive.
SO Administrator - Database - Delete Transactions...
You can also do this with a query: Delete e from [transaction] t join transactionentry e on t.storeid=e.storeid and e.transactionnumber=t.transactionnumber where time<'1/1/2000'
Delete from [transaction] where time<'1/1/2000'
The problem with deleting transactions is they can't then be used to compare inventory discrepancies with the movement history report. Maybe the way to fix this is to find out the stock status from a point in time, delete previous history, then insert new inventorytransferlog records for the new begining inventory amounts.
For a complete purge of old records: Order    Table    Command 1    RecordDeletedLog    DELETE from RecordDeletedLog where WhenDeleted < '01/01/2000' 2    InventoryTransferLog    DELETE from InventoryTransferLog where DateTransferred < '01/01/2000' 3    PurchaseOrderEntryDetail    DELETE from PurchaseOrderEntryDetail where Date < '01/01/2000' 4    PurchaseOrderEntry    DELETE POE FROM PurchaseOrderEntry POE LEFT JOIN PurchaseOrder PO ON POE.PurchaseOrderID=PO.ID AND POE.StoreID=PO.StoreID WHERE PO.DateCreated<'01/01/2000' 5    PurchaseOrder    DELETE from PurchaseOrder where datecreated < '01/01/2000' 6    QuoteTenderEntry    DELETE QTE from QuoteTenderEntry QTE join [Order] O on QTE.OrderID=O.ID WHERE O.Time<'01/01/2000' 7    OrderHistory    DELETE OH FROM OrderHistory OH LEFT JOIN [Order] O ON (OH.OrderID = O.ID) AND (OH.StoreID = O.StoreID) WHERE (O.Time)<'01/01/2000' 8    OrderEntry    DELETE OE FROM OrderEntry OE LEFT JOIN [Order] O ON (OE.OrderID = O.ID) AND (OE.StoreID = O.StoreID) WHERE (O.Time)<'01/01/2000' 9    Order    DELETE from [order] where Time < '01/01/2000' (10-11 don't delete accountreceivable as will affect current account balances) 12    Shipping    DELETE S FROM Shipping S LEFT JOIN [Transaction] T ON S.TransactionNumber=T.TransactionNumber AND S.StoreID=T.StoreID WHERE T.Time<'01/01/2000' 13    VoucherEntry    DELETE from VoucherEntry where Date < '01/01/2000' 14    TaxEntry    DELETE TE FROM TaxEntry TE LEFT JOIN [Transaction] T ON TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE T.Time<'01/01/2000' 15    TransactionEntry    DELETE TE FROM TransactionEntry TE LEFT JOIN [Transaction] T ON TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE T.Time<'01/01/2000' 17    NonTenderTransaction    DELETE from NonTenderTransaction where Time < '01/01/2000' 18    Payment    DELETE from Payment where Time < '01/01/2000' 19    DropPayout    DELETE from DropPayout where Time < '01/01/2000' 20    TenderEntry    DELETE TE FROM TenderEntry TE LEFT JOIN [Transaction] T ON TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE T.Time<'01/01/2000' 21    Transaction    DELETE from [Transaction] where Time < '01/01/2000' 22    OldJournal    DELETE from OldJournal where ClosingTime < '01/01/2000' 23    Journal    DELETE from Journal where Time < '01/01/2000' 24    Batch    DELETE from Batch where ClosingTime < '01/01/2000' 25    DailySales    DELETE from DailySales where Date < '01/01/2000' 26    TimeCard    DELETE from TimeCard where TimeOut < '01/01/2000' 27    TimeStampLog    DELETE from TimeStampLog where ServerTime < '01/01/2000' 28    HQRecordDeletedLog    DELETE from HQRecordDeletedLog where WhenDeleted < '01/01/2000' 29    Worksheet_DelGlobalCustomers    DELETE W from Worksheet_DelGlobalCustomers W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 30    Worksheet_GlobalAccountAdjustment    DELETE W from Worksheet_GlobalAccountAdjustment W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 31    Worksheet_InventoryTransfer    DELETE W from Worksheet_InventoryTransfer W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 32    Worksheet_ItemCost    DELETE W from Worksheet_ItemCost W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 33    Worksheet_ItemPrice    DELETE W from Worksheet_ItemPrice W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 34    Worksheet_ItemPriceLevel    DELETE W from Worksheet_ItemPriceLevel W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 35    Worksheet_ItemPriceLimit    DELETE W from Worksheet_ItemPriceLimit W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 36    Worksheet_ItemQuantity    DELETE W from Worksheet_ItemQuantity W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 37    Worksheet_ItemRestock    DELETE W from Worksheet_ItemRestock W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 38    Worksheet_ItemTax    DELETE W from Worksheet_ItemTax W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 39    Worksheet_ItemUpdate    DELETE W from Worksheet_ItemUpdate W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 40    Worksheet_PurchaseOrder    DELETE W from Worksheet_PurchaseOrder W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 41    Worksheet_SQL    DELETE W from Worksheet_SQL W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 42    Worksheet_SupplierList    DELETE W from Worksheet_SupplierList W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 43    Worksheet_SupplierUpdate    DELETE W from Worksheet_SupplierUpdate W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 44    WorksheetHeader_PurchaseOrder    DELETE W from WorksheetHeader_PurchaseOrder W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 45    WorksheetHistory    DELETE W from WorksheetHistory W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 46    WorksheetStore    DELETE W from WorksheetStore W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000' 47    Worksheet    DELETE from Worksheet WHERE EfectiveDate<'01/01/2000'
"Randy" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Matt:
If I do the complete purge and only care about current inventory counts, I assume the items that may still be on the shelf from that period would have lost counts?
Thanks!
"Matt Hurst" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Purging transaction and transfer history doesn't affect the current inventory count stored in the itemdynamic and item tables, but it does make it harder to figure out how the inventory level got to the current count (compare item movement history to current stock status) and like Afshin mentioned makes it harder to show contributing information for open accounts involving those transactions.
"Randy" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Your other option is to use our Archive Tool that takes the old data out and stores it a different database. This archived database being an RMS database is still available in case you want to query some receipt or reports. Further, the archive will not inadvertently take out data that may leave orphan records. For example if some detailed data from two years ago is also in an opened Accounts Receivable it will leave that record there. When it finishes it compacts and shrinks the database. Contact me for prices and more detail.
Afshin Alikhani - snipped-for-privacy@rrdisti.com
Retail Realm Distribution
= = = = = = = = =

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here. All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.