Thread: Inventory Control

Hello,

I was wondering if there was a way that once inventory reaches a certain level (for example, let's say 20), instead of giving an option for the customer to purchase it would instead say 'E-Mail or Call us to Purchase.'

I would like this occur along with the item disappearing if the inventory is at 0, as well. Not one or the other.

Is there a way to accomplish this without third-party development? Any help would be greatly appreciated.

Thanks.

Could you not use a SQL trigger? if under 20 change to call to order only.

Sadly I would not know how best to do this, just an idea.

I would think that would work.

Unfortunately, I don't know how to go about that either.

Anyone with additional input or help with that if that would work?

Code:

CREATE TRIGGER tr_u_ProductVariant
ON ProductVariant
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;

	IF UPDATE(Inventory)
	BEGIN
		UPDATE Product
		SET IsCallToOrder = 1
		WHERE ProductID = (SELECT ProductID FROM INSERTED WHERE Inventory < 20)
	END
END

This creates a trigger on the ProductVariant table. When the table is updated it checks to see if the Inventory column has been changed, which it will be when orders are created. It will then set Product.IsCallToOrder to 1 for the relevant product when the inventory drops below 20.

Totally un-tested. So I suggest you try this on a DEV db before putting it on your production db.

Without editing complied source code? The method I would go for would be to edit an existing xmlpackage.

1st step would be to add the Inventory column to select statement.

2nd add an xsl choose statement around the AddtoCartForm to show different message when the inventory was less then 20.

3rd would be testing and making sure the output showed as desired.

Originally Posted by esedirect

Code:

CREATE TRIGGER tr_u_ProductVariant
ON ProductVariant
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;

	IF UPDATE(Inventory)
	BEGIN
		UPDATE Product
		SET IsCallToOrder = 1
		WHERE ProductID = (SELECT ProductID FROM INSERTED WHERE Inventory < 20)
	END
END

This creates a trigger on the ProductVariant table. When the table is updated it checks to see if the Inventory column has been changed, which it will be when orders are created. It will then set Product.IsCallToOrder to 1 for the relevant product when the inventory drops below 20.

Totally un-tested. So I suggest you try this on a DEV db before putting it on your production db.

Thank you for this but pardon my ignorance. Once I run this through SQL....what do I do next?

It will create a trigger. A trigger is just what it sounds like.

It will sit on your database and wait for an update to be called. If an update is called on the product table the trigger will check if it (the requested update) meets its requirements (inventory is under 20) if so it will let the update finish and then change that product to call to order.

Once you run this you just leave it and it will work perfectly (I say perfectly with my little knowledge of writing triggers.)

Just one correction: the trigger is assigned to the [ProductVariant] table. So everytime the [Inventory] column is updated (ie normally, when an order is processed) it will check to see if the update brings the [Inventory] below 20. If it does then it will change the [Product].[IsCallToOrder] column to 1.

I'm going to slightly amend the SQL to:

Code:

CREATE TRIGGER tr_u_ProductVariant
ON ProductVariant
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;

	IF UPDATE(Inventory)
	BEGIN
		UPDATE Product
		SET IsCallToOrder = 1
		WHERE ProductID IN (SELECT ProductID FROM INSERTED WHERE Inventory < 20)
	END
END

Note the use of IN, because the first SQL I wrote would relyon only one record being updated, and would fail if you updated lots of [Inventory] fields at once.

The thing about this is that the [Inventory] field is on the [ProductVariant] table, so once one of a product's variant's inventory falls below 20 then the product will be marked as call-to-order (for any variant).

Thanks again for your help. I updated the SQL but nothing is displaying on the page nor is it giving me that message when I attempt to add it to the cart.

Could there be something that I am missing?

Again, thank you so much for your help.

Well the first place to start with the testing would be to have an inventory which starts off > 20. Then make some orders to bring it below 20. At each stage you'd have to check that the inventory is decrementing by the number ordered.

You would possibly need to unit test this via SSMS, by issuing an update statement which decreases the inventory quantity below 20. Then check to see if there is a change in product.iscalltoorder.

So many places where it could go awry!