How can I Clean my Items database?

hi Jose, if you talk about delete be carefull for those items which has some transaction history so the inactive is good option for you.

Go to SO Administrator program and then Connect to your database then go to Query and type the following

-- check first no transaction exists select * from inventorytransferlog where itemid in (select id from item where inactive=1) select * from inventoryoffline where itemid in (select id from item where inactive=1) select * from transactionentry where itemid in (select id from item where inactive=1) select * from orderentry where itemid in (select id from item where inactive=1) select * from purchaseorderentry where itemid in (select id from item where inactive=1)

you can put more clause like "and itemlookupcode='xxxxx'" this enable to check first there is nothing in any transaction data. once this shows zero in query records then you can delete the records delete from alias where itemid in (select id from item where inactive=1) delete from itemclasscomponent where itemid in (select id from item where inactive=1) delete from item where inactive=1

One more important thing is that you can create store procedure to accomplish this tasks if Iwill have time i will do this for you. but don't forget to rate this one.

"Jose" wrote:

Hi, thanks for your help, but I am very new at this. could you explain a little bit more? Thanks. jose

hi Jose, The login behind this is that before you delete any records from your databse system make sure that there should not any transaction exists, because if you delete an item which has already transit data it will create future problems like incosistency in your reports and then you will loss valued information. I always have procedure to do so check first transaction if exists not delete the master information.

Same log> Hi, thanks for your help, but I am very new at this. could you explain a

Jose,

Download our RMS Toolkit 30 Day Trial at:

Run the installer and select DRS Tools. Run DRS Tools and connect to your HQ database if multi-store, or your Store Operations database if single store. Select Maintenance, Mark (Items) Inactive. Press F5 to view all current candidates (zero in stock, no activity in one year). Change the date if you want to view more recent items. Select Mark All if you agree with the listing, or use the filter boxes to narrow the list. Enable the checkbox option to remove matrix class orphans. Click Save and you are done. If you are an HQ user, issue a worksheet 250 for recently changed items to all stores. If you decide to purchase our toolkit you would run this routine once a month or once a year to manage your inactive items in a few clicks. You can also quickly return inactive items to active status which is handy for seasonal items carried from one year to the next.

Greg Digital Retail Solutions

(v1.2, 1.3, and 2.0 compliant) "Save a million keystrokes."

Thanks Akber. you are a life saver. if you want you can e-mail direct me at snipped-for-privacy@reynoldcycle.com thanks again Jose

Jose,

Download our RMS Toolkit 30 Day Trial at:

Run the installer and select DRS Tools. Run DRS Tools and connect to your HQ database if multi-store, or your Store Operations database if single store. Select Maintenance, Mark (Items) Inactive. Press F5 to view all current candidates (zero in stock, no activity in one year). Change the date if you want to view more recent items. Select Mark All if you agree with the listing, or use the filter boxes to narrow the list. Enable the checkbox option to remove matrix class orphans. Click Save and you are done. If you are an HQ user, issue a worksheet 250 for recently changed items to all stores. If you decide to purchase our toolkit you would run this routine once a month or once a year to manage your inactive items. You can also quickly return inactive items to active status which is handy for seasonal items carried from one year to the next.

Greg Digital Retail Solutions

(v1.2, 1.3, and 2.0 compliant) "Save a million keystrokes."