-
AuthorPosts
-
February 27, 2011 at 8:53 am #31191
Can I have a list of the tables and views in the database?
Thanks.
February 28, 2011 at 8:46 am #32686Support
KeymasterHi – thanks for your post. We don’t publish our database schema, but if you can tell us what information you’re after then we’d happy to assist!
March 3, 2011 at 9:24 pm #32687Understandable. There is an view_invoices view, is there a similar one for invoice lines and payments?
Thanks.
March 5, 2011 at 1:01 pm #32688Support
Keymaster>> is there a similar one for invoice lines and payments?
There is a “view_payments” but there’s no view for invoice lines (the “invline” table). These need to be joined to the “inv” table on “invline.inv_id=inv.id”. For example :-
select inv.*, invline.* from inv left join invline on (invline.inv_id=inv.id)
March 13, 2011 at 8:51 pm #32689Hi there, is there a view for invoice payments or could you let me know the query for it (I’m guessing it uses the ALLOC table)? Thanks.
March 14, 2011 at 10:04 am #32690Support
KeymasterThere is a view called “view_invamtalloc” which gets back how much is allocated against an invoice. For example, the following will get all the invoice fields and the amount allocated against each invoice :-
select inv.*, view_invamtalloc.*
from inv left join view_invamtalloc on (view_invamtalloc.inv_id=inv.id)March 17, 2011 at 4:34 pm #32691Thanks again. What would be the query for reporting the invoices that have no payment allocations?
I know view_invoices has this information but for some reason when I want to create a report using a query including view_invoices and other tables, only the view_invoices fields are available in the report data.
Where am I going wrong?
March 17, 2011 at 5:28 pm #32692Support
Keymaster>> What would be the query for reporting the invoices that have no payment allocations?
select inv.*, view_invamtalloc.*
from inv left join view_invamtalloc on (view_invamtalloc.inv_id=inv.id)
where (view_invamtalloc.amount is null) or (view_invamtalloc.amount = 0)>> for some reason when I want to create a report using a query
>> including view_invoices and other tables, only the view_invoices
>> fields are available in the report data.
I’m sorry – I don’t understand what you mean. Please post the full SQL you are using and then let us know which fields are missing. -
AuthorPosts
- You must be logged in to reply to this topic.