Delete Transaction History after Testing

V5 Question
1

I have been configuring my SambaPOS and testing it. To do a final test, I would like to be able to clear out all the transaction I created so I can start fresh.

Can someone help me on how to do this?

Thanks,
Dan

1 Like
2

Go to settings database tools then use the clear database transactions

3

:confused: I was going to respond with the same but mine no longer shows “Clear Transactions”?

4
  1. Go to manage on Navigation screen
  2. On the left click settings (if it already hasn’t been highlighted) then on the left look for “Database Tools”, and click it.
  3. At the bottom you should then see “create default tasks”. This will only show if there is nothing in the selection windows.
  4. This will create “clear all database transactions”. Click it to highlight.
  5. 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.

3 Likes
5

Hey Mate - thanks for that I forgot about creating the tasks, must of removed mine at some stage


2 Likes
6

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.

1 Like
7

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?

1 Like
8

I would really like to know how to include my own SQL scripts, so if you can point me in the right direction, that would be create.

9

In your Documents folder on C drive go to SambaPOS5/Database Tasks folder. Insert your SQL Scripts there.

Name them [CBL]scriptname.sql you can create them in Notepad then just save them as format i just showed.

Here in this example I dropped a new script called Test SQL Script

Pasted image1920×1080 65.4 KB

1 Like
10

We even have an action to execute these tasks and you can use rule events to automate it to whatever you want.

Not so sure how useful it is considering we can execute SQL with jscript support now.

4 Likes
11

i try to delete all transaction but i recieved this message

Untitled-2.jpg805×547 287 KB

12

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
1 Like
13

i copy and past it and works very well. thank you

14

Thanks @QMcKay
 This really helped in 2019 :upside_down_face:

2 Likes
15

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

16

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.

17

(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.

18

nel pannello Impostazioni> account> transazioni le vedo ancora tutte come un elenco.

19

in the Settings> account> transactions panel I still see them all as a list.

20

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