P&D Report Action SQL
Answered
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
-
Official comment
This has been solved it was a syntax issue with single quotes.
-
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
-
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.
-
Regarding DocNum / DocEntry , I assume this would work since you never touched your document numbering .
if you ever touch them DocEntry <> DocNum
Good Luck
Please sign in to leave a comment.
Comments
4 comments