Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Modifying custom value...
Small and medium business | Business Central, N...
Answered

Modifying custom value of one table from another table

(0) ShareShare
ReportReport
Posted on by 1239875612039 15

Hello,

My company does quite a bit of dropshipment, and generally categorize everything based on customer & customer PO. Therefore, we would like our PO # to be available on sales documents.

I have created a custom field in "Sales Header" and "Sales Invoice Header" tables called "Purchase Order", and shown that value on the appropriate pages. Everything works there! However, I would like to be able to automatically copy our purchase order number to the "Sales Header" table, when the "Get Sales Lines" action is used. I see some documentation on records, however I cannot quite figure out how to modify the Sales Header from within the Purchase Header PageExtension (as this is the only place I can find that will allow me to set a trigger based upon the "Get Sales Order" action).

Can anyone help me? Thank you!

Categories:
Business Central
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,696 Super User 2024 Season 2 on at
    RE: Modifying custom value of one table from another table

    I think it's better to replace this field with an actual field.

    In this way, when posting, as long as the id, type, and length are consistent, the value will be automatically transferred to the posted document.

    pastedimage1681084212840v1.png

    Then there is the problem of assignment. From your Flowfield, you need "Sales Line"."Purchase Order No.". I'm not sure at what point you need this value, the following is just a simple example, you can modify it.

    pastedimage1681084275619v2.png

    Source Code:

    tableextension 50111 MyExtension2 extends "Sales Header"
{
    fields
    {
        field(50101; "MyPOFlowfield"; Text[50])
        {
            Caption = 'Flow PO #:';
            DataClassification = CustomerContent;
            Editable = false;
        }
    }
}

tableextension 50113 MyExtension3 extends "Sales Invoice Header"
{
    fields
    {
        field(50101; "MyPOFlowfield"; Text[50])
        {
            Caption = 'Flow PO #:';
            DataClassification = CustomerContent;
            Editable = false;
        }
    }
}

tableextension 50114 MyExtension4 extends "Sales Line"
{
    fields
    {
        modify("Purchase Order No.")
        {
            trigger OnAfterValidate()
            var
                SalesHeader: Record "Sales Header";
            begin
                if SalesHeader.Get(Rec."Document Type", Rec."Document Type") then
                    if SalesHeader.MyPOFlowfield <> '' then begin
                        SalesHeader.MyPOFlowfield := Rec."Purchase Order No.";
                        SalesHeader.Modify();
                    end;
            end;
        }
    }
}

    Hope this helps.

    Thanks.

    ZHU

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,139 Super User 2024 Season 2 on at
    RE: Modifying custom value of one table from another table

    try

    Rec.CALCFIELDS("MyPOFlowfield");

    SalesInvoiceRecord1."MyPOFlowfield" := Rec."MyPOFlowfield";

  • 1239875612039 Profile Picture
    1239875612039 15 on at
    RE: Modifying custom value of one table from another table

    yzhums  Sorry, I did not do a good job explaining!

    In my Sales Header tableextension, I have the following code:

    field(50101; "MyPOFlowfield"; Text[50])

           {

               Caption = 'Flow PO #:';

               FieldClass = FlowField;

               CalcFormula = lookup("Sales Line"."Purchase Order No." where("Document No." = field("No.")));

           }

    I also have a PageExtension for the sales order, to display this value, and can see this value on the sales order. That part works! What I cannot figure out is how to transfer this value to the sales invoice once it's been posted. I have a TableExtension for the sales invoice, declaring a blank field in which I can put my new value, seen here:

    field(50101; "MyPOFlowfield"; Text[50])  // Note this is not a flowfield, just a normal field.

           {

               Caption = 'Flow PO #:';

               DataClassification = ToBeClassified;

           }

    I have attempted to directly assign the Sales Invoice field the value of the Sales Header flowfield, but it doesn't seem to work.  Here is that code in the sales order PageExtension:

      actions

       {

           modify(Post)

           {

               trigger OnAfterAction()

               var

                   SalesInvoiceRecord1: Record "Sales Invoice Header";

               begin

                   SalesInvoiceRecord1."MyPOFlowfield" := Rec."MyPOFlowfield";

               end;

           }

      }

    I think it does not work because either A) the flowfield is deleted after posting or B) because I cannot assign a flowfield calculated value to a "normal" value? I am not sure which. I've been trying for hours, if you would be able to help I'd be very grateful..

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,696 Super User 2024 Season 2 on at
    RE: Modifying custom value of one table from another table

    Hi, It seems that you are missing Record.CalcFields(Any [, Any,...]) Method before reading value in Flowfield.
    More details: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/record/record-calcfields-method

    https://yzhums.com/5802/

    Also, please note that the Flowfield is a virtual field, you cannot update it or assign a value to it.

    Hope this helps.

    Thanks.

    ZHU

  • 1239875612039 Profile Picture
    1239875612039 15 on at
    RE: Modifying custom value of one table from another table

    Mohana Yadav I'm hoping you can answer another question - I have created the custom flowfield which works great, but I cannot figure out to pass that value to the sales invoice.

    I tried creating a standard field and a flowfield, and upon posting assign the value of the flowfield to the standard field. Then I would set the value of the sales invoice field equal to the standard field of the sales header. However, this doesn't seem to transfer over when I post.. Any suggestions?

    I have a normal PO that is manually defined, and a Flowfield that is pulling from the Sales Lines. Before posting, they are both correct..

    Here is a snippet of my code, within the Sales Order Page Extension:

    actions

       {

           modify(Post)

           {

               trigger OnAfterAction();

               var

                   SalesInvoiceRecord1: Record "Sales Invoice Header";

               begin

                   SalesInvoiceRecord1.Reset();

                   Rec."My_PO" := Rec."My_PO_Flowfield";   //assign the Flowfield to the normal field (overwriting it)

                   SalesInvoiceRecord1."My_PO_Flowfield" := Rec."My_PO"; //then cast this records normal field to flow value

               end;

           }

       }

  • 1239875612039 Profile Picture
    1239875612039 15 on at
    RE: Modifying custom value of one table from another table

    Thank you! (For anyone else looking at this answer in the future, I also recommend Erik Hougaard's video on the subject, which helped me a lot)

  • Verified answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,139 Super User 2024 Season 2 on at
    RE: Modifying custom value of one table from another table

    You can create a flowfield to show the Purchase Order No. of Line on the header.

  • 1239875612039 Profile Picture
    1239875612039 15 on at
    RE: Modifying custom value of one table from another table

    I'd like it to be searchable from the sales orders list, and more importantly I need it on the sales invoices after jobs have been completed. We search our manufacturer's configuration files based on our Purchase Order No., but customers will give us their Purchase Order No. when reaching out for help & technical assistance.

    I am happy to copy it from the Sales Lines to the Sales Header, but I have to be able to do it from within the "Purchase Header" PageExtension, as that's where the trigger I was hoping to use is located.

    Thanks!

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,139 Super User 2024 Season 2 on at
    RE: Modifying custom value of one table from another table

    Purchase Order No. is on the Sales Lines.

    Is there any specific reason to have it on the header lines?

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans