Deleting a list of items via a report called from a dataport

noved · 2008-03-05

I am working in Navision 3.6 (SQL Server Version). Our customer has a list of 14,000 obsolete items that they want to delete due to lack of activity.

I want to read this list and delete the corresponding record from Navision. I understand that you cannot delete from a dataport so I am passing the record to a processing report where the delete attempt is made. I end the delete in the report with a COMMIT and, through debugger, the record appears deleted. However, when testing the process with say, 10 records, only the last record is actually deleted. I have added commit statements to the dataport, as well, but am at a loss.

I am contemplating adding a field to the Item table where the dataport will mark the item for deletion. Then, separately, run the report to delete only those marked items but that seems unnecessary.

Below are the current code snippets I am working with:

* the dataport after validations to qualify the record are completed:

// Delete Customer Record if validation tests have been successful
VarItemDeleted := ItemNo;
GET(ItemNo);
ItemRec.COPY(Item);
ItemRec.SETRANGE("No.", ItemRec."No.");

REPORT.RUN(50135,FALSE,FALSE,ItemRec); //<-- pass rec to report.
COMMIT;
. . .

* the report step where deletion of passed record is executed:

OnAfterGetRecord()

DELETE(TRUE);

COMMIT;

Any suggestions on why this does not work or what to do to make it work would be greatly appreciated?

Savatage · 2008-03-05

noved wrote:

I am contemplating adding a field to the Item table where the dataport will mark the item for deletion. Then, separately, run the report to delete only those marked items but that seems unnecessary?

If you are doing this then why not just go to the Item Table.
Filter on this field.
Select All.
Hit the Delete Button.

:-k

Perhaps the criteria used to "Mark" the obsolete items in the report can be used on the item table directly w/ filters then you don't even need that field.

I'm not understanding the DATAPORT part, unless your trying to export a list of these killed items. Even then you could copy & paste into excel from the item table once you have placed your filters.

Make sure you have a backup before deleteing 14000 items - you never know when you might need that info back.

PS: I believe we had a discussion about items like this and they do get in the way sometimes. I think it was TheMave that mentioned creating a Visible boolean field along with using the "blocked" field to simply hide away these items by adding this field filter to a few forms, therefore not doing any drastic database changes.

Savatage · 2008-03-05

Not even being in the same league with Kriki the codemaster but,
your assigning ItemNo before you get it? Not sure what this code is trying to do.

// Delete Customer Record if validation tests have been successful
VarItemDeleted := ItemNo;
GET(ItemNo);
ItemRec.COPY(Item);
ItemRec.SETRANGE("No.", ItemRec."No.");

I would assume you simply use the dataitem of ITEM for your report and as it runs and meets the criteria it would delete the record.

OnAfterGetRecord()
Item.DELETE;

you could always set the DataItemTableView with all your criteria so no filters need to be manually entered

noved · 2008-03-05

The dataport is being used to read the list of item numbers provided (i.e. requested) for deletion from a text file.

So, when the dataport reads in the item number WIDGET, it validates the record, then passes that Item record for WIDGET to the report.

The report then deletes that record.

The dataport then reads the next record...

The dataport is being used primarily to read the external text file. But, yes, an Excel output is created listing items deleted and any exceptions found (i.e. existing open sales lines, etc.).

I was just looking for a way to examine items from a provided list and delete those items. The main goal is to automate the process and keep IT out of doing this repeatedly in the future and give select users the ability to remove the junk that unfortunately gets imported or entered. I just wanted to use existing methods rather than add another field to a table.

Savatage · 2008-03-05

Assuming this txt file has been carefully reviewed AND YOUR SURE YOU WANT TO DO IT THIS WAY.

WARNING..
THIS DATAPORT ONLY DELETES THE RECORD OFF THE ITEM TABLE. YOU WILL HAVE TO ADD ADDITIONAL CODE TO DELETE THE RELATED TABLES SUCH AS A PRICE TABLE OR VARIANT TABLE OR WHATEVER OTHER TABLES YOU WANT TO CLEAR!! THE OnDelete Trigger is not fired by this dataport. you should review the OnDelete Trigger of the item table & ADD that code to this DATAPORT.The dataport would be something like this - add additional code if you need to
http://savatage99.googlepages.com/Delet ... taport.txt

OBJECT Dataport 50040 Delete Item
{
  OBJECT-PROPERTIES
  {
    Date=03/05/08;
    Time=10:39:42 AM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000026;
        AutoSave=No;
        AutoUpdate=No;
        AutoReplace=No;
        OnAfterImportRecord=BEGIN
                              Item.GET(ItemToDelete);
                              IF ItemToDelete = Item."No."
                              THEN Item.DELETE;
                            END;

      }
      FIELDS
      {
        {      ;     ;ItemToDelete         }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      ItemToDelete@1000000000 : Code[20];
      Item@1000000001 : Record 27;

    BEGIN
    END.
  }
}

Savatage · 2008-03-05

TEST TEST TEST and then TEST AGAIN before running in live system. [-o<

All this needs to be cleared to.

OnDelete()
MoveEntries.MoveItemEntries(Rec);

ItemSub.SETRANGE(ItemSub.Type,ItemSub.Type::Item);
ItemSub.SETRANGE(ItemSub."No.","No.");
ItemSub.SETRANGE(ItemSub."Variant Code","Variant Filter");
ItemSub.DELETEALL;

ItemSub.RESET;
ItemSub.SETRANGE(ItemSub."Substitute Type",ItemSub."Substitute Type"::Item);
ItemSub.SETRANGE(ItemSub."Substitute No.","No.");
ItemSub.SETRANGE(ItemSub."Substitute Variant Code","Variant Filter");
ItemSub.DELETEALL;

SKU.RESET;
SKU.SETRANGE("Item No.","No.");
SKU.DELETEALL;

// UBP Start
ProcUnit.RESET;
ProcUnit.SETRANGE("Item No.","No.");
ProcUnit.DELETEALL;
// UBP End

// ItemText Start
ItemTexts.RESET;
ItemTexts.SETRANGE("Item No.","No.");
ItemTexts.DELETEALL;
// ItemText End

NonstockItemMgt.NonstockItemDel(Rec);
CommentLine.SETRANGE("Table Name",CommentLine."Table Name"::Item);
CommentLine.SETRANGE("No.","No.");
CommentLine.DELETEALL;

ItemVend.SETRANGE("Item No.","No.");
ItemVend.DELETEALL;

ItemPrice.SETRANGE("Item No.","No.");
ItemPrice.DELETEALL;

ItemTranslation.SETRANGE("Item No.","No.");
ItemTranslation.DELETEALL;

ItemUnitOfMeasure.SETRANGE("Item No.","No.");
ItemUnitOfMeasure.DELETEALL;

ItemVariant.SETRANGE("Item No.","No.");
ItemVariant.DELETEALL;

BOMComp.SETRANGE("Parent Item No.","No.");
BOMComp.DELETEALL;

ExtTextHeader.SETRANGE("Table Name",ExtTextHeader."Table Name"::Item);
ExtTextHeader.SETRANGE("No.","No.");
ExtTextHeader.DELETEALL(TRUE);

PurchOrderLine.SETCURRENTKEY("Document Type",Type,"No.");
PurchOrderLine.SETFILTER(
"Document Type",'%1|%2',
PurchOrderLine."Document Type"::Order,
PurchOrderLine."Document Type"::"Return Order");
PurchOrderLine.SETRANGE(Type,PurchOrderLine.Type::Item);
PurchOrderLine.SETRANGE("No.","No.");
IF PurchOrderLine.FIND('-') THEN
ERROR(
Text000,
TABLECAPTION,"No.",PurchOrderLine."Document Type");

SalesOrderLine.SETCURRENTKEY("Document Type",Type,"No.");
SalesOrderLine.SETFILTER(
"Document Type",'%1|%2',
SalesOrderLine."Document Type"::Order,
SalesOrderLine."Document Type"::"Return Order");
SalesOrderLine.SETRANGE(Type,SalesOrderLine.Type::Item);
SalesOrderLine.SETRANGE("No.","No.");
IF SalesOrderLine.FIND('-') THEN
ERROR(
Text001,
TABLECAPTION,"No.",SalesOrderLine."Document Type");

ProdOrderComp.SETCURRENTKEY(Status,"Item No.");
ProdOrderLine.SETRANGE("Item No.","No.");
IF ProdOrderLine.FIND('-') THEN
ERROR(
Text002,
TABLECAPTION,"No.");

ProdOrderComp.SETCURRENTKEY(Status,"Item No.");
ProdOrderComp.SETRANGE("Item No.","No.");
IF ProdOrderComp.FIND('-') THEN
ERROR(
Text014 +
Text015,
TABLECAPTION,"No.");

TransLine.SETCURRENTKEY("Item No.");
TransLine.SETRANGE("Item No.","No.");
IF TransLine.FIND('-') THEN
ERROR(
Text016,
TABLECAPTION,"No.");

ServInvLine.RESET;
ServInvLine.SETCURRENTKEY(Type,"No.","Order Date");
ServInvLine.SETRANGE(Type,ServInvLine.Type::Item);
ServInvLine.SETRANGE("No.","No.");
IF ServInvLine.FIND('-') THEN
ERROR(
Text017,
TABLECAPTION,"No.");

TroubleshSetup.RESET;
TroubleshSetup.SETRANGE(Type,TroubleshSetup.Type::Item);
TroubleshSetup.SETRANGE("No.","No.");
TroubleshSetup.DELETEALL;

ResourceSkill.RESET;
ResourceSkill.SETRANGE(Type,ResourceSkill.Type::Item);
ResourceSkill.SETRANGE("No.","No.");
ResourceSkill.DELETEALL;
DimMgt.DeleteDefaultDim(DATABASE::Item,"No.");

IF WebSite.FIND('-') THEN
SynchMgt.DeleteItem(Rec);

Savatage · 2008-03-05

Personally, I would go with the Add a new Boolean Field like "Not Visible" to the item table. Then use the Dataport to check off the field.

On the main forms & lists add "Not Visible":FALSE to the DataItemTableView

Not a big fan of deletion unless we're talking NO ENTIRES WHATSOEVER for that item. But even if you do go this route you could always filter on "Not Visible"=TRUE and hit the delete button - then the OnDelete Trigger with all the proper code will be executed as it should be.

I understand sometimes it needs to be done.

Savatage · 2008-03-06

I'm dying to know the final solution used :whistle:

Deleting a list of items via a report called from a dataport

Comments

Categories