Linking files with SQL to items in MA!N

You can easily let people open files from MA!N by double clicking an item. Whether it concerns a sent letter or files in a situation where MA!N functions as a document management system.

Setting this up only requires the filling of the [File name] with a valid file (reference). The [File name] can be filled with files from a directory with the help of SQL. The following SQL script contains queries to achieve that. 

You can use this code example and change the file paths, tables and other variables.

--Query reads the folder and converts items in MA!N into links that open files

TRUNCATE TABLE [Custom invoice files]
--update [invoices open] set [File name] = null

DECLARE @_ID INT    
DECLARE @_Folder NVARCHAR(200)
DECLARE @_Cmd NVARCHAR(2000)
DECLARE @_FileInfo TABLE (
    [ID] INT IDENTITY(1, 1),
    [File name] NVARCHAR(200), 
    [Depth] INT, 
    [IsFile] BIT,
    [Invoice code] NVARCHAR(20)
)

SET @_Folder = 
'<PATH TO FOLDER>' 

INSERT INTO @_FileInfo (
    [File name], 
    [Depth], 
    [IsFile])
    EXEC xp_dirtree @_Folder, 0, 1

DELETE @_FileInfo
    WHERE [IsFile] = 0
    OR RIGHT([File name], 4) <> '.pdf'  

DELETE @_FileInfo
    FROM @_FileInfo AS [FI]
    INNER JOIN [Custom invoice files]
    ON [FI].[File name] = [Custom invoice files].[File name]

UPDATE @_FileInfo
    SET [Invoice code] = left(right([File name],11),7)

INSERT INTO [Custom invoice files] (
    [File name],
    [Date], 
    [Depth],
    [IsFile],
    [Invoice code])
    SELECT
    left([File name],50), 
    GETDATE(),
    left([Depth],50),
    left([IsFile],50),
    left([Invoice code],50)
    FROM @_FileInfo AS [FI]

UPDATE [Invoices open]
    SET [Invoices open].[File name] = @_Folder +  [Custom invoice files].[File name] 
    FROM [Invoices open]
    INNER JOIN [Custom invoice files]
    ON [Invoices open].[Invoice code] = [Custom invoice files].[Invoice code]
    WHERE [Invoices open].[File name] IS NULL