I’ve noticed there is a column called PAM_ReleaseDate pre-programmed into the database, and I’m wondering does anyone use this to schedule the release of products onto the website?
It would be handy to be able to pre-prepare products which are under NDA until a certain date, and have them automatically go live in the early hours of the morning (rather than the current procedure of manually doing so at the start of the business day) so they are caught by the Google Feed update and FactFinder Search Update before customers start looking for them.
We had a few examples of retailers doing exactly that. Mainly comicbook stores since their release dates are often advertised ahead of time. Often it was also related to the preorders.
What you could try doing is to set up a filter on the product table that would contain something like: isnull(PAM_ReleaseDate, GetDate()) >= GetDate()
It might however need converting the date format and some other fine tuning. Be careful since if there is an error in the filter it could result in taking down all the items from the webstore.
Usually, this is used for display of the release date on the product page. However, it could easily be adapted for your purposes.
Essentially, you’d need to update the filter on the product table to also check PAM_ReleaseDate. Something like:
webitem=1 AND (CASE WHEN PAM_ReleaseDate > 0 THEN DATEDIFF(day, PAM_ReleaseDate, GetDate()) >= 0 ELSE 1 END)
I haven’t tested this but I guess you get the idea?
You could try it out in SQL Server Management Studio (or Store Ops Administrator) using something like:
SELECT COUNT(*) FROM product INNER JOIN nitroasl_pamtable ON nitroasl_pamtable.itemid = item.id WHERE webitem=1 AND (CASE WHEN PAM_ReleaseDate > 0 THEN DATEDIFF(day, PAM_ReleaseDate, now()) >= 0 ELSE 1 END)
That all makes perfect sense, and I will definitely test it in SSMS to make sure it’s returning the correct results before fiddling in NS Sync! I’d forgotten that the table-level filters were tweakable.
I finally had a chance to test this in SSMS, and a little tweaking was required. The CASE clause actually created unnecessary complexity, so I just bracketed an OR pair to pull in items with either a blank release date or a release date in the past :
WebItem=1 AND Inactive=0 AND ((ISNULL(PAM_ReleaseDate, ‘’) = ‘’) OR (DATEDIFF(hour, PAM_ReleaseDate, GETDATE()) >= 5 ))
Most of the embargos expire around 4am or 5am, but the ‘Release Date’ field in PAM only allows date entry, so I set the DATEDIFF to measure hours and set the default to 5 in order to avoid items going live at midnight.
The full SQL call to test this functionality is:
SELECT
COUNT(*)
FROM [Item]
INNER JOIN nitroasl_pamtable ON nitroasl_pamtable.itemid = item.id
WHERE WebItem=1 AND Inactive=0 AND ((ISNULL(PAM_ReleaseDate, '') = '') OR (DATEDIFF(hour, PAM_ReleaseDate, GETDATE()) > 5 ))
@donogh - as the data is stored in full DateTime format in the database could a future upgrade to PAM include the ability to enter the hours & minutes?