Mapping Vendor Item Number to Sales Lines, or clone existing field to a created one

Question

I'm trying to customise a report with a value from an unrelated table. I can't figure out how to get an existing field to populate my custom field.

Specifically, I need the Vendor Item No. from Items (or Item Card) to be available on the Sales Lines table via the field I've created in a tableextension.

I'm not getting any code errors, and the extension installs successfully. Vendor Item No. is a system default field on Items.

Within tableextension, I've tried using CalcFormula, TableRelation, and a DataTransfer procedure to make Vendor Item Custom Table to equal Vendor Item No..

I only get a blank value, with no errors:

tableextension 50102 SalesLineVendorTble extends "Sales Line"
{
    fields
    {
        //orginal field on Items is (32; "Vendor Item No."; Text[50])
        //Works to insert new field, no linked data
        field(999; "Vendor Item Custom Table"; Text[50])
        {
            //TableRelation, doesn't work
            DataClassification = ToBeClassified; //DataClassification or FlowField, choose 1
            TableRelation = Item."Vendor Item No." where("No." = field("No."));

            //CalcFormula via FlowField
            //FieldClass = FlowField; //DataClassification or FlowField, choose 1
            //CalcFormula = lookup(item."Vendor Item No." where("No." = field("No.")));
        }
    }

//Below is procedure trying to link via multiple methods
    /*
        local procedure CopyVendorItemNum()
        var
            dt: datatransfer;
            dest: Record "Sales Line";
            src: Record Item;
        begin
            //define SalesLine.CustomField is same as Item.VendorNumber
                //did not work
            //rec."Vendor Item Custom Table" := src."Vendor Item No.";

            //Set up datatransfer, link tables and copy fields
                //did not work
            dt.SetTables(Database::Item, Database::"Sales Line");
            dt.AddFieldValue(src.FieldNo("Vendor Item No."), dest.Fieldno("Vendor Item Custom Table"));
            dt.AddJoin(src.FieldNo("No."), dest.FieldNo("No."));
            dt.CopyFields();
        end;
    */
}

Inserting the field to a report's dataset is working, but with no value on the sales line, nothing shows up here. Seems fine here

reportextension 50101 PickAddVendor extends "Pick Instruction"
{
    RDLCLayout = './CustomReport.rdlc';
    dataset
    {
        add("Sales Line")
        {
            //factoryCodeTable is name for internal use
            //Vendor Item Custom Table is custom Field
            column("FactoryCodeTable"; "Vendor Item Custom Table")
            { }
} } }

Answer 1

You don't need the table extension. You can solve it on the report extension it self.

You need to create a global variable to hold the value and then use that variable as the source of your added column.

A final solution could look something like this:

reportextension 50101 "PickAddVendor" extends "Pick Instruction"
{
    RDLCLayout = './CustomReport.rdlc';

    dataset
    {
        add("Sales Line")
        {
            column(FactoryCodeTable; FactoryCodeTable) { }
        }

        modify("Sales Line") 
        {
            trigger OnAfterAfterGetRecord()
            begin
                GetFactoryCodeTable("Sales Line");
            end;
        }
    }

    local procedure GetFactoryCodeTable(SalesLine: Record "Sales Line")
    var
        Item: Record Item;
    begin
        // Reset the variable
        Clear(FactoryCodeTable);

        // Make sure it is an Item line
        if SalesLine.Type <> SalesLine.Type::Item then
            exit;

        // Get the Item - exit if it does not exist
        if not Item.Get(SalesLine."No.") then
            exit;

        // Set the variable
        FactoryCodeTable := Item."Vendor Item No.";
    end;

    var
        FactoryCodeTable: Code[20];
}

---

Bonus Information

If you want to use the field from your table extension, then the correct method would be a FlowField which you would then need to calculate in the report extension using SetAutoCalcFields in OnAfterOnPreDataItem.

TableRelation basicly just set the foreign key of the field.

DataTransfer is reserved for Install and Upgrade codeunits.