Customer and Job Report (re-posting)

[Lewis Balentine]

I can not seem to get this response on the correct thread ... many 
apologies. This will be my last attempt.


You can instructions for using SQLite to extract data (create reports) 
at the URL: http://www.keywild.com/gnucash/

I have added a section to the PDF for joining tables and running JOB 
reports ... however at this point it only contains the SQLCode. There is 
also a sample database named 'bogus-sql3.gnucash' that includes a few 
JOBS records.



The following SQLite3 code has been tested and verified:

-- list all fields for all jobs
select * from jobs;

-- list the job id of active jobs
select distinct owner_guid from jobs where active !=0;

-- list customer's id, company and contact name for customers with 
active jobs
Select customers.id as "Cust ID", customers.name as "Descr", 
customers.addr_name as "name"
from customers
where customers.guid in (select distinct owner_guid from jobs where 
active !=0);

-- implicityly join customers and jobs tables
Select customers.id, customers.name, customers.addr_name, jobs.id, 
jobs.name, jobs.reference
from customers, jobs where customers.guid = jobs.owner_guid
and customers.guid in (select distinct owner_guid from jobs where 
owner_type=2);

-- List all active customer job data ordered by the job number
Select
jobs.active, jobs.id as "Job No",
jobs.name as "Descr", jobs.reference "Ref PO",
customers.id as "Cust Id", customers.name as "Dscr", customers.addr_name 
as "Name"
from customers, jobs where customers.guid = jobs.owner_guid
and customers.guid in
(select distinct owner_guid from jobs where active !=0
  and owner_type=2
)
and jobs.active !=0
order by jobs.id;

-- List all active vendor job data ordered by the job number
Select
jobs.id as "Job No",
jobs.name as "Descr", jobs.reference "Ref PO",
vendors.id as "Vend Id", vendors.name as "Dscr", vendors.addr_name as 
"Name"
from vendors, jobs where vendors.guid = jobs.owner_guid
and vendors.guid in
(select distinct owner_guid from jobs where active !=0
  and owner_type=4
)
and jobs.active !=0
order by jobs.id;

enjoy ...





On 05/11/2016 09:31 AM, Aaron Laws wrote:
> On Tue, May 10, 2016 at 11:24 PM, Benjamin Soffer (SLF) <
> bsoffer at soffer-law.com> wrote:
>
>> Hello,
>>
>> It is possible to generate one report listing all the "customers" and all
>> the "jobs" that are associated with each of the customers?  Alternatively,
>> can one generate a list only of all the jobs?
>>
>> Thanks
>>
>> Ben
>>
> I don't use any of the "business" features of gnucash, but if you're asking
> for a second time, I'll give you what I can. If you don't see a report in
> gnucash's report set, you'll probably have to make your own. The database
> schema is very straightforward. To get the listing you requested using
> sqlite3:
>
> select j.id, j.name, c.name, c.id
> from jobs j join customers c on c.guid = j.owner_guid;
>
> (If you save as sqlite, the .gnucash file itself is a sqlite3 database.)
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.