Skip to content
+1-888-319-3663

EONE BLOG

Scripting inside SmartConnect


For all of you out there that have been doing scripting inside of SmartConnect, we have an excellent (although belated) New Year’s gift for you. We have assembled a collection of the most requested scripts over the last year and have commented and formatted each script in detail. They are now all bundled together and available for no charge in our knowledge base: Download

This collection of 20+ scripts covers a broad range of functions, from moving and renaming a file to reading entries off of the event log. Each script is written in VB.Net and has a large header section at the beginning that explains what the function of the script is and how to implement it.

Let’s walk through one of the scripts, MoveFileAddDate, to see how it works and in what ways it can be used effectively. Now, if any of you have used the SmartConnect tasks you know that there is already a prebuilt move file task. The drawback to this task is that the file name is going to be static. This script lets us add a date and/or time stamp onto the filename when we move it.
‘– Beginning of Script
‘—————————————————————————————
‘ Script Name: MoveFileAddDate
‘ Created On: 9/23/2009
‘ Author: Chris Hanson
‘ Purpose: This script moves an existing file from one location to another and
‘ adds a date stamp to the file name.
‘ History:
‘—————————————————————————————
‘—————————————————————————————
‘ Requirements: This map can be located in any task script location. The source file
‘ must exist and the date stamp that is being added to the file name
‘ must not contain any illegal characters.
‘—————————————————————————————
‘—————————————————————————————
‘ Setup: The dateFormat string determines how the Datetime object that contains
‘ the current date and time is parsed to a string. The default value
‘ will return a date without any slashes (ex. 092309). The sourceFile
‘ should be changed so that it points to an existing file, and the
‘ destFile can be set to anything that is desired (as long as it does
‘ not include illegal characters).
‘—————————————————————————————
‘———————————– CONFIGURATION ————————————-
Dim dateFormat As String = “MMddyy”
Dim sourceFile As String = “C:SmartConnectSample FilesFixed_Asset_General.csv”
Dim destFile As String = “C:SmartConnectSample FilesFixed_Asset_General.csv”
‘ Note: Do not include the date in the destination file name, the script will add it
‘ just before the extension (Ex. Fixed_Asset_General-092309.csv) in the code below.
‘—————————————————————————————

Try

‘Determine the current Datetime and convert it to the specified string format
Dim dateTimeInfo As DateTime = System.DateTime.Now
Dim dateString As String = dateTimeInfo.ToString(dateFormat)

‘Locate the index of the file extension
Dim extPosition As Integer
extPosition = destFile.LastIndexOf(“.”)

‘Insert a dash and the formatted date string into the destination file name
destFile = destFile.Substring(0, extPosition) & “-” & dateString & destFile.Substring(extPosition)

Try
‘Move the file
IO.File.Move(sourceFile, destFile)
Catch ex As Exception
‘If an error occurred, alert the user and return false to abort the map
System.Windows.Forms.MessageBox.Show(“An error occured while attempting to move the file: ” & ex.Message, “File Error”)
Return False
End Try

Catch ex As Exception
‘If an error occurred, alert the user
Messagebox.Show(ex.Message, “MoveFileAddDate Script Error”)
End Try

Return True


‘– End of Script


The first section of the script is comments that list the script name, date, purpose, etc. The single tick mark ‘ declares that anything following it on that particular line is just a comment and not more code. As you can see, almost every major line has a comment just above or below it explaining what the code is doing.

With these sample scripts, we have tried to place all the variables or configuration options in the same location in the script to make it easier for users. Just under the header comments at the beginning of the script there is a Configuration section which contains anything that would need to be modified to get the script to function correctly.

In this case, there are three variables that need to be configured. The first one is the dateFormat variable. This variable is going to determine what is appended to the file name when the script moves it. By default, dateFormat = “MMddyy”. So if our filename was “test.csv” and today’s date was 12/31/2009, then when the script moved the file the new name would be “test-123109.csv”. dateFormat can be set to other values as well. If you wanted the time, you could say dateFormat = “MMddyy-hhmm”. This would give a result of “test-123109-0943.csv” if it was 9:43 in the morning when the file was moved. The only restriction is that you shouldn’t put restricted characters into the string (ie periods or slashes) as that can mess up the file name.

The next two variables that can be configured are the sourceFile and destFile variables. The sourFile variable should be set equal to the path where the file you are going to move is located. The destFile variable should be set to where you want the file moved. You can also change the file name in the destFile if you desire. Let’s say my “test.csv” file was sitting on my desktop and I want it moved to a folder name “Completed Integrations” that is also on my desktop. I want to change the file name a bit to reflect that it was successful, as well. My configuration variables would be as follows:

dateFormat = “MMddyyyy”
sourceFile = “C:Documents and SettingsAdministratorDesktoptest.csv”
destFile = “C:Documents and SettingsAdministratorDesktopCompleted Integrationstest-integrated.csv”

The file would be moved to the Completed Integrations folder and would be named “test-integrated-12312009.csv” when the script ran.

Now, how can you use this effectively? Here’s a scenario where this script could complement other SmartConnect features. Let’s say that we have a file that is generated daily and placed in some folder on our machine. We also have a scheduled map that runs once a day that uses that file as the data source and then integrates it data into GP. Currently, somebody manually moves the file each night so that the new one can be placed in that folder.

This script could be set up in two places on the map so that the file is automatically moved to a specified folder based on whether the map succeeded or failed. The script could be put in the Tasks -> Map -> After -> Success location which would move the file to a successful integration folder. It could also be placed in the Tasks -> Map -> After -> Fail location to move the file to a failed integration folder. This would help streamline your integrations by adding another layer of automation to your system.

4 Comments

  1. Taibe Juni on May 27, 2015 at 1:13 pm

    Where are these sample scripts located? The link no longer goes anywhere.

  2. Arthur on February 20, 2019 at 9:30 pm

    updated link to download please

  3. Patrick Roth on February 21, 2019 at 12:20 pm

    updated link to correctly point to zip file

  4. Mat Bigham on October 27, 2020 at 7:48 pm

    I\’m not great at this sort of thing, but I sort of mashed together some of this as well as information from another topic to use a dynamic file name, archive a copy, then move the file. I\’m sure someone else could clean it up, but it should be noted that this also only does one file at a time.

    The other topic i used is located at this link:

    https://www.eonesolutions.com/tech-tuesday-using-script-replicate-folder-data-source/

    Script

    \’Declare the original folder the files are located in
    Dim fileFolder As String = “\\\\192.168.1.21\\folder\\Outbox\\”

    \’Declare the file to use to Archive a copy
    Dim ArchiveFolder As String = “\\\\192.168.1.21\\folder\\Outbox\\Archive\\”

    \’Declare current time to include in archive file name
    Dim dateTimeInfo as DateTime = DateTime.Now
    Dim dateString as String = dateTimeInfo.ToString(\”MMddyy-hhmmsstt\”)

    \’Declare the destination folder
    Dim newFileFolder As String = “\\\\192.168.10.98\\folder\\outbox\\” 

    \’Get the directory info from the info provided
    Dim fileName As String
    Dim dirInfo As New IO.DirectoryInfo(fileFolder)
    Dim files = dirInfo.EnumerateFiles()

    \’Grab the first file from the directory
    For Each f as FileInfo in files
    fileName = f.Name
    Exit For

    Next

    \’Locate the index of the file extension in the file name
    Dim extPosition As Integer = fileName.LastIndexOf(\”.\”)

    \’Insert a dash and the formatted date string into the Archive file name
    Dim archFile As String = fileName.Substring(0, extPosition) & \”-\” & dateString & fileName.Substring(extPosition)

    \’Copy file to archive folder
    File.Copy(fileFolder & fileName, ArchiveFolder & archFile, True)

    \’Delete the file from the destination location if a file is already there
    If File.Exists(newFileFolder & fileName) Then
    File.Delete(newFileFolder & fileName) 
    End If

    \’Move the file
    File.Move(fileFolder & fileName, newFileFolder & fileName)

    return true

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.