hi @vomikan, @whalley , @n-stein ,can you take a look

we can step to SUID now after #6965 is done

On the account views and transaction reports we of course display the ID and the user can sort by this to see the order in which transactions have been added. Putting aside the fact that this is being displayed truncation we have now lost the ability to sort by transaction order (or at least that is how it may look - the actual number may be incrementing based on time... but may confuse users).

Truncation easily fixed (but with very large display number), by wxFormat changes for displayID in transactions.cpp, mmcheckingpanel.cpp and Model_Checking.cpp

The SUID is of little interest to the user because it is too long to remember, however SUIDs are monotonic in time, so sorting by SUID is the same as sorting by time of creation. Old ID's are small numbers and have been created before the introduction of SUID, so they are also sorted by time of creation.

Proposal: instead of the ID (SUID), show the sequence number, starting from 1 for the first item in the list and incremented by 1 for each next item, with a label like # or S/N.

The ID can be a separate column, which is hidden by default.

Proposal: instead of the ID (SUID), show the sequence number, starting from 1 for the first item in the list and incremented by 1 for each next item, with a label like # or S/N.

The ID can be a separate column, which is hidden by default.

That would work, but suspect we don't need to display the ID as we don't display for any other tables and may confuse. Perhaps we just replace display of ID by the sequence number.

Can change DB SELECT queries to do an SQL RANK to generate the sequence number...

E.g. SELECT *, RANK () OVER (ORDER BY TRANSID ASC) SEQNO FROM CHECKINGACCOUNT_V1;

I'm not very good with Python and it will need tweaks to the sqlite2cpp.py to add this for the tables.

Maybe you don't need DB support to assign sequence numbers, you can do it on the fly when you generate the list of items to be displayed. If they are stored in a table, the sequence number is the table index plus 1. If there are more items in the table (like executions of scheduled transactions), you can add an additional field for the sequence number in an auxiliary table and fill it in on the fly.

Or you may assign sequence numbers also to scheduled executions. But you need to distinguish between transactions and scheduled executions in the panels, I did this by showing an empty ID field for scheduled executions.

The sequence number is not any sticky information applied to a transaction. Different filters or even a redraw may assign different sequence numbers. It is just a visual aid to the users and a handle to sort by time of creation, but its value is actually opaque. From the user's point of view, the same is more or less true for the ID, except that it is sticky. A sequence number showing the order of an item in the currently displayed list makes more sense, than the permanent order of the same item in DB.

Maybe you don't need DB support to assign sequence numbers, you can do it on the fly when you generate the list of items to be displayed. If they are stored in a table, the sequence number is the table index plus 1. If there are more items in the table (like executions of scheduled transactions), you can add an additional field for the sequence number in an auxiliary table and fill it in on the fly.

Or you may assign sequence numbers also to scheduled executions. But you need to distinguish between transactions and scheduled executions in the panels, I did this by showing an empty ID field for scheduled executions.

The sequence number is not any sticky information applied to a transaction. Different filters or even a redraw may assign different sequence numbers. It is just a visual aid to the users and a handle to sort by time of creation, but its value is actually opaque. From the user's point of view, the same is more or less true for the ID, except that it is sticky.

Possibly... Though I was thinking that the squence number would behave similarly to the existing ID (but from 1..N), i.e. it represents the order in which data is added to the table. So you if you sorted by another field, e.g. date or payee then sequence number would not reset from 1...N based on date/payee sort. I guess retaining the ID (perhaps hidden as default as you suggest) might make this still possible, but if we do this the sequence number is then not really needed.

My proposal was to assign sequence numbers when the list is generated, and to preserve them until a new list is generated. Sorting by another column of course does not change the sequence numbers. So in this respect a sequence number behaves like a temporary ID.

The transactions list from a DB fetch must be sorted by ID. Then a sequence number can be easily assigned. No other information is needed from DB.

Sorry for not being very clear in my first proposal.

By the way, for almost all (I think for all) other tables, there is no need to display the ID or a sequence number, because they have either a unique key which is more important, or other fields which are sufficient for ordering.

In transaction panels, the ID is essentially used as a substitute for a missing time-of-creation field. There is a date field, but it is not enough for sorting by time of creation.

I think you need to store the sequence number in a separate field, you cannot just overwrite the ID (SUID) with a sequence number in the internal list of transactions. If the user wants to edit or process an item in the list, then you need the ID of this item in order to interact with DB.

I think you need to store the sequence number in a separate field, you cannot just overwrite the ID (SUID) with a sequence number in the list of transactions. If the user wants to edit or process an item in the list, then you need the ID of this item in order to interact with DB.

Agreed, my original thought was to generate a sequence number in addition to all the 'real' DB fields (including ID) hence the SQL RANK(). But as you note we only really need this for the one table view so perhaps we just code for it there.

@whalley
Since the display of ID is under revision, I have a relevant question/request.

TransactionListCtrl::OnColClick forces the secondary sort column (prev_g_sortcol) to be ID when the primary (g_sortcol) is Date. This is sometimes annoying, because there is no way to sort by Date/Number or any other pairing with Date.

Could we remove this restriction, so the user can select any secondary together with Date, like in all other cases? Or if Date/ID is often needed, could we assign it to a modifier-key + click on Date?

@whalley Since the display of ID is under revision, I have a relevant question/request.

TransactionListCtrl::OnColClick forces the secondary sort column (prev_g_sortcol) to be ID when the primary (g_sortcol) is Date. This is sometimes annoying, because there is no way to sort by Date/Number or any other pairing with Date.

Could we remove this restriction, so the user can select any secondary together with Date, like in all other cases? Or if Date/ID is often needed, could we assign it to a modifier-key + click on Date?

There is discussion on this here: #4690

guys, how about this PR itself?

I'm going to merge it if there is no other objection.

Large id would be from mobile devices as sync data is common.

@whalley, @guanlisheng
I can implement sequence numbers in transaction panels, unless you already work on it. I estimate about 1 day.

@whalley, @guanlisheng I can implement sequence numbers in transaction panels, unless you already work on it. I estimate about 1 day.

Go for it. I've got used to the large transaction IDs now :-) But it would be a good usablility feature to have the sequence number.

Is there a way the sequence number can be derived based on transaction data? It would be good to have a calculated sequence number that provides consistency, otherwise it might be confusing if the same transaction had a different sequence number in the account view, account group view, all transactions view, and transaction report.

The idea is that the sequence number is only a placeholder for sorting purposes, and its value is of no interest. It is not any kind of identification and it is not sticky (it may change at every refresh). In my proposal above it is created on the fly each time a panel is created or refreshed.

Essentially it is used instead of the creation datetime. In other panels and reports the ID is also not included.

The main problem in the identification of transactions is that they don't have a unique key. Even if time is enabled, the DB does not guarantee unique datetime. ID is now way too long to be useful for this purpose.

For cross reference between panels/reports, it is maybe more convenient to have some kind of hyperlinks, instead of asking users to memorize IDs.

I think it is better to have both the ID and SN as separate columns. The users can choose which one to keep and which to hide.

@georgeef - thoughts on changing the column header text from "SN" to "Sequence #" or just "#" instead? I realize "SN" is Sequence Number, but it may not be obvious to most users. The # is generally known as the number sign.

For example, we do this on the Import dialog to number the transactions:

This was one of the options, it also has the big advantage that it doesn't need translation. On the other hand, # doesn't look so nice in the sort indicator and SN is more symmetric to ID. # could be confusing, it might be interpreted as ID, maybe it is used already with different meaning somewhere else.
At the end I decided to use the more descriptive name SN. The meaning can be explained in a tooltip.

Added a tip for SN in #7095.