Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to assign field da...
Small and medium business | Business Central, N...
Answered

How to assign field data from another table to new field

(0) ShareShare
ReportReport
Posted on by T_Mauser 397 User Group Leader

Hi,

I added a new field to the Sales Invoice Header - everything works so far.

First I took the same field ID (5971) from the Sales Header table and it worked in sandbox so far - means I got the right value from the Sales Header table to the Sales Invoice Header table.

-> But I wasn't able to deploy it into the live system because - it says I have to choose a number between 50000-....

So if I give the field ID "50150" I didn't get a value from the list as before...

Is there another way to assign field data from another table to a other field in an other table?

My code:

tableextension 50150 "Sales Invoice TableExt" extends "Sales Invoice Header"
{
    fields
    {
        field(50150; "Promised_Delivery_Date"; Date)
        {
            //How to get Data from field 5791 (Sales Header)?
            //target: 50150 = 5791;

            AccessByPermission = TableData "Sales Header" = R; 
            Caption = 'Promised Delivery Date';
        }

    }
}

  • @Yash Profile Picture
    @Yash 30 on at
    RE: How to assign field data from another table to new field

    Thanks Bert ,it worked.

  • Bert Verbeek Profile Picture
    Bert Verbeek 110 on at
    RE: How to assign field data from another table to new field

    You have to work with strange or setfilter:

    https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/record/record-setrange-method

    Then you filter the table. 

  • @Yash Profile Picture
    @Yash 30 on at
    RE: How to assign field data from another table to new field

    Hi Bert,

    In your second code you have used the FindFirst() method.But this will give only the first record of that field.If i want to get a particular record based on some condition,like for WHERE "Sr_no"=5,then how to get that record? Where to apply the WHERE condition and filter the field value? It would be of great help if you reply.Thanks. 

  • Verified answer
    T_Mauser Profile Picture
    T_Mauser 397 User Group Leader on at
    RE: How to assign field data from another table to new field

    Thank you, it's working!

    Tom

  • Verified answer
    Bert Verbeek Profile Picture
    Bert Verbeek 110 on at
    RE: How to assign field data from another table to new field

    Ok now I understand you.

    You have to create an table extension in the Sales Invoice Header:

    tableextension 50150 "Sales In Header TableExt" extends "Sales Invoice Header"
{
    fields
    {
        field(50150; Promised_Delivery_Date; Date)
        {
            //How to get Data from field 5791 (Sales Header)?
            //target: 50150 = 5791;

            Caption = 'Zugesagtes Lieferdatum';
            /*  TableRelation = "Sales Header";
             DataClassification = CustomerContent; */

        }
    }

}

    If the Sales Invoice Header is created you can add a table extension on the Sales Header to Sync the field:

    tableextension 50151 SalesHeaderExt extends "Sales Header"
{
    fields
    {
        modify("Promised Delivery Date")
        {
            trigger OnAfterValidate()
            var
                SalesInvoiceHeader: Record "Sales Invoice Header";
            begin
                SalesInvoiceHeader.Reset;
                SalesInvoiceHeader.SetRange(); //Do some filtering
                If SalesInvoiceHeader.FindFirst() then begin
                    rec."Promised Delivery Date" := SalesInvoiceHeader.Promised_Delivery_Date;
                    SalesInvoiceHeader.Modify();
                end;
            end;
        }
    }
}

    But if you do it on posting you need an event (as Vaishnavi Joshi wrote):

    codeunit 50150 SalesInvHeader
{
    trigger OnRun()
    begin
        
    end;
    
   [EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales-Post",'OnAfterSalesInvHeaderInsert','',false,false)]
   local procedure OnAfterSalesInvHeaderInsert(SalesHeader: Record "Sales Header"; var SalesInvHeader: Record "Sales Invoice Header")
   begin
       if SalesHeader."Document Type" = SalesHeader."Document Type"::Invoice then begin
           SalesInvHeader.Validate(Promised_Delivery_Date,SalesHeader."Promised Delivery Date");
           SalesInvHeader.Modify(true);
       end;
   end;
}

  • T_Mauser Profile Picture
    T_Mauser 397 User Group Leader on at
    RE: How to assign field data from another table to new field
    [quote user="Bert Verbeek"

    You don't have to use the var salesheader. Just assign it directly to the field: rec.promised delivery date  .

    And maybe without the if statrmement. Because initially your field is empty. 

    [/quote

    Hi Bert, 

    I have tried it -

    But if I don't use the var - how can I get the table and from that table the value from "Promised Delivery Date"? -> So if dont use it, it's not useable.

    The field "Promised Delivery Date" is in the Sales Header Table and the "Promised_Delivery_Date" is in the Sales Invoice Header table.

    THANK YOU FOR YOUR TIME!

    tableextension 50150 "Sales In Header TableExt" extends "Sales Invoice Header"
{
    fields
    {
        field(50150; Promised_Delivery_Date; Date)
        {
            //How to get Data from field 5791 (Sales Header)?
            //target: 50150 = 5791;

            Caption = 'Zugesagtes Lieferdatum';
            /*  TableRelation = "Sales Header";
             DataClassification = CustomerContent; */

        }
        modify(Promised_Delivery_Date)
        {
            trigger OnAfterValidate()
            var
                SalesHeader: Record "Sales Header";
            begin
                rec.Promised_Delivery_Date := SalesHeader."Promised Delivery Date"
            end;
        }

    }

}

  • Bert Verbeek Profile Picture
    Bert Verbeek 110 on at
    RE: How to assign field data from another table to new field

    You don't have to use the var salesheader. Just assign it directly to the field: rec.promised delivery date  .

    And maybe without the if statrmement. Because initially your field is empty. 

  • T_Mauser Profile Picture
    T_Mauser 397 User Group Leader on at
    RE: How to assign field data from another table to new field

    Hi Bert,

    such as this one? - but it didn't work

    tableextension 50150 "Sales In Header TableExt" extends "Sales Invoice Header"
{
    fields
    {
        field(50150; Promised_Delivery_Date; Date)
        {
            //How to get Data from field 5791 (Sales Header)?
            //target: 50150 = 5791;

            Caption = 'Promised Delivery Date';
            TableRelation = "Sales Header";
            DataClassification = CustomerContent;

        }
        modify(Promised_Delivery_Date)
        {
            trigger OnAfterValidate()
            begin
                if (rec.Promised_Delivery_Date = SalesHeader."Promised Delivery Date") then
                    Promised_Delivery_Date := SalesHeader."Promised Delivery Date"
                else
                    error('The Date delivery doesnot work');
            end;
        }

    }
    var
        SalesHeader: Record "Sales Header";

}

  • Bert Verbeek Profile Picture
    Bert Verbeek 110 on at
    RE: How to assign field data from another table to new field

    You can also do an onaftervalidate on the fields that you want to copy and assign it to the newest field.

  • Suggested answer
    Vaishnavi J Profile Picture
    Vaishnavi J 3,056 on at
    RE: How to assign field data from another table to new field

    Hi,

    You can use event subscriber when you post the sales invoice and created Sales invoice header during that time the field will be set.

    pastedimage1645118164921v1.png

    If my answer was helpful to you, please verify it so that other users know it worked. Thank you very much.

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

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,379 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans