P&D Report Action SQL

Respondida

Dustin Bellew

• 20 de mayo de 2020 20:01

I am wanting to show the customer open line items on their order for a delay / back order notification email. Below is my syntax and it does not seem to be working out. Any help is greatly appreciated. 

 

SQLHtmlTable(SELECT T0.[ItemCode], T0.[Dscription], T0.[OpenQty], T0.[ShipDate]

FROM RDR1 T0 

INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[LineStatus] ='O' AND  T1.[DocEntry] = ’$[$8.0.0]’)

 

Kind regards

 

Vote

0

• Comentario oficial

  

  Dustin Bellew

  • 20 de mayo de 2020 20:57

  This has been solved it was a syntax issue with single quotes.
• 

  Nadav Caridi

  • 20 de mayo de 2020 20:25
  • Editado

  Hi,

  How did you get to use this query ? 

  ORDR has no such column as LineStatus, and I'm pretty sure with the Dynamic syntax you should have targeted DocNum. 

  Why don't you run your queries and test them out ? you can use sql management studio or the built in query editor in SBO. 

  SELECT T0.[ItemCode], T0.[Dscription], T0.[OpenQty], T0.[ShipDate]

  FROM RDR1 T0

  INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[LineStatus] ='O' AND T1.[DocNum] = $[$8.0.0]

   

   

  Good Luck
• 

  Dustin Bellew

  • 20 de mayo de 2020 20:32

  I have tried it both ways using DocNum and DocEntry, they both work from SQL side of things. From within the P&D Report action email is where things get funky when trying to tie the $[$8.0.0] document number into the query. 
• 

  Nadav Caridi

  • 23 de mayo de 2020 06:45

  Regarding DocNum / DocEntry , I assume this would work since you never touched your document numbering . 

  if you ever touch them DocEntry <> DocNum 

  Good Luck

Iniciar sesión para dejar un comentario.