Flowing Custom field from Sales Documents to Value Entries

mysamzamysamza Member Posts: 60
I have 2 custom fields made on the headers of Sales Order, Sales Invoice, Sales Credit Memo, Posted Sales Credit Memo, and Posted Sales Invoice. And they are also on lines as a flowfield Lookup just in case I want to refer to them on the line in any future customization (saves time going finding header).

The fields are;

Final Customer Code [20]

Final Customer Name [150]

I intend to flow these to the Value Entries table once a Sales Order or Sales Invoice is posted or Sales Credit Memo is posted. For all those mentioned documents a Value Entry line is created for sure, I just had to create the same fields in the table of Value Entries and find a suitable Event Subscriber.

The only Event I manage to find and wrote my code was as below;
[EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales-Post", 'OnAfterPostSalesLine', '', true, true)]
    local procedure OnAfterPostSalesLine(var SalesHeader: Record "Sales Header"; var SalesInvLine: Record "Sales Invoice Line"; var SalesCrMemoLine: Record "Sales Cr.Memo Line");
    var
        rec_ValEntry: Record "Value Entry";
        rec_CU: Codeunit cu;
        comp: Text;

    begin
        Clear(comp);
        comp := CompanyName;

        if comp = 'FZC' then begin
            if (SalesHeader."Document Type" = SalesHeader."Document Type"::Order) then begin
                rec_CU.ModifyValueEntryLineofSalesInv(SalesInvLine);
            end;

            if (SalesHeader."Document Type" = SalesHeader."Document Type"::"Credit Memo") then begin
                rec_CU.ModifyValueEntryLineofSalesCrMemo(SalesCrMemoLine);
            end;
        end;





// in a new codeunit .al file

codeunit 50005 "CU"
{
    Permissions = tabledata "Value Entry" = RIMD;
    
    
      procedure ModifyValueEntryLineofSalesInv(var SalesInvLine: Record "Sales Invoice Line")
    var
        rec_ValEntry: Record "Value Entry";
        rec_SalesInvHeader: Record "Sales Invoice Header";
    begin
        rec_ValEntry.Reset();
        Clear(rec_ValEntry);
        rec_SalesInvHeader.Reset();
        Clear(rec_SalesInvHeader);
        rec_SalesInvHeader.SetRange("No.", SalesInvLine."Document No.");
        if rec_SalesInvHeader.FindFirst() then begin
            rec_ValEntry.SetRange("Document Type", rec_ValEntry."Document Type"::"Sales Invoice");
            rec_ValEntry.SetRange("Document No.", rec_SalesInvHeader."No.");
            if rec_ValEntry.FindFirst() then begin
                rec_ValEntry."LLC Customer" := rec_SalesInvHeader."Final LLC Customer";
                rec_ValEntry."LLC Customer Name" := rec_SalesInvHeader."Final LLC Customer Name";
                rec_ValEntry.Modify();
            end;
        end;
    end;

    procedure ModifyValueEntryLineofSalesCrMemo(var SalesCrMemo: Record "Sales Cr.Memo Line")
    var
        rec_ValEntry: Record "Value Entry";
        rec_SalesCreHeader: Record "Sales Cr.Memo Header";
    begin
        rec_ValEntry.Reset();
        Clear(rec_ValEntry);
        rec_SalesCreHeader.Reset();
        Clear(rec_SalesCreHeader);
        rec_SalesCreHeader.SetRange("No.", SalesCrMemo."Document No.");
        if rec_SalesCreHeader.FindFirst() then begin
            rec_ValEntry.SetRange("Document Type", rec_ValEntry."Document Type"::"Sales Credit Memo");
            rec_ValEntry.SetRange("Document No.", rec_SalesCreHeader."No.");
            if rec_ValEntry.FindFirst() then begin
                rec_ValEntry."LLC Customer" := rec_SalesCreHeader."Final LLC Customer";
                rec_ValEntry."LLC Customer Name" := rec_SalesCreHeader."Final LLC Customer Name";
                rec_ValEntry.Modify();
            end;
        end;
    end;
..
..
..
..
    end;

however, this Event subscriber still misses flowing the value to Value Entries so I was here to ask if there is another Event I can use that on Posting my SO, SI, Sales Credit Memo can flow my fields to value entries table.

Thanks a lot for the advice in advance!

Best Answers

  • samantha73samantha73 Member Posts: 29
    Accepted Answer
    Run below code and see what happens - also check permissions for tables - don't know what version you are on..if it works then add complexity by flow fields etc..

    codeunit 50004 U_UpdateValeEnty
    {
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales-Post", 'OnAfterPostSalesLine', '', true, true)]
    local procedure OnAfterPostSalesLine(var SalesHeader: Record "Sales Header"; var SalesInvLine: Record "Sales Invoice Line"; var SalesCrMemoLine: Record "Sales Cr.Memo Line");
    var
    rec_ValEntry: Record "Value Entry";
    rec_CU: Codeunit cu;
    comp: Text;

    begin
    // Clear(comp);
    // comp := CompanyName;

    // if comp = 'FZC' then begin
    if (SalesHeader."Document Type" = SalesHeader."Document Type"::Order) then begin
    rec_CU.ModifyValueEntryLineofSalesInv(SalesInvLine);
    end;

    if (SalesHeader."Document Type" = SalesHeader."Document Type"::"Credit Memo") then begin
    rec_CU.ModifyValueEntryLineofSalesCrMemo(SalesCrMemoLine);
    end;
    end;

    }





    codeunit 50005 "CU"
    {
    Permissions = tabledata "Value Entry" = RIMD;


    procedure ModifyValueEntryLineofSalesInv(var SalesInvLine: Record "Sales Invoice Line")
    var
    rec_ValEntry: Record "Value Entry";
    rec_SalesInvHeader: Record "Sales Invoice Header";
    begin
    rec_ValEntry.Reset();
    Clear(rec_ValEntry);
    rec_SalesInvHeader.Reset();
    Clear(rec_SalesInvHeader);
    rec_SalesInvHeader.SetRange("No.", SalesInvLine."Document No.");
    if rec_SalesInvHeader.FindFirst() then begin
    rec_ValEntry.SetRange("Document Type", rec_ValEntry."Document Type"::"Sales Invoice");
    rec_ValEntry.SetRange("Document No.", rec_SalesInvHeader."No.");
    if rec_ValEntry.FindFirst() then begin
    rec_ValEntry.U_CustomerCode := rec_SalesInvHeader."Sell-to Customer No.";
    rec_ValEntry.U_CustomerName := rec_SalesInvHeader."Sell-to Customer Name";
    rec_ValEntry.Modify();
    end;
    end;
    end;

    procedure ModifyValueEntryLineofSalesCrMemo(var SalesCrMemo: Record "Sales Cr.Memo Line")
    var
    rec_ValEntry: Record "Value Entry";
    rec_SalesCreHeader: Record "Sales Cr.Memo Header";
    begin
    rec_ValEntry.Reset();
    Clear(rec_ValEntry);
    rec_SalesCreHeader.Reset();
    Clear(rec_SalesCreHeader);
    rec_SalesCreHeader.SetRange("No.", SalesCrMemo."Document No.");
    if rec_SalesCreHeader.FindFirst() then begin
    rec_ValEntry.SetRange("Document Type", rec_ValEntry."Document Type"::"Sales Credit Memo");
    rec_ValEntry.SetRange("Document No.", rec_SalesCreHeader."No.");
    if rec_ValEntry.FindFirst() then begin
    rec_ValEntry.U_CustomerCode := rec_SalesCreHeader."Sell-to Customer No.";
    rec_ValEntry.U_CustomerName := rec_SalesCreHeader."Sell-to Customer Name";
    rec_ValEntry.Modify();
    end;
    end;
    end;
    }
  • samantha73samantha73 Member Posts: 29
    Accepted Answer
    We are still going on this :)..so out of curiosity added a flow field and pushed it to value entries using below code -only Invoice part ignore credit:

    codeunit 50001 U_UpdateValueEntry
    {
    Permissions = tabledata "Value Entry" = RIMD;

    [EventSubscriber(ObjectType::Codeunit, 22, 'OnafterInsertValueEntry', '', false, false)]
    local procedure OnBeforeInsertValueEntry(var ValueEntry: Record "Value Entry")
    var
    SalesInvHeader: Record "Sales Invoice Header";
    SalesCrMemoHdr: Record "Sales Cr.Memo Header";
    begin
    if (ValueEntry."Item Ledger Entry Type" = ValueEntry."Item Ledger Entry Type"::Sale) then
    case ValueEntry."Document Type" of
    ValueEntry."Document Type"::"Sales Credit Memo":
    begin

    if SalesCrMemoHdr.Get(ValueEntry."Document No.") then;
    ValueEntry.Description := SalesCrMemoHdr."Sell-to Customer Name";
    //ValueEntry.Description := SalesCrMemoHdr."Final LLC Customer Name";
    end;
    ValueEntry."Document Type"::"Sales Invoice":
    begin

    if SalesInvHeader.Get(ValueEntry."Document No.") then;
    //ValueEntry.Validate(Description, SalesInvHeader."Sell-to Customer Name");
    SalesInvHeader.CalcFields(U_CustomerName2);
    ValueEntry.Validate(Description, SalesInvHeader.U_CustomerName2);

    ValueEntry.Modify()
    end;
    end;
    end;
    }

Answers

  • samantha73samantha73 Member Posts: 29
    Tried this code on v17.5 SaaS and worked but instead of user fields mapped to sales header customer code and customer name. Maybe try this first with below flow
    Process -- sales order ---post ---ship & invoice
  • bbrownbbrown Member Posts: 3,114
    I'd have to say I disagree with this approach. Flow the custom values to the "Value Entries" as they are being created. No need to retrieve records that are already available in the posting process. Also, as a good general rule, avoid interacting with a table differently from how the base system does. "Value Entry" records are only ever inserted. They are never modified or deleted. So your code should never either. Among other things, this can help avoid deadlocks.

    "Value Entries" are created from "Item Journal Lines". Which in turn are created from the document lines. Follow this logic and the available events to bring your fields to these tables.

    There are no bugs - only undocumented features.
  • mysamzamysamza Member Posts: 60
    @bbrown I wrote the following code as per your suggestion, went to post a SO and it did not move my custom fields to Value Entries;
    [EventSubscriber(ObjectType::Codeunit, 22, 'OnBeforeInsertValueEntry', '', false, false)]
        local procedure OnBeforeInsertValueEntry(var ValueEntry: Record "Value Entry")
        var
           SalesInvHeader: Record "Sales Invoice Header";
            SalesCrMemoHdr: Record "Sales Cr.Memo Header";
        begin
             if (ValueEntry."Item Ledger Entry Type" = ValueEntry."Item Ledger Entry Type"::Sale) then
                 case ValueEntry."Document Type" of
                     ValueEntry."Document Type"::"Sales Credit Memo":
                        begin
                     
                            if SalesCrMemoHdr.Get(ValueEntry."Document No.") then;
                            ValueEntry."LLC Customer" := SalesCrMemoHdr."Final LLC Customer";
                            ValueEntry."LLC Customer Name" := SalesCrMemoHdr."Final LLC Customer Name";
                        end;
                    ValueEntry."Document Type"::"Sales Invoice":
                        begin
                     
                             if SalesInvHeader.Get(ValueEntry."Document No.") then;
                            ValueEntry."LLC Customer" := SalesCrMemoHdr."Final LLC Customer";
                            ValueEntry."LLC Customer Name" := SalesCrMemoHdr."Final LLC Customer Name";
                         end;
                end;
        end;
    
  • bbrownbbrown Member Posts: 3,114
    This is not what I'm saying Your custom fields should already be part of the "Item Journal Line" being used to create the "Value Entry". There should be no need to "get" the document. This fails because the posted documents have not yet been created.

    Look at how the standard process flows field values from the documents to the ledgers. Use that as your guide. There should be events along that path you can use.
    There are no bugs - only undocumented features.
  • David_SingletonDavid_Singleton Member Posts: 5,460
    edited 2021-06-01
    mysamza wrote: »
    rec_ValEntry
    

    you lost me at the terrible variable naming. :'(

    Please listen to the advise given by bbrown, he really knows what he is talking about.



    David Singleton
  • mysamzamysamza Member Posts: 60
    @David_Singleton sorry if we members here do not follow your self-defined naming conventions for variables. Unless you are talking about this https://docs.microsoft.com/en-us/dynamics-nav/naming-conventions or https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/compliance/apptest-bestpracticesforalcode which I am aware.
  • samantha73samantha73 Member Posts: 29
    Accepted Answer
    Run below code and see what happens - also check permissions for tables - don't know what version you are on..if it works then add complexity by flow fields etc..

    codeunit 50004 U_UpdateValeEnty
    {
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales-Post", 'OnAfterPostSalesLine', '', true, true)]
    local procedure OnAfterPostSalesLine(var SalesHeader: Record "Sales Header"; var SalesInvLine: Record "Sales Invoice Line"; var SalesCrMemoLine: Record "Sales Cr.Memo Line");
    var
    rec_ValEntry: Record "Value Entry";
    rec_CU: Codeunit cu;
    comp: Text;

    begin
    // Clear(comp);
    // comp := CompanyName;

    // if comp = 'FZC' then begin
    if (SalesHeader."Document Type" = SalesHeader."Document Type"::Order) then begin
    rec_CU.ModifyValueEntryLineofSalesInv(SalesInvLine);
    end;

    if (SalesHeader."Document Type" = SalesHeader."Document Type"::"Credit Memo") then begin
    rec_CU.ModifyValueEntryLineofSalesCrMemo(SalesCrMemoLine);
    end;
    end;

    }





    codeunit 50005 "CU"
    {
    Permissions = tabledata "Value Entry" = RIMD;


    procedure ModifyValueEntryLineofSalesInv(var SalesInvLine: Record "Sales Invoice Line")
    var
    rec_ValEntry: Record "Value Entry";
    rec_SalesInvHeader: Record "Sales Invoice Header";
    begin
    rec_ValEntry.Reset();
    Clear(rec_ValEntry);
    rec_SalesInvHeader.Reset();
    Clear(rec_SalesInvHeader);
    rec_SalesInvHeader.SetRange("No.", SalesInvLine."Document No.");
    if rec_SalesInvHeader.FindFirst() then begin
    rec_ValEntry.SetRange("Document Type", rec_ValEntry."Document Type"::"Sales Invoice");
    rec_ValEntry.SetRange("Document No.", rec_SalesInvHeader."No.");
    if rec_ValEntry.FindFirst() then begin
    rec_ValEntry.U_CustomerCode := rec_SalesInvHeader."Sell-to Customer No.";
    rec_ValEntry.U_CustomerName := rec_SalesInvHeader."Sell-to Customer Name";
    rec_ValEntry.Modify();
    end;
    end;
    end;

    procedure ModifyValueEntryLineofSalesCrMemo(var SalesCrMemo: Record "Sales Cr.Memo Line")
    var
    rec_ValEntry: Record "Value Entry";
    rec_SalesCreHeader: Record "Sales Cr.Memo Header";
    begin
    rec_ValEntry.Reset();
    Clear(rec_ValEntry);
    rec_SalesCreHeader.Reset();
    Clear(rec_SalesCreHeader);
    rec_SalesCreHeader.SetRange("No.", SalesCrMemo."Document No.");
    if rec_SalesCreHeader.FindFirst() then begin
    rec_ValEntry.SetRange("Document Type", rec_ValEntry."Document Type"::"Sales Credit Memo");
    rec_ValEntry.SetRange("Document No.", rec_SalesCreHeader."No.");
    if rec_ValEntry.FindFirst() then begin
    rec_ValEntry.U_CustomerCode := rec_SalesCreHeader."Sell-to Customer No.";
    rec_ValEntry.U_CustomerName := rec_SalesCreHeader."Sell-to Customer Name";
    rec_ValEntry.Modify();
    end;
    end;
    end;
    }
  • samantha73samantha73 Member Posts: 29
    Accepted Answer
    We are still going on this :)..so out of curiosity added a flow field and pushed it to value entries using below code -only Invoice part ignore credit:

    codeunit 50001 U_UpdateValueEntry
    {
    Permissions = tabledata "Value Entry" = RIMD;

    [EventSubscriber(ObjectType::Codeunit, 22, 'OnafterInsertValueEntry', '', false, false)]
    local procedure OnBeforeInsertValueEntry(var ValueEntry: Record "Value Entry")
    var
    SalesInvHeader: Record "Sales Invoice Header";
    SalesCrMemoHdr: Record "Sales Cr.Memo Header";
    begin
    if (ValueEntry."Item Ledger Entry Type" = ValueEntry."Item Ledger Entry Type"::Sale) then
    case ValueEntry."Document Type" of
    ValueEntry."Document Type"::"Sales Credit Memo":
    begin

    if SalesCrMemoHdr.Get(ValueEntry."Document No.") then;
    ValueEntry.Description := SalesCrMemoHdr."Sell-to Customer Name";
    //ValueEntry.Description := SalesCrMemoHdr."Final LLC Customer Name";
    end;
    ValueEntry."Document Type"::"Sales Invoice":
    begin

    if SalesInvHeader.Get(ValueEntry."Document No.") then;
    //ValueEntry.Validate(Description, SalesInvHeader."Sell-to Customer Name");
    SalesInvHeader.CalcFields(U_CustomerName2);
    ValueEntry.Validate(Description, SalesInvHeader.U_CustomerName2);

    ValueEntry.Modify()
    end;
    end;
    end;
    }
  • mysamzamysamza Member Posts: 60
    hello, @samantha73 thanks a ton! it works!
  • bbrownbbrown Member Posts: 3,114
    That code may work but is still inefficient. It also violates the rule on never modifying "Value Entry". See above. You can use the "OnInitValueEntryOnAfterAssignFields" event (CU 22) to update your custom fields before the "Value Entry" is inserted. Thus avoiding the need to modify it.

    You do this by moving your field values to the "ItemJnlLine" when it's created in CU 80. I'll leave you to sort that part out.
    There are no bugs - only undocumented features.
Sign In or Register to comment.