Anything we do could fundamentally change everyone’s system and it may cause more trouble. So we need to really think about it.
I believe it would not be an easy fix. That’s why I suggested using the Payment Report to be based on Payment Dates.
I just wanted support to understand the what the underlying situation is.
WHen you say payment report… do you mean the Accounting screen for account transaction documents?
You can use {REPORT PAYMENT DETAILS:P.Date,P.Time} to get exact date and time of payment
I used the following report to extract specific payment id to show the difference.
[test:1,1,1]
>Payment Amount|Payment Date|Ticket Date
{REPORT PAYMENT DETAILS:P.Amount,P.Date,P.Time,T.Date,T.Time:P.Id==3281:{0}|{1} {2}|{3} [=FD('{4}','h\:mm tt')]}
You should be able to build custom reports for what you need for now while we evaluate this. I think it won’t be quick for us to do anything. We have to understand what we should do and why first.
I will admit this statement does not make any sense, until you see what it is doing.
In this next animation I’m going to:
- End the workperiod.
- View the Cash Account.
- Run the Payments report to verify the amounts are the only amounts.
- Start a new Work Period.
- Create a new ticket and pay the ticket.
- Make a payment on the reservation ticket.
- Create a new ticket and pay the ticket.
- (keeping the payment number the same as the payment amounts)
- Viewing the Cash Account
- Running the Payment Report
- Close the work period.
- View the Cash Account.
- Run a payment report for the latest workperiod.
- Run another payment report for the previous work period.
The only thing I changed in the SambaPOS was the ability to add multiple payments to a reservation ticket (before it would cancel the previous payment) and I added a payment report. I did not do anything with the tickets or the database.
What you want to watch for, after the 2nd work period is closed:
- The payment $6 (#6) does not show up on the
{REPORT PAYMENT DETAILS}And the{ACCOUNT TRANSACTION DETAILS}. But it does show up in the SQL report. - When the report is changed to the first work period the #6 ($6) does show up in the first 2 reports but not in the SQL report.
As you can see, using the default payment report, will cause the payment to jump work periods. Even though it is recorded correctly in the database (payments table). It is when it reported out that the error occurs. The only explanation I can come up with, is that it using the Tickets table to get the payment date. This error will also occur across multiple work period/days.
Here are the database backups to play around with:
SambaPOS5_WP1_Open.zip (611.4 KB)
SambaPOS5_WP1_Closed.zip (668.3 KB)
SambaPOS5V_WP2_Closed.zip (671.0 KB)
Here is the report I used in above post:
[Report Payment Details:1,1,1]
>Payment Amount|Payment Date|Ticket Date
{REPORT PAYMENT DETAILS:P.Date,P.Time,T.Date,T.Time,P.Amount::{0} {1}|{2} [=FD('{3}','h\:mm tt')]|{4}}
[Account Report:1,1,1]
>DateTime|Description|Amount
{ACCOUNT TRANSACTION DETAILS:Payment Transaction:Cash}
[SQL Payment Report:1,1,1]
>Date|Type|Time
{REPORT SQL DETAILS:SELECT Amount, Date, Name FROM dbo.Payments WHERE Name LIKE 'Cash%' AND Date > '{Start}' AND Date < '{End}':F.Date,F.Name,F.Amount}
Reports tags are bound by work periods and the ticket dates for the screens it does not matter what the payment date was.
Pure sql is the only way to get what you want. I don’t have a simple method for you to do that with. But the issue you are explaining is known we just don’t have
A simple way to solve it yet.
Fair enough.
I just wanted to point out the problem and what is effected. When I read that there is a possibility of innocent employees getting into trouble for something that is not their fault (inaccurate reporting). I thought I would demonstrate what the problem was and where it is effecting.
I, myself, have put steps in place to compensate for the inaccurate payment report. In addition to using SQL report payment report I used to enter numbers into the accounting software. I use Workday Payment Accounts for each till that accepts Cash & Checks (with the integrated Credit Card I do not have worry about Cashing up, Cashing out, balancing…I’m not sure what the correct terminology is). When the till is Cashed Up, Cashed Out, Balanced (or whatever it’s called), I make a Credit transaction in the account and Debit a main Cash account (checks are handled the same way) for the balance at the end of that day. This will bring the balance back to zero. If a PreOrder ticket payment is paid, regardless of the PreOrder Ticket/Payment posting date, it shows up in the current balance, which will be brought back to $0 balance after the credit transaction at the end of that day.
Here is what it should look like at the end of the day:
Here is screen shot of a payment posted to an earlier date bringing that day out of balance.
At the time of the $558 credit the balance was zero. At a later date, the payment is posted to earlier that day (the $558 credit day), bringing the what was a zero balance into a $21.48 balance. I don’t know what day the $21.48 should have been posted, but using this method it does not matter. Because the balance will have included the PreOrder Payment posting along with the that days payments.
It’s a little confusing, but trust me it works. The other advantage of doing this is that if a till is forgotten to Cash-Up,(…you know the drill) the numbers carry over to the next day and can be Cashed-Up the following day.
May also add…one thing I love about Sambapos, even though something does not work right, there usually a work-a-round solution to it. It might not be easy, it might not look pretty, but with a little imagination and trial-and-error, a solution usually can be found.
4 Likes