3

How to calculate [Remaining Amount] in Dynamics Nav report:

Receivables / Customers / Costomer No. / List of Ledger Entries

I reach NAV by SQL only. Which table, and column names should I take the data from?

Update. My guess of direction is that from the table [Cust_ Ledger Entry] I have to take [Open]=1 and from the table [Detailed Cust_ Ledg_ Entry] I have to take the filed [Amount].

This is additional question. How to do calculate Remaining Amount for a given date of the past. I assume for the present it is easier.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • You know you could look for the answer in Nav itself even if you don't have license. Ctrl+F2 opens designer for any object and shift+F4 show properties. You can't see source code w/o licens but in your case there's no need for that. The table is `Customer Ledger Entry`. What is the problem with date I don't know. Just select sum of all previous records. – Mak Sim May 28 '16 at 06:24
  • @MakSim In table `Customer Ledger Entry` there is no such a column as `Amount`. Probably I have to dig in table `[Detailed Cust_ Ledg_ Entry]`. Should I sum up `[Amount]` of all records up to chosen date regardless of the `[Entry Type]`? Unfortunately, your trick on looking into the guts of Nav without license for Designer doesn't work in my case. – Przemyslaw Remin Jun 01 '16 at 11:03
  • well yes. What you described is how flowfield `Remaining Amount` is calculated in `Customer Ledger Entry` form. It is sum of `Amount` of all detailed operations where `Entry No. = Cust. Ledger Entry No.` and `Posting Date` filtered accordingly. – Mak Sim Jun 02 '16 at 15:21
  • And I've just tested. You can see properties of fields in a table even w/o developer's license. All you need is user role like `SUPER` or whatewer gives access to designer. – Mak Sim Jun 02 '16 at 15:24

1 Answers1

1

to get balance at date you just have to sum [Amount] in [Detailed Cust_ Ledg_ Entry] till target date without any other filters(). This table contains all operations

xdd
  • 535
  • 2
  • 4
  • I am pretty sure that `[Detailed Cust_ Ledg_ Entry]` should be joined with `[Entry Type]=1`. In this way it matches exactly all the records from `[Cust_ Ledger Entry]` without adding anything. Why do you think it is not necessary? – Przemyslaw Remin Jun 08 '16 at 09:03
  • in dependence what do you want to calculate: with "[Entry Type]=1" you calculate pure balance, without invoice-payment relations(applications). If you want to calculate remaining amount for certain invoice, you have to use other types. – xdd Jun 08 '16 at 10:05
  • I have run a test for join with and without `[Entry Type]=1` there is small difference. As statistician I would let it go as meaningless, but for accountant it could be important - what is that difference? Especialy for `[Open]=1` transactions. I am granting the bounty for you hoping that you may find the time to explain it. I will not accept the answer as solved until that time:-) – Przemyslaw Remin Jun 08 '16 at 14:56
  • i try example: you have two orders with one customer o1 and o2, both for $50. you have invoiced them. you have LE1 (Ledger entry) with DLE1(Detailed entry) with `type`=1, amount 50, `open`=1, and LE2, DLE2 the same. Customer pays you $100, it is LE3 for -100. now you have total balance 50+50-100 = 0. now you apply payment to invoices. for example customer pays 50 for o1, 20 for o2 and make prepaymnt for another o3. it makes DLE1_2 for -50, and LE1 `open=0' with remaining 0. DLE2_2 for -20, and LE2 `open=1' and remaining 50-20=30. and DLE3_2, DLE3_3 for -50 and -20. balance not changed = 0 – xdd Jun 09 '16 at 06:59
  • CONTINUE: but you still have o2 partial paid and waiting for money. and you have prepayment for o3, and you have to begin your work with it. – xdd Jun 09 '16 at 07:00