Deleting Items from a table

stormcandistormcandi Member Posts: 27
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?

Answers

  • lvanvugtlvanvugt Member Posts: 774
    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.
    Luc van Vugt, fluxxus.nl
    Never stop learning
    Van Vugt's dynamiXs
    Dutch Dynamics Community
  • lvanvugtlvanvugt Member Posts: 774
    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.');
    
    Luc van Vugt, fluxxus.nl
    Never stop learning
    Van Vugt's dynamiXs
    Dutch Dynamics Community
  • stormcandistormcandi Member Posts: 27
    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.
  • stormcandistormcandi Member Posts: 27
    I used your code and it worked like I need it to. Thanks so much!
  • lvanvugtlvanvugt Member Posts: 774
    \:D/
    Mark the post as Solved 8)
    Luc van Vugt, fluxxus.nl
    Never stop learning
    Van Vugt's dynamiXs
    Dutch Dynamics Community
Sign In or Register to comment.