
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.
Sub UpdateAttachments
Dim strSiteCode, strAttachPath
Dim objRS, fso, f, fl, fc
Dim strCurrentFile
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”)
End If
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.MoveFirst
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
fl.Move sNewPath
lblMissing.Caption = sNoCount
Else
‘If found, I do nothing other than record a count on a different label on the form.
sFoundCount = sFoundCount + 1
lblFound.Caption = sFoundCount
End If
Next
Set fso = Nothing
Set objRS = Nothing
End Sub
There you have it. Just a simple example of using FileSystemObject in conjuction with SalesLogix functionality. I hope you find this helpful.
Subscribe To Our Newsletter
Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!
You have Successfully Subscribed!