Pop Up Message When No BOM exists

Answered

Rahul

• April 28, 2020 02:41

Hi Experts

I have been trying to create a B1 Validation Configuration where if a Product Code is entered in the Sales Order and this Product Code (RDR1.[ItemCode]) does not exist in Bill of Materials (OITT.[Code]) a message should popup to alert the user that there is no BOM for that product code

I have created a B1 Validation Configuration and Universal Function Configuration, but I keep getting an error

Reading the error, it looks like I haven't got the SQL correct

Can someone please guide me in the right direction. Below are the photos of what I have done so far

Regards

Rahul

 

 

Vote

1

• 

  Nadav Caridi

  • April 28, 2020 06:45

  Hi , 

  Maybe Just try 

  
  IF NOT EXISTS (SELECT TOP 1 o.code FROM dbo.OITT o WHERE o.code=$[$38.1.0])
  BEGIN
  SELECT 'STOP' [result]
  END

   

  Good Luck 
• 

  Rahul

  • April 28, 2020 07:24

  Hi Nadav

  Thanks for your reply

  It works and a message pops up when there is no BOM

  also, how do I add the below criteria so that it ignores anything that belongs to this item group code?

  OITM.[ItmsGrpCod] <> '288'

  Regards

  Rahul

   

   
• 

  Nadav Caridi

  • April 28, 2020 08:49

  Hi

  In that case you need an join from the oitm to oitt 

  or just create a 2nd condition with another select 

  IF (

  NOT EXISTS (SELECT TOP 1 o.code FROM dbo.OITT o WHERE o.code=$[$38.1.0])

  AND (put 2nd condition for group code here )

  )
  BEGIN
  SELECT 'STOP' [result]
  END
• 

  Rahul

  • April 28, 2020 23:54

  Hi Nadav

  Sorry i am still a beginer at this

  Tried the below, but it wont work

   

  IF (NOT EXISTS (SELECT TOP 1 O.CODE FROM dbo.OITT O WHERE O.CODE = $[$38.1.0])
  AND T0.[ItmsGrpCod] <> '288')

  FROM OITM T0 INNER JOIN OITT T1 ON T0.[ItemCode] = T1.[Code]
  BEGIN

  SELECT 'STOP' [RESULT]

  END
• 

  Nadav Caridi

  • April 29, 2020 10:25
  • Edited

  Hi Rahul,

  I think you better do it with two queries , would be easier for you to read and understand if you're new to this 

  so query 2 should look like this (to get the group code): 

  (select top 1 t1.ItmsGrpCod from OITM t1 where t1.ItemCode=$[$38.1.0])

   

  and then just combine the two conditions into the previous query for the validation :

  IF (

  NOT EXISTS (SELECT TOP 1 o.code FROM dbo.OITT o WHERE o.code=$[$38.1.0])

  AND ((select top 1 ItmsGrpCod from OITM t1 where t1.ItemCode=$[$38.1.0])<>'288')

  )
  BEGIN
  SELECT 'STOP' [result]
  END

   

  Good Luck.
• 

  Rahul

  • April 30, 2020 05:39

  Hi Nadav

  It works perfectly

  Thank you very much

  Much appreciated

  Regards

  Rahul

   

Please sign in to leave a comment.