AW: sql query to link invoice payments to invoice id?

Derek Atkins warlord at MIT.EDU
Mon Jan 10 10:25:27 EST 2011

"Jannick Asmus" < at> writes:

> Derek Atkins wrote:
>>> I am trying to identify the invoice payments TOGETHER with the
>>> associated invoice in the sqlite file of my GnuCash file. Since
>>> there is a window to process the payment, I suspect there should be
>>> a link between the payment and the invoice guid.
>> The way they are mapped is via the LOT in the A/R account.  When you
>> post an invoice is "creates" a lot, and then payments are applied to
>> that lot until the balance reaches 0 (at which point any remaining
>> proceeds get applied to the next lot owned by the same customer).
> Aah, that explains a lot. I will see how I can manage to get things
> straight out of the sqlite file.
>> So, the mapping would be via the lot's in the splits in the A/R
>> account for the payment transaction.  Note that you could have
>> multiple splits in the A/R account in the case of a payment being
>> applied to multiple invoices.
> This seems to be a problem at the moment. I will think how I can
> handle this.

Write your report in the Gnucash Scheme language and it'll be much
easier as the data will already be bound together for you!

> Thanks for your input!

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


       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL:    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available

More information about the gnucash-user mailing list