I devised a system which allows users to view the scanned image of any selected invoice at the click of a mouse.
Here is the system:
(1) I created a UDF under the category Marketing Documents with the following properties:
Title : SupInv (short for Supplier's Invoice)
Description : Scanned Docs
Type : General
Structure : Link
(2) All suppliers' Invoices are scanned and stored in folder X.
(3) I open any AP Invoice in SAP, and link its PDF scan. All other invoices are similarly linked to their scans
(4) I wrote a simple query which lists a range of suppliers' invoices. An important field is the UDF T0.\[U_SupInv].
(5) When a user runs the query and clicks on the UDF, the scanned PDF pops up.
(6) So far so good.
(7) Except that if we forget to attach a Scan, the link is blank as expected. But if we click on the blank, it opens the folder X.
I do not want this to happen. I want the link to be disabled if it is blank, so that nothing happens if we click on it.
(8) So I modified my SQL as follows:
T0.\[U_SupInv] as 'Link'
WHEN T0.\[U_SupInv] IS NULL THEN ' '
WHEN T0.\[U_SupInv] IS NOT NULL THEN T0.\[U_SupInv]
END AS 'Link'
(9) But when I do this, none of the links works. The scanned image does not pop up for ANY invoice on the list.
(10) Where have I gone wrong? I want all links to be live, but I want blanks to be disabled.
(11) Please note that the UDF T0.\[U_SupInv] is defined as a link field. Maybe we cannot use IS NULL with link fields?
Here is my original query:
SELECT T0.[TaxDate] AS 'Doc Dt', 'Invoice' AS 'Txn Type', T0.[DocNum] AS 'Doc No.', T0.[NumAtCard] AS 'Vendor Reference', T0.[CardName] AS 'Supplier Name', T0.[DocTotal] AS 'Rs', T0.[U_SupInv] AS 'Link' FROM [dbo].[OPCH] T0 INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN [dbo].[OJDT] T5 ON T0.[TransID] = T5.[TransID] WHERE ... etc