Jump to content


Photo

Product Inventory Update

inventory quotes

  • Please log in to reply
2 replies to this topic

#1 Walter

Walter

    Member

  • Validating
  • PipPip
  • 28 posts
  • LocationIsrael

Posted 26 September 2020 - 09:24 AM

Hey guys,

Who'd be more familiar to how do SQL triggers communicate with an X2CRM, could you comment or share an idea why MySQL trigger below which supposedly should update an inventory level after insert returns a following message: CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'x2_quotes_products.quantity' in 'field list'

 

CREATE TRIGGER `INVENTORY UPDATE`
AFTER INSERT ON `x2_quotes_products`
FOR EACH ROW UPDATE x2_products
SET x2_products.inventory=x2_products.inventory x2_quotes_products.quantity
WHERE x2_quotes_products.quoteId=x2_quotes.id AND x2_quotes_products.productId=x2_products.id
 
Does the app checks x2_fields table? then it would make sense it does not recognize it, but there be a way to avoid checking this table or any other suggestion how would we make this trigger work?
 
Thanks a lot in advance!


#2 jack

jack

    Admin

  • Administrators
  • 56 posts

Posted 28 September 2020 - 07:04 AM

Hi Walter,
As this issue, it seems to be just an SQL issue can you post the X2_quotes_products table. I think it is missing the quantity column.
-Jack



#3 Walter

Walter

    Member

  • Validating
  • PipPip
  • 28 posts
  • LocationIsrael

Posted 12 October 2020 - 05:19 AM

Hi Jack, thanks for the response.

I noticed a wrong syntax in the trigger above.

For a proper inventory deduction following trigger works perfectly. (this is very raw and straight forward as it does not have any conditional checks whatsoever, like it does not check anything e.g. if the invoice/quote is paid etc.)

 

CREATE TRIGGER `Stock Update`
AFTER INSERT ON `x2_quotes_products`
FOR EACH ROW
BEGIN
UPDATE x2_products
SET inventory=(inventory-NEW.quantity)
WHERE id=NEW.productID;
END






Also tagged with one or more of these keywords: inventory, quotes

1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users