Dynamics Community Forum Thread Details

Left Outer Join returning no records

I need a query that returns remaining quantities from ILE along with sum of Reservation Entry against the associated ILE entry. I'm using a Left Outer Join to always return the ILE record regardless of whether or not there are reservations, but it is not working as I expect. No records are returned if RE is empty.

What am I doing wrong, or is my assumption about Left Outer Joins incorrect?

Thanks!

        dataitem(ItemLedgerEntry; "Item Ledger Entry")
        {
            DataItemTableFilter = "Lot No." = filter(<> ''), "Remaining Quantity" = filter(<> 0);
            column(ItemNo; "Item No.") { }
            column(LocationCode; "Location Code") { }
            column(LotNo; "Lot No.") { }
            column(PostingDate; "Posting Date") { }
            column(Expiration_Date;"Expiration Date") { }
            column(RemainingQuantity; "Remaining Quantity") { }
            dataitem(ReservationEntry; "Reservation Entry")
            {
                DataItemLink = "Source Ref. No." = ItemLedgerEntry."Entry No.";
                SqlJoinType = LeftOuterJoin;
                DataItemTableFilter =
                    "Reservation Status" = const(Reservation),
                    Positive = const(true);
                column(ReservationEntryQuantityBase; "Quantity (Base)")
                {
                    Method = Sum;
                }
            }
        }
 

It looks like my problem is due to the DataItemTableFilter being treated as a WHERE clause for the overall query.

DataItemTableFilter Property:

Problem: In Business Central Query objects, the DataItemTableFilter property on a data item acts as a WHERE clause filter in the resulting SQL query, even with a LeftOuterJoin specified. This can lead to unexpected filtering of the left table's data.
Solution: Instead of using DataItemTableFilter on the joined table, you may need to use a Column
with a Method or a FlowField to achieve the desired result, or
restructure the query logic. For complex scenarios, creating a dedicated
page might be a better approach.

I resolved by adding the ILE Lot Number to the RE DataItemLink as show below:

        dataitem(ItemLedgerEntry; "Item Ledger Entry")
        {
            DataItemTableFilter = "Lot No." = filter(<> ''), "Remaining Quantity" = filter(<> 0);
            column(ItemNo; "Item No.") { }
            column(LocationCode; "Location Code") { }
            column(LotNo; "Lot No.") { }
            column(PostingDate; "Posting Date") { }
            column(Expiration_Date;"Expiration Date") { }
            column(RemainingQuantity; "Remaining Quantity") { }
            dataitem(ReservationEntry; "Reservation Entry")
            {
                DataItemLink = "Source Ref. No." = ItemLedgerEntry."Entry No.", "Lot No." = ItemLedgerEntry."Lot No.";
                SqlJoinType = LeftOuterJoin;
                column(ReservationEntryQuantityBase; "Quantity (Base)")
                {
                    Method = Sum;
                }
            }
        }
 

Quick Links

Leaderboard > Small and medium business | Business Central, NAV, RMS