Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to get/call value ...
Small and medium business | Business Central, N...
Suggested answer

How to get/call value from flowfield in a report

(0) ShareShare
ReportReport
Posted on by Nilam Bhor 153

Hi Guys,

In vat posting setup table,there is a field.vat% .

pastedimage1680085799593v1.png

I have created vat% flowfield in my Item table extension. and i want this flowfield value get in my excel report.

How to get this flowfield value in report? on which trigger i have to call this flowfield

pastedimage1680085875678v2.png

Below is my excel report code using excel buffer.

report 50002 "Retails Item List"
{
    Caption = 'Retails Item List';
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    ProcessingOnly = true;
    UseRequestPage = true;

    dataset
    {
        dataitem(Item; Item)
        {
            dataitem("LSC Barcodes"; "LSC Barcodes")
            {
                RequestFilterFields = "Show for Item";
                DataItemLink = "Item No." = field("No.");
                DataItemTableView = sorting("Item No.", "Barcode No.");

                trigger OnAfterGetRecord()
               
                begin
                    ExcelBuffer.NewRow();
                    ExcelBuffer.AddColumn(Item."No.", false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                    ExcelBuffer.AddColumn(Item.Description, false, '', false, false, false, '', ExcelBuffer."Cell Type"::Text);
                    ExcelBuffer.AddColumn("LSC Barcodes"."Barcode No.", false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                    ExcelBuffer.AddColumn("LSC Barcodes"."Unit of Measure Code", false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                    ExcelBuffer.AddColumn(Item."VAT %", false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                    ExcelBuffer.AddColumn(Item."Unit Cost", false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                    ExcelBuffer.AddColumn(GetItemSellingPrice(Item."No."), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                    ExcelBuffer.AddColumn(GetItemSellingPricewithvat(Item."No."), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                end;
            }
            trigger OnPreDataItem()
            begin
                ExcelBuffer.Reset();
                ExcelBuffer.DeleteAll();
                ExcelBuffer.NewRow();
                ExcelBuffer.AddColumn(Item.FieldCaption("No."), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                ExcelBuffer.AddColumn(Item.FieldCaption(Description), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Text);
                ExcelBuffer.AddColumn("LSC Barcodes".FieldCaption("Barcode No."), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                ExcelBuffer.AddColumn("LSC Barcodes".FieldCaption("Unit of Measure Code"), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                ExcelBuffer.AddColumn(Item.FieldCaption("VAT %"), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                ExcelBuffer.AddColumn(Item.FieldCaption("Unit Cost"), false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                ExcelBuffer.AddColumn('Selling price without GST', false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);
                ExcelBuffer.AddColumn('Selling Price Without GST', false, '', false, false, false, '', ExcelBuffer."Cell Type"::Text);
            end;

            trigger OnPostDataItem()
            var
                Itemlbl: Label 'Retails Item List';
                ExcelFileName: Label 'Retails Item List';
            begin
                ExcelBuffer.CreateNewBook(Itemlbl);
                ExcelBuffer.WriteSheet(Itemlbl, CompanyName, UserId);
                ExcelBuffer.SetColumnWidth('B', 46);
                ExcelBuffer.SetColumnWidth('C', 15);
                ExcelBuffer.SetColumnWidth('D', 20);
                ExcelBuffer.SetColumnWidth('G', 21);
                ExcelBuffer.CloseBook();
                ExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
                ExcelBuffer.OpenExcel();
            end;
        }
    }

    local procedure GetItemSellingPrice(ItemNo: Code[30]): Decimal
    var
        Rec_Item: Record Item;
        Rec_SalesPrice: Record "Sales Price";
        SalesPriceWithoutVAT: Decimal;
    begin
        Clear(Rec_SalesPrice);
        Rec_SalesPrice.Reset();
        Rec_SalesPrice.SetRange("Item No.", ItemNo);
        Rec_SalesPrice.SetRange(Rec_SalesPrice."Sales Type", Rec_SalesPrice."Sales Type"::"All Customers");
        Rec_SalesPrice.SetCurrentKey("Starting Date");
        Rec_SalesPrice.SetAscending("Starting Date", false);
        if Rec_SalesPrice.FindFirst() then begin
            SalesPriceWithoutVAT := Rec_SalesPrice."Unit Price";
        end
        else begin
            Clear(Rec_Item);
            Rec_Item.Reset();
            Rec_Item.SetRange("No.", ItemNo);
            if Rec_Item.FindFirst() then begin
                SalesPriceWithoutVAT := Rec_Item."Unit Price";
            end;
        end;
    end;

    local procedure GetItemSellingPricewithvat(ItemNo: Code[30]): Decimal
    var
        Rec_Item: Record Item;
        Rec_SalesPrice: Record "Sales Price";
        SalesPriceWithVAT: Decimal;
    begin
        Clear(Rec_SalesPrice);
        Rec_SalesPrice.Reset();
        Rec_SalesPrice.SetRange("Item No.", ItemNo);
        Rec_SalesPrice.SetRange(Rec_SalesPrice."Sales Type", Rec_SalesPrice."Sales Type"::"All Customers");
        Rec_SalesPrice.SetCurrentKey("Starting Date");
        Rec_SalesPrice.SetAscending("Starting Date", false);
        if Rec_SalesPrice.FindFirst() then begin
            SalesPriceWithVAT := Rec_SalesPrice."LSC Unit Price Including VAT";
        end
        else begin
            Clear(Rec_Item);
            Rec_Item.Reset();
            Rec_Item.SetRange("No.", ItemNo);
            if Rec_Item.FindFirst() then begin
                SalesPriceWithVAT := Rec_Item."LSC Unit Price Incl. VAT";
            end;
        end;
    end;

    var
        ExcelBuffer: Record "Excel Buffer" temporary;
   
   
}





Is any way to do this.

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,125 Super User 2024 Season 2 on at
    RE: How to get/call value from flowfield in a report

    can you see the value in Item Card/table VAT % field?

  • Nilam Bhor Profile Picture
    Nilam Bhor 153 on at
    RE: How to get/call value from flowfield in a report

    Yes,but i didn't get result.

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,125 Super User 2024 Season 2 on at
    RE: How to get/call value from flowfield in a report

    I think it is already there

    ExcelBuffer.AddColumn(Item."VAT %", false, '', false, false, false, '', ExcelBuffer."Cell Type"::Number);

  • Nilam Bhor Profile Picture
    Nilam Bhor 153 on at
    RE: How to get/call value from flowfield in a report
    [quote user="Mohana Yadav"]

    Add the property and assign your field like below

    CalcFields = VAT %;

    [/quote]
  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,125 Super User 2024 Season 2 on at
    RE: How to get/call value from flowfield in a report

    Add the property and assign your field like below

    CalcFields = VAT %;

  • Nilam Bhor Profile Picture
    Nilam Bhor 153 on at
    RE: How to get/call value from flowfield in a report
    [quote user="Mohana Yadav"]

    You can use CalcFields property under Item dataitem and declare your field.

    CalcFields = VAT %;

    [/quote]
  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,125 Super User 2024 Season 2 on at
    RE: How to get/call value from flowfield in a report

    You can use CalcFields property under Item dataitem and declare your field.

    CalcFields = VAT %;

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

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,993 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans