Table 1251 Text-to-Account Mapping

Base Application, Microsoft.Bank.Reconciliation

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