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 <d...@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 <de...@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@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 >> de...@ihtfp.com www.ihtfp.com >> Computer and Internet Security Consultant >> >> _______________________________________________ >> gnucash-user mailing list >> gnucash-user@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 _______________________________________________ gnucash-user mailing list gnucash-user@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.
