Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #31191

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

    Thanks.

    #32686

    Support
    Keymaster

    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!

    #32687

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

    Thanks.

    #32688

    Support
    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)
    #32689

    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.

    #32690

    Support
    Keymaster

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

    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?

    #32692

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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.