Flowing Custom field from Sales Documents to Value Entries

mysamza · 2021-05-31

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!

samantha73 · 2021-06-02

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;
}

samantha73 · 2021-06-02

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;
}

samantha73 · 2021-05-31

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

bbrown · 2021-06-01

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.

mysamza · 2021-06-01

@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;

bbrown · 2021-06-01

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.

David_Singleton · 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.

mysamza · 2021-06-01

@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.

samantha73 · 2021-06-02

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;
}

samantha73 · 2021-06-02

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;
}

mysamza · 2021-06-02

hello, @samantha73 thanks a ton! it works!

bbrown · 2021-06-02

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.

Howdy, Stranger!

Quick Links

Categories

Flowing Custom field from Sales Documents to Value Entries

Best Answers

Answers