Tables and Views

[adminboy]

Can I have a list of the tables and views in the database?

Thanks.

[Support]

Hi – 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!

[adminboy]

Understandable. There is an view_invoices view, is there a similar one for invoice lines and payments?

Thanks.

[Support]

>> 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)

[adminboy]

Hi 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.

[Support]

There 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)

[adminboy]

Thanks 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?

[Support]

>> 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.

[Author]

Posts