Prev: Fields visable in designer but not in SQL
Next: Performance Tuning W2K8 x64 SQL servers
From: Jason on 28 Mar 2010 22:51
I am trying to write a sql query that will return a list of open sales
invoices and include what the invoice amount is and how much is remaining. I
see the table [Sales Invoice Header] but it doesn't seem to show what the
current amount due is on an invoice. What table do I need to join to? Is
there a document that talks about how the tables all link?
From: Luc Van Dyck on 29 Mar 2010 06:05
"Jason" wrote:

> I am trying to write a sql query that will return a list of open sales
> invoices and include what the invoice amount is and how much is remaining. I

You should use the table "Customer Ledger Entry" instead. This table has a
field "Remaining Amount" which tells you the amount which needs to be payed.
A posted Sales Invoice results in one record in the Customer Ledger Entry
table.

Use the field "Document Type" to filter out only Invoices.

Luc Van Dyck
webmaster http://mibuso.com
"Your favourite knowledge base"
From: Volker Strähle on 29 Mar 2010 07:40
Hi Jason,

why don't You use report 106?

Volker

From: Jason on 29 Mar 2010 10:40
"Luc Van Dyck" wrote:

> "Jason" wrote:
>
> > I am trying to write a sql query that will return a list of open sales
> > invoices and include what the invoice amount is and how much is remaining. I
>
> You should use the table "Customer Ledger Entry" instead. This table has a
> field "Remaining Amount" which tells you the amount which needs to be payed.
> A posted Sales Invoice results in one record in the Customer Ledger Entry
> table.
>
> Use the field "Document Type" to filter out only Invoices.
>


Thanks. Just what I was looking for.
From: Jason on 29 Mar 2010 10:41
I am building a few reports that are going to be ran out side of the NAV
interface so I have to deal with the SQL tables directly.
 |  Next  |  Last
Pages: 1 2
Prev: Fields visable in designer but not in SQL
Next: Performance Tuning W2K8 x64 SQL servers