mibuso.com
Home› NAV Three Tier
Options

Iterating over record set twice

markclemarkcle Member Posts: 65
in NAV Three Tier
Hi All

Working on an extension where I need to swap items on Sales orders and Blanket Sales Orders (where qty shipped = 0).

If I have sales order lines linked to the blanket lines, I need to do the following:
  1. Disconnect the Sales line from the blanket
  2. change the item on the line
  3. Change the item on the blanket line
  4. Relink the sales line to the blanket

Having problems on the last step. How can I keep the original record set and iterate over it again?
        // Get list of blanket lines
        BlanketSalesLine.SETRANGE("Document Type", BlanketSalesLine."Document Type"::"Blanket Order");             // Need to add blankets too
        BlanketSalesLine.SETRANGE(Type, BlanketSalesLine.Type::Item);
        BlanketSalesLine.SetFilter("No.", '= %1', SourceItemNo);
        BlanketSalesLine.SetFilter("Quantity Shipped", '= 0');

                // For each blanket line, find related sales lines
                SalesLine.SETRANGE("Document Type", SalesLine."Document Type"::Order);             
                SalesLine.SETRANGE(Type, SalesLine.Type::Item);
                SalesLine.SetFilter("No.", '= %1', SourceItemNo);
                SalesLine.SetFilter("Blanket Order No.", '= %1', BlanketSalesLine."Document No.");
                SalesLine.SetFilter("Blanket Order Line No.", '= %1', BlanketSalesLine."Line No.");
                SalesLine.SetFilter("No.", '= %1', SourceItemNo);
                SalesLine.SetFilter("Quantity Shipped", '= 0');

               IF SalesLine.FINDSET(TRUE) THEN
                    REPEAT
                       // update sales lines with new item and disconnect from blanket
                    UNTIL SalesLine.NEXT = 0;

               // Update blanket with new item
               BlanketSalesLine.Validate("No.", NewItemNo);       // Change item no and validate
               BlanketSalesLine.MODIFY(TRUE);

               // now iterate over these sales lines again and re-link blanket

Answers

  • AlexeyShaminAlexeyShamin Member Posts: 80
    Hello!
    Create temp variable SalesLineTemp and save values - "Document Type", "Document No", "Line No", "Blanket Order No" and "Blanket Order Line No. "

    After modification restore values "Blanket Order No." and "Blanket Order Line No." from SalesLineTemp
  • markclemarkcle Member Posts: 65
    Thanks Alexey

    I can't see how that would work

    Let says I have one blanket line that links to 3 sales order lines

    I can store my blanket line details,. but I need to break the link on all three sales lines, change that item, then change the item on the blanket and then change the 3 sales lines. I could use an array or list, but I was hoping to use some kind of recordset (copy of original set with blanket ref's still intact. This way I can just iterate over the original copy of sales lines again.

    For example in SQL, i would get lines into temp table or table var, update or sales lines and blanket lines and then go back back to temp table to find original blanket refs from the original sales lines

    Best

    Mark
  • AlexeyShaminAlexeyShamin Member Posts: 80
    Hello Mark!
    This is example:

    BlanketSalesLine.SETRANGE("Document Type", BlanketSalesLine."Document Type"::"Blanket Order");
    BlanketSalesLine.SETRANGE(Type, BlanketSalesLine.Type::Item);
    BlanketSalesLine.SetFilter("No.", '= %1', SourceItemNo);
    BlanketSalesLine.SetFilter("Quantity Shipped", '= 0');
    IF BlanketSalesLine.FINDSET THEN
    REPEAT
    // For each blanket line, find related sales lines
    SalesLine.SETRANGE("Document Type", SalesLine."Document Type"::Order);
    SalesLine.SETRANGE(Type, SalesLine.Type::Item);
    SalesLine.SetFilter("No.", '= %1', SourceItemNo);
    SalesLine.SetFilter("Blanket Order No.", '= %1', BlanketSalesLine."Document No.");
    SalesLine.SetFilter("Blanket Order Line No.", '= %1', BlanketSalesLine."Line No.");
    SalesLine.SetFilter("No.", '= %1', SourceItemNo);
    SalesLine.SetFilter("Quantity Shipped", '= 0');

    IF SalesLine.FINDSET(TRUE) THEN
    REPEAT
    //Create Copy
    SalesLineTemp.COPY(SalesLine);
    SalesLineTEmp.INSERT;
    // update sales lines with new item and disconnect from blanket

    UNTIL SalesLine.NEXT = 0;

    // Update blanket with new item
    BlanketSalesLine.Validate("No.", NewItemNo); // Change item no and validate
    BlanketSalesLine.MODIFY(TRUE);
    //Restore Values
    IF SalesLineTemp.FINDSET THEN
    REPEAT
    SalesLine.GET(SalesLineTEmp."Document Type", "SalesLineTEmp."Document No.", SalesLineTEmp."Line No.")
    SalesLine.""Blanket Order No" := SalesLineTEmp."Blanket Order No";
    SalesLIne."Blanket Order Line No. " := SalesLIneTemp."Blanket Order Line No. ";
    SalesLIne.MODIFY

    UNTIL SalesLineTemp.NEXT = 0;
    UNTIL BlanketSalesLine.NEXT = 0;
  • ResolusResolus Member Posts: 40
    Why do you want to keep the original recordset of SalesLine?
    Just use the same variable, on which filters have been set, and repeat over it again in the last step?

    // now iterate over these sales lines again and re-link blanket
IF SalesLine.FINDSET(TRUE) THEN
  REPEAT
    // relink to blanket line
  UNTIL SalesLine.NEXT = 0;
  • markclemarkcle Member Posts: 65
    Resolus wrote: »
    Why do you want to keep the original recordset of SalesLine?
    Just use the same variable, on which filters have been set, and repeat over it again in the last step?

    // now iterate over these sales lines again and re-link blanket
IF SalesLine.FINDSET(TRUE) THEN
  REPEAT
    // relink to blanket line
  UNTIL SalesLine.NEXT = 0;

    Thanks for the reply. I guess this is the thing I'm not clear on. My original filers included

    SalesLine.SetFilter("Blanket Order No.", '= %1', BlanketSalesLine."Document No.");
    SalesLine.SetFilter("Blanket Order Line No.", '= %1', BlanketSalesLine."Line No.");

    But now that I have had to remove those blanket references, I thought the filter will no longer find the same records.

    What I'm not sure about is if I re-use

    IF SalesLine.FINDSET(TRUE) THEN

    is it reapplying the filters? or am I looping through the same records found in SetFilter statements


    I'll give your example a try

    Thanks again for taking time to reply

    Best

    Mark
  • markclemarkcle Member Posts: 65
    I had a quick try and testing now, but the insert statement in this block gives me error saying record already exists:
    IF SalesLine.FINDSET(TRUE) THEN
REPEAT
//Create Copy
SalesLineTemp.COPY(SalesLine);
SalesLineTEmp.INSERT;
// update sales lines with new item and disconnect from blanket

UNTIL SalesLine.NEXT = 0;
  • markclemarkcle Member Posts: 65
    Did some more testing and still no luck. SalesLines finds no records, which is correct, as there are no make orders from the blankets; however, TempSalesLine finds every sales line (of any type) in the system.

    What I can't figure out is how TempSalesLine even gets populated as the records are inserted inside the SaleLine Repeat...Until

    This code does nothing except display messages
    begin
        //Message('Finding Blanket Sales Lines For Item: %1, Replacing With: %2', SourceItemNo, NewItemNo);
        BlanketSalesLine.SETRANGE("Document Type", BlanketSalesLine."Document Type"::"Blanket Order");
        BlanketSalesLine.SETRANGE(Type, BlanketSalesLine.Type::Item);
        BlanketSalesLine.SetFilter("No.", '= %1', SourceItemNo);
        BlanketSalesLine.SetFilter("Quantity Shipped", '= 0');
        IF BlanketSalesLine.FINDSET THEN
            REPEAT
                // For each blanket line, find related sales lines
                TempSalesLine.Reset();
                SalesLine.SETRANGE("Document Type", SalesLine."Document Type"::Order);
                SalesLine.SETRANGE(Type, SalesLine.Type::Item);
                SalesLine.SetFilter("Blanket Order No.", '= %1', BlanketSalesLine."Document No.");
                SalesLine.SetFilter("Blanket Order Line No.", '= %1', BlanketSalesLine."Line No.");
                SalesLine.SetFilter("No.", '= %1', SourceItemNo);
                SalesLine.SetFilter("Quantity Shipped", '= 0');
                LineCount := LineCount + 1;                 // Update line count var
                SalesOrderList += 'Blanket: ' + BlanketSalesLine."Document No." + ' Line: ' + Format(BlanketSalesLine."Line No.") + ' Item: ' + BlanketSalesLine."No." + '\';


                // loop through all sales lines for this blanket line
                IF SalesLine.FINDSET(TRUE) THEN
                    REPEAT
                        SalesOrderList += ' - Sales Line: ' + SalesLine."Document No." + ' Line: ' + Format(SalesLine."Line No.") + ' Item: ' + SalesLine."No." + '\';

                        //Create Copy
                        TempSalesLine.COPY(SalesLine);
                        TempSalesLine.INSERT;


                    // update sales lines with new item and disconnect from blanket
                    UNTIL SalesLine.NEXT = 0;


                // Update blanket with new item


                //Restore Values
                IF TempSalesLine.FINDSET() THEN
                    REPEAT
                        SalesOrderList += ' - Temp Sales Line: ' + TempSalesLine."Document No." + ' Line: ' + Format(TempSalesLine."Line No.") + ' Item: ' + TempSalesLine."No." + '\';
                    UNTIL TempSalesLine.NEXT = 0;
            UNTIL BlanketSalesLine.NEXT = 0;
        Message('Complete %1 blanket sales lines ' + '\' + SalesOrderList, LineCount);
    end;

    Output is as follows when searching for SourceItemNo 4002:

    Complete 4 blanket sales lines
    Blanket: 1002 Line: 15000 Item: 4002
    - Temp Sales Line: S-ORD101011 Line: 10000 Item: 6005
    - Temp Sales Line: S-ORD101011 Line: 20000 Item: 6005
    - Temp Sales Line: 1002 Line: 15000 Item: 4002
    - Temp Sales Line: 1002 Line: 20000 Item: 4002
    - Temp Sales Line: 1002 Line: 30000 Item: 1900-S
    - Temp Sales Line: 1002 Line: 40000 Item: 1960-S
    - Temp Sales Line: 1003 Line: 10000 Item: 4002
    - Temp Sales Line: 1003 Line: 20000 Item: 4002
    - Temp Sales Line: 1003 Line: 30000 Item: 1896-S
    Blanket: 1002 Line: 20000 Item: 4002
    - Temp Sales Line: S-ORD101011 Line: 10000 Item: 6005
    - Temp Sales Line: S-ORD101011 Line: 20000 Item: 6005
    - Temp Sales Line: 1002 Line: 15000 Item: 4002
    - Temp Sales Line: 1002 Line: 20000 Item: 4002
    - Temp Sales Line: 1002 Line: 30000 Item: 1900-S
    - Temp Sales Line: 1002 Line: 40000 Item: 1960-S
    - Temp Sales Line: 1003 Line: 10000 Item: 4002
    - Temp Sales Line: 1003 Line: 20000 Item: 4002
    - Temp Sales Line: 1003 Line: 30000 Item: 1896-S
    Blanket: 1003 Line: 10000 Item: 4002
    - Temp Sales Line: S-ORD101011 Line: 10000 Item: 6005
    - Temp Sales Line: S-ORD101011 Line: 20000 Item: 6005
    - Temp Sales Line: 1002 Line: 15000 Item: 4002
    - Temp Sales Line: 1002 Line: 20000 Item: 4002
    - Temp Sales Line: 1002 Line: 30000 Item: 1900-S
    - Temp Sales Line: 1002 Line: 40000 Item: 1960-S
    - Temp Sales Line: 1003 Line: 10000 Item: 4002
    - Temp Sales Line: 1003 Line: 20000 Item: 4002
    - Temp Sales Line: 1003 Line: 30000 Item: 1896-S
    Blanket: 1003 Line: 20000 Item: 4002
    - Temp Sales Line: S-ORD101011 Line: 10000 Item: 6005
    - Temp Sales Line: S-ORD101011 Line: 20000 Item: 6005
    - Temp Sales Line: 1002 Line: 15000 Item: 4002
    - Temp Sales Line: 1002 Line: 20000 Item: 4002
    - Temp Sales Line: 1002 Line: 30000 Item: 1900-S
    - Temp Sales Line: 1002 Line: 40000 Item: 1960-S
    - Temp Sales Line: 1003 Line: 10000 Item: 4002
    - Temp Sales Line: 1003 Line: 20000 Item: 4002
    - Temp Sales Line: 1003 Line: 30000 Item: 1896-S
  • AlexeyShaminAlexeyShamin Member Posts: 80
    In my example TempSalesLine - temp record.
    In your example TempSalesLine - normal record
  • markclemarkcle Member Posts: 65
    Thanks for reply Alexey. Where did you declare it temporary?
  • AlexeyShaminAlexeyShamin Member Posts: 80
    In BC try this one:
    procedure TestMethodName();
    var
    MyRecord: Record "Absence Header" temporary;
    begin

    In NAV - open variable properties - set temporary - Yes
Sign In or Register to comment.