Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Unanswered

Avoid duplicates in temporary table

(0) ShareShare
ReportReport
Posted on by 47
Hey,
 
I have a temporary table which is populated inside an IF condition in the OnOpenPage() trigger on my "Purchase Order Filtered" page. 
I'm getting some duplicates as in the query from which I get the data, a PO may be repeated many times, I can't avoid this, So I'm getting an error as the No. and Line No. are the keys.
I've been trying to find a way to see if the record already exists in the temporary table before doing the rec.insert(), I tried using the rValidate record and the SetRange() and IsEmpty() methods, but they don't find the already inserted record, so I'll appreciate your help figuring out how this should work.
 
 
table 60101 PurchaseOrderFilteredTemp
{
    DataClassification = ToBeClassified;
    TableType = Temporary;
    ColumnStoreIndex = "No.", "Line No.";
 
    fields
    {
        field(1; "Document Type"; Enum "Purchase Document Type")
        {
            DataClassification = ToBeClassified;
        }
        field(2; "No."; Code[20])
        {
            DataClassification = ToBeClassified;
            TableRelation = "Purchase Header";
        }
        field(3; "Line No."; Integer)
        {
            DataClassification = ToBeClassified;
        }
        field(4; Entity; Code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(5; Department; Code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(6; "Project Code"; Code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(7; "Project Name"; Text[50])
        {
            DataClassification = ToBeClassified;
        }
        field(8; "G/L Account"; Code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(9; "Approver User ID"; Code[50])
        {
            DataClassification = ToBeClassified;
        }
 
    }
 
    keys
    {
        key(PK; "No.", "Line No.")
        {
            Clustered = true;
        }
    }
}
 
 
 
page 60101 "Purchase Order Filtered"
{
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Lists;
    SourceTable = PurchaseOrderFilteredTemp;
    SourceTableTemporary = true;
 
    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field("No."; rec."No.")
                {
                    ApplicationArea = All;
                    Importance = Standard;
 
                    trigger OnDrillDown()
                    var
                        PurchaseOrderListPage: Page "Purchase Order";
                        PurchaseOrderRecord: Record "Purchase Header";
                    begin
                        PurchaseOrderRecord.SetRange(PurchaseOrderRecord."No.", rec."No.");
                        PurchaseOrderListPage.SetTableView(PurchaseOrderRecord);
                        PurchaseOrderListPage.Editable(false);
                        PurchaseOrderListPage.Run();
                    end;
 
                }
                field("Line No."; rec."Line No.")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
                field("Entity"; rec."Entity")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
                field("Department"; rec."Department")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
                field("Project Code"; rec."Project Code")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
                field("Project Name"; rec."Project Name")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
                field("G/L Account"; rec."G/L Account")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
                field("ApproverUserID"; rec."Approver User ID")
                {
                    ApplicationArea = All;
                    Importance = Standard;
                }
            }
        }
    }
 
    trigger OnOpenPage()
    var
        qListToFilter: Query "Purch.Order and CriteriaFilter";
        glaccount: Enum "Purchase Line Type";
        rValidate: Record PurchaseOrderFilteredTemp temporary;
 
    begin
 
        glaccount := glaccount::"G/L Account";
 
        if qListToFilter.Open() then begin
            while qListToFilter.Read() do begin
                if (searchCriteria(qListToFilter.LineProjectCode, qListToFilter.Project))
                        or (searchCriteria(qListToFilter.LineTypeNo, qListToFilter.GLAccount))
 
                then begin
 
                    rValidate.Reset();
                    rValidate.SetRange("No.", qListToFilter.No);
                    rValidate.SetRange("Line No.", qListToFilter.LineNo);
 
                    if rvalidate.IsEmpty then begin
                        rec.Init();
                        rec."Document Type" := qListToFilter.DocumentType;
                        rec."No." := qListToFilter.No;
                        rec."Line No." := qListToFilter.LineNo;
                        rec.Entity := qListToFilter.LineEntityCode;
                        rec.Department := qListToFilter.LineDepartmentCode;
                        rec."Project Code" := qListToFilter.LineProjectCode;
                        rec."Project Name" := qListToFilter.LineProjectName;
                        rec."G/L Account" := qListToFilter.LineTypeNo;
                        rec."Approver User ID" := qListToFilter.ApproverUserID;
 
                        rec.Insert();
                    end;
 
                end;
            end;
        end;
        qListToFilter.Close();
    end;

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,661 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,379 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans