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

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

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

T0.[DocStatus]

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

WHERE T0.[CardCode] =  $[OCRD.cardcode]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Excellent work that man! I have just added WHERE T0.[DocStatus] = 'O' to only see open documents.

Thanks man

[Former Member · Accepted solution]

Hello Sir, how can I get Golden Arrow next to DocNum? I used FOR BROWSE and even tried swapping for DocEntry.

Thank you

[vicky_williams · Accepted solution]

In the universal function configuration you need to fill in the 'Format' section (on the right above the SQL)

Enter the column number, then the table name for it to access in square brackets:

In this case I think it should be as below:

1[ODPI]

If there is more than one column you need to put the arrow on the separate with ;

e.g.

1[ODPI];2[OCRD]

[Former Member · Accepted solution]

Well done Vicky. Do you want to give a session at the next User Group meeting!?

[kothandaraman_nagarajan · Accepted solution]

Hi Tim,

Try this and getting link for document number:

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

T0.[DocStatus]

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

WHERE T0.[CardCode] =  $[OCRD.cardcode] and T0.docstatus = 'O'
for browse

Thanks & Regards,

Nagarajan