Friday, February 26, 2010

GoldMine: Managing Linked Documents

A client asked: What is the best way to manage linked documents and attachments?

There a number of GoldMine features and SQL queries you can employ to clean up your Linked Documents (or prevent them from getting messed up in the first place).

First, to avoid creating a large number of duplicate linked documents, you can select the overwrite option instead of the rename option in the Email Options Advanced Tab. If an attachment is the exact same name, it will overwrite it. The concern with this is if the attachment is logo.jpg or some common file name, you can end up with unpredictable results.

Besides GoldMine settings, you can use Windows and SQL functions to clean up your linked documents/attachments after the fact. One thing to identify is what you really want to clean up - just the links or the actual documents. To clean up the links, you can use SQL queries. To delete the actual attached files, you can use Windows.

Each step needs to be done carefully so that you do not delete needed links or files. In SQL, you can use the following queries to select and delete links to the documents/attachments.

Be careful, the code below deletes all links for all contacts. To limit that, you need to add more WHERE clauses to the Delete query.

SELECT [ACCOUNTNO]
,[RECTYPE]
,[CONTACT]
,[TITLE]
FROM [db_name].[dbo].[CONTSUPP]
WHERE [db_name].[dbo].[CONTSUPP].[RECTYPE]='L' order by [TITLE] ASC

Delete FROM [db_name].[dbo].[CONTSUPP]
WHERE [db_name].[dbo].[CONTSUPP].[RECTYPE]='L' AND [db_name].[dbo].[CONTSUPP].[TITLE]='GIF Image'

Once this is complete, go to the directory where GoldMine stores your attachments and delete the actual physical files. You may want to do this only for image and vCard files that you don't need. You may want to copy files to a separate folder before deleting.

As always, make sure the database and the folders are backed up before proceeding.

No comments: