Problem with Nav C/AL Queries

attieffe

Hello,

i'm using NAV 2017 and i attempt to the the following (the real case is more complex but i just ask for a specific function):

I have a table Items and i want to use a Query that give me bak ONE record for ITEM, with some static field and some computed fields:
- purchased quantity (sum of quantity from Item Ledger Entry with TYPE PURCHASE OR TRANSFER, in a specific period and location filters)
- purchased value (sum of COST AMOUNT from Item Ledger Entry with TYPE PURCHASE OR TRANSFER, in a specific period and location filters)
- sale quantity (sum of quantity from Item Ledger Entry with TYPE SALE, in a specific period and location filters)
- sale value (using the purchase value not the sell one) (sum of COST AMOUNT from Item Ledger Entry with TYPE PURCHASE OR TRANSFER, in a specific period and location filters)

So i setup a Query with 3 level:
level 1: ITEM
level 2: ILE_BUY linked by fields on ITEM table, datefilter, location filter, Types Purchase/Transfer
level 3: ILE_SALE linked by fields on ITEM table, datefilter, location filter, Type Sale

Now the problem:
There is one record on table ITEM,
ONE record on Item Ledger Entry of Purchase TYPE and quantity 2
TWO record on Item Ledger Entry of Sale type with quantity -1 / -1

Running the query i found:
Purchase quantity amount => 4 (2 expected)
Sale quantity amount => -2


So the problem is that it brings those 2 PURCHASED quantity on EACH SALE record, so it SUMS 2 times 2 quantity, so it seems a problem on the joining that do something not expected.

I tried moving both Item ledger entry at the same LEVEL it says me that is not possible to have many DataItem at same level.

If I remove all the SALE part on the design the Purchase quantity is correct => 2

I also tried changing DataItemLinkType to all possible variant type but no one worked as expected.

I post an image of my current setup:









Thank you