web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Item Ledger Entry tabl...
Small and medium business | Business Central, N...
Suggested Answer

Item Ledger Entry table and Item Variant Table

(0) ShareShare
ReportReport
Posted on by Maro9595 105

Dear all Happy New year,

Am currently developing a report which will display Item No, Entry type invoiced Qty(in positive digit), Customer/Vendor  Item variant code, a description I know some of the data can be obtained by linking two tables Item Ledge Entry and Item Variant. Now when I run my report I got all information for all Variant while am specifying which variant i want in request page. below is my piece of code and how report behaves.

dataset
    {
        dataitem("Item Ledger Entry"; "Item Ledger Entry")
        {
            RequestFilterFields = "Item No.", "Entry Type";

            column(Item_No_; "Item No.") { }
            column(Invoiced_Quantity; "Invoiced Quantity") { }
            column(Entry_Type; "Entry Type") { }

            dataitem("Item Variant"; "Item Variant")
            {
                RequestFilterFields = Code;
                DataItemLink = "Item No." = field("Item No.");
                column(Code; Code) { }
                column(Description; Description) { }

            }
pastedimage1642422980833v1.png
I did only Sales of Variant TEST002 red one but when pulling data on report all other comes.
thank you for your assistance.
Categories:
Dynamics 365 Business Central
Dynamics 365 Business Central
I have the same question (0)
  • Suggested answer
    JAngle Profile Picture
    JAngle 133 on at

    I don't understand the need to include the item variant table. The "variant code" is a field on the item ledger entry table. Just have that as an additional request filter field. If you want a supplementary part to the report where it pivots sales by variant then simply use the tools in built to RDLC or Power BI for that.

  • Maro9595 Profile Picture
    Maro9595 105 on at

    Hello Josh,

    Thank you for your reply, let say I need information for the Variant code's description(which is in the Item Variant table) how can I proceed

  • Suggested answer
    JAngle Profile Picture
    JAngle 133 on at

    add a trigger for OnAfterGetRecord and fill a variable with the result by using the ItemVariant.GET() statement. Then add the variable as a column so it appears as part of the final dataset

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 95,647 Super User 2025 Season 2 on at

    Hi, please add the red part below and try again.

    dataitem("Item Ledger Entry"; "Item Ledger Entry")
            {
                RequestFilterFields = "Item No.", "Entry Type";

                column(Item_No_; "Item No.") { }
                column(Invoiced_Quantity; "Invoiced Quantity") { }
                column(Entry_Type; "Entry Type") { }

                dataitem("Item Variant"; "Item Variant")
                {
                    RequestFilterFields = Code;
                    DataItemLink = "Item No." = field("Item No."), Code = field("Variant Code");
                    column(Code; Code) { }
                    column(Description; Description) { }
                }
            }
    Hope this will help.
    Thanks.
    ZHU
  • Maro9595 Profile Picture
    Maro9595 105 on at

    Thank you again, Josh, for your support,

    I have added the Variant code column in the Item ledger entry as you said and I added trigger, well the variant code and other information from Item ledger entries are coming but no Variant descriptions.

    if I add a trigger as you said I get an error'Item variant does not exist' please see the code below

    dataset

       {

           dataitem("Item Ledger Entry"; "Item Ledger Entry")

           {

               RequestFilterFields = "Item No.", "Entry Type", "Variant Code";

               column(Item_No_; "Item No.") { }

               column(Invoiced_Quantity; "Invoiced Quantity") { }

               column(Entry_Type; "Entry Type") { }

               column(Variant_Code; "Variant Code") { }

               column(ItmVar; ItmVar.Description) { }

               trigger OnAfterGetRecord()

               begin

                   ItmVar.get();

               end;

  • Suggested answer
    JAngle Profile Picture
    JAngle 133 on at

    Yun’s suggestion shouldn’t be overlooked.

    Worth reading up on the usage of .get() : docs.microsoft.com/.../record-get-method

    You need to pass it the primary key values. I’d also make it so you can skip over genuine errors. So, if itemvar.get(Item.”No.”, Item.”Variant Code”) then

    VariantCodeDescVar:= itemvar.Description;

    The column would then be variantcodedescvar as that has been filled with the value if it exists.

  • Maro9595 Profile Picture
    Maro9595 105 on at

    Thank you, Josh

    appreciate your support

  • Maro9595 Profile Picture
    Maro9595 105 on at

    Thank you again ZHU much respect

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

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

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,577

#2
YUN ZHU Profile Picture

YUN ZHU 888 Super User 2025 Season 2

#3
Jainam M. Kothari Profile Picture

Jainam M. Kothari 778 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans