How to create a smartlist to show invoices without a physical PDF document

Not too long ago, I answered a pretty interesting question on the Dynamics GP newsgroup. The user's company images signed delivery tickets and store these as PDF documents on a shared location on their network. The PDF document is named after the corresponding invoice number in Dynamics GP, for example, if the invoice number is INV010001, the corresponding image of the delivery ticket would be stored as INV010001.PDF





















The user wanted to know if it was possible to create a smartlist to show invoices without a scanned PDF image of the delivery ticket.

Solution

When you think of this problem from the eyes of a user, it seems almost impossible to write a SmartList that would produce the results wanted by the user. But when you breakdown the problem, the real issue is, how do we create a SQL Server view that is able to show whether a file exists for a specific Dynamics GP invoice document.

The solution is to create a SQL Server user-defined function (UDF) that is able to return whether a file exists or not. Then we can incorporate this UDF into our view query. Since views can be addressed from SmartList Builder then we are good to go.

First the UDF.

dbo.IsFileExist

-- Created by Mariano Gomez, MVP

-- Check if the UDF exists and drop
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
   [IsFileExist]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

   DROP FUNCTION dbo.IsFileExist
GO

-- Create the UDF
CREATE FUNCTION dbo.IsFileExist(@Path VARCHAR(200), @FileName VARCHAR(200))
RETURNS INT
AS
  BEGIN
    DECLARE @FullPath VARCHAR(200);
    DECLARE @FileExists INT;

    -- Check for the back-slash at the end of the path string
    IF RIGHT(RTRIM(@Path), 1) <> '\'
      SET @Path = RTRIM(@Path) + '\';

    -- concat the path with the file name
    SET @FullPath = RTRIM(@Path) + RTRIM(@FileName);

    -- run the xp_fileexist system stored proc to retrieve wheter the file exists or not
    EXEC master.dbo.xp_fileexist @FullPath, @FileExists OUT;
    RETURN @FileExists;
  END;
GO

GRANT EXECUTE ON dbo.IsFileExist TO DYNGRP
GO

The above UDF uses the undocumented system stored procedure xp_fileexist which, when executed in its native form, will display information about the file passed in as a parameter. For example:

exec master..xp_fileexist 'c:\boot.ini

... produces the following results:

File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1           0                   1

(1 row(s) affected)

Now that we have the UDF function in place, we can prepare the view to be used with SmartList Builder.

dbo.vShowSOPAttachments

-- Created by Mariano Gomez, MVP
CREATE VIEW dbo.vShowSOPAttachments AS
  WITH SOP_CTE (SOPNUMBE, SOPPDF) AS (
    SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP10100
    UNION ALL
    SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP30200
  )
  SELECT SOPNUMBE, SOPPDF, dbo.IsFileExist('C:\', SOPPDF) AS [File Exists] FROM SOP_CTE;
GO

GRANT SELECT ON dbo.vShowSOPAttachments TO DYNGRP

Note the call to the dbo.IsFileExists UDF. With the view in place you can follow standard SmartList Builder procedures to implement and deploy the view. For information managing security and changes to SmartList Builder smartlist click here.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/