[GNC] Search/report by description

[david whiting]

The query I showed in the previous email will show you what you paid for
whatsits, and this code will show you what you charged for dinguses,
limiting to a customer called Mycustomer

SELECT invoices.date_posted, invoices.id, invoices.notes,
  customers.name AS customer,
  description, quantity_num, quantity_denom, i_price_num, i_price_denom,
  quantity_num / CAST(quantity_denom AS FLOAT) AS quantity,
  i_price_num / CAST(i_price_denom AS FLOAT) AS price
  FROM entries
  LEFT JOIN invoices ON entries.invoice = invoices.guid
  LEFT JOIN customers ON invoices.owner_guid = customers.guid
  WHERE description LIKE '%dingus%'
  AND customers.name LIKE '%mycustomer%'

David


On Wed, 25 Aug 2021 at 23:17, david whiting <dw at davidwhiting.me.uk> wrote:

> While you can't do this directly from gnucash, if you use the sqlite
> backend (or other sql backend) you can write your own queries
> fairly easily. For example, you could save your gnucash file as an sqlite
> file and use sqlitebrowser to view it and write queries
> https://sqlitebrowser.org/dl/ (I'm suggesting sqlitebrowser because it is
> free and available for windows, mac and linux).
>
> This query, for example, will find whatsits:
>
> SELECT invoices.date_posted, invoices.id, invoices.notes,
> description, quantity_num, quantity_denom, b_price_num, b_price_denom,
> quantity_num / CAST(quantity_denom AS FLOAT) AS quantity,
> b_price_num / CAST(b_price_denom AS FLOAT) AS price
> FROM entries
> LEFT JOIN invoices ON entries.bill = invoices.guid
> WHERE description LIKE '%whatsits%'
>
> You can limit the dates posted in WHERE clause. I haven't tried to link to
> customers, but it should be possible.
>
> David
>
>
>
> On Wed, 25 Aug 2021 at 20:06, Derek Atkins <derek at ihtfp.com> wrote:
>
>> Hi,
>> Sorry, there is no way to search Invoice Line-Item data.
>> Unclear if PieCash can do that or not.
>> -derek
>>
>> On Wed, August 25, 2021 2:51 pm, R. Victor Klassen wrote:
>> > I’m looking for a report or means of searching all bills/invoices for a
>> > specific pattern in the description field of any line.  I would further
>> > like to restrict it (optionally) by date range or customer/vendor.
>> >
>> > For example I might want to know what we paid for whatsits last year and
>> > from whom all we bought them.  They might be a small subset of what went
>> > in the account and they might have wound up in multiple accounts
>> depending
>> > on their intended end use.
>> >
>> > Similarly I might want to know what we charged for dinguses. And there
>> > might have been some that were accounted as greenhouse dinguses, while
>> > others were grown in the field, but just now I want to know what we
>> > charged customer x for them.
>> >
>> > I’m willing to use piecash if necessary but not write scheme.
>> >
>> > Sent from my iPhone
>> > _______________________________________________
>> > gnucash-user mailing list
>> > gnucash-user at gnucash.org
>> > To update your subscription preferences or to unsubscribe:
>> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> > If you are using Nabble or Gmane, please see
>> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> > -----
>> > Please remember to CC this list on all your replies.
>> > You can do this by using Reply-To-List or Reply-All.
>> >
>>
>>
>> --
>>        Derek Atkins                 617-623-3745
>>        derek at ihtfp.com             www.ihtfp.com
>>        Computer and Internet Security Consultant
>>
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> To update your subscription preferences or to unsubscribe:
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> If you are using Nabble or Gmane, please see
>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>>
>
>
> --
> David Whiting
>


-- 
David Whiting