For some time, I’ve had the question “What did we charge for x” or “What did we
pay for y”? The way I have typically answered it was to go to the account
where x or y would be found in the description field, and then find an invoice
number or bill number from the register, and then go to the search invoice or
search bill menu items, giving the bill or invoice number. This works, but is
somewhat tedious, especially if I should want to check more than one bill or
invoice. The trouble being, of course, that just looking at the split doesn’t
tell be how many of x or y were being bought/sold in that transaction, so I
can’t see the unit price without the extra step.
So I bit the bullet and figured out how to do an Sqlite3 query directly to get
what I want to know for invoices. It appears to work, although it is not
brief or immediately obvious to those not versed in sql:
.separator :
.headers on
select ( date / 10000000000 ) as year, /* strip apart the date into year month
day to make it more readable */
( date - ( date / 10000000000 ) * 10000000000 ) / 100000000 as month ,
( date - ( date / 100000000 ) * 100000000 ) / 1000000 as day,
description, ( i_price_num + 0.0 ) / i_price_denom as price, /*
convert rational price to decimal, add 0.0 so as to get non-integer division */
name, I.id
from entries as E /* An entry is a line in an invoice or bill */
join invoices as I on E.invoice=I.guid /* the invoice field in
the entry is the invoice's guid */
join customers as C on C.guid=I.owner_guid /* the owner guid in
the invoice identifies the vendor */
where date > 20140101000000
and date < 20170731000000
and description like '%eat%’ /* % is a 0 or more matching wild card */
limit 40;
.exit
I can change the date range or the pattern in the description and all is good.
It may not be elegant, but it works. Here’s my question, and I suspect there
are only two or three folks out there who know the answer: When I change the
word “customers” to “vendors” I get garbage. By which I mean that the name
fields appear to have the names of vendors, as expected, but the description
fields are from entries in invoices, not bills: they are things we only sell,
never buy. Ideas?
_______________________________________________
gnucash-user mailing list
[email protected]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
