Portal | Homepage | Blog

Using PAM_ReleaseDate to schedule products?

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.

Thanks in Advance!

Hi Gareth,

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.

Regards,

Peter

Hi Gareth,

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)

I see Pete has replied too. Either will work.

Regards,
Donogh

1 Like

Thanks @donogh & @peter_szczepanowski,

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.

1 Like

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?

Hi Gareth,

We can certainly add it to the suggestion list.

@maciej.torbus could you add this for Gareth please?

Thanks,
Donogh

Hi Donogh and Gareth,

This suggestion has been added to the list.

Thank you
Maciej

1 Like