Iterating over record set twice

markcle

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

AlexeyShamin

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

markcle

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

AlexeyShamin

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;

Resolus

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;

markcle

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

markcle

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;

markcle

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

AlexeyShamin

In my example TempSalesLine - temp record.
In your example TempSalesLine - normal record

markcle

Thanks for reply Alexey. Where did you declare it temporary?

AlexeyShamin

In BC try this one:
procedure TestMethodName();
var
MyRecord: Record "Absence Header" temporary;
begin

In NAV - open variable properties - set temporary - Yes