I see that many people store the Ticket ID and then use it to load and print the bill. But in this updated version, there’s no need to store the Ticket ID anymore. You can simply use an SQL report tag to get the last Ticket ID based on the LastPaymentDate, and then use the Execute Print Job action to print the bill.
i use this constraint to check if there is a ticket paid and its TicketId
'[=TN('{REPORT SQL DETAILS:SELECT TOP 1 t.Id AS TicketId FROM Tickets t JOIN Payments p ON p.TicketId = t.Id JOIN TicketEntities te ON te.Ticket_Id = t.Id JOIN Entities e ON e.Id = te.EntityId WHERE e.EntityTypeId = (SELECT Id FROM EntityTypes WHERE Name = 'Tables') ORDER BY p.Date DESC:F.TicketId::{0}}')]'!=''
this is the sql report that checks that you put in the Ticket Ids parameter of the Execute print job action
{REPORT SQL DETAILS:SELECT TOP 1 t.Id AS TicketId FROM Tickets t JOIN Payments p ON p.TicketId = t.Id JOIN TicketEntities te ON te.Ticket_Id = t.Id JOIN Entities e ON e.Id = te.EntityId WHERE e.EntityTypeId = (SELECT Id FROM EntityTypes WHERE Name = 'Tables') ORDER BY p.Date DESC:F.TicketId::{0}}
This approach is much cleaner and more efficient:
You only need one action to print the bill.
Even if the last paid ticket is reopened, it will still print the previous valid paid ticket.
Note: This method requires SQL Server (Express or higher) and access to SQL features. It will not work with LocalDB or limited environments without full SQL support (e.g. SQL Server Management Studio - SSMS).
First is in a multi-terminal setting. Let us say Terminal A settles a ticket, then Terminal B settles a ticket. Terminal A wants to reprint its paid ticket but instead prints the one from Terminal B.
The second is with the multiple joins on columns that are not indexed can be quite taxing as the number of records grows. (p.TicketId, te.Ticket_Id, te.EntityId, e.EntityTypeId).
If you wanted to keep the way you’re doing it now, you can eliminate the subquery for EntityType.Id with another join:
SELECT TOP (1)
t.Id AS TicketId
FROM dbo.Tickets t
INNER JOIN dbo.Payments p
ON p.TicketId = t.Id
INNER JOIN dbo.TicketEntities te
ON te.Ticket_Id = t.Id
INNER JOIN dbo.Entities e
ON e.Id = te.EntityId
INNER JOIN dbo.EntityTypes et
ON et.Id = te.EntityTypeId
WHERE et.Name = 'Tables'
ORDER BY p.Date DESC;
Unless there’s a reason to constrain the query to only one EntityType, I would remove it leaving a query like this:
SELECT TOP (1)
t.Id AS TicketId
FROM dbo.Tickets t
INNER JOIN dbo.Payments p
ON p.TicketId = t.Id
ORDER BY p.Date DESC;
Throw the query in a script and you can pass a terminal name to it:
function (getLastTicktIdForTerminal(terminalName){
var q = "SELECT TOP (1) t.Id AS TicketId FROM dbo.Tickets t INNER JOIN dbo.Payments p ON p.TicketId = t.Id INNER JOIN dbo.Terminals trm ON trm.Id = p.TerminalId WHERE trm.Name = '" + terminalName + "' ORDER BY p.Date DESC;"
var result = sql.Query(query).First;
return result;
}
Thanks for the suggestions, I have different entity types so I only posted the Tables entity type coz I use that one to reprint my receipts and works according to my setup and I only use one terminal to cash up my tickets… Still learning to do sql , thanks for correcting my sql will try and implement some of that
This method doesn’t seem quite right to me. I believe there’s a simpler way.
When a payment is received, you can save the ticket ID as a LOCAL SETTING and trigger a rule to print it.
Since you’re saving it as a LOCAL SETTING, it will work smoothly on every terminal.
Also, the last ticket ID is not always the last one paid. For example:
If you have 5 open tickets and you receive payment for the 3rd one, it won’t be the last ID in the database table.
this updated method using an SQL report tag has some advantages depending on your workflow:
No need to store or manage ticket IDs manually — the SQL query dynamically fetches the latest paid ticket based on the LastPaymentDate, not just the last ticket ID. This makes it cleaner for setups where automation is preferred over local setting storage.
It handles reopened or edited tickets well — since it focuses on the actual payment timestamp rather than just ticket creation order.
that’s why the SQL used in this method specifically queries the Payments table and sorts by p.Date DESC to ensure it retrieves the most recent payment, not the last ticket inserted.
But I don’t see what the issue is of storing the Ticket ID in a Local Setting? You are essentially doing a database lookup, which could involve network traffic, vs a Local Setting stored in memory of the local terminal. It creates an unnecessary overhead IMHO.
I am not sure if my setups are using exactly the same as older tutorials posted on the forum, but I am storing the Ticket ID into the Local Setting on Ticket Closing for Paid tickets, so that would update the stored Ticket ID with the last ticket that was paid, regardless if that was via the normal workflow or if it was re-opened, payments removed and a new payment was added.
I just wanted to add that storing in a local setting via automation is not a manual process. You said No need to store or manage ticket IDs manually. Were you actually writing down ticket id and entering it somewhere?
What I meant was that the SQL method doesn’t require setting up and managing additional rules/actions to store or retrieve the TicketId — it’s all done dynamically at runtime via the {REPORT SQL DETAILS} tag.
another advantage of the SQL method is that if you reopen a settled ticket, it gets skipped — the system will fetch the next most recently paid ticket. But with the local setting method, it will still print the reopened (now unpaid) ticket
It’s a good option. Advantages to both methods. The last situation you mentioned can be avoided with automation as well. But yes both are good methods depending on what you need.
Note: This method requires SQL Server (Express or higher) and access to SQL features. It will not work with LocalDB or limited environments without full SQL support (e.g. SQL Server Management Studio - SSMS).