items on SALE for a period..not taken off after sale date passes

Posted by Mickie on February 8, 2007, 1:11 pm

Please log in for more thread options

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?

Posted by Jason on February 8, 2007, 1:41 pm

Please log in for more thread options

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 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?

Posted by Terrible Tom on February 8, 2007, 3:30 pm

Please log in for more thread options

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:

> 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...

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

"Jason" wrote:

> 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 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?

Posted by Jason on February 8, 2007, 4:43 pm

Please log in for more thread options

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'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:

>> 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...

> 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

> "Jason" wrote:

>> 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 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?

Posted by Terrible Tom on February 8, 2007, 7:57 pm

Please log in for more thread options

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.

Again, I apologize for any offense.
Tom

"Jason" wrote:

> 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'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:

> >> 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...

> >

> > 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

> >

> > "Jason" wrote:

> >

> >> 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 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?

> >>