Codeunit 703 Find Record Management

Base Application, Microsoft.Utilities

src/Layers/W1/BaseApp/Utilities/FindRecordManagement.Codeunit.al502 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.Utilities;using Microsoft.Finance.AllocationAccount;using Microsoft.Finance.GeneralLedger.Account;using Microsoft.Finance.GeneralLedger.Ledger;using Microsoft.FixedAssets.FixedAsset;using Microsoft.Inventory.Item;using Microsoft.Projects.Resources.Resource;using System.Reflection;using System.Text;codeunit 703 "Find Record Management"{    Permissions = tabledata "G/L Entry" = r;    trigger OnRun()    begin    end;    var        WrongParameterTypeErr: Label 'Parameter type must be Record or RecordRef.';    procedure FindLastEntryIgnoringSecurityFilter(var RecRef: RecordRef) Found: Boolean;    var        IsHandled: Boolean;        xSecurityFilter: SecurityFilter;    begin        OnBeforeFindLastEntryIgnoringSecurityFilter(RecRef, Found, IsHandled);        if IsHandled then            exit(Found);        xSecurityFilter := RecRef.SecurityFiltering;        RecRef.SecurityFiltering(RecRef.SecurityFiltering::Ignored);        Found := RecRef.FindLast();        if RecRef.SecurityFiltering <> xSecurityFilter then            RecRef.SecurityFiltering(xSecurityFilter)    end;    [Scope('OnPrem')]    procedure GetIntFieldValue(RecRef: RecordRef; FieldNo: Integer): Integer;    var        IntFields: list of [Integer];    begin        IntFields.Add(FieldNo);        GetIntFieldValues(RecRef, IntFields);        exit(IntFields.Get(1));    end;    [Scope('OnPrem')]    procedure GetIntFieldValues(RecRef: RecordRef; var IntFields: list of [Integer])    var        FieldNos: list of [Integer];        FieldNo: Integer;        FieldValue: Variant;    begin        FieldNos := IntFields;        clear(IntFields);        foreach FieldNo in FieldNos do            if IsFieldValid(RecRef, FieldNo, FieldType::Integer, FieldValue) then                IntFields.Add(FieldValue)            else                IntFields.Add(0);    end;    procedure GetLastEntryIntFieldValue(SourceRec: Variant; FieldNo: Integer): Integer;    var        IntFields: list of [Integer];    begin        IntFields.Add(FieldNo);        GetLastEntryIntFieldValues(SourceRec, IntFields);        exit(IntFields.Get(1));    end;    procedure GetLastEntryIntFieldValues(SourceRec: Variant; var FieldNoValues: List of [Integer])    var        RecRef: RecordRef;        FieldNo: Integer;        FirstIteration: Boolean;    begin        ConvertVariantToRecordRef(SourceRec, RecRef);        RecRef.Reset();        FirstIteration := true;        foreach FieldNo in FieldNoValues do            if RecRef.FieldExist(FieldNo) then                if FirstIteration then begin                    RecRef.SetLoadFields(FieldNo);                    FirstIteration := false;                end else                    RecRef.AddLoadFields(FieldNo);        FindLastEntryIgnoringSecurityFilter(RecRef);        GetIntFieldValues(RecRef, FieldNoValues);    end;    local procedure ConvertVariantToRecordRef(SourceRec: Variant; var RecRef: RecordRef)    begin        case true of            SourceRec.IsRecordRef:                RecRef := SourceRec;            SourceRec.IsRecord:                RecRef.GetTable(SourceRec);            else                Error(WrongParameterTypeErr);        end;    end;    local procedure IsFieldValid(RecRef: RecordRef; FieldNo: Integer; ExpectedFieldType: FieldType; var Value: Variant): Boolean    var        FldRef: FieldRef;    begin        Clear(Value);        if RecRef.FieldExist(FieldNo) then begin            FldRef := RecRef.Field(FieldNo);            if FldRef.Type = ExpectedFieldType then begin                if FldRef.Class = FieldClass::FlowField then                    FldRef.CalcField();                Value := FldRef.Value();                exit(true);            end;        end;    end;    procedure FindNoFromTypedValue(Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; Value: Code[20]; UseDefaultTableRelationFilters: Boolean): Code[20]    var        Item: Record Item;        FoundNo: Code[20];    begin        OnBeforeFindNoFromTypedValue(Type, Value, FoundNo);        if FoundNo <> '' then            exit(FoundNo);        if Type = Type::Item then            exit(Item.GetItemNo(Value));        FoundNo := FindNoByDescription(Type, Value, UseDefaultTableRelationFilters);        if FoundNo <> '' then            exit(FoundNo);        exit(Value);    end;    [Scope('OnPrem')]    procedure FindNoByDescription(Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; Description: Text; UseDefaultTableRelationFilters: Boolean): Code[20]    var        GLAccount: Record "G/L Account";        ResultValue: Text;        RecordView: Text;    begin        if UseDefaultTableRelationFilters and (Type = Type::"G/L Account") then            RecordView := GetGLAccountTableRelationView();        if FindRecordByDescriptionAndView(ResultValue, Type, Description, RecordView) = 1 then            exit(CopyStr(ResultValue, 1, MaxStrLen(GLAccount."No.")));        exit('');    end;    procedure FindRecordByDescription(var Result: Text; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; SearchText: Text): Integer    begin        exit(FindRecordByDescriptionAndView(Result, Type, SearchText, ''));    end;    [Scope('OnPrem')]    procedure FindRecordByDescriptionAndView(var Result: Text; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; SearchText: Text; RecordView: Text) RecordsCount: Integer    var        RecRef: RecordRef;        SearchFieldRef: array[4] of FieldRef;        SearchFieldNo: array[4] of Integer;        KeyNoMaxStrLen: Integer;        RecWithoutQuote: Text;        RecFilterFromStart: Text;        RecFilterContains: Text;        MatchCount: Integer;        IsHandled: Boolean;    begin        // Try to find a record by SearchText looking into "No." OR "Description" fields        // SearchFieldNo[1] - "No."/Code        // SearchFieldNo[2] - "Description"/"Name"        // SearchFieldNo[3] - "Base Unit of Measure" (used for items)        Result := '';        if SearchText = '' then            exit(0);        GetRecRefAndFieldsNoByType(RecRef, Type, SearchFieldNo);        RecRef.SetView(RecordView);        SearchFieldRef[1] := RecRef.Field(SearchFieldNo[1]);        SearchFieldRef[2] := RecRef.Field(SearchFieldNo[2]);        if SearchFieldNo[3] <> 0 then            SearchFieldRef[3] := RecRef.Field(SearchFieldNo[3]);        IsHandled := false;        MatchCount := 0;        OnBeforeFindRecordByDescriptionAndView(Result, Type, RecRef, SearchFieldRef, SearchText, RecordView, MatchCount, IsHandled);        if IsHandled then            exit(MatchCount);        // Try GET(SearchText)        KeyNoMaxStrLen := SearchFieldRef[1].Length;        if StrLen(SearchText) <= KeyNoMaxStrLen then begin            SearchFieldRef[1].SetRange(CopyStr(SearchText, 1, KeyNoMaxStrLen));            RecRef.SetLoadFields(SearchFieldRef[1].Number);            if RecRef.FindFirst() then begin                Result := SearchFieldRef[1].Value();                exit(1);            end;        end;        SearchFieldRef[1].SetRange();        ClearLastError();        RecWithoutQuote := ConvertStr(SearchText, '''()&|', '?????');        // Try FINDFIRST "No." by mask "Search string *"        if TrySetFilterOnFieldRef(SearchFieldRef[1], RecWithoutQuote + '*') then begin            RecRef.SetLoadFields(SearchFieldRef[1].Number);            if RecRef.FindFirst() then begin                Result := SearchFieldRef[1].Value();                exit(1);            end;        end;        SearchFieldRef[1].SetRange();        ClearLastError();        // Two items with descrptions = "aaa" and "AAA";        // Try FINDFIRST by exact "Description" = "AAA"        SearchFieldRef[2].SetRange(CopyStr(SearchText, 1, SearchFieldRef[2].Length));        RecRef.SetLoadFields(SearchFieldRef[1].Number);        if RecRef.FindFirst() then begin            Result := SearchFieldRef[1].Value();            exit(1);        end;        SearchFieldRef[2].SetRange();        // Example of SearchText = "Search string ''";        // Try FINDFIRST "Description" by mask "@Search string ?"        SearchFieldRef[2].SetFilter('''@' + RecWithoutQuote + '''');        RecRef.SetLoadFields(SearchFieldRef[1].Number);        if RecRef.FindFirst() then begin            Result := SearchFieldRef[1].Value();            exit(1);        end;        SearchFieldRef[2].SetRange();        // Try FINDSET "No." OR "Description" by mask "@Search string ?*"        RecFilterFromStart := '''@' + RecWithoutQuote + '*''';        if SearchFieldRef[1].Type <> SearchFieldRef[1].Type::Code then begin //already processed with Try FINDFIRST "No." by mask "Search string *"                        SearchFieldRef[1].SetFilter(RecFilterFromStart);            GetRecordsSearchResult(RecRef, SearchFieldRef[1].Number, Result, RecordsCount);            SearchFieldRef[1].SetRange();        end;        SearchFieldRef[2].SetFilter(RecFilterFromStart);        OnBeforeFindRecordStartingWithSearchString(Type, RecRef, RecFilterFromStart);        GetRecordsSearchResult(RecRef, SearchFieldRef[1].Number, Result, RecordsCount);        SearchFieldRef[2].SetRange();        if RecordsCount > 0 then            exit(RecordsCount);        // Try FINDSET "No." OR "Description" OR additional field by mask "@*Search string ?*"        RecFilterContains := '''@*' + RecWithoutQuote + '*''';        RecRef.FilterGroup := -1;        SearchFieldRef[1].SetFilter(RecFilterContains);        SearchFieldRef[2].SetFilter(RecFilterContains);        if SearchFieldNo[3] <> 0 then            SearchFieldRef[3].SetFilter(RecFilterContains);        OnBeforeFindRecordContainingSearchString(Type, RecRef, RecFilterContains);        GetRecordsSearchResult(RecRef, SearchFieldRef[1].Number, Result, RecordsCount);        if RecordsCount > 0 then            exit(RecordsCount);        // Try FINDLAST record with similar "Description"        IsHandled := false;        OnFindRecordByDescriptionAndViewOnBeforeFindRecordWithSimilarName(RecRef, SearchText, SearchFieldNo, IsHandled);        if not IsHandled then begin            RecRef.SetLoadFields(SearchFieldRef[1].Number);            if FindRecordWithSimilarName(RecRef, SearchText, SearchFieldNo[2]) then begin                Result := SearchFieldRef[1].Value();                exit(1);            end;        end;        // Try find for extension        MatchCount := 0;        OnAfterFindRecordByDescriptionAndView(Result, Type, RecRef, SearchFieldRef, SearchFieldNo, SearchText, MatchCount);        if MatchCount <> 0 then            exit(MatchCount);        // Not found        exit(0);    end;    local procedure GetRecordsSearchResult(var RecRef: RecordRef; SearchFieldOneId: Integer; var Result: Text; var RecordsCount: Integer)    begin        RecRef.SetLoadFields(SearchFieldOneId);        if not RecRef.IsEmpty() then            UpdateResultFilter(Result, RecordsCount, RecRef, SearchFieldOneId);    end;    local procedure UpdateResultFilter(var Result: Text; var RecordsCount: Integer; var RecRef: RecordRef; SelectionFieldID: Integer)    var        SelectionFilterManagement: Codeunit SelectionFilterManagement;        FieldRef: FieldRef;        TextBuilder: TextBuilder;    begin        Result := DelChr(Result, '<>', '|');        if RecRef.FindSet() then            repeat                FieldRef := RecRef.Field(SelectionFieldID);                TextBuilder.Append('|' + SelectionFilterManagement.AddQuotes(Format(FieldRef.Value)));                RecordsCount += 1;            until ((RecRef.Next() = 0) or (RecordsCount > GetMaxRecordCountToReturn()));        if RecordsCount = 1 then begin //to avoid adding single quotes            Result := Format(FieldRef.Value);            exit;        end;        Result += TextBuilder.ToText();        Result := DelChr(Result, '<>', '|');    end;    internal procedure GetMaxRecordCountToReturn() Result: Integer    begin        Result := 30;        OnGetMaxRecordCountToReturnOnBeforeExit(Result);        exit(Result);    end;    procedure FindRecordWithSimilarName(RecRef: RecordRef; SearchText: Text; DescriptionFieldNo: Integer): Boolean    var        TypeHelper: Codeunit "Type Helper";        Description: Text;        RecCount: Integer;        TextLength: Integer;        Treshold: Integer;    begin        if SearchText = '' then            exit(false);        TextLength := StrLen(SearchText);        if TextLength > RecRef.Field(DescriptionFieldNo).Length then            exit(false);        Treshold := TextLength div 5;        if Treshold = 0 then            exit(false);        RecRef.Reset();        RecRef."SecurityFiltering" := SECURITYFILTER::Filtered;        RecRef.Ascending(false); // most likely to search for newest records        RecRef.AddLoadFields(DescriptionFieldNo);        if RecRef.FindSet() then            repeat                RecCount += 1;                Description := RecRef.Field(DescriptionFieldNo).Value();                if Abs(TextLength - StrLen(Description)) <= Treshold then                    if TypeHelper.TextDistance(UpperCase(SearchText), UpperCase(Description)) <= Treshold then                        exit(true);            until (RecRef.Next() = 0) or (RecCount > 1000);        exit(false);    end;    local procedure GetRecRefAndFieldsNoByType(RecRef: RecordRef; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var SearchFieldNo: array[4] of Integer)    var        GLAccount: Record "G/L Account";        Item: Record Item;        FixedAsset: Record "Fixed Asset";        Resource: Record Resource;        ItemCharge: Record "Item Charge";        StandardText: Record "Standard Text";        AllocationAccount: Record "Allocation Account";    begin        OnBeforeGetRecRefAndFieldsNoByType(RecRef, Type, SearchFieldNo);        case Type of            Type::"G/L Account":                begin                    RecRef.Open(DATABASE::"G/L Account");                    SearchFieldNo[1] := GLAccount.FieldNo("No.");                    SearchFieldNo[2] := GLAccount.FieldNo(Name);                    SearchFieldNo[3] := 0;                end;            Type::Item:                begin                    RecRef.Open(DATABASE::Item);                    SearchFieldNo[1] := Item.FieldNo("No.");                    SearchFieldNo[2] := Item.FieldNo(Description);                    SearchFieldNo[3] := Item.FieldNo("Base Unit of Measure");                end;            Type::Resource:                begin                    RecRef.Open(DATABASE::Resource);                    SearchFieldNo[1] := Resource.FieldNo("No.");                    SearchFieldNo[2] := Resource.FieldNo(Name);                    SearchFieldNo[3] := 0;                end;            Type::"Fixed Asset":                begin                    RecRef.Open(DATABASE::"Fixed Asset");                    SearchFieldNo[1] := FixedAsset.FieldNo("No.");                    SearchFieldNo[2] := FixedAsset.FieldNo(Description);                    SearchFieldNo[3] := 0;                end;            Type::"Charge (Item)":                begin                    RecRef.Open(DATABASE::"Item Charge");                    SearchFieldNo[1] := ItemCharge.FieldNo("No.");                    SearchFieldNo[2] := ItemCharge.FieldNo(Description);                    SearchFieldNo[3] := 0;                end;            Type::" ":                begin                    RecRef.Open(DATABASE::"Standard Text");                    SearchFieldNo[1] := StandardText.FieldNo(Code);                    SearchFieldNo[2] := StandardText.FieldNo(Description);                    SearchFieldNo[3] := 0;                end;            Type::"Allocation Account":                begin                    RecRef.Open(DATABASE::"Allocation Account");                    SearchFieldNo[1] := AllocationAccount.FieldNo("No.");                    SearchFieldNo[2] := AllocationAccount.FieldNo(Name);                    SearchFieldNo[3] := 0;                end;        end;        OnAfterGetRecRefAndFieldsNoByType(RecRef, Type, SearchFieldNo);    end;    local procedure GetGLAccountTableRelationView(): Text    var        GLAccount: Record "G/L Account";    begin        GLAccount.SetRange("Direct Posting", true);        GLAccount.SetRange("Account Type", GLAccount."Account Type"::Posting);        GLAccount.SetRange(Blocked, false);        exit(GLAccount.GetView(false));    end;    [TryFunction]    local procedure TrySetFilterOnFieldRef(var FieldRef: FieldRef; "Filter": Text)    begin        FieldRef.SetFilter(Filter);    end;    [IntegrationEvent(false, false)]    local procedure OnAfterFindRecordByDescriptionAndView(var Result: Text; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var RecRef: RecordRef; SearchFieldRef: array[4] of FieldRef; SearchFieldNo: array[4] of Integer; SearchText: Text; var MatchCount: Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnAfterGetRecRefAndFieldsNoByType(RecRef: RecordRef; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var SearchFieldNo: array[4] of Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeFindLastEntryIgnoringSecurityFilter(var RecRef: RecordRef; var Found: Boolean; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeFindNoFromTypedValue(var Type: Option; var Value: Code[20]; var FoundNo: Code[20])    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeFindRecordByDescriptionAndView(var Result: Text; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var RecRef: RecordRef; SearchFieldRef: array[4] of FieldRef; SearchText: Text; RecordView: Text; var MatchCount: Integer; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeFindRecordContainingSearchString(Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var RecRef: RecordRef; RecFilterFromStart: Text)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeFindRecordStartingWithSearchString(Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var RecRef: RecordRef; RecFilterFromStart: Text)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeGetRecRefAndFieldsNoByType(RecRef: RecordRef; Type: Option " ","G/L Account",Item,Resource,"Fixed Asset","Charge (Item)",,,,,"Allocation Account"; var SearchFieldNo: array[4] of Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnGetMaxRecordCountToReturnOnBeforeExit(var Result: Integer)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnFindRecordByDescriptionAndViewOnBeforeFindRecordWithSimilarName(RecRef: RecordRef; var SearchText: Text; var SearchFieldNo: array[4] of Integer; var IsHandled: Boolean)    begin    end;}