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

Report using multiple tables in AL

(0) Share

Report

Posted on by matthewjd24

Hello all,

I'm working on an AL extension that generates a report and it's going pretty well. I'm following Microsoft's walkthrough. However, I've come to an issue where it's not pulling the related record from the Reservation Entry table. Only the first embedded dataitem (in this case, "Prod. Order Line") is retrieving values. The second embedded dataitem ("Reservation Entry") has blank values in the report. The same is true if I add more dataitems within the Production Order dataitem, they will just be blank. It's only the first. I'm confused about why that is.

report 50003 CertificateOfConformance
{
    DefaultLayout = RDLC;
    RDLCLayout = 'MyRDLReport.rdl';

    dataset
    {
        dataitem("Production Order"; "Production Order")
        {
            column(RmsNumber; "No.") { }
            column(PartNumber; "Source No.") { }
            column(Description; Description) { }
            dataitem("Prod. Order Line"; "Prod. Order Line")
            {
                DataItemTableView = sorting("Prod. Order No.");
                DataItemLink = "Prod. Order No." = field("No.");

                column(Quantity; "Quantity") { }
            }
            dataitem("Reservation Entry"; "Reservation Entry")
            {
                DataItemTableView = sorting("Source ID");
                DataItemLink = "Source ID" = field("No.");

                column(SalesOrderNumber; "Source ID") { }
                column(EntryNumber; "Entry No.") { }
            }
        }
    }
}

I have the same question (0)

All responses (19)

Answers (0)

Suggested answer

Inge M. Bruvik 1,105 Moderator on at

Like (1)

Report

Have you checked that there actually is a reservation for the production order you are using for your test?

The scope of the company is decided by the actual company you run the report in.

So when you run the report you need to be in the company that holds the data you want to test on.

Was this reply helpful? Yes No
matthewjd24 77 on at

Like (0)

Report

I have verified that there is a reservation for the production order unfortunately.

That makes sense how it uses the current company when you run the report.

Was this reply helpful? Yes No
Inge M. Bruvik 1,105 Moderator on at

Like (1)

Report

Ok, i will try to create some demo data and run try to run the report in my environment here.

Was this reply helpful? Yes No
matthewjd24 77 on at

Like (0)

Report

Thank you I appreciate the help. It's confusing because the walkthrough I'm using seems to do it the same way. I don't understand why only the first embedded data item "Prod. Order Line" makes it into the report, and any ones I add after it will be blank.

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,105 Moderator on at

Like (1)

Report

I have run the report on my data and it actually picks the correct data.

So your issue must be in the report layout.

If you run the report and send it to Excel you will see the data from the reservation lines showing up as expected.

So the issue you are facing must be connected to your report layout.

Was this reply helpful? Yes No
matthewjd24 77 on at

Like (0)

Report
Thank you Inge I was not aware that you can export it to Excel. When I try to do that though I get this error:

Are you using the same code as me? Here is my full code:

pageextension 50003 PageCocButton extends "Released Production Order" { actions { addafter("Shortage List") { action(MakeCofC) { Caption = 'Make CofC'; Promoted = true; PromotedCategory = Process; ApplicationArea = All; ToolTip = 'Create Certificate of Conformance'; trigger OnAction() var ProdOrder: Record "Production Order"; begin ProdOrder.Reset(); CurrPage.SetSelectionFilter(ProdOrder); if ProdOrder.FindFirst() then Report.Run(Report::"CertificateOfConformance", true, true, ProdOrder); end; } } } } report 50003 CertificateOfConformance { DefaultLayout = RDLC; RDLCLayout = 'MyRDLReport.rdl'; dataset { dataitem("Production Order"; "Production Order") { column(RmsNumber; "No.") { } column(PartNumber; "Source No.") { } column(Description; Description) { } dataitem("Item"; "Item") { DataItemTableView = sorting("No."); DataItemLink = "No." = field("Source No."); column(PartRev; "Common Item No.") { } column(CustomerPartNumber; "Customer Part Number") { } } dataitem("Prod. Order Line"; "Prod. Order Line") { DataItemTableView = sorting("Prod. Order No."); // Set a filter on the child data item, **CustLedgerEntry** to select only the records where the // value of `Customer."No."` field and the `"Customer Ledger Entry"."Customer No."` field matches. DataItemLink = "Prod. Order No." = field("No."); column(Quantity; "Quantity") { } } dataitem("Reservation Entry"; "Reservation Entry") { DataItemTableView = sorting("Source ID"); DataItemLink = "Source ID" = field("No."); column(SalesOrderNumber; "Source ID") { } column(EntryNumber; "Entry No.") { } // dataitem("Reservation Entry2"; "Reservation Entry") // { // DataItemLink = "Entry No." = field("Entry No."); // DataItemTableView = WHERE("Quantity (Base)" = filter(< 0)); // } } } } }

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,105 Moderator on at

Like (1)

Report

Well this is not your report failing. This is the action you use to run the report.

In the report property you should add

ApplicationArea = all;

UsageCategory = Lists;

Then you can search for your report using the tell me fuction and run it independently.

Much easier to identify errors in the report then when you take away other factors.

In your launch.json you can also define your report as the startup object

"startupObjectId": 50149, // the number of your report

"startupObjectType": "Report",

Then the report will run automatically when you publish it from VS Code.

Was this reply helpful? Yes No
matthewjd24 77 on at

Like (0)

Report

I added

"ApplicationArea = all;

UsageCategory = Lists;"

to the report property and modified the launch.json to run the report from VS Code. However, when I run it and try to Send it to Excel (data only), I still get that error. Are you doing data only or data and layout? If I do data and layout, the report is missing the same values.

Was this reply helpful? Yes No
Inge M. Bruvik 1,105 Moderator on at

Like (1)

Report

Weird that you get that error when you run data only.

And i get this result.

Was this reply helpful? Yes No
matthewjd24 77 on at

Like (0)

Report

Do you have the same code for the report as I've posted? If so I don't know what to make of it.

Was this reply helpful? Yes No