Query Prompt Database Connection With Sql Expression
G'day, I'm trying to generate a delivery label from connection to the accounting package (SAP B1) database via a cusom MS-SQL expression. I've had to generate the custom SQL statement to select the descrete delivery address for the customer, as many customers have multiple delivery locations.
What I'm having issues with is connecting the query prompt to the sales document so, it selects the correct details for the order to be despatched.
The query is:
SELECT T0.[DocNum], T1.[CardCode], T1.[CardName], T0.[NumAtCard], T2.[Address], T2.[StreetNo], T2.[Street], T2.[Block], T2.[City], T2.[State], T2.[ZipCode], T2.[County]
FROM ORDR T0
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
INNER JOIN CRD1 T2 ON T1.[CardCode] = T2.[CardCode]
WHERE T0.[ShipToCode] = T2.[Address]
So I need the query prompt to select the descrete T0.[DocNum] sales order record and I can't get it to work. I know I'm close, so please help with that last line of code. I know I need to link it in the WHERE clause and I've tried variations of the following theme:
WHERE T0.[ShipToCode] = T2.[Address] AND T0.[DocNum]='?QueryPrompt1'
but all I get is: IDdispatch error # 3079
So close, yet so far. What am I doing wrong?
Regards
Rohan
-
Hi Rohan, can you attach a sample of your database preferably in Excel Format. Where Sheet1 = T0 Sheet2 =T1 and Sheet3=T3?
0 -
G'day sai.htet
Table is attached with one of our internal companies as the customer. The WHERE clause fields in T0 and T2 enables selection of the delivery address for that particular order. we have many customers with multiple delivery locations hence the WHERE clause to link the correct address details.
So my record selection criteria is via T0.[DocNum], which is the sales order number. This is then the prompt when printing.
The forum wouldnt give me permission to upload the xlsx file, so here is the dropbox link:
https://www.dropbox.com/s/4xlfry0h2had7cf/Order%20tables.xlsx?dl=0
I appreciate your help.
Rohan0 -
SELECT * FROM `'T0 (sales order table)$'` , `'T1 (customer table)$'` , `'T2 (cust del address table)$'` WHERE `'T0 (sales order table)$'`.`T0#(CardCode)` = `'T1 (customer table)$'`.`T1#(CardCode)` AND `'T0 (sales order table)$'`.`T0#(ShipToCode)` = `'T2 (cust del address table)$'`.`T2#(Address)1` AND (`'T0 (sales order table)$'`.`T0#(DocNum)` = ?QueryPrompt1)
Hi Rohan,
I was able to get it to work using the Joins and Query tabs in BTW, please see the SQL statement that BTW generates.
So the user enters the DocNum and BTW finds the corresponding records...
Try removing the single quote around your QueryPrompt1
0 -
Check this out. Oh to attach files Choose -> More Reply Options bottom right
0 -
Sorry for being late to respond, but that worked sai.htet. Thank you for your help.
0
请先登录再写评论。
评论
5 条评论