Table Relation Required for Sales Line, Item Ledger Entry, Customer Ledger Entry

Business Central/NAV Developers Forum
1

Hi All

I need to join 3 tables Sales Line, Item Ledger Entry, Customer Ledger Entry to find Sales by Product Group Code and find Sales Ageing(how fast the money has been recovered?).

Can someone tell me what is the relation between these 3 tables: Sales Line, Item Ledger Entry, Customer Ledger Entry and how to join them?

Also about Sales Ageing, does anyone know how to calculate it?

Any help is appreciated.

Regards

Anu

2

Use Document No for linkage

3

That is incorrect. The Document No. in the Item Ledger is the shipment no. In Customer Ledger, the invoice no.

4

Instead of Sales line you should be using Sales Invoice Line table. Sales line table stores un posted data while posted invoice data is stored in Sales invoice line.

5

Thanks for the reply. I need to generate 2 reports:

  1. Sales by Product Group and Customer

  2. The time it take to get payment from the date I raised invoice

For the first report, it seems from your reply that I need to use Sales Invoice Line. How can I get Product Group? For the second report, how do i know the payment date from the invoice date?

6

Sorry Matt,

I thought It is Sales invoice line

Anu

For second report you need to look at Detail.Cust Ledger entry table where you can get payment entrty aginst invoice

7

First why not the Item ledger Entry table it already contains the customer no & the product group.
Plus all the other fields you need.

Source Type = Customer
Source No: Customer No
Entry Type = Sale
Product Group Code
Item No.
Posting Date:
Document No:
etc
etc

You can link everything you’re looking for to this table - or GET the info from other tables - depending on your need & design.

8

Ya. This looks promising.

I had a look at Item Ledger and Customer Ledger table. There are values like 1 , 2, 3 etc in Document Type(In Customer Ledger) and Entry Type(In Item Ledger) and I cant find any master table to know which no corresponds to which type of entry.

So for e.g. how do I know which no corresponds to “Sales” Entry Type?

9

sales line table filter type=item

Item ledger entry dataitem link item no = no. Product group code = Product group code

customer ledger entry data item link selltocustomerno = customer no

The two tables(dataitems) are indented to sales line table

10

Sai Ram, how can you use Sales line table for getting the posted sales data ?

Its always sales invoice line and not sales Line.

Regards,

11

How do I find Document Type and Entry Type values? for e.g. what is Entry Type = 2 means?

12

Go to table designer and select the field entry type and press Shift+F4 and you will see the proeprties of that field

Look at the property optionstring.

It always start with ‘0’