Query based upon metadata

[jonathan...@gmail.com]

Because beancount doesn't (yet?) support tags attached to postings—only transactions—I've been trying to figure out how to use metadata to accomplish the same thing. I see that querying based upon metadata is supported, but I don't see anything the in query language specification regarding how to query based upon metadata. Further, neither bean-web nor fava really have first-class support for metadata other than to display it.

As an example, supposing I have the following, how would I query to get all transactions and/or postings related to invoice 200? How could I query to find all #payments related to invoice 200?

2000-01-01 open Assets:Checking USD

2000-01-01 open Assets:Receivables USD

2000-01-01 open Expenses:Billable:Client1 USD

2000-01-01 open Expenses:Billable:Client2 USD

2000-01-01 open Expenses:Other USD

2016-01-01 * "Amazon.com" "purchase on behalf of self, client 1, and client 2"

  Assets:Checking            -6.00 USD

  Expenses:Billable:Client1   1.00 USD

    invoice: "100"

  Expenses:Billable:Client2   2.00 USD

    invoice: "200"

  Expenses:Other              3.00 USD

2016-01-02 * "generate invoice for client 1"

  invoice: "100"

  Expenses:Billable:Client1  -1.00 USD

  Assets:Receivables

2016-01-02 * "generate invoice for client 2"

  invoice: "200"

  Expenses:Billable:Client1  -2.00 USD

  Assets:Receivables

2016-01-03 * "payment received from client 1" #payment

  invoice: "100"

  Assets:Receivables         -1.00 USD

  Assets:Checking

2016-01-03 * "payment received from client 2" #payment

  invoice: "200"

  Assets:Receivables         -2.00 USD

  Assets:Checking

[Martin Blais]

The SQL query environment isn't really documented yet.

Here's why:

- I created this SQL tool in order to experiment and figure out what a good minimum set of functions for accounting would be needed. Turns out it works great so far; I've almost completely replace my own usage of bean-web by SQL commands. It should be time to add a bunch of tests and start baking this IMO, I just have to find the time. In the meantime, whenever someone needs a function, I just add it. Very liberal about new ideas for the shell TBH, it's there for us to discover accounting-specific things we might want to do. In fact, the ANY_META() function in the example below was suggested recently from another user.

- Perhaps more importantly, I want to expand this tool much further and beyond the context of Beancount and will do that at some point; the next version of this tool will be better tested and clearly documented. I'll put the effort there.

To learn all that's in the shell at the moment, the best way is to read the source code for the shell's environment (apologies for that):

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/query/query_env.py

This contains all the available functions.

If you need anything, let me know.

Anyhow, something like this works right now:

  select date, description, position, balance  where any_meta('invoice') = '200'  and ('payment' in tags) 

You can add a clause on the account name to further restrict.

Using ANY_META() causes a lookup to occur on both the posting and the transaction's metadata (falling back on transaction if posting lookup fails).

Finally, you can also easily script any kind of filter you'd like if it's not there. See this doc for details:

https://docs.google.com/document/d/1QftxNvQPdH-MikMBHupftU6F4IsNZP5FlFh1LCbVgk8/edit#heading=h.sx7ubnicolsb

--
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 beancount+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/16380e36-e03e-4d0c-a09e-206382abd59f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.