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

Item Ledger Entry table and Item Variant Table

(0) Share

Report

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) { }

}

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

All responses (8)

Answers (0)

Maro9595 105 on at

Like (0)

Report

RE: Item Ledger Entry table and Item Variant Table

Thank you again ZHU much respect
Maro9595 105 on at

Like (0)

Report

RE: Item Ledger Entry table and Item Variant Table

Thank you, Josh

appreciate your support
Suggested answer

JAngle 33,159 on at

Like (1)

Report

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 105 on at

Like (0)

Report

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 74,036 Super User 2024 Season 2 on at

Like (1)

Report

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 33,159 on at

Like (0)

Report

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 105 on at

Like (0)

Report

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 33,159 on at

Like (1)

Report

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.