Fill up custom temporary table with item ledger entry table data

(5) Share

Report

Posted on by CU22100733-0

281

Hello, I have created a custom table which is of type temporary, it has fields item no., description, location, available qty on hands for that location. I want to fill up this with item ledger entry table data in this temporary table's page.when I open the page, I want to see the data, so please suggest me code for this one.

Categories:

Dynamics 365 Business Central

Microsoft Dynamics NAV

Dynamics 365 Business Central migration

I have the same question (0)

All responses (6)

Answers (3)

Verified answer

Ramesh Kumar 7,529 Super User 2025 Season 2 on at

Like (1)

Report
Temporary table page to show item availability per location. You can achieve this by using AL code in the OnOpenPage or OnAfterGetRecord trigger of the Page to populate your temporary table from the Item Ledger Entry table.

trigger OnOpenPage() var ItemLedgerEntry: Record "Item Ledger Entry"; TempAvailability: Record TempItemAvailability temporary; Item: Record Item; begin // Loop through all Item Ledger Entries ItemLedgerEntry.SetRange("Entry Type", ItemLedgerEntry."Entry Type"::"Positive Adjmt."); if ItemLedgerEntry.FindSet() then repeat // Look for existing combination of Item No. + Location Code if not TempAvailability.Get(ItemLedgerEntry."Item No.", ItemLedgerEntry."Location Code") then begin TempAvailability.Init(); TempAvailability."Item No." := ItemLedgerEntry."Item No."; TempAvailability."Location Code" := ItemLedgerEntry."Location Code"; // Optional: pull item description if Item.Get(ItemLedgerEntry."Item No.") then TempAvailability.Description := Item.Description; TempAvailability."Available Quantity" := 0; end; // Add to available quantity TempAvailability."Available Quantity" += ItemLedgerEntry."Remaining Quantity"; TempAvailability.Insert(true); // Use Insert(true) for temp record until ItemLedgerEntry.Next() = 0; end;

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/triggers-auto/page/devenv-onopenpage-page-trigger

Thanks

Ramesh

Was this reply helpful? Yes No
Verified answer

Tech-Lucky 1,267 Super User 2025 Season 2 on at

Like (1)

Report

As suggested by Ramesh, this is the appropriate trigger to write the code in. However, I would recommend using Rec to insert the data instead of the TempAvailability variable. This is because if you use a separate temporary record variable, you won't be able to bind it to the page as a data source, and the data won't be displayed. Using Rec ensures that the page recognizes and displays the data correctly.

Was this reply helpful? Yes No
Suggested answer

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

Like (1)

Report

Personally, I think using Query may have better performance.

Dynamics 365 Business Central: How to make a query searchable (Add Query to Tell me)

https://yzhums.com/59511/

Dynamics 365 Business Central: Using the query as a data source for a page (Query.Open Method)

https://yzhums.com/4869/

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No
Verified answer

CU22100733-0 281 on at

Like (2)

Report

Thanks @Ramesh Kumar & @Tech-Lucky for the suggestions--- I modifies and used the procedure on open page--

Procedure Getiledata()

var

ile: Record "Item Ledger Entry";

begin

ile.Reset();

ile.SetRange("Entry Type", ile."Entry Type"::"Positive Adjmt.");

ile.Setfilter("Remaining Quantity", '<>%1', 0);

if ile.FindSet() then

// Look for existing combination of Item No. + Location Code

Rec.Reset();

Rec.SetRange("Item No.", ile."Item No.");

Rec.SetRange("Location Code", ile."Location Code");

if Rec.FindFirst() then begin

Rec."Stock Available" += ile."Remaining Quantity";

Rec.Modify();

end else

if not Rec.FindFirst() then begin

repeat

Rec.Init();

Rec."Entry No." := GetNextEntryNo();

Rec.Validate("Item No.", ile."Item No.");

Rec."Location Code" := ile."Location Code";

Rec."Stock Available" := 0;

Rec."Stock Available" += ile."Remaining Quantity"; // Add to available quantity

Rec.Insert(false);

until ile.Next() = 0;

end;

end;

Was this reply helpful? Yes No
CU22100733-0 281 on at

Like (1)

Report

Hello @Ramesh Kumar and @Tech-Lucky, can you please suggest me here - https://community.dynamics.com/forums/thread/details/?threadid=6361c1ff-7320-f011-998a-7c1e52466c21

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,647 Super User 2025 Season 2 on at

Like (2)

Report

Adding details: https://www.youtube.com/watch?v=q2P9QGCywOQ

Was this reply helpful? Yes No