Problems with auto-reference # in combined Receipts & Payments tab

With introduction of the combined Receipts & Payments tab in place of Bank Transactions and Cash Transactions, reference numbers were automatically assigned where they were absent. Unfortunately, in some situations the result can be intermixing of two sequential numbering schemes.

For example, if a user was not entering reference numbers for bank transactions, which were optional, all those unnumbered transactions get new numbers, starting at 1. But since cash transactions were recently being auto-numbered, they have reference numbers already, which are retained. So the two sequences are now mixed. In the illustration below, a scheme was being used for cash transaction numbering where each year’s transactions started fresh at YYxxx:

08%20AM

The first two entries shown, both on 03/07/2015, are numbered 25, then 24 (dates are in descending order). The next two, from the auto-numbered scheme described above, disrupt the sequence.

It is also possible, depending on the ranges of numbers involved and when or whether the user entered bank transaction numbers, to end up with duplicate numbers in the combined sequence.

If an automatic sequence is going to be retroactively applied to existing transactions, previous entries should have at least been converted to custom fields. This creates complete mayhem in record systems. There can now be hundreds or thousands of transactions with numbers that make no sense.

Is it possible now, after numbers have been applied, to somehow recover and generate a coherent sequence? Preferably, for the users who had implemented year-based numbering, a multi-step process should be available, where the first transaction in a new year could be given a number manually, then subsequent transactions would auto-number.

Maybe there is a possibility of recovering by waiting until an improved version fixing this bug is released, then restoring a backup from prior to the version that introduced the problem?

One thing I realized is that sequential reference numbers don’t really work for Manager.

They work in a system where you can’t go back and change something retrospectively but as is the case with Manager, you can - and it’s encouraged - to go back and fix the mistakes, enter missing transactions etc. This will inevitably make sequence look messy. E.g. older transactions will inevitably end up with higher reference number than newer transactions.

Maybe reference numbers in Manager should be tied up to transaction date.

For example, rather than having simple number, maybe reference number should be in format yyMMdd##

Where yyMMdd is the date of transaction and ## is the sequence within the day.

So every day is new sequence.

For example, the first payment on 25/08/2018 would have reference 18082501, then you enter fifth receipt on 2 days before and the reference would be 18082305

This way transactions dated earlier would always have lower reference number.

1 Like

You raise some excellent points, @lubos . Unaddressed so far, though, are two main issues:

  1. In many jurisdictions, users are required to produce sequentially numbered transactions, sales invoices being one example. Your date-based reference number probably would not satisfy those requirements. One might argue that a date and sequence number was still sequential, since dates move only in one direction. But there are problems when forgotten transactions are added, mistakes are corrected, etc.

  2. Users with existing historical transactions cannot be burdened with version changes that modify their past records. Customers, suppliers, and employees already have received prior transactions. Renumbering after the fact would make it impossible to answer inquiries, cross-reference payments, and track obligations. Some users have already been jeopardized by the changes so far.

Ultimately, an important question must be answered: What is the purpose of a reference number?

One possible answer is that it uniquely verifies a transaction’s position in a sequence, giving internal and external reviewers the ability to determine whether records are missing. The existing approach satisfies this use only if transactions are never deleted. It is of questionable use when they can be, or when the reference number itself can be edited. To operate rigorously under a system requiring sequential numbering, you really need to prohibit deletion of transactions, even when erroneous. You would need to provide some sort of voiding capability that preserves the record. That runs counter to Manager’s design in several ways.

One way around the drawbacks mentioned above would involve investigating what requirements around the world actually say. Do they require strictly sequential references, or only unique? Are gaps ever permissible?

Another possible answer is that the reference is only meant to uniquely identify a transaction. In other words, it is literally a reference, not a sequence number. Many bank confirmation numbers are like this. They are akin to the keys Manager uses internally to identify things in the database. They don’t have to make any sense to the user. Your date-based reference fits this model, while also being useful for telling when the transaction was created. But you would need to decide whether to use the issue date or actual creation date as the basis of the reference.

I think you’re faced with a fundamental design choice here.

Just my opinion, but after using Quickbooks for 16 years before adopting manager, there were autogenerated transaction numbers for audit trail purposes and reference numbers for manual entry that were for the users 'reference’
Reference numbers were never automatic, sequential, etc. They were just a field that the user could put their own unique number etc. in to enable them to identify that particular transaction.

Some jurisdictions are very fussy about sequence numbers and numbered printed reports to make it difficult to “cook the books” while others are more lax on this and rely on different methods to ensure accounts are kept in an orderly and law-abiding manner.

I’d agree with Tut’s argument that if you are Implementing a hard-wired sequencing system, then you must not be able to delete an entry already made. I was quite happy with the user-entered reference number.

I used the reference number as the cheque number when writing cheques and it was nice to see it on the invoice - now, I have the system generated sequence number and have to lookup what was the cheque number.

If we go by hardcode which unlikely then by all means design audit reference number with addition of ‘reference’ number to be defined by users (one by system, another by user.) There is also I wanted a design where once created, you can only rectify by edit it or change the status become inactive with reason why is inactive and the effect to the report is 0 or display ‘inactive’ by line items (transaction list), something along that line. to ensure there is accountable and traceable events occurred on the books. Unless the role are administrator which have the full access to delete.

If wondering why we need both by system created and user defined reference, for following reasons.

System

  • Integrity and traceability
  • Comply with most requirement internationally.
  • To be integrated with audit trail feature.
  • There is a room external application for modules.
  • Can be sort System based sequence.

User

  • Associate with external documents
  • Identifiable humanly
  • Cater their own ‘format’ reference sequence.
  • If there is newly created, they still can sort it with existing manager’s filter transaction list.

IF there still fussing about seeing system generated reference number, lubos may add ‘hide system reference number’ feature globally or by tab.

For now, I’m going to keep automatic references based on a sequence. It’s universal and the most common approach.

However, I won’t be forcing automatic reference numbers on everyone anymore. In previous versions, once automatic reference numbers were enabled, there was no way not to use them.

I’m experimenting with how to implement this option and I came up with something like this when creating new receipt or payment:

If the box is checked, you let the system to generate new reference number however you can leave the box unchecked and the system won’t generate any reference number and will leave the field empty (useful if you don’t want to use reference numbers).

Previously if you left the reference field empty, new reference number was forced on you. There was no way not to use them at all. This new mechanism allows for both ways.

2 Likes

I agree this is the best approach. First, it is simple and easily understood by users or auditors. Second, it minimizes disruption of historical numbering. Depending on where in the last series of version changes users may have updated, if they did, some will be caught be surprise and face a daunting task of editing a lot of what began as bank and cash transactions but are now receipts and payments. But I don’t see a way around that.

I strongly support this approach. It satisfies users who have been demanding automatic numbering for everything while also allowing those who want informative content in their references to put it in.

The checkbox works. It is very obvious at the top of the form. Even without a label, it invites experimentation, and the first time anyone enters a receipt or payment, they will probably figure it out.

Overall, I think this probably the best compromise solution between requirements and desires for different situations.

1 Like