[GNC] GnuCash losing Notes, or just a display problem in the Register?

Wed May 23 18:39:12 EDT 2018

For that sqlite search, look at the ‘slots’ table, specifically find all rows that have ’notes’ as the ’name’ value and then look at the ’string_val’ field for the actual note text.

The following query would return all data in a row that contained a note with actual text. (but not return rows without notes) The first rows will likely be notes for accounts, followed by transaction notes:

SELECT * FROM slots WHERE string_val IS NOT “” AND name = “notes”;

(it appears the string_val column contains blank strings, so ‘IS NOT NULL’ as a condition will not work)

This version would give you only the transaction guid and the note itself. The guid is a key to both the transactions table which contains the description and date, and to the splits table which contain the info for each split in the transaction. (at least two per, with the memos, values, accounts, etc.)

SELECT obj_guid, string_val FROM slots WHERE string_val IS NOT “” AND name = “notes”;

Be warned, I really don’t recommend trying to insert this data from one file to another via SQL without speaking to a developer. I’m presenting this only to verify if the notes are in fact missing from that version of the file.

Regards,
Adrien

> On May 23, 2018, at 4:55 PM, Adrien Monteleone <adrien.monteleone at lusfiber.net> wrote:
> 
> First, be sure to view the newer file with an sqlite viewer and verify the notes are indeed missing. There are several free viewers around the web, including a Firefox plugin if that’s what you use.(not sure if Chrome/Chromium has one)
> 
> If they are indeed gone, short of figuring out how to pull those notes out and re-insert them in the new file:
> 
> Do you have a backup from just before moving to 3.x?
> 
> If so, do you still have 2.6.x installed?
> 
> If not, you can install it (with a different name alongside 3.x) then open that backup file, save it as XML, then re-open the XML with 3.x.(in a worst case, you might have to overwrite the 3.x installation with 2.6.21 to accomplish this and then re-install 3.1)
> 
> You’ll of course have to find a way to add-in your transactions since the original migration, but you should get your 10 years worth of notes back.
> 
> While an SQL solution might be faster, that’s only if you already know how to do it. The time spent on learning SQL and then the implementation will likely take much longer than restoring a backup and then filling in gaps. There can also be complications with db editing outside of the GnuCash app. So restoring a backup is also likely much ’safer’ for overall data integrity at this time.
> 
> Regards,
> Adrien
> 
>> On May 23, 2018, at 3:10 PM, Randy Orrison <randy.orrison at gmail.com> wrote:
>> 
>> Hi,
>> 
>> Thanks for the confirmation.  I have created bug https://bugzilla.gnome.org/show_bug.cgi?id=796369.
>> 
>> Unfortunately, saving as XML and reopening the file did not restore any of my missing notes.  Newly created notes in the XML file are being saved and display correctly after re-opening the file, but 10 years of notes appear to be gone.
>> 
>> If someone could create a tool to extract notes from an old SQLite file that still contained them, and restore them into my current file, that would be very helpful.
>> 
>> Randy
>> 
>> On 23 May 2018 at 00:28, Adrien Monteleone <adrien.monteleone at lusfiber.net> wrote:
>> Randy,
>> 
>> The good news is that your notes are probably still there.
>> 
>> But you did find a bug.
>> 
>> I was using sqlite3 before the 3.x jump and I’ve been testing it back and forth with XML but there are still some show stoppers for me. (this would be a new one on the list) So I had an sqlite3 copy of my file to try out.
>> 
>> Sure enough, transactions that have notes do not show their notes using the sqlite3 backend, but they do when using the XML backend.
>> 
>> So, quick work around for you, is to save the file as XML and use that for the time being and keep the sqlite3 version for testing till your comfortable with the results.
>> 
>> Also, please file a bug report on this. Be sure to set it as ’SQL-Backend’ since we know that notes works properly otherwise. This also isn’t specific to Windows because I’m on a Mac and see the same bug.
>> 
>> Regards,
>> Adrien
>> 
>> 
>> 
>>> On May 22, 2018, at 5:00 PM, Randy Orrison <randy.orrison at gmail.com> wrote:
>>> 
>>> I've just noticed in Version 3.1 that all my Notes have vanished from the
>>> register.  I use GnuCash with the register set to Basic Ledger and Double
>>> Line Mode, so I always see the Description and Notes fields.  I've noticed
>>> today that all of the Notes fields are blank, including some that I know
>>> I've entered.  I looked in Bugzilla but couldn't find an existing bug -
>>> have I just missed it?
>>> 
>>> I'm using Version 3.1, Build ID: 3.0-118-gd2ef5fd0f+ (2018-04-28), on
>>> Windows 10; I think I'm using the SQLlite storage.
>>> 
>>> Detailed steps:
>>> Select an account, go to the bottom new transaction line:
>>> 
>>> Enter the date, Description, an account, an amount, and something in the
>>> Notes field, and hit Enter
>>> See that the Notes appear in the register:
>>> 
>>> 
>>> Close the tab for the register
>>> Re-open the account
>>> See that the Notes still appear in the register for that transaction
>>> Close GnuCash
>>> Re-open GnuCash, then re-open the account
>>> See that the test note, and all other notes, are gone
>>> 
>>> 
>>> I really really hope this is just a display issue and the notes aren't
>>> actually lost from the data.
>>> 
>>> If this isn't already in Bugzilla I'll add it.
>>> 
>>> Randy
>>> <image.png><image.png><image.png>_______________________________________________
>>> gnucash-user mailing list
>>> gnucash-user at gnucash.org
>>> 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.
>> 
>> 
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> 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.
>> 
> 