Thread: Removing Only Specific Products when their inventory = 0

Hello,
I am trying to figure out what would be necessary if I wanted to have certain products, who's inventory is equal to 0 to be unavailable for purchase.

We track our inventory in our store and we generally allow items with 0 or less inventory to show because we accomodate dropships and backorders. We do however have certain items we close out and once they are gone, we cannot sell them anymore.

I realize there are flags that will hide products that don't have positive inventory, but as far as I know, they apply storewide.

We would like to simply apply this logic to specific products. Our old website had a 'minimum inventory level' quantity per item, not storewide.

Is there anything I can do to automatically remove certain products when their inventory gets to 0?
Otherwise there is a delay between the time the order is placed and when it is processed by our fulfillment system (which pushes the Published=N flag back to storefront for that specific item).

What you probably want is this thread

http://forums.aspdotnetstorefront.co...entory-Control

Originally Posted by esedirect

What you probably want is this thread

http://forums.aspdotnetstorefront.co...entory-Control

Thanks for the info, the trigger looks like the best approach for me.

I'm thinking the below is what i would need:

CREATE TRIGGER tr_u_ProductVariant
on ProductVariant
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(Inventory)
BEGIN
UPDATE ProductVariant
set Published = 0
where variantID in (select VariantID from INSERTED
where Cast(CAST(ExtensionData AS XML).query('data(/AllowBackorder)') as varchar(10))='N' AND Inventory <= 0)

--MORE CODE TO CHECK IF PRODUCT HAS ANY REMAINING PUBLISHED VARIANTS, IF NOT
-- UNPUBLISH THE PRODUCT ITSELF
END
END

What I'm thinking for the whole trigger is below, any feedback would be appreciated.

Code:

create TRIGGER tr_u_ProductVariant
on ProductVariant
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	IF UPDATE(Inventory)
	BEGIN
		
		UPDATE ProductVariant 
		set Published = 0
		where variantID in (select VariantID from INSERTED)
		and cast(CAST(ExtensionData AS XML).query('data(/AllowBackorder)') as varchar(10))='N' 
		AND Inventory <= 0
		
		Update product set published = 0 
		where productID in (
			select p.productID from product p left join (
				select pv.productID, count(*) as publishedVars 
					from productVariant pv 
					where pv.published = 1
					group by pv.productID
				) as pvp on p.productID = pvp.productID
			where p.productID in ( SELECT productID from INSERTED ) and pvp.PublishedVars is NULL 
		)
	
	END
END

My logic is, if the variant is 'Not Allow Backorder' and it gets to 0 inventory, unpublish the variant.
Check the product, if the product has 0 published variants, also unpublish the product.

As long as you test it in a dev db and it works, then you should be good to go!