Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / Item Ledger Entry tabl...
Business Central forum
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:
Reports and Business Intelligence
Other
Sales
Inventory and warehouse management
  • 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,155 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 69,639 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,155 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,155 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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans