Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

Item Ledger Entry table and Item Variant Table

(0) ShareShare
ReportReport
Posted on by 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(CodeCode) { }
                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.
  • Maro9595 Profile Picture
    Maro9595 105 on at
    RE: Item Ledger Entry table and Item Variant Table

    Thank you again ZHU much respect

  • Maro9595 Profile Picture
    Maro9595 105 on at
    RE: Item Ledger Entry table and Item Variant Table

    Thank you, Josh

    appreciate your support

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,159 on at
    RE: Item Ledger Entry table and Item Variant Table

    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
    RE: Item Ledger Entry table and Item Variant Table

    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
    YUN ZHU Profile Picture
    YUN ZHU 74,036 Super User 2024 Season 2 on at
    RE: Item Ledger Entry table and Item Variant Table

    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
  • Suggested answer
    JAngle Profile Picture
    JAngle 33,159 on at
    RE: Item Ledger Entry table and Item Variant Table

    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

  • Maro9595 Profile Picture
    Maro9595 105 on at
    RE: Item Ledger Entry table and Item Variant Table

    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 33,159 on at
    RE: Item Ledger Entry table and Item Variant Table

    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.

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

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans