|
Posted by Robert on March 28, 2007, 4:46 pm
Please log in for more thread options 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:
> 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 using the SELECT command, to preview
> the items that would be affected by the UPDATE command; run
> SELECT * FROM ITEM
> WHERE QUANTITY = 0
> AND DEPARTMENTID = 8
> AND SUPPLIERID = 4
>
> Hope this helps....remember BACKUP FIRST...!
> "Robert" wrote:
>
> > How can i specify to run this by department name ?
> > Can i also add a filter last sold before specific date ?
> > Thanks
> >
> >
> >
> >
> > "convoluted" wrote:
> >
> > > 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:
> > >
> > > > 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
> > > >
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap