web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Seeking guidance on fi...
Small and medium business | Business Central, N...
Suggested Answer

Seeking guidance on filtering for temporary table

(1) ShareShare
ReportReport
Posted on by CU27040908-7 6
I am currently working on a requirement that involves the creation of Drop Ship Purchase Orders from Sales Orders. The process involves generating new Purchase Orders based on a unique combination of Style, Color, and Construction code. For instance, Lines 1 and 3 will be consolidated onto a single purchase order as all three fields—Style, Color, and Construction code—match. However, Line 2 will be allocated to a different purchase order as it differs in at least one of these fields.
 
Line 1: Item No: 10000 - Style Name: Shirt - Color Name: Black - Construction code: Ply
Line 2: Item No: 10001 - Style Name: Shirt - Color Name: Black - Construction code: ''
Line 3: Item No: 10000 - Style Name: Shirt - Color Name: Black - Construction code: Ply

To address this, I've implemented a condition named /SalesLineCombinationExist/ to verify and filter records based on the style name, color name, and construction code. If a matching record is found, the system will exit with a result of 'false.' Conversely, if no matching values are detected, the system will exit with a result of 'true' and insert the information into a temporary folder for further processing.

The issue with the code below is that despite variations among these lines, they all pass through the 'else' condition, resulting in the addition of values to the temporary table. Could you explain why duplicate values aren't being identified within the 'if TempSalesLine.FindFirst()' section? If this approach is incorrect, could you guide me on the correct methodology?

 
 
 local procedure SalesLineCombinationExist(SalesLine: Record /Sales Line/): Boolean
begin
  TempSalesLine.SetRange(/DDISCH Style Name/, SalesLine./DDISCH Style Name/);
        TempSalesLine.SetRange(/DDISCH Color Name/, SalesLine./DDISCH Color Name/);
         TempSalesLine.SetRange(/DDISCH Construction Code/, SalesLine./DDISCH Construction Code/);
        TempSalesLine.Reset();
        if TempSalesLine.FindFirst() then
            exit(false)
        else begin
            // Combination doesn't exist, insert into TempSalesLine table
            TempSalesLine.Init();
            TempSalesLine./Document Type/ := SalesLine./Document Type/;
            TempSalesLine./Document No./ := SalesLine./No./;
            TempSalesLine./Line No./ := SalesLine./Line No./;
            TempSalesLine./DDISCH Style Name/ := SalesLine./DDISCH Style Name/;
            TempSalesLine./DDISCH Color Name/ := SalesLine./DDISCH Color Name/;
            TempSalesLine./DDISCH Construction Code/ := SalesLine./DDISCH Construction Code/;
            TempSalesLine.Insert(true);
            exit(true);
        end;
end;
 
//global variable
var
 TempSalesLine: Record /Sales Line/ temporary; 
Categories:
Dynamics 365 Business Central
Dynamics 365 Business Central
  • Suggested answer
    Ariba Mehdi Profile Picture
    Ariba Mehdi 166 on at
    Hi there,

    Remove the line TempSalesLine.Reset();
    I believe, the reset method is clearing out all the ranges you have set.

    And if it still doesn't work, change if TempSalesLine.FindFirst() then to if TempSalesLine.FindSet() then.


    Hope this helps!
  • CU27040908-7 Profile Picture
    CU27040908-7 6 on at
    Thank you! I made it work for that and all other scenarios with both TempSalesLine.FindFirst() then to if TempSalesLine.FindSet() then.
     
    However, it still throws me an error when I enter more lines as below scenario. 
     
    Line 1: Item No: 10000 - Style Name: Shirt - Color Name: Black - Construction code: Ply
    Line 2: Item No: 10001 - Style Name: Shirt - Color Name: Black - Construction code: ''
    Line 3: Item No: 10002 - Style Name: Shirt - Color Name: Black - Construction code: ''
    Line 4: Item No: 10000 - Style Name: Shirt - Color Name: Black - Construction code: Ply
    Line 5: Item No: 10002 - Style Name: Shirt - Color Name: Black - Construction code: ''
    Line 6: Item No: 10001 - Style Name: Shirt - Color Name: Black - Construction code: Ply
     
    Error: There were no lines to be retrieved from sales order [SO No.]
     
    Also: We're using "Purch.-Get Drop Shpt." codeunit with "Code" procedure to sort the lines that match together.  
     
       //Procedure = SalesLineCombinationExist      
     
    TempSalesLine.SetFilter("No.", SalesLine."No.");
            TempSalesLine.SetFilter("SCH Style Name", SalesLine."SCH Style Name");
            TempSalesLine.SetFilter("SCH Color Name", SalesLine."SCH Color Name");
            TempSalesLine.SetFilter("SCH Construction Code", SalesLine."SCH Construction Code");
     
            if not TempSalesLine.FindSet() then
                repeat
                    TempSalesLine.Init();
                    TempSalesLine."No." := SalesLine."No.";
                    TempSalesLine."Document Type" := SalesLine."Document Type";
                    TempSalesLine."Document No." := SalesLine."No.";
                    TempSalesLine."SCH Style Name" := SalesLine."SCH Style Name";
                    TempSalesLine."SCH Color Name" := SalesLine."SCH Color Name";
                    TempSalesLine."SCH Construction Code" := SalesLine."SCH Construction Code";
                    TempSalesLine.Insert();
                    exit(true);
                until (TempSalesLine.Next() = 0)
            else
                exit(false)
     
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
      //Procedure that calls SalesLineCombinationExist    

    SalesLine: Record "Sales Line";
            PurchHeader: Record "Purchase Header";
            CreateDropShipPO: Codeunit "Purch.-Get Drop Shpt.";
     
     
            SalesLine.SetCurrentKey("SCH Style Name");
            SalesLine.SetRange("Document Type", SalesHeader."Document Type");
           SalesLine.SetRange("Document No.", SalesHeader."No.");
            SalesLine.SetFilter("SCH Style Name", '<>%1', '');
            while SalesLine.Next() <> 0 do
                if SalesLineCombinationExist(SalesLine) then begin 
                    PurchHeader := CreatePurchHeader(SalesLine, SalesHeader);
                    Clear(CreateDropShipPO);
                    Run(PurchHeader);
                    SCH_UpdateNewDropShipPurchLine(PurchHeader, SalesLine);
                end;
  • CU27040908-7 Profile Picture
    CU27040908-7 6 on at
    This is fixed. Thank you 
     
     
  • Teddy Herryanto (That NAV Guy) Profile Picture
    Teddy Herryanto (Th... 14,306 Super User 2026 Season 1 on at
    is TempSalesLine a global variable?
    it is hard to judge without seeing the whole picture.
  • Suggested answer
    Teddy Herryanto (That NAV Guy) Profile Picture
    Teddy Herryanto (Th... 14,306 Super User 2026 Season 1 on at
    I would suggest to use Query instead of Temporary Sales Line to find distinct value.
     
    You can refer to below link on how to use Query. It will be cleaner and faster with Query.
  • CU27040907-0 Profile Picture
    CU27040907-0 2 on at
    I appreciate all for answering, you made my day. I am seeking guidance on filtering for temporary tables and I am glad I found your post where I found my answer. When I was searching for it on a Google search, I also found https://www.grabmyessay.com/making-personal-statement website link where I found an essay writer who will help me make a personal statement essay assignment for me. I am not so good at writing an essay assignment and that is why I took interest in that website link.

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 715 Super User 2026 Season 2

#2
AndrewThomas81 Profile Picture

AndrewThomas81 390 Super User 2026 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 386 Super User 2026 Season 2

Last 30 days Overall leaderboard

Featured topics

Microsoft Training Manuals

Product updates

Dynamics 365 release plans