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
コメント