Deleting Items from a table

stormcandi · 2015-07-02

I have a function that is taking the No. value in the Item table and writing the value to a new table I created called 'Blocked Items'. The items going in this table are the items that are 'Blocked' = TRUE in the 'Item' table. Once the item list is in the 'Blocked Items' table then I set all 'Item.Blocked' = FALSE. This is so I can do a compare and set 'BLOCKED' = TRUE for the same items at a later time. The function I have setting 'Blocked' = TRUE the items is below:

  IF "Blocked Items".FINDSET THEN BEGIN
    REPEAT
      Statement := FALSE;
      IF Item.FINDSET THEN BEGIN
        REPEAT
         IF "Blocked Items"."No." = Item."No." THEN BEGIN
          Item.Blocked := TRUE;
          Item.MODIFY;
          Statement := TRUE;
         END;
        UNTIL (Item.NEXT = 0) OR (Statement = TRUE);
      END;
    UNTIL "Blocked Items".NEXT = 0;
    "Blocked Items".DELETEALL;
    MESSAGE('Items reblocked.');
    END
  ELSE BEGIN
    MESSAGE('No items to block exist.');
  END;

The problem that I am having is that without the DELETEALL everything works as it should setting 'Blocked' = TRUE the corresponding items in the 'Item' table. Once I add the DELETEALL it doesn't set 'Blocked' = TRUE the items but the items in the 'Blocked Items' table are deleted. What am I doing wrong?

lvanvugt · 2015-07-03

Don't have an answer to your question as such (will try out myself to see if I can find something), but a remark regarding your code: why are looping through the Item table?

  ...
      IF Item.FINDSET THEN BEGIN
        REPEAT
         IF "Blocked Items"."No." = Item."No." THEN BEGIN
          Item.Blocked := TRUE;
          Item.MODIFY;
          Statement := TRUE;
         END;
        UNTIL (Item.NEXT = 0) OR (Statement = TRUE);
      END;
  ...

This seems very inefficient to me. As you have the primary key values (PK) of the items in the Blocked Item table you can directly 'recover' the corresponding record in the Item table using GET (it's a 1:1 relation isn't it?):

  ...
      IF Item.GET("Blocked Items"."No.") THEN BEGIN
        Item.Blocked := TRUE;
        Item.MODIFY;
      END;
  ...

Instead of running x times through the whole Item table you only have to recover x records from the Item table.
But maybe I am missing the point here.

lvanvugt · 2015-07-03

Just tested it and it's working fine with me. What version (build) are you doing this on?

I have used the following code:

IF BlockedItem.FINDSET THEN BEGIN
  REPEAT
    IF Item.GET(BlockedItem."Item No.") THEN BEGIN
      Item.Blocked := TRUE;
      Item.MODIFY;
    END;
  UNTIL BlockedItem.NEXT = 0;
  BlockedItem.DELETEALL;
  MESSAGE('Items reblocked.');
END ELSE
  MESSAGE('No items to block exist.');

stormcandi · 2015-07-03

I am new to NAV so am still figuring out how to do things. Thanks for the note about how to make the code more efficient. I will definitely put that into place. I am on Version 7.10.

stormcandi · 2015-07-03

I used your code and it worked like I need it to. Thanks so much!

lvanvugt · 2015-07-03

\:D/
Mark the post as Solved 8)

Deleting Items from a table

Answers

Categories