After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 796537 - Transaction Report cannot sort by "num"
Transaction Report cannot sort by "num"
Status: RESOLVED OBSOLETE
Product: GnuCash
Classification: Other
Component: Reports
3.1
Other Windows
: Normal normal
: future
Assigned To: gnucash-reports-maint
gnucash-reports-maint
Depends on:
Blocks:
 
 
Reported: 2018-06-08 00:57 UTC by Alex Mak
Modified: 2018-06-30 00:11 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Accounts page (198.51 KB, image/jpeg)
2018-06-09 01:56 UTC, Alex Mak
Details
Display page (172.42 KB, image/jpeg)
2018-06-09 01:57 UTC, Alex Mak
Details
Filter page (169.85 KB, image/jpeg)
2018-06-09 01:57 UTC, Alex Mak
Details
General page (178.49 KB, image/jpeg)
2018-06-09 01:58 UTC, Alex Mak
Details
Sorting (178.21 KB, image/jpeg)
2018-06-09 01:58 UTC, Alex Mak
Details
Split action for num setting (168.94 KB, image/jpeg)
2018-06-09 01:59 UTC, Alex Mak
Details
Results page 1 (638.21 KB, image/jpeg)
2018-06-09 02:00 UTC, Alex Mak
Details
Results page 2 (658.24 KB, image/jpeg)
2018-06-09 02:00 UTC, Alex Mak
Details
Results page 2 (658.24 KB, image/jpeg)
2018-06-09 02:03 UTC, Alex Mak
Details
Num is missing (525.74 KB, image/jpeg)
2018-06-09 02:53 UTC, Alex Mak
Details
Option Summary (294.71 KB, image/jpeg)
2018-06-12 05:12 UTC, Alex Mak
Details

Description Alex Mak 2018-06-08 00:57:17 UTC
Transaction Report is not able to sort by "num" , even sort by primary or secondary key.
Comment 1 John Ralls 2018-06-08 18:36:06 UTC
I'm unable to replicate this. Please tell us step-by-step what you did and attach a supporting screenshot showing that you've set the sort key and that the report isn't sorted.
Comment 2 Chris 2018-06-09 01:04:02 UTC
@Alex: for screenshot please enable 'General / Add options summary' to 'Always'

Also please report setting for File > Properties > Accounts > Use split action field for number
Comment 3 Alex Mak 2018-06-09 01:56:38 UTC
Created attachment 372609 [details]
Accounts page
Comment 4 Alex Mak 2018-06-09 01:57:14 UTC
Created attachment 372610 [details]
Display page
Comment 5 Alex Mak 2018-06-09 01:57:44 UTC
Created attachment 372611 [details]
Filter page
Comment 6 Alex Mak 2018-06-09 01:58:19 UTC
Created attachment 372612 [details]
General page
Comment 7 Alex Mak 2018-06-09 01:58:53 UTC
Created attachment 372613 [details]
Sorting
Comment 8 Alex Mak 2018-06-09 01:59:52 UTC
Created attachment 372614 [details]
Split action for num setting
Comment 9 Alex Mak 2018-06-09 02:00:24 UTC
Created attachment 372615 [details]
Results page 1
Comment 10 Alex Mak 2018-06-09 02:00:57 UTC
Created attachment 372616 [details]
Results page 2
Comment 11 Alex Mak 2018-06-09 02:03:33 UTC
Created attachment 372617 [details]
Results page 2
Comment 12 Chris 2018-06-09 02:10:13 UTC
I suspect the File > Properties > Use split action field for number should be unticked. I think this setting was created to mimic Quicken behaviour. Try untick and refresh.
Comment 13 Alex Mak 2018-06-09 02:16:45 UTC
Hi All

Step 1 : Reports --> Transaction Report
Step 2 : Select account in the account page
Step 3 : In the Sorting page, select primary key = number

Results : There is some blank gap in the number field cannot be sort and seem appear in random order. (See the attachment Results page 1 & 2)

Use split action field for number is ticked.
Comment 14 Chris 2018-06-09 02:19:55 UTC
try untick split-action-field-for-number
Comment 15 Alex Mak 2018-06-09 02:21:20 UTC
(In reply to Chris from comment #14)
> try untick split-action-field-for-number

Just try to untick split-action-field-for-number, the "num" field cannot shown in the report at all ...
Comment 16 Chris 2018-06-09 02:26:33 UTC
It'll be in Display page, should be 'Num' otherwise please resend screenshot when 'split-action-field' is unticked
Comment 17 Alex Mak 2018-06-09 02:52:42 UTC
(In reply to Chris from comment #16)
> It'll be in Display page, should be 'Num' otherwise please resend screenshot
> when 'split-action-field' is unticked

See the capture "Num is missing".jpg after untick
Comment 18 Alex Mak 2018-06-09 02:53:10 UTC
Created attachment 372618 [details]
Num is missing
Comment 19 Chris 2018-06-09 03:40:11 UTC
I think all these numbers that you wish to sort by were stored in the *split/action* field (which is meant for "ATM" "Refund" etc) rather than *transaction/number* (which was meant to show check numbers) field.

Where/how did this data get entered, manually? If so then may be best to keep using 'split-action-for-num-field', because to correct it manually will be rather difficult.

I really think the split-action setting was designed to shoehorn quicken users into gnucash and causes confusion as above, and it's rather unfortunate that users who enable it get into difficulties as above.

I don't think there's an easy solution.

Least painful solution is to not use Number field and use Date field for sorting. Copy each "Split/Action" data onto "Transaction/Number" data and untick split-action-for-num-field.

More painful solution is to amend transaction.scm to achieve the desired sorting, which will involve hacking scheme and will be overwritten when upgrading Gnucash.
Comment 20 Alex Mak 2018-06-09 04:00:48 UTC
(In reply to Chris from comment #19)
> I think all these numbers that you wish to sort by were stored in the
> *split/action* field (which is meant for "ATM" "Refund" etc) rather than
> *transaction/number* (which was meant to show check numbers) field.
> 
> Where/how did this data get entered, manually? If so then may be best to
> keep using 'split-action-for-num-field', because to correct it manually will
> be rather difficult.
> 
> I really think the split-action setting was designed to shoehorn quicken
> users into gnucash and causes confusion as above, and it's rather
> unfortunate that users who enable it get into difficulties as above.
> 
> I don't think there's an easy solution.
> 
> Least painful solution is to not use Number field and use Date field for
> sorting. Copy each "Split/Action" data onto "Transaction/Number" data and
> untick split-action-for-num-field.
> 
> More painful solution is to amend transaction.scm to achieve the desired
> sorting, which will involve hacking scheme and will be overwritten when
> upgrading Gnucash.

However, the sorting is work at 2.6.x version ...
Comment 21 Alex Mak 2018-06-09 04:12:17 UTC
(In reply to Alex Mak from comment #20)
> (In reply to Chris from comment #19)
> > I think all these numbers that you wish to sort by were stored in the
> > *split/action* field (which is meant for "ATM" "Refund" etc) rather than
> > *transaction/number* (which was meant to show check numbers) field.
> > 
> > Where/how did this data get entered, manually? If so then may be best to
> > keep using 'split-action-for-num-field', because to correct it manually will
> > be rather difficult.
> > 
> > I really think the split-action setting was designed to shoehorn quicken
> > users into gnucash and causes confusion as above, and it's rather
> > unfortunate that users who enable it get into difficulties as above.
> > 
> > I don't think there's an easy solution.
> > 
> > Least painful solution is to not use Number field and use Date field for
> > sorting. Copy each "Split/Action" data onto "Transaction/Number" data and
> > untick split-action-for-num-field.
> > 
> > More painful solution is to amend transaction.scm to achieve the desired
> > sorting, which will involve hacking scheme and will be overwritten when
> > upgrading Gnucash.
> 
> However, the sorting is work at 2.6.x version ...

Actually, I put the actual transaction date from bank statement into the "Num"  for reconcile purpose, the sorted records will just matched with the bank statement order.

The official reconcile tool is not user friendly for me and usable for me ...
Comment 22 Chris 2018-06-09 05:45:57 UTC
I think in this case, split/action fields were being populated with your transaction-date, and any missing split/action cannot be sorted properly. I don't really think there's an easy solution at all; the split-action-for-num-field being ticked will have created a database that's difficult to shoehorn into the current transaction.scm.

Most users (and accountants) will advocate ignoring the bank statement date and order, and just ensure all expected transactions are present (in any order).
Comment 23 Chris 2018-06-09 12:45:56 UTC
>> More painful solution is to amend transaction.scm to achieve the desired
>> sorting, which will involve hacking scheme and will be overwritten when
>> upgrading Gnucash.

> However, the sorting is work at 2.6.x version ...

Which is odd, because although transaction.scm was upgraded, the sorting key for "number" was intentionally kept intact, unless a bug was introduced.
Comment 24 Alex Mak 2018-06-10 09:21:26 UTC
Hi All

I think I found the reason why it is not working. In the sorting page, Seem there is a option "Primary subtotal for data key" . it is grey color and cannot be changed if the primary key is number. It is equal to "Monthly". So all the blank of the num field is appeared at the beginning of each Month. However, I cannot disable the primary subtotal ... It is grey color as well ...

Seem it will do a monthly subtotal for all case ...
Comment 25 Chris 2018-06-10 09:28:41 UTC
Hi Alex this indeed indicates a subtle bug. It's 'primary subtotal for date key' which aims to provide monthly/weekly/etc subtotals for Posting-Date (rather than Num).

It would help to investigate this bug - please generate the 'monthly-empty-number' report via options, and enable General/Add options summary to 'always'.

I really wish to know the options summary printed at the top of the report to try replicate, and amend transaction.scm to fix it.
Comment 26 Alex Mak 2018-06-10 11:39:20 UTC
(In reply to Chris from comment #25)
> Hi Alex this indeed indicates a subtle bug. It's 'primary subtotal for date
> key' which aims to provide monthly/weekly/etc subtotals for Posting-Date
> (rather than Num).
> 
> It would help to investigate this bug - please generate the
> 'monthly-empty-number' report via options, and enable General/Add options
> summary to 'always'.
> 
> I really wish to know the options summary printed at the top of the report
> to try replicate, and amend transaction.scm to fix it.

What is "Monthly-empty-number" ?
Comment 27 Chris 2018-06-10 14:57:51 UTC
I meant - generate the report which brings all the empty number fields to the top.
Comment 28 Alex Mak 2018-06-12 05:12:52 UTC
Created attachment 372644 [details]
Option Summary
Comment 29 Alex Mak 2018-06-12 05:13:19 UTC
(In reply to Chris from comment #25)
> Hi Alex this indeed indicates a subtle bug. It's 'primary subtotal for date
> key' which aims to provide monthly/weekly/etc subtotals for Posting-Date
> (rather than Num).
> 
> It would help to investigate this bug - please generate the
> 'monthly-empty-number' report via options, and enable General/Add options
> summary to 'always'.
> 
> I really wish to know the options summary printed at the top of the report
> to try replicate, and amend transaction.scm to fix it.

Option summary as attached.
Comment 30 Chris 2018-06-12 14:49:58 UTC
............ I found the cause for this genuine bug.

TECH ALERT:
It's exactly as described. The split-action-for-num-field isn't actually being respected for the sortkey-list generation, and the original cause is the reason that jralls needed to add (gnc-current-session-exist) during sortkey-list definition.

In 2.6.xx, the sortkey was being specified during the report rendering according to report options, therefore a current session did exist, and the split-action setting could be retrieved, and the right QofQuery was chosen.

In 3.0 the sortkey-list was being defined at the top level, which means the session did not exist; yet we need to retrieve the split-action setting to define the sortkeys. Jralls's solution to query (gnc-current-session-exist) to prevent a dialog box was effective, but flawed: it meant the sortkey was always 'trans-num' rather than 'trans-num or split-action'.

In scheme split-action cannot be modified so I couldn't test it.

SOLUTION:
We cannot rely on the sortkey-list being static anymore. It needs to be redefined per report instance. It must be changed to a function, taking split-action as a parameter to dynamically define the sortkeys.

LAYMAN VERSION:
Bug fix. split-action setting sorting should match 2.6 behaviour. Thank you for reporting this bug!
Comment 31 John Ralls 2018-06-30 00:11:33 UTC
GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=796537. Please continue processing the bug there and please update any external references or bookmarks.