Skip to main content

Notifications

Announcements

No record found.

Business Central forum
Suggested answer

Report DataItem Based on Temporary Table

Posted on by 571

So I have a report that works fine in BC 365 v19.5 on-prem. Now I'm porting this same report over to my BC 365 SaaS sandbox. It doesn't produce any results. The intent is to generate item labels based on a Posted Purchase Receipt lines. Since we have serialization in place, I can't just drill down into each Item No. based the matching Item Ledger Entry. I am drilling down into a temporary ILE based on each Serial No. value that rolls up to the Item No. I'll paste the report code below. Any suggestions would be greatly appreciated!

/// 
/// Report DCH GS1 DataBar Rcpt (ID 50105).
/// 
report 50105 "DCH GS1 DataBar Rcpt"
{
    DefaultLayout = RDLC;
    RDLCLayout = 'Layouts/DCH QR Code Tag.rdlc';
    ApplicationArea = All;
    Caption = 'DCH GS1 DataBar - Receipt';
    UsageCategory = ReportsAndAnalysis;
    EnableExternalImages = true;

    dataset
    {
        dataitem(Items; Item)
        {
            DataItemTableView = SORTING("No.");
            PrintOnlyIfDetail = true;
            RequestFilterFields = "No.";

            dataitem("ItemLedgerEntry"; "Item Ledger Entry")
            {
                DataItemLink = "Item No." = Field("No.");
                DataItemLinkReference = Items;
                DataItemTableView = SORTING("Entry No.");
                RequestFilterFields = "Item No.", "Document No.";

                dataitem("ItemSubledgerEntry"; "Item Ledger Entry")
                {
                    UseTemporary = true;
                    DataItemLink = "Item No." = Field("Item No."), "Serial No." = Field("Serial No.");
                    DataItemLinkReference = ItemLedgerEntry;
                    DataItemTableView = SORTING("Item No.");

                    column(Item_Description; Item.Description)
                    {
                    }
                    column(Item_No_Text; ItemNoSerialNoText)
                    {
                    }
                    column(Item_No; Item."No."   ' : '   "ItemLedgerEntry"."Serial No.")
                    {
                    }
                    column(Barcode_To_Print; '*'   BarcodeText   '*')
                    {
                    }
                    column(Barcode_To_Print_HumanReadable; BarcodeToPrint)
                    {
                    }
                    column(Item_Price; CurrentPrice)
                    {
                    }
                    column(QrCode; Barcode)
                    {
                    }
                }

                trigger OnAfterGetRecord()
                var
                    WrkText: Text[50];
                begin
                    if Item."No." <> "Item No." then
                        Item.Get("Item No.");

                    if Item."Item Tracking Code" = '' then begin
                        ItemILE.Reset();
                        ItemILE.SetRange("Item No.", "ItemLedgerEntry"."Item No.");
                        ILECount := ItemILE.Count;
                        NonSerializedItemCount  = 1;
                        if NonSerializedItemCount > 1 then
                            CurrReport.Break();
                    end;

                    WrkText := Item."No."   '1';
                    WrkText := WrkText.PadLeft(14, '0');
                    if "ItemLedgerEntry"."Serial No." <> '' then begin
                        BarcodeToPrint := '(01)'   WrkText   '(21)'   "ItemLedgerEntry"."Serial No.";
                        BarcodeText := '01'   WrkText   '21'   "ItemLedgerEntry"."Serial No.";
                    end else begin
                        BarcodeToPrint := '(01)'   WrkText;
                        BarcodeText := '01'   WrkText;
                    end;

                    Barcode := 'https://api.qrserver.com/v1/create-qr-code/?data='   BarcodeText;
                    FetchCurrentPrice(Item."No.");

                    QtyCount := ItemLedgerEntry.Quantity;
                    for i := 1 to QtyCount do begin
                        ItemSubledgerEntry.Init();
                        ItemSubledgerEntry.Copy(ItemLedgerEntry);
                        ItemSubledgerEntry."Entry No." := RowCount;
                        ItemSubledgerEntry.Insert();
                        RowCount  = 1;
                        NoOfBarcodesPrinted  = 1;
                    end;
                end;

                trigger OnPreDataItem()
                begin
                    SetRange(Open, true);
                    Clear(NonSerializedItemCount);
                end;
            }

            trigger OnPostDataItem()
            begin
                if NoOfBarcodesPrinted = 0 then
                    Message(NoNosFounds)
                else
                    Message(BarcodesWerePrinted, NoOfBarcodesPrinted);
            end;
        }
    }

    requestpage
    {

        layout
        {
        }

        actions
        {
        }

        trigger OnInit()
        begin
            RowCount := 1;
        end;
    }

    labels
    {
    }

    var
        Item: Record Item;
        BarcodeToPrint: Text[100];
        NoOfBarcodesPrinted: Integer;
        ItemNoSerialNoText: Label 'Item No. : Serial No.';
        NoNosFounds: Label 'No QR codes to be printed were found for the item';
        BarcodesWerePrinted: Label '%1 QR codes were printed';
        BarcodeText: Text[100];
        Barcode: Text;
        PriceListLine: Record "Price List Line";
        PriceStatus: Enum "Price Status";
        PriceAssetType: Enum "Price Asset Type";
        PriceSourceType: Enum "Price Source Type";
        PriceSourceGroup: Enum "Price Source Group";
        PriceType: Enum "Price Type";
        CurrentDate: Date;
        ItemRec: Record Item;
        CurrentPrice: Decimal;
        ItemILE: record "Item Ledger Entry";
        ILECount: Integer;
        NonSerializedItemCount: Integer;
        ILE: Record "Item Ledger Entry";
        QtyCount: Decimal;
        RowCount: Integer;
        i: Integer;

    local procedure FetchCurrentPrice(var ItemNo: Code[20]): Decimal
    begin
        CurrentDate := Today();
        CurrentPrice := 0;
        PriceListLine.Reset();
        PriceListLine.SetFilter(Status, Format(PriceStatus::Active));
        PriceListLine.SetFilter("Asset Type", Format(PriceAssetType::Item));
        PriceListLine.SetFilter("Source Type", '=%1|=%2', PriceSourceType::"All Customers", PriceSourceType::"Customer Price Group");
        PriceListLine.SetFilter("Source Group", Format(PriceSourceGroup::Customer));
        PriceListLine.SetFilter("Price Type", Format(PriceType::Sale));
        PriceListLine.SetFilter("Asset No.", ItemNo);
        PriceListLine.SetFilter("Starting Date", '<=%1|=%2', CurrentDate, 0D);
        PriceListLine.SetFilter("Ending Date", '>=%1|=%2', CurrentDate, 0D);
        if PriceListLine.FindFirst() = false then begin
            ItemRec.Reset();
            ItemRec.SetFilter("No.", ItemNo);
            if ItemRec.FindFirst() then begin
                CurrentPrice := ItemRec."Unit Price";
            end
        end else begin
            CurrentPrice := PriceListLine."Unit Price";
        end;
        exit(CurrentPrice);
    end;
}

Categories:
  • Suggested answer
    Nitin Verma Profile Picture
    Nitin Verma 20,991 Super User on at
    RE: Report DataItem Based on Temporary Table

    Great, thanks for the update.

  • Suggested answer
    Greg Kujawa Profile Picture
    Greg Kujawa 571 on at
    RE: Report DataItem Based on Temporary Table

    I see what the issue is. I am filtering the results against ILE rows that are Open = True. When I was testing this out, a few of the Posted Purchase Receipt documents referenced items that went through Purchase Return Orders. All good as-is with the report code.

  • Greg Kujawa Profile Picture
    Greg Kujawa 571 on at
    RE: Report DataItem Based on Temporary Table

    Thanks for the reply. The actual code block where the QR code is generated doesn't make or break the report. I have a separate report that generates a label based on a single item from the Item Card page. That report works just fine. I can tell that the issue is with the temporary Item Ledger Entry table I am creating based on the serialized items. I will try to put that logic into the page extension, and then set that temporary table as the report source. That way perhaps the report handling this logic can be bypassed.

  • Nitin Verma Profile Picture
    Nitin Verma 20,991 Super User on at
    RE: Report DataItem Based on Temporary Table

    Hi

    You can replace this line Barcode := 'api.qrserver.com/.../ + BarcodeText; to below function or as you like. You can replace your URL

     local procedure GenerateQRCode(QRCodeValue: text[250]): text
        var
            Base64Convert: Codeunit "Base64 Convert";
            TempBlob: Codeunit "Temp Blob";
            client: HttpClient;
            response: HttpResponseMessage;
            InStr: InStream;

        begin
            if QRCodeValue <> '' then begin
                client.get('barcode.tec-it.com/barcode.ashx + QRCodeValue + '&code=QRCode', response);
                TempBlob.CreateInStream(InStr);
                response.Content().ReadAs(InStr);
                QRCode := Base64Convert.ToBase64(InStr);
                exit(QRCode);
            end;
        end;

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,835 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,526 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans