I set up a SALE using the wizard.
Sales prices were effective JAN1 though FEB1.
The sale is now over but, the checkbox is still checked on all the items saying they are still on sale.
This screws up my online software because there is a SALE flag by each item that is on sale (by the checkbox).
Why doesn't the ONSALE get flagged to FALSE after the sale date is past?
This is pretty irritating, isn't it... The problem is that short of some scheduled check, there is not much you can do about this in a databse environment. A Yes/No field (or numeric 0/1 field) can't be automatically updated based on the value of another field without outside intervention...
My solution to this is to run a nightly scheduled task using osql and Windows Scheduled tasks. Every day I have a SQL statement run on each of my SO servers:
osql -S servername -d databasename -U username -P password -Q "UPDATE Item Set SaleType = 0 where SaleEndDate < getdate()"
Just make sure to do the osql command after closing.
You can also remove the sale start and end dates in the same manner if you don't want them there... Use these strings in the above osql command:
UPDATE Item Set SaleStartDate = '' where SaleEndDate < getdate() UPDATE Item Set SaleEndDate = '' where SaleEndDate < getdate()
Note: Those are two apostrophes, not a double quote.
Now, you will want to do the same thing at HQ, because a Style 250 or 260 later down the road will overwrite these fields I believe. Maybe not a
250... not sure. Anyway, you will want to make sre everything is matched up.It hasn't caused any problems yet. If anyone sees any flaws in my system, please let me know!
I'm reading this message using the MS web interface. The post below is labeled as an 'Answer' to the original post (which is correctly labeled as a 'Question').
I presume that someone at MS reviewed this topic and marked Jason's response as an Answer because it was technically accurate.
I certainly seems like a pretty lame excuse for an 'Answer' to an inherently flawed sale price system.
The ANSWER would for RMS to automatically uncheck the 'on sale' box when the sale ends.
This paragraph explains the logic:
The very purpose of RMS is to intervene between the end user and the database. Unless you issue a query, RMS makes all changes to the database. Is it too much to ask for another Inventory Wizard Task called 'End Sale Pricing' or something like that?
For MS to formally call Jason's solution an 'Answer' is a weak attempt to justify not fixing a known issue with their product.
Tom
"Jas> This is pretty irritating, isn't it... The problem is that short of some
Geez, Tom. It was an "answer" by all rights to Mickie's question. Perhaps you would be more comfortable with "solution." I'd be happy with "recommendation."
We all agree the system is flawed, but you are certainly not encouraging user community responses, however half-a$$ed they are.
I checked the "DID THIS POST ANSWER THE QUESTION" button, not the microsoft guys. It answered it because he informed me the program is FLAWED in this instance and there is nothing that can be done but run a query....arg.
My next "SUGGESTION" will be for them to fix it. PLEASE CHECK YES so maybe they will take our advice.
Mickie
"Terrible Tom" wrote:
Jason,
I wasn't complaining about your response. Your answer was a good one. My point was that it shouldn't take a scheduled osql task to reset the on sale flag for items that are no longer on sale. Your answer was thorough and correct--but beyond the technical capability of many RMS users. No offense intended. Quite the opposite, in fact. I plan to implement your solution.
The part I should have brought up was this: "The problem is that short of some scheduled check, there is not much you can do about this in a database environment." I'm still trying to figure out why RMS doesn't have the 'scheduled check' built right in.
I see that Mickie (the original poster) was the one that checked the 'Answer' button, so maybe my cynicism got the better of me regarding MS this time.
In any case, I certainly don't want to do anything to discourage community responses. This group is my primary source of tech support.
Aga> Geez, Tom. It was an "answer" by all rights to Mickie's question. Perhaps
Point taken...
I won't be using Jason's 'clear the sale dates' idea - but I did use the SET SaleType = 0 query. I can see the value in knowing when the last sale on any given item began & ended.
newsgroups (100s) and marking them answered! ;-)
time I ran a sale on something. Others see it differently and Jason's script will do exactly what Mickie wants to do
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.