We have alot of items that are one-time purchases and will not be purchase again for resale. What would be the benefit for making these items inactive?
I believe inactive is a boolean datatype in the item table, is that correct? Would the following query work.
update item
set inactive = 1
where itemlookupcode in (select itemlookupcode from item where quantity = 0 and lastsold < '01/01/2010')
Danny
*This post is locked for comments
run this first to see the affected results
select
itemdynamic.lastsold
from itemdynamic left join item on itemdynamic.itemid = item.id
where itemdynamic.lastsold in (select max(itemdynamic.lastsold)
from itemdynamic group by itemid)
=======================
Hi,
There is a bug in HQ , that it doesn’t update item LastSold field based on store activities.
So you need first to update item table by running a SQL query (as follow ):
update item
set item.lastsold = itemdynamic.lastsold
from itemdynamic left join item on itemdynamic.itemid = item.id
where itemdynamic.lastsold in (select max(itemdynamic.lastsold)
from itemdynamic group by itemid)
Note: do not forget to back up your database first.
Hope this is usefull.
Have a nice day
Hi Sad,
I'm very new to RMS and using SQL/querying etc...I am using HQ, so how do I get LastSold to update? Using Worksheet 401: Request Data Upload?
I have a lot of aged inventory/one-time purchases that I would like to make inactive (since it sounds like deleting is a bad idea...) however filtering by LastSold in HQ is getting me totally different results than in Store.
Thanks for any help!
Hi Danny,
Here's a couple of things you need to know:
1. LastSold is not a good field to use in your query - use LastUpdated instead. LastUpdated is updated when you receive as well as sell an item - your query will make new items inactive that have not yet sold, and may be sitting on the shelf. If you think quantity > 0 will take care of that, you're pretty trusting. But I would leave it in there, altered to quantity <> 0, as you do not want to inactivate items with a negative quantity (doesn't hurt anything, just not good practice).
2. Inactive items are not automatically filtered out of reports - you have to use the filter "Inactive = No". That filter exists by default in some of the RMS canned reports, especially the Item reports. If you remove this filter, inactive items will show up.
3. The description and alias fields are already indexed.
4. Although you want to filter inactive items from inventory reports, you do NOT want to filter them out of Sales Reports. After all, you sold them - don't you want to show that information?
RMS
I was under the understand that if an item was INACTIVE - it would not show up if searched in Store Operations (POS). I also thought that it would speed-up search in Store Operations.
I have several thousands item that are no longer in-stock and will not be restocked.
Can I also create an additional index to speed-up search of item in Store Operation since we often do searches via description or alias.
Dan
I make the items inactive but still can be sale on POS cause only show a message with a notification, if you click on yes the item is added to the transaction, how to block this item from the POS to not sale anymore.
Hi Sad
I only have one store - so HQ is not involved.
Danny
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (