Dynamics 365 Business Central: Display Customer Name and Vendor Name (Source Name) in General Ledger Entries

Dynamics 365 Business Central

Hi, Readers.
Recently I received a question, is there a way to display customer name and vendor name in General Ledger Entries?

Indeed there is no Customer Name field and Vendor Name field in the G/L Entry (17) table. But there are Source Type and Source No. fields that can be used (hidden by default).

Standard code of G/L Entry (17) table:

Gen. Journal Source Type:

You can use Design or page extension to display Source Type and Source No. fields in General Ledger Entries.

Now you’ve got Customer No. (Source Type = Customer) and Vendor No. (Source Type = Vendor).

The next question is how to display the name based on Source Type and Source No.. In this post, I will share four common ways that I hope will help you.

1. Create a variable or a procedure on the page to display the name

1. You can create a new variable, then put the logic to retrieve the customer name and vendor name on trigger OnAfterGetRecord(), and show that variable on the page.

For example:

Source Code:

pageextension 50122 "GL Entries Source Name Ext" extends "General Ledger Entries"
{
    layout
    {
        addafter("Source Code")
        {
            field("Source Type"; Rec."Source Type")
            {
                Caption = 'Source Type';
                ApplicationArea = All;
            }
            field("Source No."; Rec."Source No.")
            {
                Caption = 'Source No.';
                ApplicationArea = All;
            }

            field("ZY Source Name"; SourceName)
            {
                Caption = 'Source Name';
                ApplicationArea = All;
            }
        }
    }

    var
        SourceName: Text[100];

    trigger OnAfterGetRecord()
    var
        Cust: Record Customer;
        Vend: Record Vendor;
        FA: Record "Fixed Asset";
        BankAccount: Record "Bank Account";
        Employee: Record Employee;
    begin
        SourceName := '';
        Case Rec."Source Type" of
            Rec."Source Type"::Customer:
                If Cust.GET(Rec."Source No.") then
                    SourceName := Cust.Name;
            Rec."Source Type"::Vendor:
                If Vend.GET(Rec."Source No.") then
                    SourceName := Vend.Name;
            Rec."Source Type"::"Fixed Asset":
                If FA.GET(Rec."Source No.") then
                    SourceName := FA.Description;
            Rec."Source Type"::"Bank Account":
                If BankAccount.GET(Rec."Source No.") then
                    SourceName := BankAccount.Name;
            Rec."Source Type"::Employee:
                If Employee.GET(Rec."Source No.") then
                    SourceName := Employee.FullName();
        end;
    end;
}

2. You can create a new procedure that has a return value, then put the logic to retrieve the customer name and vendor name on the new procedure, and show the return value on the page directly.

Source Code:

pageextension 50122 "GL Entries Source Name Ext" extends "General Ledger Entries"
{
    layout
    {
        addafter("Source Code")
        {
            field("Source Type"; Rec."Source Type")
            {
                Caption = 'Source Type';
                ApplicationArea = All;
            }
            field("Source No."; Rec."Source No.")
            {
                Caption = 'Source No.';
                ApplicationArea = All;
            }

            field("ZY Source Name"; GetSourceName())
            {
                Caption = 'Source Name';
                ApplicationArea = All;
            }
        }
    }

    local procedure GetSourceName(): Text[100]
    var
        Cust: Record Customer;
        Vend: Record Vendor;
        FA: Record "Fixed Asset";
        BankAccount: Record "Bank Account";
        Employee: Record Employee;
    begin
        Case Rec."Source Type" of
            Rec."Source Type"::Customer:
                If Cust.GET(Rec."Source No.") then
                    exit(Cust.Name);
            Rec."Source Type"::Vendor:
                If Vend.GET(Rec."Source No.") then
                    exit(Vend.Name);
            Rec."Source Type"::"Fixed Asset":
                If FA.GET(Rec."Source No.") then
                    exit(FA.Description);
            Rec."Source Type"::"Bank Account":
                If BankAccount.GET(Rec."Source No.") then
                    exit(BankAccount.Name);
            Rec."Source Type"::Employee:
                If Employee.GET(Rec."Source No.") then
                    exit(Employee.FullName);
        end;
    end;
}

Test:

Note: This will reduce the performance because the variable or the return value needs to be retrieved every time you open the page. And you cannot search or filter this column.

2. Use flowfields

Using flowfield would be the easiest way to do it. But not the best way, because you cannot include “Source Type” as a condition (Flowfields cannot be conditioned). If you want to display both the customer name and the vendor name, you have to create two flowfields, one for customer name and one for vendor name. And if you have a Vendor and a Customer having the same No., the system will still look up the data. For example, the system will look up the Customer, even if the entry originates from the Vendor.

PS: In order to solve the problem above, you could create a new table. Store the data you need to look up, such as customer master and vendor master. Then use Flowfield to the new table.

For example:
New table:

table 50100 "ZY GL Source Name"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; "Source Type"; Enum "Gen. Journal Source Type")
        {
            Caption = 'Source Type';
            DataClassification = CustomerContent;
        }
        field(2; "Source No."; Code[20])
        {
            Caption = 'Source No.';
            DataClassification = CustomerContent;
            TableRelation = IF ("Source Type" = CONST(Customer)) Customer
            ELSE
            IF ("Source Type" = CONST(Vendor)) Vendor
            ELSE
            IF ("Source Type" = CONST("Bank Account")) "Bank Account"
            ELSE
            IF ("Source Type" = CONST("Fixed Asset")) "Fixed Asset"
            ELSE
            IF ("Source Type" = CONST(Employee)) Employee;
        }
        field(3; "Source Name"; Text[100])
        {
            Caption = 'Source Name';
            DataClassification = CustomerContent;
        }
    }

    keys
    {
        key(PK; "Source Type", "Source No.")
        {
            Clustered = true;
        }
    }

}

Look up to new table.

tableextension 50111 "GL Entry Source Name Ext" extends "G/L Entry"
{
    fields
    {
        field(50100; "ZY Source Name"; Text[100])
        {
            Caption = 'Source Name';
            Editable = false;
            FieldClass = FlowField;
            CalcFormula = lookup("ZY GL Source Name"."Source Name" where("Source Type" = field("Source Type"),
                                                                        "Source No." = field("Source No.")));
        }
    }
}

Transfer data when installing.

codeunit 50100 InsertSourceName
{
    Subtype = Install;

    trigger OnInstallAppPerCompany()
    begin
        InsertCustomers();
        InsertVendors();
    end;

    local procedure InsertCustomers()
    var
        Cust: Record Customer;
        ZYGLSourceName: Record "ZY GL Source Name";
    begin
        ZYGLSourceName.Reset();
        ZYGLSourceName.SetRange("Source Type", ZYGLSourceName."Source Type"::Customer);
        if ZYGLSourceName.IsEmpty then begin
            Cust.Reset();
            ZYGLSourceName.Init();
            if Cust.FindSet() then
                repeat
                    ZYGLSourceName."Source Type" := ZYGLSourceName."Source Type"::Customer;
                    ZYGLSourceName."Source No." := Cust."No.";
                    ZYGLSourceName."Source Name" := Cust.Name;
                    ZYGLSourceName.Insert();
                until Cust.Next() = 0;
        end;
    end;

    local procedure InsertVendors()
    var
        Vend: Record Vendor;
        ZYGLSourceName: Record "ZY GL Source Name";
    begin
        ZYGLSourceName.Reset();
        ZYGLSourceName.SetRange("Source Type", ZYGLSourceName."Source Type"::Vendor);
        if ZYGLSourceName.IsEmpty then begin
            Vend.Reset();
            ZYGLSourceName.Init();
            if Vend.FindSet() then
                repeat
                    ZYGLSourceName."Source Type" := ZYGLSourceName."Source Type"::Vendor;
                    ZYGLSourceName."Source No." := Vend."No.";
                    ZYGLSourceName."Source Name" := Vend.Name;
                    ZYGLSourceName.Insert();
                until Vend.Next() = 0;
        end;
    end;
}

Update data when changed. (For example, customer table)

codeunit 50101 UpdateGLSourceName
{
    [EventSubscriber(ObjectType::Table, Database::Customer, 'OnAfterInsertEvent', '', false, false)]
    local procedure InsertCustomer(var Rec: Record Customer)
    var
        ZYGLSourceName: Record "ZY GL Source Name";
    begin
        if not ZYGLSourceName.Get(ZYGLSourceName."Source Type"::Customer, Rec."No.") then begin
            ZYGLSourceName.Init();
            ZYGLSourceName."Source Type" := ZYGLSourceName."Source Type"::Customer;
            ZYGLSourceName."Source No." := Rec."No.";
            ZYGLSourceName."Source Name" := Rec.Name;
            ZYGLSourceName.Insert();
        end;
    end;

    [EventSubscriber(ObjectType::Table, Database::Customer, 'OnAfterModifyEvent', '', false, false)]
    local procedure ModifyCustomer(var Rec: Record Customer)
    var
        ZYGLSourceName: Record "ZY GL Source Name";
    begin
        if ZYGLSourceName.Get(ZYGLSourceName."Source Type"::Customer, Rec."No.") then begin
            ZYGLSourceName."Source No." := Rec."No.";
            ZYGLSourceName."Source Name" := Rec.Name;
            ZYGLSourceName.Modify();
        end;
    end;

    [EventSubscriber(ObjectType::Table, Database::Customer, 'OnAfterDeleteEvent', '', false, false)]
    local procedure DeleteCustomer(var Rec: Record Customer)
    var
        ZYGLSourceName: Record "ZY GL Source Name";
    begin
        if ZYGLSourceName.Get(ZYGLSourceName."Source Type"::Customer, Rec."No.") then
            ZYGLSourceName.Delete();
    end;

    [EventSubscriber(ObjectType::Table, Database::Customer, 'OnAfterRenameEvent', '', false, false)]
    local procedure RenameCustomer(var Rec: Record Customer; xRec: Record Customer)
    var
        ZYGLSourceName: Record "ZY GL Source Name";
    begin
        if ZYGLSourceName.Get(ZYGLSourceName."Source Type"::Customer, xRec."No.") then begin
            ZYGLSourceName."Source No." := Rec."No.";
            ZYGLSourceName."Source Name" := Rec.Name;
            ZYGLSourceName.Modify();
        end;
    end;
}

3. Create a new field and add a logic to insert customer and vendor name while posting

You can create a new field in G/L Entry (17) table, and add a logic to insert customer name and vendor name into one of the events while posting. Better performance, but if you add this feature after going live, you need to synchronize the history data in G/L Entry.

Add new field.

Display the field on the page.

Insert customer name and vendor name while posting.

codeunit 50100 TransferSourceName
{
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Gen. Jnl.-Post Line", 'OnAfterInitGLEntry', '', false, false)]
    local procedure MyProcedure(var GLEntry: Record "G/L Entry")
    var
        Cust: Record Customer;
        Vend: Record Vendor;
        FA: Record "Fixed Asset";
        BankAccount: Record "Bank Account";
        Employee: Record Employee;
    begin
        Case GLEntry."Source Type" of
            GLEntry."Source Type"::Customer:
                If Cust.GET(GLEntry."Source No.") then
                    GLEntry."ZY Source Name" := Cust.Name;
            GLEntry."Source Type"::Vendor:
                If Vend.GET(GLEntry."Source No.") then
                    GLEntry."ZY Source Name" := Vend.Name;
            GLEntry."Source Type"::"Fixed Asset":
                If FA.GET(GLEntry."Source No.") then
                    GLEntry."ZY Source Name" := FA.Description;
            GLEntry."Source Type"::"Bank Account":
                If BankAccount.GET(GLEntry."Source No.") then
                    GLEntry."ZY Source Name" := BankAccount.Name;
            GLEntry."Source Type"::Employee:
                If Employee.GET(GLEntry."Source No.") then
                    GLEntry."ZY Source Name" := Employee.FullName();
        end;
    end;
}

Test video:

4. Use the free extension in AppSource

In AppSource, there is a free extension called G/L Source Names. (Add the Source Name column to your General Ledger Entries)

Choose Extension Marketplace on the Extension Management page.

Search for G/L Source Names and choose Get it now.

Select the terms and privacy and then choose Continue.

Choose Install.

The installation is complete.

You can see that the Source Name field has been added.

In conclusion, if you are a developer, I recommend the first and third ways. If you are an end-user, I recommend using the fourth way directly.

END

Hope this will help.

Thanks for reading.

ZHU

コメント

タイトルとURLをコピーしました