Codeunit 703 Find Record Management, source in 29
Source29
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;
}