I recently had to build a solution that would run through the SalesLogix attachments directory, and move any items that didn’t have a corresponding record in the attachments table. To do this, I utilized functionality from the FileSystemObject. It’s a pretty simple script if you’re familiar with the FSO, but I thought I’d just outline what I did here.
Dim strSiteCode, strAttachPath
Dim objRS, fso, f, fl, fc
Dim sCheckVal, sNewPath
Dim sFoundCount, sNoCount
‘First of all, we need to get the attachment path for the machine we’re on. I first check SystemInfo to get the site code, then BranchOptions to get the attachment path for that sitecode.
strSiteCode = GetField(“PRIMARYSERVER”, “SYSTEMINFO”, “”)
strAttachPath = GetField(“ATTACHMENTPATH”, “BRANCHOPTIONS”, “SITECODE = ‘” & strSiteCode & “‘”)
‘Next, we create a new RecordSet object, returning all filenames from the Attachment table
Set objRS = CreateObject(“ADODB.Recordset”)
Set objRS.ActiveConnection = Application.GetNewConnection
objRS.open “SELECT ATTACHID, FILENAME FROM ATTACHMENT”
‘We also need to create an object for the FileSystemObject
Set fso = CreateObject(“Scripting.FileSystemObject”)
‘Instead of deleting mismatched files outright, we’re moving them to a “Delete” folder under attachments. This code creates that folder if it does not already exist.
If not fso.FolderExists(strAttachPath & “Delete”) Then
fso.CreateFolder(strAttachPath & “Delete”)
Set f = fso.GetFolder(strAttachPath)
Set fc = f.Files
For Each fl in fc
sCheckVal = fl.name
sNewPath = strAttachPath & “Delete” & sCheckVal
‘After determining the name of the first file in the Attachments folder, I use the ADO “find” function to see if the file exists in the Attachments table
objRS.Find “filename = ‘” & fl.Name & “‘”
If (objRS.BOF = True) or (objRS.EOF = True) Then
‘If not found, the file is moved to the new folder. I added a count which I display on a label on the form launching the code.
sNoCount = sNoCount + 1
lblMissing.Caption = sNoCount
‘If found, I do nothing other than record a count on a different label on the form.
sFoundCount = sFoundCount + 1
lblFound.Caption = sFoundCount
Set fso = Nothing
Set objRS = Nothing
There you have it. Just a simple example of using FileSystemObject in conjuction with SalesLogix functionality. I hope you find this helpful.