On 31/05/24 15:03, Maurice de Laat wrote:
Currently I do have an unique tag for each invoice which is being used
when the invoice is received as well as when the invoice is being payed.
It would be great if I could use these tags to find the invoices yet to
be payed.
If you record the invoicing and payments in transactions that look like
2024-04-30 * "Invoice Aaa"
invoice: 123
Assets:Receivable 2.00 EUR
Income:Work
2024-05-31 * "Invoice Bbb"
invoice: 124
Assets:Receivable 3.00 EUR
Income:Work
2024-05-30 * "Payment Aaa"
invoice: 123
Assets:Bank 2.00 EUR
Assets:Receivable
With a fairly recent beanquery, you can use a query like this one:
SELECT
entry.meta['invoice'] as invoice,
sum(position) as outstanding
FROM
#postings
WHERE
root(account, 2) = 'Assets:Receivable'
GROUP BY
entry.meta['invoice']
HAVING
not empty(sum(position))
With an older beanquery it would look like:
SELECT
entry_meta('invoice') as invoice,
sum(position) as outstanding
WHERE
account ~ '^Assets:Receivable'
GROUP BY
entry_meta('invoice')
HAVING
NOT empty(sum(position))
The HAVING clause and the empty() function are not in the bean-query
distributed with beancount. With such an old version, as suggested by
Paul, you can order by the outstanding balance and filter "by eye" the
rows with an empty field:
SELECT
entry_meta('invoice') as invoice,
sum(position) as outstanding
WHERE
account ~ '^Assets:Receivable'
GROUP BY
entry_meta('invoice')
ORDER BY 2 DESC
This approach allows to easily account for partial payments.
Cheers,
Dan
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/6bf3c0d4-e7dd-46de-b6a0-a2dc9765de6a%40grinta.net.
