Message to alert Inactive Item

Answered

Rahul

• May 05, 2020 06:23

Hi Experts

I am trying to create a message box so that when a user is entering a Product Code RDR1.[ItemCode] in the Sales Order and that Product Code is inactive in the Item Master Data OITM.[frozenFor] it will show a message to alert the user that the Product Code is inactive

Reading some help from the guys on this forum, I tried to create a B1 Validation Configuration with the below SQL Condition, without any luck. I am getting a syntax error

 

IF (SELECT TOP 1 frozenFor FROM dbo.OITM WHERE T0.ItemCode=$[$38.1.0])='Y'))

BEGIN

SELECT 'STOP' [result]

END

 

Can someone please point out where I am going wrong?

Regards

Rahul

Vote

0

• Official comment

  

  Yaremi Miranda Lopez

  • May 13, 2020 19:53

  Hello Rahul,

  The problem is your query definition, I suggest you test the SQL Querys in the query manager when using them in conditions.

  The following syntax worked for me:

  IF((SELECT T0.[frozenFor] FROM OITM T0 WHERE T0.[ItemCode] =$[$38.1.0] )='Y')
  BEGIN
  SELECT 'STOP' FOR BROWSE
  END
• 

  Nadav Caridi

  • May 05, 2020 06:54

  Hi,

  I think you have a missing ( .

   

  Good luck.
• 

  Rahul

  • May 05, 2020 07:05

  I put two open (( and two close )) and still getting an error. Is there an easy way to figure out where it is missing?

   

  IF ((SELECT TOP 1 frozenFor FROM dbo.OITM WHERE T0.ItemCode=$[$38.1.0])='Y')

  BEGIN

  SELECT 'STOP' [result]

  END

   
• 

  Nadav Caridi

  • May 05, 2020 07:09

  you also never gave OITM alias of T0 
• 

  Rahul

  • May 05, 2020 07:17

  Still doesn't want to play ball

   

  IF ((SELECT TOP 1 frozenFor FROM T0.OITM WHERE T0.ItemCode=$[$38.1.0])='Y')

  BEGIN

  SELECT 'STOP' [result]

  END
• 

  Luca Zanni

  • May 05, 2020 07:29
  • Edited

  Hey Rahul,

  as Nadav said, you need to give to OITM the alias T0 (or don't use it at all as it is just one table you are referring to). Also the TOP 1 is redundant, as you will always have only one item per itemcode.

  Check if this SELECT works for you

  SELECT frozenFor FROM OITM WHERE ItemCode = $[$38.1.0]

  Just out of curiosity, what is the [result] for? You might want to take that out as well and try again.
• 

  Rahul

  • May 05, 2020 07:35

  Hi Luca

  I am trying to create a message box so that when a user is entering a Product Code in the Sales Order and that Product Code is inactive in the Item Master Data it will show a message to alert the user that the Product Code is inactive

  This B1 Validation is to check if the Item Code is Inactive. If it is active it does nothing, but if it is inactive then a message should pop up to alert the user that the item code is inactive. I have created a UF for the message

  Tried your solution, didn't get any errors, but there was no message to alert the part number is inactive

  Regards

  Rahul

   
• 

  Rahul

  • May 25, 2020 00:26

  Hi Yaremi

  It works perfectly

  Thanks a lot

  Regards

  Rahul

Please sign in to leave a comment.