BUG: Preorder transaction value date incorrect

I couldn’t see this listed anywhere else, I think I have spotted a bug with the date stored in the database in the AccountTransactionValues table when working with PreOrder tickets. It seems that the transaction DateTime is set to the time of creating the preorder ticket, rather than when the ticket is finally converted to a regular ticket, settled and closed.

To recreate this issue…

  • Create two ticket types:
    • Reservations (Preorder ticket)
    • Sales
  • Create a reservation ticket and add some items to it
  • Wait a day or so (open and closed work periods in-between)
  • Open the reservation ticket and use an Automation Command to fire a “Change Ticket Properties” action to convert the ticket from a reservation in to a sales ticket:
    • Ticket Type Name: Sales Ticket
    • Is Pre Order: False
    • Change Ticket Date: True
  • Close and re-open this ticket (which is now a sales ticket)
  • Settle the sales ticket using cash (or any other payment)

:white_check_mark: Ticket.Date:
:white_check_mark: Ticket.LastUpdateTime
:white_check_mark: Ticket.Version
:white_check_mark: Ticket.LastPaymentDate
:white_check_mark: Payments.Date
:x: AccountTransactionDocuments.Date
:x: AccountTransactionValues.Date

For all the items list with a check-mark the date is correctly updated to reflect the date on which the pre-order ticket was converted in to the Sales Ticket and settled. The item with the red cross shows that the database entry for that table and field for some reason inherits the original date of the pre-order ticket, and not the DateTime for when the ticket was converted and the transaction was really made.

The problem with this is (at least) two fold…

  • In reports to list transactions for “today” (the day on which the ticket was converted), the payment for the ticket will not be listed.
  • In the accounts screens the payment will be listed as having occurred in the past (the date the reservation was made) and not on the actual date the payment was made.

Have I done something wrong in my set-up? I can’t believe I’m the first person to experience this?

Are you updating the ticket date to current period.
I’ve always rules out preorder option as the reporting can get messy.

Yes. I am converting the ticket, settling and closing the ticket within a new Work Period and then trying to view the transaction in the current work period. As a result, the ticket date (and all the other dates listed with the green check mark) get changed to fall within the current work period, with the exception of the one item I listed above with the red cross.

Interestingly, if I run a count of the number of payments then this returns the correct number. It’s only when I try to calculate the total transaction value everything becomes a mess.

It’s because all but one of the dates are changed to reflect the new date that I think this must just be a bug and not by design. If that final date matched the others then all reporting would work correctly.

Are you changing the date though?
The ticket date as far as I understand isnt brought into current period automatically…


If you report payment details it would as payment date is current.

1 Like

Yes. I have Change Ticket Date set to true, and as a result all but one of the dates are set to the current work period as expected… Just one date is incorrect.
It’s really strange though, because the ticket date actually needs to be updated (and it is), but the account transaction value date is a new database entry yet somehoe gets given a date from the past (so it’s not that it is not being updated, it’s being set for the first time with completely the wrong date.).

It probably would since the Payments.Date field does get set correctly, but I am using the {ACCOUNT TRANSACTION TOTAL:SALES Payment Transaction:Cash USD} report tag to get the total value of transactions, but since this is based on the transaction it returns the wrong figure.

The problem is also pretty bad since it leaves payments out of place in the accounts screen.

When is the payment account transaction dated then? The same old date?

Yep, it gets set to the date of when the pre-order ticket was first created.

Do you see the same your end @JTRTech?

I have created a fresh database with minimal config (only creating this reservation+sales ticket automation).

I could either upload a backup of this DB or export the config using the DB export tool if that would help?

Never used preorder so not an area I’m great with.
Maybe it’s not come up as not many really used account transaction reporting.
If you do report ticket type expression does it show the altered new date?

Woah… Sorry, I have just realised I was wrong about the AccountTransactionDocuments.Date this is also the incorrect date.

I have updated my original report.

Yep, ticket based reports work fine… If I count all sales tickets for today it correctly shows all walk-in tickets (created, settled and closed today) AND reservation tickets converted to sales tickets (which were created lasted week but then converted, settled and closed as sales tickets today)

The more I think about it, sent the original account transactions created with ticket?
Haven’t checked for a long time but doesn’t an unpaid ticket transfer to receivables on close? Then payment is from receivables to bring it back to 0?
This is where preorder confused me.
Every system I have used generates sale at time of transaction, if payment is later this is then at that time and in between there is a ledger balance/debt. Which I’m sure is how receivables in samba works.
What do you get with an ‘open ticket’?
I can’t see that samba would backdate account transaction on ticket final close to a date it no longer has given that ticket date has been changed which is what made me think that it is creating the ‘sale’ transaction at that time, hence why changing ticket date isn’t reflected as the account transaction is already created.

Nothing goes into receivables until payment is made. No sale is made until payment is recorded.

Receivables does what it sounds like. Sales happen we receive money then it goes to proper account, credit, cash etc. Receivables should always be 0 balance.

There are no transactions if payment is not made.

I don’t think that is correct @Jesse

As far as I can see, looking at a vanilla install, when you add items to a ticket and then close the ticket a value is stored in the receivables account since the money to settle the open ticket is still essentially pending. When the money is paid an the inverse of the ticket value is posted to receivables and it is at that point the receivables account returns to 0

60 USD ticket remains unpaid, hence it is pending in receivables.

Hmm you may be right. I was basing it off the transaction types. Sale transaction Is tied to ticket type

Even with the fact the receivables would correctly show that this pre-order ticket will need to be paid off at some point, I still don’t understand why the payment is listed as today, but the document and the transaction value is list as having happened in the past.

It probably has to.do.with how sale is tied.tp ticket

But surely its not helpful that the payment is today, yet the account says the payment was last week… If that is in any way a feature then it would be helpful to have it as an optional feature that can be disabled?

For now I am going to run these two scripts to bring everything to the correct dates…

Update Docs
   SET Docs.Date = Pmts.Date
   From AccountTransactionDocuments Docs
      INNER JOIN Payments Pmts
         ON Docs.Id = Pmts.AccountTransaction_AccountTransactionDocumentId

Update Vals
   SET Vals.Date = Pmts.Date
   From AccountTransactionValues Vals
      INNER JOIN Payments Pmts
         ON Vals.AccountTransactionDocumentId = Pmts.AccountTransaction_AccountTransactionDocumentId				

Moving forward, I will also add a WHERE pmts.Date > dateadd(day,datediff(day,0,GETDATE()),0) and maybe trigger this to run everytime a ticket gets closed just to keep everything up to date?