On the left click settings (if it already hasn’t been highlighted) then on the left look for “Database Tools”, and click it.
At the bottom you should then see “create default tasks”. This will only show if there is nothing in the selection windows.
This will create “clear all database transactions”. Click it to highlight.
Click execute task at the bottom.
If done correctly it should take you back to login screen. Once logged in you shouldnt see any previous work periods and no tickets. Then to ensure its working, once you open new work period,
Check your reports and date back to other days to ensure there’s no values. Accounts should also have no values there.
You can add your own tasks as well. I’ll show you how. Those tasks are SQL script files. You can write your own SQL script files and name them with appropriate name and they will show up there.
I was going to ask @Jesse and can make it another topic, but is this how you handle “End of Financial Year” for a Location?
Most Companies I have dealt with have a EOFY reporting season, so they would want to see Sales reset to '$0.00". Customer Balances obviously carried forward, but the interesting Accounts would be “Discounts” alike as these I think would need to go to “$0.00” as well?
There are a couple lines in the SQL script that should execute before another - the order is a bit incorrect. It should be like this:
DELETE FROM [TicketEntities]
GO
DELETE FROM [Tickets]
GO
DELETE FROM [CostItems]
GO
DELETE FROM [InventoryTransactionDocuments]
GO
DELETE FROM [InventoryTransactions]
GO
DELETE FROM [AccountTransactionDocuments]
GO
DELETE FROM [AccountTransactions]
GO
DELETE FROM [AccountTransactionValues]
GO
DELETE FROM [Calculations]
GO
DELETE FROM [Orders]
GO
DELETE FROM [PaidItems]
GO
DELETE FROM [PeriodicConsumptionItems]
GO
DELETE FROM [PeriodicConsumptions]
GO
DELETE FROM [ProductTimerValues]
GO
DELETE FROM [Payments]
GO
DELETE FROM [WarehouseConsumptions]
GO
DELETE FROM [WorkPeriods]
GO
UPDATE [Numerators] SET Number = 0
GO
UPDATE [EntityStateValues] SET EntityStates = '[{"S":"Available","SN":"Status"}]' Where EntityStates like '%Status%'
GO
i used this query and it completes with errors.
In the end I deleted all the transactions but in the Settings> account> transactions panel I still see them all as a list.
I need to clear the database of all transactions because I have changed companies and cannot keep old receipts in the database.
thanks for help and sorry my english
What error did you get? If it was a timeout run again as if clearing allot of data it can timeout. I once cleared 2 years worth and had to run a few times before it cleared everything out.
Failing that run the script directly in sql manager.
(12168 righe interessate)
Messaggio 547, livello 16, stato 0, riga 3
The DELETE statement conflicted with the REFERENCE constraint “FK_dbo.Orders_dbo.Tickets_TicketId”. The conflict occurred in database “SambaPOS5”, table “dbo.Orders”, column ‘TicketId’.
The statement has been terminated.
(1 riga interessata)
(0 righe interessate)
Messaggio 547, livello 16, stato 0, riga 7
The DELETE statement conflicted with the REFERENCE constraint “FK_dbo.InventoryTransactions_dbo.InventoryTransactionDocuments_InventoryTransactionDocumentId”. The conflict occurred in database “SambaPOS5”, table “dbo.InventoryTransactions”, column ‘InventoryTransactionDocumentId’.
The statement has been terminated.
(1 riga interessata)
(1 riga interessata)
Messaggio 547, livello 16, stato 0, riga 11
The DELETE statement conflicted with the REFERENCE constraint “FK_dbo.AccountTransactions_dbo.AccountTransactionDocuments_AccountTransactionDocumentId”. The conflict occurred in database “SambaPOS5”, table “dbo.AccountTransactions”, column ‘AccountTransactionDocumentId’.
The statement has been terminated.
Messaggio 547, livello 16, stato 0, riga 13
The DELETE statement conflicted with the REFERENCE constraint “FK_dbo.AccountTransactionValues_dbo.AccountTransactions_AccountTransactionId_AccountTransactionDocumentId”. The conflict occurred in database “SambaPOS5”, table “dbo.AccountTransactionValues”.
The statement has been terminated.
The script is trying to delete a record that is referencing or is referenced by another record in the database.
While Q’s script contains the same as the one I have, the order of operations is different. Please try this one (I know for sure it works on current Builds (5.2.x+):
DELETE FROM [TicketEntities]
GO
DELETE FROM [Tickets]
GO
DELETE FROM [AccountTransactionDocuments]
GO
DELETE FROM [AccountTransactions]
GO
DELETE FROM [AccountTransactionValues]
GO
DELETE FROM [Calculations]
GO
DELETE FROM [CostItems]
GO
DELETE FROM [InventoryTransactionDocuments]
GO
DELETE FROM [InventoryTransactions]
GO
DELETE FROM [Orders]
GO
DELETE FROM [PaidItems]
GO
DELETE FROM [PeriodicConsumptionItems]
GO
DELETE FROM [PeriodicConsumptions]
GO
DELETE FROM [ProductTimerValues]
GO
DELETE FROM [Payments]
GO
DELETE FROM [WarehouseConsumptions]
GO
DELETE FROM [WorkPeriods]
GO
UPDATE [Numerators] SET Number = 0
GO
UPDATE [EntityStateValues] SET EntityStates = '[{"S":"Available","SN":"Status"}]' Where EntityStates like '%Status%'
GO
You can paste this into a new query in Microsoft SQL Server Management Studio, or save it as [CBL]Clear Database Transactions.sql and place in Documents\SambaPOS5\Database Tasks
Now it has cleared all transactions but the query still always ends with this error conflict:
(0 righe interessate) Messaggio 547, livello 16, stato 0, riga 5 The DELETE statement conflicted with the REFERENCE constraint “FK_dbo.AccountTransactions_dbo.AccountTransactionDocuments_AccountTransactionDocumentId”. The conflict occurred in database “SambaPOS5”, table “dbo.AccountTransactions”, column ‘AccountTransactionDocumentId’. The statement has been terminated.
