Table 1251 Text-to-Account Mapping, source in 29
Source29
src/Layers/W1/BaseApp/Bank/Reconciliation/TexttoAccountMapping.Table.al407 lines, Copyright (c) Microsoft Corporation. MIT
// ------------------------------------------------------------------------------------------------
// Copyright (c) Microsoft Corporation. All rights reserved.
// Licensed under the MIT License. See License.txt in the project root for license information.
// ------------------------------------------------------------------------------------------------
namespace Microsoft.Bank.Reconciliation;
using Microsoft.Bank.BankAccount;
using Microsoft.Finance.GeneralLedger.Account;
using Microsoft.Finance.GeneralLedger.Journal;
using Microsoft.Purchases.Vendor;
using Microsoft.Sales.Customer;
/// <summary>
/// Defines automatic mapping rules that match bank statement transaction text to specific accounts.
/// This table enables automatic posting of recurring transactions by recognizing text patterns
/// in bank statement descriptions and applying them to predefined general ledger accounts,
/// customers, vendors, or bank accounts. Provides high-confidence automatic matching for
/// routine transactions such as utilities, rent, salaries, and other predictable payments.
/// </summary>
/// <remarks>
/// Key features include text pattern matching, automatic debit/credit account assignment,
/// balancing source account configuration, vendor-specific mappings, and integration with
/// payment application workflows. Rules support exact text matching and enable automatic
/// posting of transactions that would otherwise require manual intervention.
/// Commonly used for bank charges, recurring payments, payroll transactions, and vendor payments.
/// </remarks>
table 1251 "Text-to-Account Mapping"
{
Caption = 'Text-to-Account Mapping';
DataCaptionFields = "Mapping Text";
DataClassification = CustomerContent;
fields
{
/// <summary>
/// Sequential line number for the text-to-account mapping rule.
/// Provides unique identification and ordering of mapping rules.
/// </summary>
field(1; "Line No."; Integer)
{
Caption = 'Line No.';
}
/// <summary>
/// Text pattern to match against bank statement transaction descriptions.
/// When this text is found in transaction descriptions, the mapping rule is applied automatically.
/// </summary>
field(2; "Mapping Text"; Text[250])
{
Caption = 'Mapping Text';
ToolTip = 'Specifies the text on the payment that is used to map the payment to a customer, vendor, or general ledger account when you choose the Apply Automatically function in the Payment Reconciliation Journal window.';
NotBlank = true;
trigger OnValidate()
begin
"Mapping Text" := CopyStr(RecordMatchMgt.Trim("Mapping Text"), 1, 250);
end;
}
/// <summary>
/// General ledger account to be debited when this mapping rule is applied.
/// Must be a posting account that allows direct posting for transaction processing.
/// </summary>
field(3; "Debit Acc. No."; Code[20])
{
Caption = 'Debit Acc. No.';
ToolTip = 'Specifies the debit account that payments with this text-to-account mapping are matched with when you choose the Apply Automatically function in the Payment Reconciliation Journal window.';
TableRelation = "G/L Account" where("Account Type" = const(Posting),
Blocked = const(false),
"Direct Posting" = const(true));
}
/// <summary>
/// General ledger account to be credited when this mapping rule is applied.
/// Must be a posting account that allows direct posting for transaction processing.
/// </summary>
field(4; "Credit Acc. No."; Code[20])
{
Caption = 'Credit Acc. No.';
ToolTip = 'Specifies the credit account that payments with this text-to-account mapping are applied to when you choose the Apply Automatically function in the Payment Reconciliation Journal window.';
TableRelation = "G/L Account" where("Account Type" = const(Posting),
Blocked = const(false),
"Direct Posting" = const(true));
}
/// <summary>
/// Type of balancing source account for the automatic posting.
/// Determines the account type (G/L Account, Customer, Vendor, Bank Account) for balancing entries.
/// </summary>
field(5; "Bal. Source Type"; Option)
{
Caption = 'Bal. Source Type';
ToolTip = 'Specifies the type of balancing account that amounts on payments or incoming documents that have this text to account mapping are posted to. The Bank Account option is used only for incoming documents and cannot be used in payment reconciliation journals.';
OptionCaption = 'G/L Account,Customer,Vendor,Bank Account';
OptionMembers = "G/L Account",Customer,Vendor,"Bank Account";
trigger OnValidate()
begin
Validate("Bal. Source No.", '');
end;
}
/// <summary>
/// Account number for the balancing source account.
/// Specifies the specific account within the chosen source type for balancing entries.
/// </summary>
field(6; "Bal. Source No."; Code[20])
{
Caption = 'Bal. Source No.';
ToolTip = 'Specifies the balancing account to post amounts on payments or incoming documents that have this text to account mapping. The Bank Account option in the Bal. Source Type cannot be used in payment reconciliation journals.';
TableRelation = if ("Bal. Source Type" = const("G/L Account")) "G/L Account" where("Account Type" = const(Posting),
Blocked = const(false))
else
if ("Bal. Source Type" = const(Customer)) Customer
else
if ("Bal. Source Type" = const(Vendor)) Vendor
else
if ("Bal. Source Type" = const("Bank Account")) "Bank Account";
}
/// <summary>
/// Specific vendor number for vendor-related text mappings.
/// Used to create vendor-specific mapping rules that apply only to transactions from this vendor.
/// </summary>
field(7; "Vendor No."; Code[20])
{
Caption = 'Vendor No.';
ToolTip = 'Specifies the number of the vendor that incoming documents containing the mapping text will be created for, or that payments will be posted to.';
TableRelation = Vendor;
}
}
keys
{
key(Key1; "Line No.")
{
Clustered = true;
}
key(Key2; "Mapping Text", "Vendor No.")
{
Enabled = false;
}
}
fieldgroups
{
}
trigger OnInsert()
begin
CheckMappingText();
end;
trigger OnModify()
begin
CheckMappingText();
end;
var
RecordMatchMgt: Codeunit "Record Match Mgt.";
AlreadyExistErr: Label 'Entry with Mapping Text=''%1'' already exists.', Comment = '%1 is the value currently inserted.';
BalAccountNoQst: Label 'The Bal. Account No. field must have a value if the Bal. Source Type field contains %1.\\Affected Mapping Text: %2. Do you want to quit without saving the data?', Comment = '%1 is option: Vendor or Customer and %2 is the record value in this field.';
GLAccountNoQst: Label 'The Debit Acc. No. field or the Credit Acc. No. field must have a value if the Bal. Source Type field contains %1.\\Affected Mapping Text: %2. Do you want to quit without saving the data?', Comment = '%1 is option: G/L Account and %2 is the record value in this field.';
FilterInvalidCharTxt: Label '(&)', Locked = true;
procedure InsertRec(GenJnlLine: Record "Gen. Journal Line")
var
TextToAccMapping: Record "Text-to-Account Mapping";
LastLineNo: Integer;
begin
if RecordMatchMgt.Trim(GenJnlLine.Description) <> '' then begin
TextToAccMapping.SetFilter("Mapping Text", '%1', '@' + RecordMatchMgt.Trim(GenJnlLine.Description));
if TextToAccMapping.FindFirst() then
Copy(TextToAccMapping)
else begin
TextToAccMapping.Reset();
if TextToAccMapping.FindLast() then
LastLineNo := TextToAccMapping."Line No.";
Init();
"Line No." := LastLineNo + 10000;
Validate("Mapping Text", GenJnlLine.Description);
SetBalSourceType(GenJnlLine);
if "Bal. Source Type" <> "Bal. Source Type"::"G/L Account" then
"Bal. Source No." := GenJnlLine."Account No."
else begin
"Debit Acc. No." := GenJnlLine."Account No.";
"Credit Acc. No." := GenJnlLine."Account No.";
end;
if "Mapping Text" <> '' then
Insert();
end;
Reset();
end;
PAGE.Run(PAGE::"Text-to-Account Mapping", Rec);
end;
procedure InsertRecFromBankAccReconciliationLine(BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line")
var
TextToAccMapping: Record "Text-to-Account Mapping";
LastLineNo: Integer;
begin
if RecordMatchMgt.Trim(BankAccReconciliationLine."Transaction Text") <> '' then begin
TextToAccMapping.SetFilter("Mapping Text", '%1', '@' + RecordMatchMgt.Trim(BankAccReconciliationLine."Transaction Text"));
if TextToAccMapping.FindFirst() then
Copy(TextToAccMapping)
else begin
TextToAccMapping.Reset();
if TextToAccMapping.FindLast() then
LastLineNo := TextToAccMapping."Line No.";
Init();
"Line No." := LastLineNo + 10000;
Validate("Mapping Text", BankAccReconciliationLine."Transaction Text");
SetSourceTypeFromReconcLine(BankAccReconciliationLine);
case "Bal. Source Type" of
"Bal. Source Type"::Customer,
"Bal. Source Type"::Vendor:
"Bal. Source No." := BankAccReconciliationLine."Account No.";
"Bal. Source Type"::"G/L Account":
begin
"Debit Acc. No." := BankAccReconciliationLine."Account No.";
"Credit Acc. No." := BankAccReconciliationLine."Account No.";
end;
end;
OnInsertRecFromBankAccReconciliationLineOnBeforeInsert(Rec, BankAccReconciliationLine);
if "Mapping Text" <> '' then
Insert();
end;
Reset();
Commit();
end;
PAGE.RunModal(PAGE::"Text-to-Account Mapping", Rec);
end;
procedure GetAccountNo(Amount: Decimal): Code[20]
begin
if Amount >= 0 then
exit("Debit Acc. No.");
exit("Credit Acc. No.");
end;
procedure GetPaymentDocType(var PaymentDocType: Option; ActualSourceType: Option; Amount: Decimal)
var
GenJournalLine: Record "Gen. Journal Line";
ExpectedSourceType: Option;
begin
if Amount >= 0 then
ExpectedSourceType := "Bal. Source Type"::Vendor
else
ExpectedSourceType := "Bal. Source Type"::Customer;
if ExpectedSourceType = ActualSourceType then
PaymentDocType := GenJournalLine."Document Type"::Payment.AsInteger()
else
PaymentDocType := GenJournalLine."Document Type"::Refund.AsInteger();
end;
procedure GetDocTypeForPmt(var DocType: Option; PaymentDocType: Option)
var
GenJournalLine: Record "Gen. Journal Line";
begin
case PaymentDocType of
GenJournalLine."Document Type"::Payment.AsInteger():
DocType := GenJournalLine."Document Type"::Invoice.AsInteger();
GenJournalLine."Document Type"::Refund.AsInteger():
DocType := GenJournalLine."Document Type"::"Credit Memo".AsInteger();
end;
end;
local procedure SetBalSourceType(GenJournalLine: Record "Gen. Journal Line")
begin
case GenJournalLine."Account Type" of
GenJournalLine."Account Type"::Customer:
"Bal. Source Type" := "Bal. Source Type"::Customer;
GenJournalLine."Account Type"::Vendor:
"Bal. Source Type" := "Bal. Source Type"::Vendor;
end;
end;
local procedure SetSourceTypeFromReconcLine(BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line")
begin
case BankAccReconciliationLine."Account Type" of
BankAccReconciliationLine."Account Type"::Customer:
"Bal. Source Type" := "Bal. Source Type"::Customer;
BankAccReconciliationLine."Account Type"::Vendor:
"Bal. Source Type" := "Bal. Source Type"::Vendor;
BankAccReconciliationLine."Account Type"::"Bank Account":
"Bal. Source Type" := "Bal. Source Type"::"Bank Account";
end;
end;
procedure IsBalSourceNoEnabled(): Boolean
begin
exit(not ("Bal. Source Type" in ["Bal. Source Type"::"G/L Account", "Bal. Source Type"::"Bank Account"]));
end;
local procedure CheckMappingText()
var
TextToAccMapping: Record "Text-to-Account Mapping";
begin
TextToAccMapping.SetFilter("Mapping Text", '%1', '@' + "Mapping Text");
TextToAccMapping.SetRange("Vendor No.", "Vendor No.");
TextToAccMapping.SetFilter("Line No.", '<>%1', "Line No.");
if not TextToAccMapping.IsEmpty() then
Error(AlreadyExistErr, "Mapping Text");
end;
procedure CheckEntriesAreConsistent(): Boolean
var
TextToAccMapping: Record "Text-to-Account Mapping";
begin
TextToAccMapping.SetFilter("Bal. Source Type", '%1|%2', TextToAccMapping."Bal. Source Type"::Vendor, TextToAccMapping."Bal. Source Type"::Customer);
TextToAccMapping.SetRange("Bal. Source No.", '');
if TextToAccMapping.FindFirst() then begin
if DIALOG.Confirm(BalAccountNoQst, true, TextToAccMapping."Bal. Source Type", TextToAccMapping."Mapping Text")
then begin
TextToAccMapping.DeleteAll(true);
exit(true);
end;
exit(false);
end;
TextToAccMapping.SetRange("Bal. Source Type", TextToAccMapping."Bal. Source Type"::"G/L Account");
TextToAccMapping.SetRange("Debit Acc. No.", '');
TextToAccMapping.SetRange("Credit Acc. No.", '');
if TextToAccMapping.FindFirst() then begin
if DIALOG.Confirm(GLAccountNoQst, true, TextToAccMapping."Bal. Source Type"::"G/L Account", TextToAccMapping."Mapping Text")
then begin
TextToAccMapping.DeleteAll(true);
exit(true);
end;
exit(false);
end;
TextToAccMapping.SetRange("Bal. Source Type", TextToAccMapping."Bal. Source Type"::"Bank Account");
TextToAccMapping.SetRange("Debit Acc. No.", '');
TextToAccMapping.SetRange("Credit Acc. No.", '');
if TextToAccMapping.FindFirst() then begin
if DIALOG.Confirm(GLAccountNoQst, true, TextToAccMapping."Bal. Source Type"::"Bank Account", TextToAccMapping."Mapping Text")
then begin
TextToAccMapping.DeleteAll(true);
exit(true);
end;
exit(false);
end;
// Exit normally
exit(true)
end;
procedure SearchEnteriesInText(var TextToAccountMapping: Record "Text-to-Account Mapping"; LineDescription: Text; VendorNo: Code[20]): Integer
var
TempTextToAccountMapping: Record "Text-to-Account Mapping" temporary;
TempDefaultTextToAccountMapping: Record "Text-to-Account Mapping" temporary;
ResultCount: Integer;
begin
if SearchExactMapping(TextToAccountMapping, LineDescription, VendorNo) then
exit(1);
TextToAccountMapping.Reset();
TextToAccountMapping.SetRange("Vendor No.", VendorNo);
if not TextToAccountMapping.FindSet() then
exit(0);
repeat
if TextToAccountMapping."Mapping Text" = '' then // Default mapping
TempDefaultTextToAccountMapping.Copy(TextToAccountMapping)
else
if StrPos(UpperCase(LineDescription), UpperCase(TextToAccountMapping."Mapping Text")) > 0 then begin
TempTextToAccountMapping.Copy(TextToAccountMapping);
TempTextToAccountMapping.Insert();
end;
until TextToAccountMapping.Next() = 0;
ResultCount := TempTextToAccountMapping.Count();
if ResultCount = 0 then
if TempDefaultTextToAccountMapping."Line No." <> 0 then begin
TextToAccountMapping.Copy(TempDefaultTextToAccountMapping);
exit(1);
end;
if ResultCount <> 1 then
exit(ResultCount);
TempTextToAccountMapping.FindFirst();
TextToAccountMapping.Copy(TempTextToAccountMapping);
exit(ResultCount);
end;
local procedure SearchExactMapping(var TextToAccountMapping: Record "Text-to-Account Mapping"; LineDescription: Text; VendorNo: Code[20]): Boolean
begin
TextToAccountMapping.Reset();
TextToAccountMapping.SetRange("Vendor No.", VendorNo);
TextToAccountMapping.SetFilter("Mapping Text", '%1', '@' + DelChr(LineDescription, '=', FilterInvalidCharTxt));
exit(TextToAccountMapping.FindFirst());
end;
[IntegrationEvent(false, false)]
local procedure OnInsertRecFromBankAccReconciliationLineOnBeforeInsert(var TextToAccountMapping: Record "Text-to-Account Mapping"; BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line")
begin
end;
}