Assistance on filtering factbox results

(0) Share

Report

Posted on by xavierespi

Hello everyone, I'm new to AL and still learning about how the language functions. Recently, I've been trying to create a fact box that get me Item Totals by each location.

The format is so: Location Code - Item No. - Quantity Available

I'm able to get the data that I need but the issue is that I get multiple entries for one location and Item No, When I only need one entry per location. Can anyone assist me on this ? I tried playing around with the source tables and changed it to Location which helped reduce the number of results I get but then Quantity available remains at zero.

Source Code:

page 50146 "Availability by Location"
{
    Caption = 'Availability by Location';
    PageType = CardPart;
    SourceTableTemporary = True;
    SourceTable = "Item Ledger Entry";
    //SourceTable = Location;
    layout
    {
        area(Content)
        {
            repeater(group)
            {

                field("Code"; Rec."Location Code")
                {
                    ApplicationArea = All;
                }
                field("Item No."; Rec."Item No.")
                {
                    ApplicationArea = All;
                }
                field("Qty Available"; Rec."Quantity by Location")
                {
                    Caption = 'Quantity Available';
                    ApplicationArea = All;
                    //"Item Ledger Entry".setCurrentKey()
                    DrillDown = true;
                    trigger OnDrillDown()
                    var
                        ILE: Record "Item Ledger Entry";
                        LocationMatrixPage: Page "Items by Location Matrix";
                        Item: Record Item;
                        ItemVariant: Record "Item Variant";
                        Text000: Label 'The record was found';
                    begin
                        ItemVariant.SetFilter("Item No.", Rec."Item No.");
                        PAGE.Run(50036, ItemVariant);
                    end;
                }

            }

        }
    }

    var 

        Item: Record "Item Ledger Entry";
        Location: Record Location;
}

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

JAngle 133 on at

Like (1)

Report

Try stocking keeping unit table. Will mean you need to create the data. It has a flowfield for inventory, qty. on sales, qty. on purchase.

Was this reply helpful? Yes No
xavierespi 5 on at

Like (0)

Report

Is there a way I could accomplish my goal while using just the Item Ledger entry table ?

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,105 Moderator on at

Like (1)

Report

You should be able to use the Item table if you set the location filter for every location.

That is more efficient for your performance that doing all the calculation of the item ledger table.

So for the item you want to look at you loop trough your locations.

In the location filter on your item you put the location code and then do a calcfields on the inventory column.

Was this reply helpful? Yes No
Verified answer

YUN ZHU 95,432 Super User 2025 Season 2 on at

Like (1)

Report
Hi, Query object is best performance when doing this kind of customization in Business Central.

The following is an example of a standard feature.

query 2554 "Items By Location" { QueryType = Normal; OrderBy = ascending (No); Caption = 'Items By Location'; QueryCategory = 'Item List'; elements { dataitem(Item; Item) { column(No; "No.") { Caption = 'No.'; } column(Description; Description) { } dataitem(Item_Ledger_Entry; "Item Ledger Entry") { DataItemLink = "Item No." = Item."No."; column(Location_Code; "Location Code") { Caption = 'Location Code'; } column(Remaining_Quantity; "Remaining Quantity") { Caption = 'Remaining Quantity'; Method = Sum; } } } } trigger OnBeforeOpen() begin end; }

And then you can use Query.Open Method to add query data to a page.

https://yzhums.com/4869/

Hope this helps as well.

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,698 Moderator on at

Like (0)

Report

Hi xavierespi ,

You can use item by Location as Zhu mentioned.

Thanks.

Was this reply helpful? Yes No
xavierespi 5 on at

Like (0)

Report

Ill give this a try and update you on the results

Was this reply helpful? Yes No
Suggested answer

xavierespi 5 on at

Like (0)

Report

Thank you Yen Zhu, I was able to get it working !

Was this reply helpful? Yes No