Query Prompt Database Connection With Sql Expression 关注

0
Avatar
Legacy Poster

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

5 评论

0
Avatar
Legacy Poster
评论操作 固定链接

Hi Rohan, can you attach a sample of your database preferably in Excel Format. Where Sheet1 = T0 Sheet2 =T1 and Sheet3=T3?

0
Avatar
Legacy Poster
评论操作 固定链接

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.
 
Rohan

0
Avatar
Legacy Poster
评论操作 固定链接

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
Avatar
Legacy Poster
评论操作 固定链接

Check this out. Oh to attach files Choose -> More Reply Options bottom right

0
Avatar
Legacy Poster
评论操作 固定链接

Sorry for being late to respond, but that worked sai.htet. Thank you for your help.

请先登录再写评论。