External Program

[Mark Mercer]

Hi Aaron, I've tried exporting to sqlite but the file always comes out 
blank.  I've also tried a csv file but it's also blank.

I'll give you a more detailed explanation here now.  From what I can see 
there are only three tables in the database that have records in them 
(other than the "version" table).  I've listed them below along with the 
records that are relevant to what I'm doing.

Accounts
guid, name, type, commodity_guid, commodity_scu, non_std_scu, 
parent_guid... etc...
'5e5acaea00cf48203a8b44d58f96ffad', 'Sales', 'INCOME', 
'f845d29aff6356d67df273c864277df2', '100', '0', 
'94d60e0b5a702628676e7791c2e019a5', '', 'Sales', '0', '0'


Transactions
guid, currency_guid, num, post_date, enter_date, description
'b3fae0a5fd1b5fbe6aac4ae840f38d3c', 'f845d29aff6356d67df273c864277df2', 
'3', '2016-06-08 02:30:00', '2016-06-09 01:55:55', 'Test'


Splits
guid, tx_guid, account_guid, memo, action, reconcile_state, 
reconcile_date, value_num, value_denom, quantity_num, quantity_denom, 
lot_guid
'4ba293ddd80a04ed8510e61e6e3d40c2', 'b3fae0a5fd1b5fbe6aac4ae840f38d3c', 
'5e5acaea00cf48203a8b44d58f96ffad', '', '', 'n', NULL, '-1000', '100', 
'-1000', '100', NULL
'abd191aa748822fdac2112b629708ee5', 'b3fae0a5fd1b5fbe6aac4ae840f38d3c', 
'7d0fd461835281509cf260e8a1baa272', '', '', 'n', NULL, '1000', '100', 
'1000', '100', NULL

So, its easy to see that the split table tx_guid refers to the 
transaction record and that the split table account_guid refers to the 
account record.  The split table links it all together.  And it uses two 
records of opposing values because they have to be reconciled or 
something.  I don't know enough about accounting to know what a split is 
or exactly why we need to do all this reconciling business but I'm 
willing to live with it if that's the way it's supposed to be done.
The denom field represents basically the number of cents in a dollar and 
the value field when divided by the denom shows the amount of the 
transaction (so I've entered a $10 sale).
Then I manually duplicated the three records and replaced the first 
character in the guid's with M so I know I made them and changed the 
value of the copy to $22.22.  The records I created are shown below but 
gnucash doesn't display them at all.

New Transactions (I created)
'M3fae0a5fd1b5fbe6aac4ae840f38d3c', 'f845d29aff6356d67df273c864277df2', 
'4', '2016-06-08 02:30:00', '2016-06-09 01:55:55', 'Test2'

New Splits (I created)
'Mba293ddd80a04ed8510e61e6e3d40c2', 'M3fae0a5fd1b5fbe6aac4ae840f38d3c', 
'5e5acaea00cf48203a8b44d58f96ffad', '', '', 'n', NULL, '-2222', '100', 
'-2222', '100', NULL
'Mbd191aa748822fdac2112b629708ee5', 'M3fae0a5fd1b5fbe6aac4ae840f38d3c', 
'7d0fd461835281509cf260e8a1baa272', '', '', 'n', NULL, '2222', '100', 
'2222', '100', NULL


For this reason I just suspected gnucash does some sort of checksum on 
the guid's and it could identify the ones I inserted as bogus. But if 
not this method SHOULD work.  Ideas?


On 10/06/2016 11:30 AM, Aaron Laws wrote:
> On Thu, Jun 9, 2016 at 6:34 PM, Mark Mercer <markm at woodjoint.ca 
> <mailto:markm at woodjoint.ca>> wrote:
>
>     Thank you both for your replies.
>
>     What I actually did was I went into gnucash and created a record
>     in one of the sections and then I opened up SQL workbench and
>     looked through the tables.  The record I created actually made
>     entries in three or four tables so I studied what each table was
>     for which guid's pointed to where and then duplicated the records
>     in the database and assigned new guid's that I made up.  I opened
>     gnucash and it did not display the new records that I created. Any
>     idea's?  I must be missing something.
>
>     I will use gnucash to enter most records but for these sales
>     receipts I think it will be a bit clunky.  I would rather create a
>     robust app on my own that can enter the data in a few clicks and
>     selecting stuff from dropdows etc.
>
>     If you could help me a little further to get this working it would
>     make everything so much simpler for me.
>
>     Thanks again.
>
>
> I'll be happy to dig into this with you and help you more, but I 
> really need to know what you've found and what you're doing. Perhaps a 
> minimal sqlite .gnucash file? I'm not sure how to generate a customer 
> receipt.