Hello John,
Thanks for your help. Here's the final select query that worked for me:
SELECT *
FROM gnucash.transactions AS t
JOIN gnucash.splits AS s
ON s.tx_guid = t.guid
JOIN gnucash.accounts AS a
ON s.account_guid = a.guid
WHERE t.guid = '[my-guid]';
My next task is to insert data into these tables while maintaining data
integrity. I'm building myself a mobile web application that will allow me to
enter transaction data. The new transaction data is queued online and my local
system will pickup the new data and insert it into my local MySQL into these
tables on a scheduled basis.
Based on my analysis of these tables, here's my thinking on how to insert the
data correctly. Please let me know if I'm missing anything obvious.
First insert a record in 'gnucash.transactions'
Take the new 'gnucash.transactions.guid' value and insert two records into
'gnucash.splits'
The value for 'gnucash.splits.tx_guid' maps to new 'gnucash.transactions.guid'
value
The value for 'gnucash.splits.account_guid' maps to an existing
'gnucash.accounts.guid' value for each corresponding account
Values for 'gnucash.splits.value_num' and 'gnucash.splits.quantity_num' need to
correspond to the *_denom value
For example if *_denom = 100, then a real value of $1.25 will translate to '125'
Also, while I'm inserting new records I'll need to create my own new guid values
Does this look good? Are there any other tables I should be concerned about
when
adding new transactions?
Your help is greatly appreciated.
Matt
----- Original Message ----
From: John Ralls <jra...@ceridwen.us>
To: Matt Riley <mrile...@yahoo.com>
Cc: gnucash-devel@gnucash.org
Sent: Sun, March 6, 2011 9:32:30 PM
Subject: Re: Database Tables Transactions and Accounts
On Mar 6, 2011, at 7:37 PM, Matt Riley wrote:
> Hello,
>
> I would like to know if there's a cross reference table or file that maps the
> 'guid' database column of the tables 'transactions' and 'accounts' together.
> I
> explored the other database tables and at first glance there does not appear
> to
>
> be one. I'm using MySQL as the local database.
>
> Any help is greatly appreciated.
Transactions aren't directly linked to accounts, splits are, so to put together
a transaction with its accounts you'll do
something like
select * from t=transactions, s=splits, a=accounts join on s.tx_guid = t.guid
and s.account_guid = a.guid...
(The syntax isn't quite right, but you get the idea.)
Regards,
John Ralls
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
