[GNC] Order of transactions

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[GNC] Order of transactions

Lisa Kurtz
Hi,

I just started using GnuCash, and am still trying to figure out all the
tips and trick of how to use this. I am coming from Quicken, and one of the
things that I was able to do in there that I can't seem to figure out in
GnuCash is choosing the order that the transactions are posting. For
example, in my checking account, I am able to set quicken to input the
transactions with the same date as I enter them, I.E.,

2/26   Deposit
2/26   Check
2/26   EFT
2/26   EFT
2/26   Check
2/26   EFT
etc.

In GnuCash, it looks like it will automatically order them to Deposits,
then EFT's, then Checks:

2/26 Deposit
2/26 EFT
2/26 EFT
2/26 EFT
2/26 Check
2/26 Check

I am really not a fan of this. Is there any way for me to choose how these
transaction are organized in the ledger? I am hoping to be able to at least
list checks before EFT's.

I hope my description makes sense.

Thanks in advance for your help,
Lisa
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

David Cousens
Lisa

In the register display for an account you will see a field Num. You can
specify a number for each transaction which  will determine the order in
which the transactions are displayed.This however affects all registers in
which the transaction appears as per the second reference below. It is
really meant for entering cheque numbers.

See
https://www.gnucash.org/docs/v3/C/gnucash-guide/txns-register-oview.html,
https://www.gnucash.org/docs/v3/C/gnucash-help/book-options.html

The following posts contain useful info as well
http://gnucash.1415818.n4.nabble.com/Ordering-Transactions-on-same-date-td4683272.html

and the FAQ
http://wiki.gnucash.org/wiki/FAQ#Q:_How_do_I_order_transactions_in_a_register_so_deposits_are_before_withdrawls.3F
reproduced below:

Q: How do I order transactions in a register so deposits are before
withdrawals?
A: Enter deposits (debits) first, then withdrawals (credits).

When ordering transactions in a register window (the display of transactions
for a particular account), you're really ordering the splits for that
account. The fields examined, in order, are:

The Posted Date. This is the one that's visible and editable in the register
window.
The Transaction's Num field 'unless' you have selected "Use Split Action
Field for Number" in the Accounts tab of Book Options (accessed by
File>Properties in the menus) in which case it will sort on the Action field
for the split 'in the account displayed on the register window'. Note that
there is also a preference on the General tab to make that selection
automatically on new files.
The Transaction Entry Date/time: This is the date and time that the
transaction was first created. It is not editable in the User Interface.
The Transaction Description field.
The Split's memo field.
The Reconcile field.
The amount of the split.
The value of the split.
The Reconcile Date.
The GUID identifying the split.
Thus the Num or Action field is consulted only if the Posted Date is the
same, the Entry Date/Time only if the Num or Action fields are the same, and
so on.

This question is sometimes asked as "how do I reorder transactions so that
they'll match my bank statement?". Don't. You're keeping your own books, not
the bank's. Use the Reconcile window to reconcile the two.

If you insist on reordering, the only way to do it in the UI is to edit the
Num fields on the transactions in question or the Action field for the split
if you've selected that option; this might cause you problems if you're
using that field for something else, like check numbers.

David Cousens





-----
David Cousens
--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
David Cousens
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Adrien Monteleone-2
Lisa,

I will add to David’s advice that you can use the NUM field other than a simple 1,2,3,4...

You might, for example, need or want to keep an actual transaction number with the transaction.

For that case, you can put the real transaction number in the Action or Memo field of the split.

Otherwise, you can use the NUM field to sort transactions with a time value. (with 4 simple caveats)

1. Using the NUM field this way doesn’t magically turn it into a time field, you’re just going to put time info there. It will still think and treat the info as the transaction NUMber.

2. The sorting algorithm stops when it encounters a non-numeric. So you can’t use the : separator for hours:minutes.

3. This also means you can’t use an AM/PM (a/p) designation, you must use 24 hour time.

4. Since these are just numbers, you might not in the future be able to distinguish them from actual transaction numbers so you’ll need some way to indicate this is a time entry for sorting purposes.

I’ve solved this conundrum by entering the time (or best approximation) of the actual transaction occurrence in 24 hour notation, without a separator, and with the suffix “t” for “time”.

So a transaction I’m entering off a receipt in my Cash account (where this is usually the biggest trouble as a Cash account can’t ever be negative which can and likely will happen at some point if you leave GnuCash to its own devices) that has a real-world time stamp of 3:12pm will be entered with the NUM field containing ‘1512t'. (sans the quotes of course)

This will sort the transaction properly based on its actual order of occurrence and at a glance I know this is not a real transaction number. If this transaction also has one of those, I put it in the Action field. (but I have NOT set the ‘Use Action Field for NUM’ preference as I do not want to do so — it would mess up the sorting. I just want a place to put my info.)

Hope this helps.

-----


David,

Thanks for that detailed run-down of the sort order. (I hadn’t bothered to step through the code yet on that one)

Regards,
Adrien

> On Mar 2, 2019, at 5:38 PM, David Cousens <[hidden email]> wrote:
>
> Lisa
>
> In the register display for an account you will see a field Num. You can
> specify a number for each transaction which  will determine the order in
> which the transactions are displayed.This however affects all registers in
> which the transaction appears as per the second reference below. It is
> really meant for entering cheque numbers.
>
> See
> https://www.gnucash.org/docs/v3/C/gnucash-guide/txns-register-oview.html,
> https://www.gnucash.org/docs/v3/C/gnucash-help/book-options.html
>
> The following posts contain useful info as well
> http://gnucash.1415818.n4.nabble.com/Ordering-Transactions-on-same-date-td4683272.html
>
> and the FAQ
> http://wiki.gnucash.org/wiki/FAQ#Q:_How_do_I_order_transactions_in_a_register_so_deposits_are_before_withdrawls.3F
> reproduced below:
>
> Q: How do I order transactions in a register so deposits are before
> withdrawals?
> A: Enter deposits (debits) first, then withdrawals (credits).
>
> When ordering transactions in a register window (the display of transactions
> for a particular account), you're really ordering the splits for that
> account. The fields examined, in order, are:
>
> The Posted Date. This is the one that's visible and editable in the register
> window.
> The Transaction's Num field 'unless' you have selected "Use Split Action
> Field for Number" in the Accounts tab of Book Options (accessed by
> File>Properties in the menus) in which case it will sort on the Action field
> for the split 'in the account displayed on the register window'. Note that
> there is also a preference on the General tab to make that selection
> automatically on new files.
> The Transaction Entry Date/time: This is the date and time that the
> transaction was first created. It is not editable in the User Interface.
> The Transaction Description field.
> The Split's memo field.
> The Reconcile field.
> The amount of the split.
> The value of the split.
> The Reconcile Date.
> The GUID identifying the split.
> Thus the Num or Action field is consulted only if the Posted Date is the
> same, the Entry Date/Time only if the Num or Action fields are the same, and
> so on.
>
> This question is sometimes asked as "how do I reorder transactions so that
> they'll match my bank statement?". Don't. You're keeping your own books, not
> the bank's. Use the Reconcile window to reconcile the two.
>
> If you insist on reordering, the only way to do it in the UI is to edit the
> Num fields on the transactions in question or the Action field for the split
> if you've selected that option; this might cause you problems if you're
> using that field for something else, like check numbers.
>
> David Cousens

_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Derek Atkins-3
Adrien Monteleone <[hidden email]> writes:

> Lisa,
>
> I will add to David’s advice that you can use the NUM field other than
> a simple 1,2,3,4...
>
> You might, for example, need or want to keep an actual transaction
> number with the transaction.
>
> For that case, you can put the real transaction number in the Action
> or Memo field of the split.

There really is no need to do this.  The Number field is a string-entry,
it will let you input anything you want.  The SORTING mechanism from the
Num field is a NUMERIC sort; it effectively performs the C-function call
atoi() on the contents.  This means you could do something like:

1.16274
2.0023
3.172323

And it will sort based on the 1, 2, 3.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek
--
       Derek Atkins                 617-623-3745
       [hidden email]             www.ihtfp.com
       Computer and Internet Security Consultant
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Adrien Monteleone-2
Yes, that is why I was explaining the abuse of the NUM field as a time field for sorting and how to do it in a way that works. If you are doing this, (or instead using 1,2,3,4 ) *and* you want to keep track of a transaction number, then you’ll have to put it somewhere else so you can still sort that transaction in the order you want. One obvious choice for an alternative would be the Action field for that particular split line. Another would be the Note field. (especially if the Action field is also otherwise utilized) Another option would be the Memo field for the split.

Regards,
Adrien

> On Mar 7, 2019, at 10:57 AM, Derek Atkins <[hidden email]> wrote:
>
> Adrien Monteleone <[hidden email]> writes:
>
>> Lisa,
>>
>> I will add to David’s advice that you can use the NUM field other than
>> a simple 1,2,3,4...
>>
>> You might, for example, need or want to keep an actual transaction
>> number with the transaction.
>>
>> For that case, you can put the real transaction number in the Action
>> or Memo field of the split.
>
> There really is no need to do this.  The Number field is a string-entry,
> it will let you input anything you want.  The SORTING mechanism from the
> Num field is a NUMERIC sort; it effectively performs the C-function call
> atoi() on the contents.  This means you could do something like:
>
> 1.16274
> 2.0023
> 3.172323
>
> And it will sort based on the 1, 2, 3.
>
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>
> -derek
> --
>       Derek Atkins                 617-623-3745
>       [hidden email]             www.ihtfp.com
>       Computer and Internet Security Consultant
>


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Stephen M. Butler
In reply to this post by Derek Atkins-3
On 3/7/19 8:57 AM, Derek Atkins wrote:

> Adrien Monteleone <[hidden email]> writes:
>
>> Lisa,
>>
>> I will add to David’s advice that you can use the NUM field other than
>> a simple 1,2,3,4...
>>
>> You might, for example, need or want to keep an actual transaction
>> number with the transaction.
>>
>> For that case, you can put the real transaction number in the Action
>> or Memo field of the split.
> There really is no need to do this.  The Number field is a string-entry,
> it will let you input anything you want.  The SORTING mechanism from the
> Num field is a NUMERIC sort; it effectively performs the C-function call
> atoi() on the contents.  This means you could do something like:
>
> 1.16274
> 2.0023
> 3.172323
>
> And it will sort based on the 1, 2, 3.
>
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
> -derek

In one of my books, I did 'yyyy-nnnn' for the field and was able to sort
on the entire string.  I've sold that business and don't do that for my
personal book.

--
Stephen M Butler, PMP, PSM
[hidden email]
[hidden email]
253-350-0166
-------------------------------------------
GnuPG Fingerprint:  8A25 9726 D439 758D D846 E5D4 282A 5477 0385 81D8

_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Derek Atkins-3
In reply to this post by Adrien Monteleone-2
Adrien Monteleone <[hidden email]> writes:

> Yes, that is why I was explaining the abuse of the NUM field as a time
> field for sorting and how to do it in a way that works. If you are
> doing this, (or instead using 1,2,3,4 ) *and* you want to keep track
> of a transaction number, then you’ll have to put it somewhere else so
> you can still sort that transaction in the order you want. One obvious
> choice for an alternative would be the Action field for that
> particular split line. Another would be the Note field. (especially if
> the Action field is also otherwise utilized) Another option would be
> the Memo field for the split.

Yes, if you want to order your transactions in a specific order but then
also be able to sort by a transaction number then yes, you would need to
do what you suggest and put the default-sort-order into Num, and the
transaction number into another field.

But why would you want to do that?

You could still SEARCH for transaction number even if you overload num
with <sortNum>.<TxnNum>.  But yes, you couldn't sort on the <TxnNum>
portion -- I just don't see the use case for that.

*shrugs*

> Regards,
> Adrien

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins                 617-623-3745
       [hidden email]             www.ihtfp.com
       Computer and Internet Security Consultant
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Adrien Monteleone-2
I didn’t mean to still be able to sort by the real transaction number, but if you have one, and you’ve repurposed the NUM field, you’ll likely want a place to put it just for reference. I simply offered an overview of the remaining options. Overloading NUM as a time field is pretty much giving up on using it to hold a real transaction number. (though one user piped in they used a yyyy.nnnn format that seems to work, though I don’t see how with the intervening “.”)

The ideal would be to edit the time portion of the transaction's entry but of course, that means more code and maintenance for a non-widespread use case. The occurrence of negative balances is rare unless you extensively use a cash account (where it is impossible in the real world) or you frequently have just the right types of transactions in some other asset so that the GnuCash ordering produces this condition.

Regards,
Adrien


> On Mar 8, 2019, at 8:54 AM, Derek Atkins <[hidden email]> wrote:
>
> Adrien Monteleone <[hidden email]> writes:
>
>> Yes, that is why I was explaining the abuse of the NUM field as a time
>> field for sorting and how to do it in a way that works. If you are
>> doing this, (or instead using 1,2,3,4 ) *and* you want to keep track
>> of a transaction number, then you’ll have to put it somewhere else so
>> you can still sort that transaction in the order you want. One obvious
>> choice for an alternative would be the Action field for that
>> particular split line. Another would be the Note field. (especially if
>> the Action field is also otherwise utilized) Another option would be
>> the Memo field for the split.
>
> Yes, if you want to order your transactions in a specific order but then
> also be able to sort by a transaction number then yes, you would need to
> do what you suggest and put the default-sort-order into Num, and the
> transaction number into another field.
>
> But why would you want to do that?
>
> You could still SEARCH for transaction number even if you overload num
> with <sortNum>.<TxnNum>.  But yes, you couldn't sort on the <TxnNum>
> portion -- I just don't see the use case for that.
>
> *shrugs*
>
>> Regards,
>> Adrien
>
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>
> -derek
>
> --
>       Derek Atkins                 617-623-3745
>       [hidden email]             www.ihtfp.com
>       Computer and Internet Security Consultant
>


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Derek Atkins-3
Adrien,

On Fri, March 8, 2019 2:41 pm, Adrien Monteleone wrote:
> I didn’t mean to still be able to sort by the real transaction number, but
> if you have one, and you’ve repurposed the NUM field, you’ll likely want a
> place to put it just for reference. I simply offered an overview of the
> remaining options. Overloading NUM as a time field is pretty much giving
> up on using it to hold a real transaction number. (though one user piped
> in they used a yyyy.nnnn format that seems to work, though I don’t see how
> with the intervening “.”)

As I said, the string field is converted to an integer using the
C-language atoi() function and then the sort happens using the numerical
value.  So the string yyyy.nnnn with be converted into the number yyyy and
that will be used to sort.  If yyyy is 0001, this would be sorted before a
yyyy of 2.  Basically, the conversion for sorting will stop at the first
non-numeral character.  That's why using yyyy.nnnn works.  Or yyyy-nnnn.
Even yyyyAnnnn will work (this is all assuming the yyyy and nnnn are all
numerals).

So 00011.16298462 will come before 2.294362.   If it were a pure string
compare, then this would not be the case.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins                 617-623-3745
       [hidden email]             www.ihtfp.com
       Computer and Internet Security Consultant

_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Adrien Monteleone-2
I understood that already, but thanks for the detail.

What doesn’t work is sorting off the nnnn part, because the sorting stops at “.”, correct?

That’s why you can’t use “:” in the faux timestamp as the sorting will stop before it gets to the minutes portion, and why you have to use a 24 hour notation because “a/am” or “p/pm” will be ignored.

Regards,
Adrien

> On Mar 8, 2019, at 1:55 PM, Derek Atkins <[hidden email]> wrote:
>
> Adrien,
>
> On Fri, March 8, 2019 2:41 pm, Adrien Monteleone wrote:
>> I didn’t mean to still be able to sort by the real transaction number, but
>> if you have one, and you’ve repurposed the NUM field, you’ll likely want a
>> place to put it just for reference. I simply offered an overview of the
>> remaining options. Overloading NUM as a time field is pretty much giving
>> up on using it to hold a real transaction number. (though one user piped
>> in they used a yyyy.nnnn format that seems to work, though I don’t see how
>> with the intervening “.”)
>
> As I said, the string field is converted to an integer using the
> C-language atoi() function and then the sort happens using the numerical
> value.  So the string yyyy.nnnn with be converted into the number yyyy and
> that will be used to sort.  If yyyy is 0001, this would be sorted before a
> yyyy of 2.  Basically, the conversion for sorting will stop at the first
> non-numeral character.  That's why using yyyy.nnnn works.  Or yyyy-nnnn.
> Even yyyyAnnnn will work (this is all assuming the yyyy and nnnn are all
> numerals).
>
> So 00011.16298462 will come before 2.294362.   If it were a pure string
> compare, then this would not be the case.
>
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>
> -derek
>
> --
>       Derek Atkins                 617-623-3745
>       [hidden email]             www.ihtfp.com
>       Computer and Internet Security Consultant
>
>


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Order of transactions

Derek Atkins-3
Adrien Monteleone <[hidden email]> writes:

> I understood that already, but thanks for the detail.
>
> What doesn’t work is sorting off the nnnn part, because the sorting
> stops at “.”, correct?

Correct.

> That’s why you can’t use “:” in the faux timestamp as the sorting will
> stop before it gets to the minutes portion, and why you have to use a
> 24 hour notation because “a/am” or “p/pm” will be ignored.

Indeed.  You would need to explitly use an HHMM number in 24-hour format
without punctuation.

> Regards,
> Adrien

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek
--
       Derek Atkins                 617-623-3745
       [hidden email]             www.ihtfp.com
       Computer and Internet Security Consultant
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.