Help with SQL Query in Universal Function

[Former Member]

Hi - I have added a function button (Using Boyum) to BP Master Data. I want it to show me all AR DownPayment invoices for that particular BP. I saved the following SQL in a Universal Function:

SELECT T1.[DocNum], T0.[CardName], T0.[CardCode], T1.[DocDate], T1.[NumAtCard],

T1.[DocStatus]

FROM ODPI T0 INNER JOIN DPI1 T1 ON T0.CardCode = T1.CardCode 

WHERE T0.[CardCode] =  $[$5.0.0]

FOR BROWSE

But get this error:

Invalid query tree  [300-33]. The SQL error is 8180: SQL: SELECT T1.[DocNum], T0.[CardName], T0.[CardCode], T1.[DocDate], T1.[NumAtCard],

T1.[DocStatus]

FROM ODPI T0 INNER JOIN DPI1 T1 ON T0.CardCode = T1.CardCode

WHERE T0.[CardCode] =  'test'

(Please note 'test' is the CardCode of the BP)

Advice is appreciated!

Thanks

[vicky_williams]

Hi Tim

I think a couple of the T1's in your query should be T0's

(Docnum, [NumAtCard] and [DocStatus] dont appear to be in the DPI1 table, but are in the ODPI table)

Try this one(?)

SELECT T0.[DocNum], T0.[CardName], T0.[CardCode], T1.[DocDate], T0.[NumAtCard], T0.[DocStatus]

FROM ODPI T0  INNER JOIN DPI1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[CardCode] = $[$5.0.0]

I haven't tried it on a function button though

Cheers

Vicky

[Former Member]

Thanks Vicky - I'm starting to think the error is in the Validation as I still get the same error even with this simplified query

SELECT T1.[DocNum], T1.[DocDate], T1.[DocStatus]

FROM ODPI T0 INNER JOIN DPI1

WHERE T0.[CardCode] =  $[$5.0.0]

FOR BROWSE

I'll continue the investigation!