web
You’re offline. This is a read only version of the page.
close
Skip to main content
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 Community member 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
I have the same question (0)
  • Suggested answer
    Ariba Mehdi Profile Picture
    Ariba Mehdi 164 on at
    Seeking guidance on filtering for temporary table
    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!
  • Community member Profile Picture
    Community member 6 on at
    Seeking guidance on filtering for temporary table
    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;
  • Community member Profile Picture
    Community member 6 on at
    Seeking guidance on filtering for temporary table
    This is fixed. Thank you 
     
     
  • Teddy Herryanto (That NAV Guy) Profile Picture
    Teddy Herryanto (Th... 14,261 Super User 2025 Season 2 on at
    Seeking guidance on filtering for temporary table
    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,261 Super User 2025 Season 2 on at
    Seeking guidance on filtering for temporary table
    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.
  • Community member Profile Picture
    Community member 2 on at
    Seeking guidance on filtering for temporary table
    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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

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

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,901

#2
YUN ZHU Profile Picture

YUN ZHU 2,079 Super User 2025 Season 2

#3
Sumit Singh Profile Picture

Sumit Singh 2,041

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans