How to make items with QTY=0 Inactive ?

Loading thread data ...

Hi Robert Keep in mind that setting the item as inactive will also make it inactive for physical inventory purposes...if this is fine with you, here's the sql query you need...

In SO administrator, connect to your database BACKUP FIRST...! THEN run the following query

UPDATE ITEM SET INACTIVE = 1 WHERE QUANTITY = 0;

You may want to do this after the store closes just to make sure it affects the items that you know should be affected....or you may consider adding an additional constraint to the query, such as adding a specific supplier code that you know has many items that fit in this category...hope this helps

"Robert" wrote:

Reply to
convoluted

How can i specify to run this by department name ? Can i also add a filter last sold before specific date ? Thanks

"c> Hi Robert

Reply to
Robert

The item table references things like supplier ID and department ID let's say you wanted to specify a specific supplier and a specific department in your update query.

first run the following statement select * from supplier (you will see a list of your suppliers; jot down the supplier ID(s) of suppliers that you know have items that you want to make inactive

then run the following statement select * from department (you will see a list of your departments; jot down the department ID that should contain items that you want to make inactive

say you want to run the update query for supplier id 4 and department id 8

the query will be UPDATE ITEM SET INACTIVE = 1 WHERE QUANTITY = 0 AND DEPARTMENTID = 8 AND SUPPLIERID = 4

You can also run this same query but us> How can i specify to run this by department name ?

Reply to
convoluted

I'm trying to cleanup my database for few reasons.

Is there a way to make all items that On hand QTY of 0 Inactive ? This would help me i generating reports and printing labels.

I tired inventory wizard and filters but the On hand qty is not one of the filter options. Seems this should be easy to run as SQL Query but i do not know the command

Thanks for all help

Thanks Robert

Reply to
Robert

Hello

This worked well but the end result is not what i had in mind. Once i made the item inactive there seems to be no way to find the item again , other than run the item QTY report for inactive items. If i try to add a new item with the same UPC it tells me the item exists but still does not show me the items details.

I can still sell those items, but i can't easily find and edit them.

The main reason why i was trying to make items with 0 QTY inactive was so i would not print labels for items we currently do not have in stock. There is no filter for item QTY in label wizard so this seemed like a good idea.

Is there another/ better way to do this.?

Thanks for all your Help

"c> The item table references things like supplier ID and department ID

Reply to
Robert

Hello

This worked well but the end result is not what i had in mind. Once i made the item inactive there seems to be no way to find the item again , other than run the item QTY report for inactive items. If i try to add a new item with the same UPC it tells me the item exists but still does not show me the items details.

I can still sell those items, but i can't easily find and edit them.

The main reason why i was trying to make items with 0 QTY inactive was so i would not print labels for items we currently do not have in stock. There is no filter for item QTY in label wizard so this seemed like a good idea.

Is there another/ better way to do this.?

Thanks for all your Help

"convoluted" wrote:

Reply to
CptSoft

Reply to
convoluted

Thanks for the tips this worked well Never noticed the stock on hand option, Thanks Again

"Jeff @ Check Po> Robert,

type | Next | select Stock on Hand | select the items you wish to print labels for, if All of them, select Add All button | click the Set Quantity button in the upper right | Next to select your label format | next to print them.

Reply to
Robert

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.