Difference between revisions of "Auto Loader"

From SmartWiki
Jump to: navigation, search
(→‎Configuration - Essentials)
 
(230 intermediate revisions by 13 users not shown)
Line 1: Line 1:
−
A [[SmartSimple]] feature used to automatically upload files of data related to [[Working with Companies|companies]], [[Adding Contacts to SmartSimple|contacts]] and [[Universal Tracking Application|Universal Tracking Applications]].
+
An '''Autoloader''' is a SmartSimple feature used to automatically import data related to [[organization|companies]], [[contact]]s or [[Universal Tracking Application]]s. This allows you the ability to automatically update information with data from external text files which can be vital if you need to keep customer or project data in sync with an existing legacy or enterprise resource planning (ERP) system.
  
−
==Overview==
 
  
−
The Autoloader feature provides you with the ability to automatically update SmartSimple information with information from external text files.  This feature is vital if you need to keep SmartSimple customer or project data in sync with an existing ERP or other legacy system.
 
  
−
An Autoloader can be triggered whenever a file is added to a designated [[SmartFolder]].  These files can be added manually or uploaded automatically using the file synchronization plug-in – [[SmartSync for Folder Synchronization|SmartSync]].
+
==Overview==
 +
The '''Autoloader''' feature provides the ability to automatically create/update SmartSimple information with data from an external file. A general overview of the process is as follows: 
  
−
* Once a file is added to the [[SmartFolders Overview|SmartFolder]], the autoloader process will recognize that a file has been added and start the upload process.
+
* Once a file is added to the [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]], the Autoloader process will recognize that a file has been added and start the upload process.
 
* Once the file has been processed, it will not be processed a second time unless a new file of the same name is added to the folder.
 
* Once the file has been processed, it will not be processed a second time unless a new file of the same name is added to the folder.
−
* Each record from the upload file will update the associated record in the database.
+
* Each record from the uploaded file will update the associated record in the database.
−
* A log file created indicates the success or failure of each record update and an email can be sent to a designated user indicating the success of the upload.
+
* A log file will be created to indicate the success or failure of each record update and an email can be sent to a designated user to notify them of the success (or failure) of the upload.
−
* If a new version of the file is added to the folder, then this flag is reset and new file will be processed.
+
* If a new version of the file is added to the folder, the new file will be processed.
−
* You can create any number of Autoloaders within the system to provide for different upload processes.
+
* An Autoloader can upload to different objects within your SmartSimple instance: [[organization|companies]], [[contact]]s, [[Universal Tracking Application]] {{ l1 }} or {{ l2 }}, {{l3}},[[Category:Transactions|Transactions]], [[Notes_Overview|Notes]], [[Associations/|Associations]].
−
* An Autoloader can upload [[Organisation Hierarchy|company]], [[Adding Contacts to SmartSimple|contacts]] or [[Universal Tracking Application]] {{l1}} or {{l2}} data.
 
  
−
Before you can deploy this feature it is necessary to:
+
=Autoloader=
 +
==Prerequisites==
 +
* You must be a {{GUA}} in your [[instance]] of SmartSimple to access the Autoloader feature and set up an upload process.
 +
* The user uploading the file must have write permissions to the folder where the file is to be stored.
 +
* You '''must''' not use the upload folder for any purpose other than uploading files, as the autoloader will attempt to process any file added to the folder.
  
−
* Define the [[SmartFolders Overview|SmartFolder]], where the upload file will be stored.
+
===Creating a SmartFolder/Configuration Folder===
−
* Define the format and layout of the data file – the number of columns in the upload file, the delimiter between each field and the field order.
+
* Any [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] can be used as the source for an Autoloader.
−
* Specify a field in the upload file that the system will use to match an upload record to an existing customer record.
+
* The folder must be used '''exclusively''' for the Autoloader, since every file uploaded will be processed.
−
* Determine if you wish to use an automated process for files by using the SmartSync utility or manually add the files to the folder.
+
* Incorrect files or incorrectly formatted files '''will be processed''' and could lead to data loss and/or creation of invalid data.
−
 
 
−
Each record in the upload file is used to update one set of fields in the entity to be updated.
 
−
The existing value in the SmartSimple field will be replaced by the value on the field in the upload file.
 
  
−
==Creating a SmartFolder==
+
'''IMPORTANT:  '''Because any file uploaded to a [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] that is associated to an autoloader will be processed, it is extremely important that the SmartFolder/Configuration Folder is permissioned and labelled appropriately.
−
* Any SmartFolder can be used as the source for an Autoloader.
 
−
* The folder must be used '''exclusively''' for the Autoloader, since every file uploaded will be processed.
 
−
* Incorrect files or incorrectly formatted files '''will be processed''' so could lead to data loss and/or creation of invalid data.
 
−
'''IMPORTANT''' Because any file uploaded to a SmartFolder that is attached to an autoloader will be processed it is extremely important that the SmartFolder is permissioned and labelled appropriately.
 
−
* SmartFolders for autoloaders should be permissioned such that only contacts that need to be able to upload files for autoloading are permitted to do so.
 
−
* It is '''best practice''' to put all Autoloader enabled SmartFolders into a parent folder named '''Autoloader''' (with role-restricted access) to help ensure no inappropriate files are uploaded to the folder.  
 
  
 +
* [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] for Autoloaders should be permissioned such that only contacts that need to be able to upload files for autoloading are permitted to do so.
 +
* It is '''best practice''' to put all Autoloader enabled [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] into a parent folder named '''Autoloader''' (with role-restricted access) to help ensure no inappropriate files are uploaded to the folder.
 +
* In creating a new [[SmartFolders_Overview#Configuration_Folders|Configuration Folder]], specify "Autoloader/Export Storage" as Folder Type.
  
−
==Preparing the Upload File==
+
===Preparing the file to upload into the autoloader===
 +
Define the format and layout of the data file - the number of columns in the upload file, the delimiter between each field and the field order.
  
 
* You must be able to specify a unique field that will be used to link each record in the upload file to each record in the customer record. You cannot use a standard field such as customer name or phone number.
 
* You must be able to specify a unique field that will be used to link each record in the upload file to each record in the customer record. You cannot use a standard field such as customer name or phone number.
−
* This common field needs to be present in each record on the upload file and in each customer record. For instance, a unique account number field that you add to the customer table can be linked to the same account number field in the upload data.
+
* This common field needs to be present in each record on the upload file and in each customer record. For instance, a unique account number field that you add to the customer table can be linked to the same account number field in the upload data.
−
* The file to be uploaded must be in '''ASCII''' format. There is no specific file naming convention.
+
* The file to be uploaded must be in '''ASCII''' format. There is no specific file naming convention.
 
* Each record in the file must be separated by a CR/LF (ASCII 13,10 /D,A) character combination.
 
* Each record in the file must be separated by a CR/LF (ASCII 13,10 /D,A) character combination.
 
* Each field must be delimited by one of the following characters:
 
* Each field must be delimited by one of the following characters:
Line 48: Line 45:
 
  Colon – “:” (ASCII 58/3A)
 
  Colon – “:” (ASCII 58/3A)
  
−
:This can be accomplished either using Notepad to create and edit the data for import which can be saved with '''txt''' as the file extension.
+
: This can be accomplished either using Notepad to create and edit the data for import which can be saved with '''txt''' as the file extension.
−
:Alternately you can use the '''Save As''' feature within Microsoft Excel and save the document as a '''csv''' file.
+
: Alternately you can use the '''Save As''' feature within Microsoft Excel and save the document as a '''csv''' file.
 +
'''Notes:'''
 +
* If any of the data to be imported contains commas and you are creating a '''csv''' file from Excel you should export the data using pipes (|) as the separators rather than commas. To do this you need to change the settings on your PC prior to creating the '''csv''' file. The method of doing this depends on your PCs Operating System, but is roughly outlined below:
  
−
'''Notes:'''
+
  Start -> Settings -> Control Panel -> Regional Settings -> Additional Settings Button.
−
* If any of the data to be imported contains commas and you are creating a '''csv''' file from Excel you should export the data using pipes (|) as the separators rather than commas. To do this you need to change the settings on your PC prior to creating the '''csv''' file. The method of doing this depends on your PCs Operating System, but is roughly outlined below:
 
−
  Start -> Settings -> Control Panel -> Regional Settings.
 
 
  Click the "Number" tab and in the "List Separator" field,  
 
  Click the "Number" tab and in the "List Separator" field,  
 
  Replace the current default separator with the one you want to use (such as the pipe symbol | ).  
 
  Replace the current default separator with the one you want to use (such as the pipe symbol | ).  
 
* If you use Microsoft Excel to create a '''csv''' file it is recommended that you open the document with Notepad to review the contents prior to Autoloading. If any of the data within the Excel document had line breaks within cells it will create a line break in the '''csv''' document. Each new line in the '''csv''' document will start a new record, thus you must ensure all inner-cell line breaks are removed from either Excel or the '''txt''' document.
 
* If you use Microsoft Excel to create a '''csv''' file it is recommended that you open the document with Notepad to review the contents prior to Autoloading. If any of the data within the Excel document had line breaks within cells it will create a line break in the '''csv''' document. Each new line in the '''csv''' document will start a new record, thus you must ensure all inner-cell line breaks are removed from either Excel or the '''txt''' document.
 
* Alternately when performing a CSV import, any columns that have line breaks or delimiters within the content can be contained within double quotes. NOTE: Any double quotes within that content MUST be escaped with a backslash \ in front of the double quote so they aren't treated as the closing quote of the column. Example of a 4-column CSV:
 
* Alternately when performing a CSV import, any columns that have line breaks or delimiters within the content can be contained within double quotes. NOTE: Any double quotes within that content MUST be escaped with a backslash \ in front of the double quote so they aren't treated as the closing quote of the column. Example of a 4-column CSV:
 +
 
  1st Record|Draft|"This content"|555-1212
 
  1st Record|Draft|"This content"|555-1212
 
  2nd Record|Submitted|"That content"|123-4567
 
  2nd Record|Submitted|"That content"|123-4567
Line 68: Line 66:
 
  6th Record|Submitted|"Normal Content again"|111-2233
 
  6th Record|Submitted|"Normal Content again"|111-2233
  
 +
* If the file has special characters, convert encoding to UTF-8.
 +
* If columns has a number sign after the comma, always add double quotes to avoid data problem when processing the file.
 +
 +
'''Note:'''
 +
 +
* The number of rows in the selected file cannot exceed 100,000
 +
* The number of columns in the selected file cannot exceed 100
 +
* File should not contain a cell that exceeds the character limit of 32,000
 +
 +
==Configuration - Essentials==
 +
These are the mandatory fields in creating a basic autoloader:
 +
 +
1. Essential settings in the '''General Tab'''
 +
 +
* Set the '''Loader Name'''
 +
* Select the '''Folder Type''': [[SmartFolders Overview|SmartFolder]] or [[SmartFolders_Overview#Configuration_Folders|Configuration Folder]]
 +
* Click the Folder Lookup button and select the folder assigned to this autoloader
 +
* Select the '''File Type'''
 +
* Select the '''File Encoding'''
 +
* Define the '''Record Type''' for this autoloader
 +
* Select '''Item''' of the '''Record Type'''
 +
* Activate the autoloader
 +
 +
2. Essential settings in the '''File Layout Tab'''
 +
 +
* Select the '''Number of Columns''' - based on the file sample to be uploaded into this autoloader
 +
* '''Ignore Lines''' should be 1, if the file has header in row 1
 +
* Select '''Field Delimiter''' from the dropdown list
 +
* Define the column mapping based on the field columns of the file to the '''Record Type''' fields from the dropdown.<br />Note that you can toggle the identifier if the file has a unique identifier. This will ensure integrity of the data being added/updated.
 +
 +
3. Essential settings in the '''Process Tab'''
 +
 +
* Go to the '''Create / Update Options''' section and select '''Create / Update Behaviour''' for the autoloader.<br />Once all three tabs have been configured, click on '''SAVE''' button. The autoloader is now ready to process the file that will be fed to the [[SmartFolders Overview|SmartFolder]] or [[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] selected for this autoloader.
 +
 +
==Configuration - Advanced==
 +
===Column Mapping===
 +
Once you have defined the number of fields, you define the field order to match the column order in the upload file.
 +
 +
: [[Image:Auto9.png|thumb|none|900px]]
 +
* The '''number of columns''' you select determines the number of fields you can map.
 +
* '''Identifier'''. Enable the column that you have identified as the identifier.  This is important when you are updating existing records. Single/multiple field(s) is/are required to match an uploaded record to an existing record within the system.  
 +
* The''' Table header''' field is only required if you are using an XML or JSON file. You can fill out this section with the column headers of the file you want to upload.
 +
* '''Field '''is selected thru a dropdown box. (see the Reference section below for information on standard field names)
 +
 +
 +
<span style="font-size: medium;">'''IMPORTANT:''' </span><br />The following fields must only be included in the Autoloader field mappings if you are updating '''existing''' records. These fields '''must not''' be used when using the Auto Loader to create new records.
 +
 +
::* '''Company ID''' (Company Auto Loader)
 +
::* '''User ID''' (Contact Auto Loader)
 +
::* '''Resource ID''' (Resource Auto Loader)
 +
::* '''Tracking ID''' ({{l2}} / {{l3}} Auto Loader)
 +
 +
 +
* When using the fields above the IDs being Auto Loaded must match IDs of records present within the system.
 +
* If used the fields should be marked as the Identifier.
 +
* These fields would normally be used if you have extracted data from [[SmartSimple]] including those ID fields, updated the data outside of [[SmartSimple]] and wish to import the updated information.
 +
 +
 +
'''NOTE:'''  
 +
 +
1. If you wish to deactivate a user using the Autoloader then select the '''Disable User Login (use value of 1)''' Field option and ensure that the uploaded file has a value of 1 in the relevant cells.
 +
 +
2. If the Item or App Level option is set to one of the Association options then you can only have Associations ID as identifier. The system we will force the Identifier when Associations ID is selected in mapping, and will only alert them when they try to set any other columns as identifier.
 +
 +
===Manual/Automated Autoloader===
 +
====Triggered by sending a file to the SmartFolder/Configuration Folder====
 +
====Automated Schedule====
 +
Here is a more detailed look at the automated [[Scheduler]].
 +
 +
===Decryption===
 +
<!-- 128992 - PGP encryption keys added in the Integration Key Management-->
 +
PGP decryption is available in the autoloader and occurs when the file is picked up for processing.
 +
PGP encryption and decryption keys should be added in the [[Integration Key Management|Integration_Key_Management]] section.
 +
To configure this setting, go to the autoloader configuration page and under the General tab go to the ''Decryption'' section, select the desired PGP decryption key to use before the autoloader processes the file.
 +
===Uploading Specific Files to Specific Records in Bulk===
 +
The autoloader also allows you to upload specific files to specific records in bulk. Previously, if you had several files across multiple records that needed to be updated within a specific UTA, user, or company, finding each record and manually uploading those files would be tedious, especially if the files needed to be updated on a regular cycle. Now you can upload all the files in bulk and the system will unpack and distribute those files to the fields you specified accordingly.
 +
 +
To use this feature, follow these steps:
 +
 +
# Create a CSV file containing mandatory columns ('''File Name''' and '''Custom Field ID''') and the relevant identifier columns for the record type.
 +
# Create a ZIP file that contains the files you want to upload or replace. Make sure the ZIP file and the CSV file have the same name.
 +
# Go to '''Menu Icon''' > '''Autoloaders''' > Create or edit an autoloader > Select the desired '''Processing Folder'''.
 +
# Set the '''File Type''' to “CSV, Text”.
 +
# Under '''Record Type''', select the desired record type you want to target ("UTA", "People", or "Company").
 +
# Under Item, select the new “File Upload” option. If you selected a UTA as a your '''Record Type''', you will be prompted to select the desired Level 1, 2, or 3.
 +
# Configure the column mappings as usual.
 +
# Upload the CSV and ZIP file to the processing folder you selected in Step 3.
 +
 +
<u>'''Note:'''</u> This new option does not create new records or update other columns. The “File Upload” item only uploads files.
 +
<!-- 143944 - New feature in autoloader to upload files -->
 +
[[File:2023-07-ticket-143944-1.png|thumb|none|800px|Use the new "File Upload" option to have the autoloader unpack a ZIP file and upload/replace files to specific upload fields as specified in a CSV file.]]
 +
 +
[[File:2023-07-ticket-143944-2.png|thumb|none|800px|The CSV file must have the same name as the ZIP file and it must specify the record ID, upload field ID, and file name.]]
 +
 +
===External Data Source===
 +
This section allows for the configuration of an external file source if the user does not want to manually upload the file.
 +
 +
[[Image:Autoloader_DS1.png ]]
 +
 +
The '''External Data Source''' is a drop-down list with 5 options available:
 +
 +
Additional fields are exposed when one of these options is selected, with the exact number and type of fields dependent on the selected option.
 +
 +
====FTP and SFTP====
 +
[[Image:AutoFTP2.PNG]]
 +
 +
* '''Disable processing. Download file only''' - if checked then the system will only pull the file across into SmartSimple, but will not load the contents of the file
 +
* '''Delete File from Data Source After Downloaded''' - file will be deleted from the sftp site
 +
* '''Append Datetime Stamp to File Name''' - will append date and time when the file picked up from the sftp do not have a datestamp
 +
<!--see ticket 18426 - autoloader FTP settings-->
 +
* '''URL''' - the URL where the file(s) can be retrieved from. e.g. ''my.sftp.com'' or ''my.sftp.com:2222''
 +
* '''Login Name''' - username to allow access to the system
 +
* '''Login Password''' - password to allow access to the system
 +
* '''Use Private Key''' - enable this setting when using keys as credentials to authenticate
 +
* '''Private Key File''' - upload the private key file
 +
* '''Path''' - exact folder location on the system where the files can be found i.e. "/" for root directory or "/in" or "/out"
 +
* '''File Names''' - name of the files to be loaded via the Autoloader. e.g. HRFeed.csv, Payment_*.csv (Use '''*''' for wildcards in filenames. < br/>
 +
 +
Note that when settings are saved, a '''Verify Connection''' button will appear next to the Login Name Textbox. This button can be used to verify that the url, username, password are correct.
 +
 +
====HTTP====
 +
[[Image:AutoHTTP.png]]
 +
 +
* '''Disable processing. Download file only''' - if checked then the system will only pull the file across into SmartSimple, but will not load the contents of the file
 +
 +
<!--see ticket 18426 - autoloader FTP settings-->
 +
* '''URL''' - the URL where the file(s) can be retrieved from
 +
* '''File Names''' - name of the files to be loaded via the Autoloader
 +
 +
====SOAP====
 +
[[Image:AutoSOAP.png]]
 +
 +
* '''Disable processing. Download file only''' - if checked then the system will only pull the file across into SmartSimple, but will not load the contents of the file
 +
 +
<!--see ticket 18426 - autoloader FTP settings-->
 +
* '''URL''' - the URL where the file(s) can be retrieved from
 +
* '''Login Name''' - username to allow access to the system
 +
* '''Login Password''' - password to allow access to the system
 +
* '''Function''' - The function called by the Web Service call.
 +
* '''Request Body''' - The content of the Web Service message to be sent to the external service.
 +
* '''Callback''' - Method to sends the response back to the client.
 +
 +
====OData====
 +
<!--Upgrade May 2018 - Ticket#62753-->
 +
[[Image:External-OData1.PNG]]
 +
* '''OData Version''' - Version of OData available from the endpoint
 +
* '''URL''' - the endpoint of OData
 +
* '''Login Name''' - username to allow access to the system
 +
* '''Login Password''' - password to allow access to the system
 +
 +
===File Preparation===
 +
This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.
 +
 +
It is only available when the '''File Type''' is either '''CSV,Text''' or '''Fixed Length Text'''.
 +
 +
It is not available when '''File Type''' is XML.
  
−
===Autoloader Constraints===
+
See [[Autoloader File Preparation]] for configuration instructions.
−
* You must be a {{GUA}} in your [[instance]] of SmartSimple to access the Autoloader feature and set up an upload process.
 
−
* The user uploading the file must have write permissions to the folder where the file is to be stored.
 
−
* You '''must''' not use the upload folder for any purpose other than uploading files, as the autoloader will attempt to process any file added to the folder.
 
  
−
==Instructions==
+
[[Image:AutoFilePreparation.png]]
  
 +
==Settings Explained==
 
This section describes the detailed steps required to use the Autoloader. Carefully examine, using a file editor, the file that will be uploaded.
 
This section describes the detailed steps required to use the Autoloader. Carefully examine, using a file editor, the file that will be uploaded.
  
 
Ensure that the file meets the file layout criteria outlined in the previous section:
 
Ensure that the file meets the file layout criteria outlined in the previous section:
 +
 
* Choose the Configuration, [[Global Settings]] menu.
 
* Choose the Configuration, [[Global Settings]] menu.
−
* Scroll to the end of the page.
+
* Stay on the System tab and scroll down the page.
−
:[[Image:Auto.png]]
 
 
* Click the '''Autoloader''' link.
 
* Click the '''Autoloader''' link.
 
* Click the New Autoloader tab.
 
* Click the New Autoloader tab.
 +
* Go thru each of the three tabs.
 +
 +
 +
==='''General''' tab===
 +
: [[File:Auto6b.PNG]]
 +
'''Setting the Autoloader Name'''
  
−
 
−
===Setting the Autoloader Name===
 
  
 
As you can define any number of autoloaders each autoloader process must be uniquely named.
 
As you can define any number of autoloaders each autoloader process must be uniquely named.
 +
 
* Set the name for this autoloader process.
 
* Set the name for this autoloader process.
−
:[[Image:Auto3.png]]
+
 
−
+
: [[File:Auto3.PNG|thumb|none|400px]]
−
===Setting the SmartFolder===
+
'''Setting the Folder Type'''
−
* Click the Folder Lookup button to display the [[SmartFolders Overview|SmartFolder]], list.
+
 
 +
* Select Folder Type: [[SmartFolders Overview|SmartFolder]]  or [[SmartFolders_Overview#Configuration_Folders|Configuration Folder]]
 +
* Click the Folder Lookup button to display the list.
 
* Select the folder where you plan to add the file.
 
* Select the folder where you plan to add the file.
−
:[[Image:Auto4.png]]
 
  
 +
: [[Image:Auto4.png|thumb|none|400px]]
 
The folder name will be added to the Folder box.
 
The folder name will be added to the Folder box.
  
−
===Setting the File Properties===
 
  
−
The File Type can be a CSV,Text file, an XML file or Fixed Length.
+
'''Setting the File Properties'''
  
−
The File Encoding can be UTF-8, UTF-16 or ANSI. This option is hidden if the File Type is XML.
 
−
:[[Image:AutoFile.png]]
 
−
 
−
===Defining the Record Type===
 
  
−
The Record type can be Company, Contact, Resource or a [[Universal Tracking Application]].
+
The File Type can be a CSV,Text file, an XML file, Fixed Length, or JSON data.
 +
 
 +
The File Encoding can be UTF-8, UTF-16 or ANSI. This option is hidden if the File Type is XML.
  
−
If the selected Record type is Company or Contact then the user is required to populate an additional Item field. The possible options here can include Company or Contact, Event and Transctions.
+
: [[Image:AutoFile.png|thumb|none|400px]]
 +
'''Defining the Record Type'''
  
−
If the selected Record type is a [[UTA]] then the user is required to populate an additional Level field. The possible options here can include Level 1, Level 2, Level 3 and Transctions.
 
  
−
:[[Image:Auto5.png]]
+
The Record type can be Company, Contact, Resource or a [[Universal Tracking Application]].
  
−
If the selected Record type is a [[UTA]] and the selected Level is Transaction then the user is required to populate an additional drop-down field reflecting all the levels that the transactions are enabled against.
+
If the selected Record type is Company or Contact then the user is required to populate an additional Item field. The possible options here can include Company or Contact, Event,Transactions, Address and Associations (User or Company) .
−
 
−
:[[Image:Auto5b.png]]
 
  
−
==Additional Settings==
+
If the selected Record type is a [[UTA]] then the user is required to populate an additional Level field. The possible options here can include Level 1, Level 2, Level 3, Transactions, L1 Company Association, L1 Contact Association, L2 Company Association and L2 Contact Association.
  
−
You must set the following additional settings:
+
: [[Image:Auto5.png|thumb|none|400px]]
−
:[[Image:Auto6.png]]
+
If the selected Record type is a [[UTA]] and the selected Level is Transaction then the user is required to populate an additional drop-down field reflecting all the levels that the transactions are enabled against.
−
* '''Activate''' – enables the Autoloader.  Until this check box is enabled the Autoloader will not execute.
 
−
* '''Trigger Formulas''' – enables the triggering of any template formulas that may exist for the selected record type and item/level.  If this check box is not enabled then these formulas will not execute.
 
−
* '''Delete First (use with caution)''' – all existing data will be deleted from the [[UTA]] level selected ('''This setting only applies to UTA data''').
 
−
::* '''IMPORTANT''': Delete First is only used to delete UTA data where the data will be completely replaced with uploaded data. '''Use with caution'
 
−
* '''Email Log To''' – Sets the email address of the person that you wish to receive a message every time the autoloader executes with a report of the upload process.  This report is also stored in the same folder as the uploaded file.
 
−
* '''Frequency''' – drop-down list that allows the user to select when the autoloader should be run. Options are '''On File Upload''' or '''Scheduled'''.
 
−
* '''Launch Hour''' - only displayed if '''Frequency''' is set to '''Scheduled'''. Sets the time on a daily basis that the autoloader will execute. This should be a comma delimited list of the hour values in 24-hour format.
 
−
* '''Workflow before loader starts''' – drop-down list that contains a list of all workflows in the system. It defaults to No and if users wants to have a workflow execute before the autoloader adds the data to the system they should select the relevant workflow.
 
−
* '''Workflow after loader ends''' – drop-down list that contains a list of all workflows in the system. It defaults to No and if users wants to have a workflow execute after the autoloader adds the data to the system they should select the relevant workflow.
 
  
 +
: [[Image:Auto5b.png|thumb|none|400px]]
 +
'''Activate Autoloader'''
  
−
===New Record===
 
  
−
The New Record section of the Autoloader is used to determine how new records in the upload file should be handled.
+
Enable this check box for the Autoloader to execute.
  
−
The exact fields that are displayed vary depending on the '''Record Type''' and '''Item''' or '''List''' values previously selected.
 
  
−
====Company Record / Company Item====
+
'''External Data Source'''
−
:[[Image:Auto7.png]]
 
−
* '''Create''' - If you wish to create new record, then enable the Create check box.  If you do not enable this check box and new records are located in the upload file they will be rejected.
 
−
* '''Account''' – determines where in the organisational hierarchy new records should be added.
 
−
* '''Is External''' – choose this option when creating external companies under the root company.
 
−
* '''Category''' – determines the organisational category that you wish to be associated with the new records.
 
−
* '''Trigger Workflow''' – drop-down list of all workflows related to the '''Record Type'''. It defaults to N/A.
 
−
* ''' Trigger On All Records ''' - check-box that is populated is users wants to have a workflow execute against all records, whether new records or updated records.
 
  
  
−
====Contact Record / Contact Item====
+
Autoloader can be connected an external data source to pull the file and move the file to the SmartFolder.
−
:[[Image:Auto7b.png]]
 
−
* '''Create''' - If you wish to create new record, then enable the Create check box.  If you do not enable this check box and new records are located in the upload file they will be rejected.
 
−
* '''Account''' – determines where in the organisational hierarchy new records should be added.
 
−
* '''Role''' – determines the organisational category that you wish to be associated with the new records.
 
−
* '''Trigger Workflow''' – drop-down list that of all workflows related to the '''Record Type'''. It defaults to N/A.
 
−
* ''' Trigger On All Records ''' - check-box that is populated is users wants to have a workflow execute against all records, whether new records or updated records.
 
  
 +
: [[File:Connectiontypes.PNG|thumb|none|400px]]
 +
Select a connection type and complete the ''Schedule'' section below to specify the launch times of the autoloader everyday.
  
−
====Company or Contact Record / Event or Transaction Item====
 
−
:[[Image:Auto7c.png]]
 
−
* '''Create''' - If you wish to create new record, then enable the Create check box.  If you do not enable this check box and new records are located in the upload file they will be rejected.
 
−
* '''Type''' – drop-down list of all Event or Transaction types, depending on the exact '''Item''' selected. It defaults to N/A
 
−
* '''Status''' – drop-down list of all Event or Transaction statuses, depending on the exact '''Item''' selected. It defaults to N/A
 
−
* '''Trigger Workflow''' – drop-down list that of all workflows related to the '''Record Type'''. It defaults to N/A.
 
−
* ''' Trigger On All Records ''' - check-box that is populated is users wants to have a workflow execute against all records, whether new records or updated records.
 
  
  
−
====UTA Record / all Levels ====
+
'''File Layout''' Tab
−
:[[Image:Auto7d.png]]
 
−
* '''Create''' - If you wish to create new record, then enable the Create check box.  If you do not enable this check box and new records are located in the upload file they will be rejected.
 
−
* '''Type''' – drop-down list of all Level 1, Level 2, Level 3 or Transaction types, depending on the exact '''Level''' selected. It defaults to N/A
 
−
* '''Status''' – drop-down list of all Level 1, Level 2, Level 3 or Transaction statuses, depending on the exact '''Item''' selected. It defaults to N/A
 
  
 +
The File Layout section is used to determine the number of columns in the file and the Field Delimiter.
  
 +
: [[Image:Auto8.png]]
 +
* '''Number of Columns''' - The number of columns you select is used to define the number of columns in the upload file, and the number of fields that can be updated in the database.
 +
** The maximum number of fields that can be updated is 100 fields in a single file.
 +
** If more than 100 fields are required, then you will need to create two upload files.
 +
* '''Ignore Lines''' - You can specify the number of rows to ignore from the beginning of the file. This can be used to ignore column headers, if they exist as the first row in any file.
 +
* '''Field Delimiter''' - drop-down list showing the delimiter options used to separate different values in the file into different columns. Will not be presented when the File Type is XML or JSON.
 +
* '''Record Node Name''' - Only presented when the File Type is XML.
 +
* '''Record Node Filter''' - Only presented when the File Type is XML.
  
−
===File Layout===
+
==='''Process''' tab===
 +
: [[Image:Auto6.png]]
 +
* '''Workflow before loader starts''' – drop-down list that contains a list of all workflows in the system. It defaults to No and if users wants to have a workflow execute before the Autoloader adds the data to the system they should select the relevant workflow.
 +
* '''Workflow after loader ends''' – drop-down list that contains a list of all workflows in the system. It defaults to No and if users wants to have a workflow execute after the Autoloader adds the data to the system they should select the relevant workflow.
 +
* '''Run report after loader ends '''- drop-down list that contains a list of all reports in the system.  It will ran the configured '''Scheduled Export''' of the report.''' Frequency''' on the report should bet set to '''On Demand'''.
 +
* '''Run sequencial Autoloader after loader ends''' – [[Linking Autoloaders]]
 +
* '''Trigger Formulas''' – enables the triggering of any template formulas that may exist for the selected record type and item/level. If this check box is not enabled then these formulas will not execute.
 +
* '''Send email with log when Autoloader is done''' – Sends email every time the Autoloader executes with a report of the upload process.(Note: The log file is also stored in the same folder as the uploaded file.)
 +
* '''Send email notification when Autoloader failed to complete''' – Sends email when ftp/sftp connection failed, or when autoloader process was terminated unexpectedly.
 +
* '''Send email with log when Autoloader has any failed records '''- Sends email when autoloader fails to complete.
 +
* '''Email To''' – Sets the email address who will receive a message. Multiple email addresses can be included separated by commas.
  
−
The File Layout section is used to determine the number of columns in the file, the delimiter between the columns and the use of a '''HTML table''' to display the data.
 
−
:[[Image:Auto8.png]]
 
−
* Number of Column - The number of columns you select is used to define the number of columns in the upload file, and the number of fields that can be updated in the database.
 
−
:* The maximum number of fields that can be updated is 100 fields in a single file.
 
−
:* If more than 100 fields are required, then you will need to create two upload files.
 
−
* Ignore Lines - You can specify the number of rows to ignore from the beginning of the file. This can be used to ignore column headers, if they exist as the first row in any file.
 
−
* Field Delimiter - drop-down list showing the delimiter options used to separate different values in the file into different columns. Will not be presented when the File Type is XML.
 
−
* Use HTML Table - An HTML table is very useful where imported data contains multiple records and a single record set of fields exists within SmartSimple. For example, if you wished to import multiple lines of sales information into a single text box field you would enable the HTML table feature and then map all the fields into a single field indicating the column and row delimiters. Will not be presented when the File Type is XML.
 
−
* Column Delimiter - free text field to allow entry of any delimiters required to separate different values in the file into different columns. Will not be presented when the File Type is XML. 
 
−
* Row Delimiter - free text field to allow entry of any delimiters required to separate different values in the file into different rows. Will not be presented when the File Type is XML.
 
−
* Record Node Name - Only presented when the File Type is XML.
 
−
* Record Node Filter - Only presented when the File Type is XML.
 
  
  
 +
====Create Options====
 +
This section of the Autoloader is used to determine how new records in the upload file should be handled.
  
−
===Defining the Column Mapping===
+
The exact fields that are displayed vary depending on the '''Record Type''' and '''Item''' or '''List''' values previously selected.
  
−
Once you have defined the number of fields, you define the field order to match the column order in the upload file.
+
=====Company Record / Company Item=====
−
:[[Image:Auto9.png]]
+
: [[Image:Auto7.png]]
−
* The number of columns you select determines the number of fields you can map.
+
* '''Create/Update Behavior''' - If you wish to create new record, then enable the '''Create new or update matching record''''' check box. If you enable the other check box '''''Update only, do not create new''', existing records will be updated but new records will be rejected.
−
* The field name is selected from the Field combo box.
+
* '''Duplicate Match Behavior''' - behavior when multiple records are matched by the same identifier key. Drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).
−
* In order for the update to succeed, a single field is required to match an upload record to an existing database record this is indicated as the Identifier field.  Customer data will generally be an account number type field.
+
** Update Unique Match Only - will only update when one matching record is found
−
* The Table header field is only required if you are using the HTML Table feature or when loading an XML file.
+
** Update All Matches - will update all matching records
−
* The '''Duplicate Match Beahviour''' drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).  
+
* '''Account''' – determines where in the organisational hierarchy new records should be added.
−
:* Update Unique Match Only - will only update when one matching record is found
+
* '''Is External''' – choose this option when creating external companies under the root company.
−
:* Update All Matches - will update all matching records  
+
* '''Category''' – determines the organisational category that you wish to be associated with the new records.
 +
* '''Trigger Workflow''' – drop-down list of all workflows related to the '''Record Type'''. It defaults to N/A.
 +
* ''' Trigger On All Records ''' - check-box that is populated is you wish to have a workflow execute against all records, whether new records or updated records. This can be used to create companies with multiple categories.
  
−
<font size="3">'''IMPORTANT:''' </font><br>
+
=====Contact Record / Contact Item=====
−
The following fields must only be included in the Autoloader field mappings if you are updating '''existing''' records. These fields '''must not''' be used when using the Auto Loader to create new records.
+
: [[Image:Auto7b.png]]
−
::* '''Company ID''' (Company Auto Loader)
+
* '''Create/Update Behavior''' - If you wish to create new record, then enable the '''Create new or update Matching record''' check box. If you do not enable the other check box '''Update only, do not create new''' existing records will be updated but new records will be rejected.
−
::* '''User ID''' (Contact Auto Loader)
+
* '''Duplicate Match Behavior''' - behavior when multiple records are matched by the same identifier key.
−
::* '''Resource ID''' (Resource Auto Loader)
+
* '''Account''' – determines where in the organisational hierarchy new records should be added.
−
::* '''Tracking ID''' ({{l2}} / {{l3}} Auto Loader)
+
* '''Role''' – determines a user role that you wish to be associated with the new records. If you wish to have multiple roles associated with a user then you will need to trigger a workflow against each record (see below for further details).
 +
* '''Trigger Workflow''' – drop-down list that of all workflows related to the '''Record Type'''. It defaults to N/A.
 +
* ''' Trigger On All Records ''' - check-box that is populated is users wants to have a workflow execute against all records, whether new records or updated records. This can be used to activate users with multiple roles and to e-mail a password to newly created contacts automatically.
  
−
* When using the fields above the IDs being Auto Loaded must match IDs of records present within the system.
+
=====Company or Contact Record / Event or Transaction Item=====
−
* If used the fields should be marked as the Identifier.
+
: [[Image:Auto7c.png]]
−
* These fields would normally be used if you have extracted data from [[SmartSimple]] including those ID fields, updated the data outside of [[SmartSimple]] and wish to import the updated information.
+
* '''Create/Update Behavior''' - If you wish to create new record, then enable the '''Create new or update Matching record''' check box. If you do not enable the other check box '''Update only, do not create new''' existing records will be updated but new records will be rejected.
 +
* '''Duplicate Match Behavior''' - behavior when multiple records are matched by the same identifier key. Drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).
 +
** Update Unique Match Only - will only update when one matching record is found
 +
** Update All Matches - will update all matching records
 +
* '''Type''' – drop-down list of all Event or Transaction types, depending on the exact '''Item''' selected. It defaults to N/A
 +
* '''Status''' – drop-down list of all Event or Transaction statuses, depending on the exact '''Item''' selected. It defaults to N/A
 +
* '''Trigger Workflow''' – drop-down list that of all workflows related to the '''Record Type'''. It defaults to N/A.
 +
* ''' Trigger On All Records ''' - check-box that is populated if users wants to have a workflow execute against all records, whether new records or updated records.
  
−
==Updating Account Owners==
+
=====UTA Record / all Levels=====
 +
: [[Image:AutoloaderNewRecordUTA.png|link=]]
 +
* '''Create/Update Behavior''' - If you wish to create new record, then enable the '''Create new or update Matching record''' check box. If you do not enable the other check box '''Update only, do not create new''' existing records will be updated but new records will be rejected.
 +
* '''Duplicate Match Behavior''' - behavior when multiple records are matched by the same identifier key. Drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).
 +
** Update Unique Match Only - will only update when one matching record is found
 +
** Update All Matches - will update all matching records
 +
* '''Type''' – drop-down list of all Level 1, Level 2, Level 3 or Transaction types, depending on the exact '''Level''' selected. It defaults to N/A
 +
* '''Status''' – drop-down list of all Level 1, Level 2, Level 3 or Transaction statuses, depending on the exact '''Item''' selected. It defaults to N/A
 +
* '''Trigger Workflow''' – drop-down list of all workflows related to the '''Record Type'''. It defaults to N/A.
 +
* ''' Trigger On All Records ''' - check-box that is populated if you wish to have a workflow execute against all records, whether new records or updated records.
  
−
When you are updating company type information you can automatically associate an account field to a user field in order to establish “account ownership”. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and Item to company.
+
====Updating Account Owners====
−
:[[Image:Auto10a.png]]
+
When you are updating company type information you can automatically associate an account field to a user field in order to establish “account ownership”. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and Item to company.
−
:''For example, if the imported account file contains an employee ID field, then this can be matched to the user table – providing each user with an employee ID field.''
 
  
 +
[[Image:Auto10a.png]]<br />
 +
''For example, if the imported account file contains an employee ID field, then this can be matched to the user table – providing each user with an employee ID field.''
  
−
==Updating Account Parent==
+
====Updating Account Parent====
 +
When you are updating company type information you can automatically associate an account field to a parent account field in order to establish “company hierarchy”. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and Item to company.
  
−
When you are updating company type information you can automatically associate an account field to a parent account field in order to establish “company hierarchy”.  This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and Item to company.
+
[[Image:Auto10b.png]]<br />
−
:[[Image:Auto10b.png]]
+
''For example, if the imported account file contains a parent ID field, then this can be matched to the account table – providing each company with a parent company.''<br />
−
:''For example, if the imported account file contains a parent ID field, then this can be matched to the account table – providing each company with a parent company.''
+
'''Example: Updating Account Parent AutoLoader Configuration:'''<br />
 +
[[File:Update_Account_Parent_Example.JPG]]<br />
 +
'''The AutoLoader Parent ID field is an Account field that will get populated with the second value (Parent ID) from the CSV record below.'''<br />
 +
'''Example: AutoLoader Source File:'''<br />
 +
[[File:Update_Account_Parent_Example_-_Source_File.JPG]]<br />
 +
'''Action: A two column report with the Company ID and the Parent ID needs to be created, exported to a CSV file and uploaded to the associated AutoLoader SmartFolder. '''<br />
 +
Result: The Account records with a Company ID IN (278520,297733,316703) will have their Parent ID changed to 349238.
  
 +
====Updating Event / Transaction Parent Company====
 +
When you are updating company type information related to an Event or a Transaction you can automatically associate an account field to a transaction or event field in order to establish a parent for the transaction. This includes using Account standard fields.
 +
If no matches exist then you can select the required action using the '''No parent matched action''' drop-down.
  
−
==Updating Event / Transaction Parent Company ==
+
* Reject Record - record is not loaded into the system
 +
* Add as orphan record - record is loaded into the system but without being attached to a parent company
 +
* Attach to dummy account - record is loaded into the system and is attached to the parent company specified in the '''Default Parent ID''' field.
  
−
When you are updating company type related to a Transaction information you can automatically associate an account field to a transaction field in order to establish a parent for the transaction. 
 
−
If no matches exist then you can select the required action using the '''No parent matched action''' drop-down.
 
−
:* Reject Record - record is not loaded into the system
 
−
:* Add as orphan record - record is loaded into the system but without being attached to a parent company
 
−
:* Attach to dummy account - record is loaded into the system and is attached to the parent company specified in the '''Default Parent ID''' field.
 
  
 
This section is only displayed at the bottom of the Autoloader page if you have set the '''Record Type''' to Company and the '''Item''' to either Event or Transaction.
 
This section is only displayed at the bottom of the Autoloader page if you have set the '''Record Type''' to Company and the '''Item''' to either Event or Transaction.
−
:[[Image:Auto10c.png]]
 
  
  
−
==Updating Parent Company==
+
[[Image:Auto10c.png]]
  
−
When you are updating people type information you can automatically associate an account field to a user field in order to establish “account ownership”. This section is only displayed at the bottom of the Autoloader page if you have set the Type to People.
+
====Updating Parent Company====
−
:[[Image:Auto10f.png]]
+
When you are updating people type information you can automatically associate an account field to a user field in order to attach the user to the correct account. This section is only displayed at the bottom of the Autoloader page if you have set the Type to People.
  
 +
[[Image:Auto10f.png]]
  
−
==Updating Event / Transaction Parent User ==
+
====Updating Event / Transaction Parent User====
−
 
+
When you are updating user type related to a Transaction information you can automatically associate a user field to a transaction field in order to establish a parent user for the transaction.
−
When you are updating user type related to a Transaction information you can automatically associate a user field to a transaction field in order to establish a parent user for the transaction.
 
 
If no matches exist then you can select the required action using the '''No parent matched action''' drop-down.
 
If no matches exist then you can select the required action using the '''No parent matched action''' drop-down.
−
:* Reject Record - record is not loaded into the system
 
−
:* Add as orphan record - record is loaded into the system but without being attached to a parent company
 
−
:* Attach to dummy account - record is loaded into the system and is attached to the parent company specified in the '''Default Parent ID''' field.
 
  
−
This section is only displayed at the bottom of the Autoloader page if you have set the '''Record Type''' to People and the '''Item''' to either Event or Transaction.
+
* Reject Record - record is not loaded into the system
−
:[[Image:Auto10d.png]]
+
* Add as orphan record - record is loaded into the system but without being attached to a parent company
 +
* Attach to dummy account - record is loaded into the system and is attached to the parent company specified in the '''Default Parent ID''' field.
  
  
−
==Branch, Company and Owner Custom Field Matching==
+
This section is only displayed at the bottom of the Autoloader page if you have set the '''Record Type''' to People and the '''Item''' to either Event or Transaction.
−
 
 
−
When you are updating UTA information you can automatically associate a standard field on the UTA with user and company fields in order to establish  individual or organizational ownership.  This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to [[UTA]] and the Level to Level 1.
 
  
−
:[[Image:Auto10e.png]]
+
[[Image:Auto10f.png]]
  
 +
====Branch, Company and Owner Custom Field Matching====
 +
When you are updating UTA information you can automatically associate a standard field on the UTA with user and company fields in order to establish individual or organizational ownership. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to [[UTA]] and the Level to Level 1.
  
−
==Updating Tracking Parent==
+
[[Image:Auto10e.png]]
  
−
When you are updating UTA Level 2 or 3 information you can automatically associate a field on the level 2 with a field on the level 1, or associate a field on the level 3 with a field on the level 1, in order to establish a relationship between the entities.  
+
====Updating Tracking Parent====
 +
When you are updating UTA Level 2 or 3 information you can automatically associate a field on the level 2 with a field on the level 1, or associate a field on the level 3 with a field on the level 1, in order to establish a relationship between the entities.
  
 
If no matches exist then you can select the required action using the No parent matched action drop-down.
 
If no matches exist then you can select the required action using the No parent matched action drop-down.
Line 276: Line 434:
 
* Reject Record - record is not loaded into the system
 
* Reject Record - record is not loaded into the system
 
* Add as orphan record - record is loaded into the system but without being attached to a parent company
 
* Add as orphan record - record is loaded into the system but without being attached to a parent company
−
* Attach to dummy account - record is loaded into the system and is attached to the parent entity specified in the Default Parent ID field.  
+
* Attach to dummy account - record is loaded into the system and is attached to the parent entity specified in the Default Parent ID field.
−
 
 
 
This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to [[UTA]] and the Level to Level 2 or Level 3.
 
This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to [[UTA]] and the Level to Level 2 or Level 3.
  
−
:[[Image:Auto10g.png]]
+
[[Image:Auto10g.png]]
  
−
 
+
====Parent record match field ids====
−
==Parent record match field ids==
+
This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.
−
 
 
−
This section allows for manipulation of the data prior to the autolaoder actually loading the data onto the system.
 
  
 
It is only available when the '''File Type''' is '''XML'''.
 
It is only available when the '''File Type''' is '''XML'''.
  
−
:[[Image:AutoXML.png]]
+
[[Image:AutoXML.png]]
  
 +
====Ad hoc upload url====
 +
This is an Autoloader setting that provides a more friendly and accessible user interface for importing data into the system as well as allowing users to verify the file before uploading it to a SmartFolder for processing by the Autoloader. This feature is intended to be used when External Data Source is not configured.
  
−
==File Preparation==
+
[[Image:AdhocAutoload.png|100.px|border]]
  
−
This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.
+
==='''History''' Tab===
 +
<span style="color: #ff0000;">Available after February 2019 upgrade.</span> <br/>
 +
A new Autoloader feature that shows a new History information tab. The list includes the file name, user who uploaded the file, start time, end time, records added/updated and status of last run.
  
−
It is only available when the '''File Type''' is either '''CSV,Text''' or '''Fixed Length Text'''.
+
[[Image:Autoloader-History.PNG]]
−
 
 
−
It is not available when '''File Type''' is XML.
 
  
−
:[[Image:AutoFilePreparation.png]]
+
* '''#''' - item number
−
 
+
* '''File''' - filename that was processed. If the file is deleted from the SmartFolder, the filename will not be displayed on the list.
−
 
+
* '''Uploaded By''' - name of the user who uploaded the file
−
==External Data Source==
+
* '''Start Time''' - date and time the file was processed
−
 
+
* '''End Time''' - date and time the file was completed
−
This section allows for the configuration of an external file source if the user does not want to manually upload the file.
+
* '''Added''' - number of records that were added
 +
* '''Updated''' - number of records that were updated
 +
* '''Failed''' - number of records that failed to update/add
 +
* '''Status''' - status of the process.   
 +
** '''''Processing''''' - the file is currently being processed and is not yet complete
 +
** '''''Completed''''' - the file was successfully processed and was completed
 +
** '''''Interrupted''''' - the process was terminated i.e. GORT was terminated abruptly (someone restarted the server)
 +
** '''''Data Problem''''' - the file has data problem i.e. pound sign at the start of the column . data with # should be enclosed in double quotes e.g. ,"another column","#111 Peter Street","another column",
 +
** '''''Connection Failed''''' - external data source connection failed
 +
** '''''No file / download file failed''''' - connection was established but no file was existing in the external data source subfolder
 +
** '''''File problem''''' 
 +
** '''''File failed pre-processing''''' 
 +
** '''''Error processing file''''' 
 +
** '''''Wrong file type''''' 
 +
* '''Process ID''' - unique identifier of the process
  
−
:[[Image:AutoDataSource.png]]
+
==Example autoloader configurations==
 +
===Organization autoloader===
 +
===HR Feed autoloader===
 +
===UTA L1 autoloader===
 +
===Example how to use '''''ad hoc upload url'''''===
 +
When you click on the Upload File button then you will be presented with page one of the Ad Hoc Autoloader screen. Use the 'Choose file' button to select the file to be loaded and then click the 'Upload' button.
  
−
The '''External Data Source''' is a drop-down list with 4 options available:
+
[[Image:AdhocAutoload1.png|100.px|border]]
−
:* FTP
 
−
:* HTTP
 
−
:* SFTP
 
−
:* SOAP
 
  
−
Additional fields are exposed when one of these options is selected, with the exact number and type of fields dependent on the selected option.  
+
You will be presented with page 2 of the Ad Hoc Autoloader screen, which provides users the option to review the file being uploaded, including what data us being mapped to what field.
  
−
===FTP and SFTP===
+
[[Image:AdhocAutoload2.png|100.px|border]]
  
−
:[[Image:AutoFTP.png]]
+
If there is no issue with the data and the mappings then click on the 'Add to SmartFolder and Autolader queue' button. You should be presented with page 3 of the Ad Hoc Autoloader screen, which confirms that the file has been successfully added to the SmartFolder.
  
−
* Disable processing. Download file only - if checked then the system will only pull the file across into SmartSimple, but will load the contents of the file
+
[[Image:AdhocAutoload3.png|100.px|border]]
−
* URL - the URL where the file(s) can be retrieved from
 
−
* Login Name -  username to allow access to the system
 
−
* Login Password -  password to allow access to the system
 
−
* Path -  exact folder location on the system where the files can be found
 
−
* File Names - name of the files to be loaded via the Autoloader
 
−
 
  
−
===HTP===  
+
==Other considerations==
 +
===Important note about XML Files===
 +
If the contents of any the elements in an XML file being autoloaded contains certain special characters it will disrupt the XML structure, making the message unreadable by the receiving server. The '''less than''' symbol (<) and '''ampersand''' (&) are two primary special characters to be concerned about. When including fields that may contain those characters you should use a special wrapper so the receiving understands that they are data content and not part of the XML structure:
  
−
:[[Image:AutoHTTP.png]]
+
*
 +
*: <span style="font-size: small;">&lt;![CDATA[ '''''CONTENTS''''' ]]></span>
  
−
* Disable processing. Download file only  - if checked then the system will only pull the file across into SmartSimple, but will load the contents of the file
+
For example, an '''address''' or '''company name''' could contain ampersands, so should be included as:
−
* URL - the URL where the file(s) can be retrieved from
 
−
* File Names - name of the files to be loaded via the Autoloader
 
  
 +
<pre>JaneSmith</pre><address> </address>
 +
* It is advisable to use CDATA for any field containing text since the contents are often unpredictable.
  
−
===SOAP===
+
===Note about JSON Files===
 +
JSON format should start with a '''data''' node, as shown in the example below
  
−
:[[Image:AutoSOAP.png]]
+
<pre>{
 +
"data":[{
 +
"Name":"Acme Corporation",
 +
"Phone":"416-123-4567",
 +
"CompanyID":"496246"
 +
},
 +
{
 +
"Name":"Super Banana Ltd.",
 +
"Phone":"800-555-1234",
 +
"CompanyID":"496244"
 +
},
 +
{
 +
"Name":"Red Apple Supply",
 +
"Phone":"917-123-0000",
 +
"CompanyID":"496248"
 +
},
 +
{
 +
"Name":"Orange you Glad",
 +
"Phone":"410-222-2030",
 +
"CompanyID":"496242"
 +
}
 +
]
 +
}
 +
</pre>
  
−
* Disable processing. Download file only - if checked then the system will only pull the file across into SmartSimple, but will load the contents of the file
+
===Reference: Standard Field Names and Variables===
−
* URL - the URL where the file(s) can be retrieved from
+
The below table can be used to compare Level 1 Standard Field terminology found in the [[UTA]], on the Autoloader, in the Database, and Variable syntax]]
−
* Login Name -  username to allow access to the system
 
−
* Login Password -  password to allow access to the system
 
−
* Function
 
−
* Request Body
 
−
* Callback
 
−
 
  
 +
{|
 +
|-
 +
| style="background-color: #c8c8c8;"|'''UTA L1 Standard Field Name '''
 +
| style="background-color: #c8c8c8;"|'''Autoloader Standard Field Name'''
 +
| style="background-color: #c8c8c8;"|'''Database name'''
 +
| style="background-color: #c8c8c8;"|'''Variable List'''
 +
|-
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"|Level 1 ID
 +
| style="background-color: #f0f0f0;"|opportunityid
 +
| style="background-color: #f0f0f0;"|@opportunityid@
 +
|-
 +
| style="background-color: #dcdcdc;"|Application Type
 +
| style="background-color: #dcdcdc;"|Type Name
 +
| style="background-color: #dcdcdc;"|opportunitytypename
 +
| style="background-color: #dcdcdc;"|@type@
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Type ID
 +
| style="background-color: #dcdcdc;"|opportunitytype
 +
| style="background-color: #dcdcdc;"|@typeid@
 +
|-
 +
| style="background-color: #f0f0f0;"|Application Name
 +
| style="background-color: #f0f0f0;"|Name
 +
| style="background-color: #f0f0f0;"|name
 +
| style="background-color: #f0f0f0;"|@name@
 +
|-
 +
| style="background-color: #dcdcdc;"|Customer
 +
| style="background-color: #dcdcdc;"|Client
 +
| style="background-color: #dcdcdc;"|company
 +
| style="background-color: #dcdcdc;"|@client@
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Client ID
 +
| style="background-color: #dcdcdc;"|companyid
 +
| style="background-color: #dcdcdc;"|@clientid@
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Client Custom Field
 +
| style="background-color: #dcdcdc;"|companycfield
 +
| style="background-color: #dcdcdc;"| 
 +
|-
 +
| style="background-color: #f0f0f0;"|Status
 +
| style="background-color: #f0f0f0;"|Status
 +
| style="background-color: #f0f0f0;"|stagename
 +
| style="background-color: #f0f0f0;"|@status@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Status ID
 +
| style="background-color: #f0f0f0;"|stageid
 +
| style="background-color: #f0f0f0;"|@stausid@
 +
|-
 +
| style="background-color: #dcdcdc;"|Revenue
 +
| style="background-color: #dcdcdc;"|Revenue
 +
| style="background-color: #dcdcdc;"|revenue
 +
| style="background-color: #dcdcdc;"|@revenue@
 +
|-
 +
| style="background-color: #f0f0f0;"|Currency
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"| 
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Currency (USD,CAD etc.)
 +
| style="background-color: #f0f0f0;"|currencyid
 +
| style="background-color: #f0f0f0;"|@currencyname@/ @currencycode@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Currency Exchange (USD,CAD etc.)
 +
| style="background-color: #f0f0f0;"|excur
 +
| style="background-color: #f0f0f0;"|@excurrencycode@/ @excurrencyname@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Exchange Rate
 +
| style="background-color: #f0f0f0;"|excur_rate
 +
| style="background-color: #f0f0f0;"|@exrate@
 +
|-
 +
| style="background-color: #dcdcdc;"|Close Date
 +
| style="background-color: #dcdcdc;"|Close Date (yyyy-mm-dd)
 +
| style="background-color: #dcdcdc;"|closedate
 +
| style="background-color: #dcdcdc;"|@closedate@
 +
|-
 +
| style="background-color: #f0f0f0;"|Probability
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|@probability@
 +
|-
 +
| style="background-color: #dcdcdc;"|Description
 +
| style="background-color: #dcdcdc;"|Description
 +
| style="background-color: #dcdcdc;"|description
 +
| style="background-color: #dcdcdc;"|@description@
 +
|-
 +
| style="background-color: #f0f0f0;"|Requirement
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|@requirements@
 +
|-
 +
| style="background-color: #dcdcdc;"|Owner
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"| 
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Owner ID
 +
| style="background-color: #dcdcdc;"|updatedby
 +
| style="background-color: #dcdcdc;"|@ownerid@
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Owner (firstname lastname)
 +
| style="background-color: #dcdcdc;"|updatedbyname
 +
| style="background-color: #dcdcdc;"|@owner@
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Owner Email
 +
| style="background-color: #dcdcdc;"|updatedbyemail
 +
| style="background-color: #dcdcdc;"|@owner.email@
 +
|-
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|Owner Custom Field
 +
| style="background-color: #dcdcdc;"|updatedbyfield
 +
| style="background-color: #dcdcdc;"| 
 +
|-
 +
| style="background-color: #f0f0f0;"|Branch
 +
| style="background-color: #f0f0f0;"|Branch
 +
| style="background-color: #f0f0f0;"|branch
 +
| style="background-color: #f0f0f0;"|@branch@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Branch ID
 +
| style="background-color: #f0f0f0;"|branchid
 +
| style="background-color: #f0f0f0;"|@branchid@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Branch Custom Field
 +
| style="background-color: #f0f0f0;"|branchcfield
 +
| style="background-color: #f0f0f0;"| 
 +
|-
 +
| style="background-color: #dcdcdc;"|Workflow
 +
| style="background-color: #dcdcdc;"|n/a
 +
| style="background-color: #dcdcdc;"|n/a
 +
| style="background-color: #dcdcdc;"|n/a
 +
|-
 +
| style="background-color: #f0f0f0;"|Contacts
 +
| style="background-color: #f0f0f0;"|n/a (added Import Contacts on UTA)
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"|n/a
 +
|-
 +
| style="background-color: #dcdcdc;"|Activity List
 +
| style="background-color: #dcdcdc;"|n/a (needs separate autoloader)
 +
| style="background-color: #dcdcdc;"|n/a
 +
| style="background-color: #dcdcdc;"|https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
 +
|-
 +
| style="background-color: #f0f0f0;"|End Date
 +
| style="background-color: #f0f0f0;"|End Date (yyyy-mm-dd)
 +
| style="background-color: #f0f0f0;"|oenddate
 +
| style="background-color: #f0f0f0;"|@enddate@
 +
|-
 +
| style="background-color: #dcdcdc;"|Start Date
 +
| style="background-color: #dcdcdc;"|Start Date (yyyy-mm-dd)
 +
| style="background-color: #dcdcdc;"|ostartdate
 +
| style="background-color: #dcdcdc;"|@startdate@
 +
|-
 +
| style="background-color: #f0f0f0;"|Modified Date
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|@modifieddate@
 +
|-
 +
| style="background-color: #dcdcdc;"|Institutions
 +
| style="background-color: #dcdcdc;"|n/a (needs a workflow)
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
 +
|-
 +
| style="background-color: #f0f0f0;"|End Time
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|@hour(fullenddate)@:@minute(fullenddate)@
 +
|-
 +
| style="background-color: #dcdcdc;"|Start Time
 +
| style="background-color: #dcdcdc;"|n/a
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|@hour(fullstartdate)@:@minute(fullstartdate)@
 +
|-
 +
| style="background-color: #f0f0f0;"|Person
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"| 
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Person ID
 +
| style="background-color: #f0f0f0;"|oppeopleid
 +
| style="background-color: #f0f0f0;"|@personid@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Person (firstname lastname)
 +
| style="background-color: #f0f0f0;"|oppeoplename
 +
| style="background-color: #f0f0f0;"|@person@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Person Email
 +
| style="background-color: #f0f0f0;"|oppeopleemail
 +
| style="background-color: #f0f0f0;"|@person.email@
 +
|-
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|Person Custom Field
 +
| style="background-color: #f0f0f0;"|oppeoplefield
 +
| style="background-color: #f0f0f0;"| 
 +
|-
 +
| style="background-color: #dcdcdc;"|UTA Providers
 +
| style="background-color: #dcdcdc;"|n/a (needs a workflow task)
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
 +
|-
 +
| style="background-color: #f0f0f0;"|Status Indicator
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|n/a
 +
|-
 +
| style="background-color: #dcdcdc;"|Transactions
 +
| style="background-color: #dcdcdc;"|n/a (needs separate autoloader)
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
 +
|-
 +
| style="background-color: #f0f0f0;"|UTA Providers (L2)
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
 +
|-
 +
| style="background-color: #dcdcdc;"|Modified By
 +
| style="background-color: #dcdcdc;"|n/a
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|@modifiedby@/ @modifiedbyid@
 +
|-
 +
| style="background-color: #f0f0f0;"|Created Date
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|@createddate@
 +
|-
 +
| style="background-color: #dcdcdc;"|Created By
 +
| style="background-color: #dcdcdc;"|n/a
 +
| style="background-color: #dcdcdc;"| 
 +
| style="background-color: #dcdcdc;"|n/a
 +
|-
 +
| style="background-color: #f0f0f0;"|Invitations
 +
| style="background-color: #f0f0f0;"|n/a
 +
| style="background-color: #f0f0f0;"| 
 +
| style="background-color: #f0f0f0;"|n/a
 +
|}
  
−
[[Category:Global Settings]][[Category:Integration]]
+
==See Also==
 +
* [[How to Add Users and Contacts]]
 +
* [[Autoloader File Preparation]]
 +
* [[Linking Autoloaders]]
 +
[[Category:Data Import]]
 +
[[Category:Global Settings]]

Latest revision as of 16:07, 4 September 2024

An Autoloader is a SmartSimple feature used to automatically import data related to companies, contacts or Universal Tracking Applications. This allows you the ability to automatically update information with data from external text files which can be vital if you need to keep customer or project data in sync with an existing legacy or enterprise resource planning (ERP) system.


Contents

Overview

The Autoloader feature provides the ability to automatically create/update SmartSimple information with data from an external file. A general overview of the process is as follows: 

  • Once a file is added to the SmartFolder/Configuration Folder, the Autoloader process will recognize that a file has been added and start the upload process.
  • Once the file has been processed, it will not be processed a second time unless a new file of the same name is added to the folder.
  • Each record from the uploaded file will update the associated record in the database.
  • A log file will be created to indicate the success or failure of each record update and an email can be sent to a designated user to notify them of the success (or failure) of the upload.
  • If a new version of the file is added to the folder, the new file will be processed.
  • An Autoloader can upload to different objects within your SmartSimple instance: companies, contacts, Universal Tracking Application Level 1 or Level 2, Level 3,, Notes, Associations.

Autoloader

Prerequisites

  • You must be a Global User Administrator in your instance of SmartSimple to access the Autoloader feature and set up an upload process.
  • The user uploading the file must have write permissions to the folder where the file is to be stored.
  • You must not use the upload folder for any purpose other than uploading files, as the autoloader will attempt to process any file added to the folder.

Creating a SmartFolder/Configuration Folder

  • Any SmartFolder/Configuration Folder can be used as the source for an Autoloader.
  • The folder must be used exclusively for the Autoloader, since every file uploaded will be processed.
  • Incorrect files or incorrectly formatted files will be processed and could lead to data loss and/or creation of invalid data.

IMPORTANT:  Because any file uploaded to a SmartFolder/Configuration Folder that is associated to an autoloader will be processed, it is extremely important that the SmartFolder/Configuration Folder is permissioned and labelled appropriately.

  • SmartFolder/Configuration Folder for Autoloaders should be permissioned such that only contacts that need to be able to upload files for autoloading are permitted to do so.
  • It is best practice to put all Autoloader enabled SmartFolder/Configuration Folder into a parent folder named Autoloader (with role-restricted access) to help ensure no inappropriate files are uploaded to the folder.
  • In creating a new Configuration Folder, specify "Autoloader/Export Storage" as Folder Type.

Preparing the file to upload into the autoloader

Define the format and layout of the data file - the number of columns in the upload file, the delimiter between each field and the field order.

  • You must be able to specify a unique field that will be used to link each record in the upload file to each record in the customer record. You cannot use a standard field such as customer name or phone number.
  • This common field needs to be present in each record on the upload file and in each customer record. For instance, a unique account number field that you add to the customer table can be linked to the same account number field in the upload data.
  • The file to be uploaded must be in ASCII format. There is no specific file naming convention.
  • Each record in the file must be separated by a CR/LF (ASCII 13,10 /D,A) character combination.
  • Each field must be delimited by one of the following characters:
Comma – “,” (ASCII 44/2C)
Tab – (ASCII 9/9)
Pipe – “|” (ASCII 179/B3)
Semi colon – “;” (ASCII 59/3B)
Colon – “:” (ASCII 58/3A)
This can be accomplished either using Notepad to create and edit the data for import which can be saved with txt as the file extension.
Alternately you can use the Save As feature within Microsoft Excel and save the document as a csv file.

Notes:

  • If any of the data to be imported contains commas and you are creating a csv file from Excel you should export the data using pipes (|) as the separators rather than commas. To do this you need to change the settings on your PC prior to creating the csv file. The method of doing this depends on your PCs Operating System, but is roughly outlined below:
Start -> Settings -> Control Panel -> Regional Settings -> Additional Settings Button.
Click the "Number" tab and in the "List Separator" field, 
Replace the current default separator with the one you want to use (such as the pipe symbol | ).
  • If you use Microsoft Excel to create a csv file it is recommended that you open the document with Notepad to review the contents prior to Autoloading. If any of the data within the Excel document had line breaks within cells it will create a line break in the csv document. Each new line in the csv document will start a new record, thus you must ensure all inner-cell line breaks are removed from either Excel or the txt document.
  • Alternately when performing a CSV import, any columns that have line breaks or delimiters within the content can be contained within double quotes. NOTE: Any double quotes within that content MUST be escaped with a backslash \ in front of the double quote so they aren't treated as the closing quote of the column. Example of a 4-column CSV:
1st Record|Draft|"This content"|555-1212
2nd Record|Submitted|"That content"|123-4567
3rd Record|Draft|"Broken  
Content"|444-5555
4th Record|Draft|"Normal Content Test"|321-9876
5th Record|Submitted|"Backslash Escaped \" quote content
Also with 
multiple line breaks"|777-8888
6th Record|Submitted|"Normal Content again"|111-2233
  • If the file has special characters, convert encoding to UTF-8.
  • If columns has a number sign after the comma, always add double quotes to avoid data problem when processing the file.

Note:

  • The number of rows in the selected file cannot exceed 100,000
  • The number of columns in the selected file cannot exceed 100
  • File should not contain a cell that exceeds the character limit of 32,000

Configuration - Essentials

These are the mandatory fields in creating a basic autoloader:

1. Essential settings in the General Tab

  • Set the Loader Name
  • Select the Folder Type: SmartFolder or Configuration Folder
  • Click the Folder Lookup button and select the folder assigned to this autoloader
  • Select the File Type
  • Select the File Encoding
  • Define the Record Type for this autoloader
  • Select Item of the Record Type
  • Activate the autoloader

2. Essential settings in the File Layout Tab

  • Select the Number of Columns - based on the file sample to be uploaded into this autoloader
  • Ignore Lines should be 1, if the file has header in row 1
  • Select Field Delimiter from the dropdown list
  • Define the column mapping based on the field columns of the file to the Record Type fields from the dropdown.
    Note that you can toggle the identifier if the file has a unique identifier. This will ensure integrity of the data being added/updated.

3. Essential settings in the Process Tab

  • Go to the Create / Update Options section and select Create / Update Behaviour for the autoloader.
    Once all three tabs have been configured, click on SAVE button. The autoloader is now ready to process the file that will be fed to the SmartFolder or Configuration Folder selected for this autoloader.

Configuration - Advanced

Column Mapping

Once you have defined the number of fields, you define the field order to match the column order in the upload file.

Auto9.png
  • The number of columns you select determines the number of fields you can map.
  • Identifier. Enable the column that you have identified as the identifier.  This is important when you are updating existing records. Single/multiple field(s) is/are required to match an uploaded record to an existing record within the system.  
  • The Table header field is only required if you are using an XML or JSON file. You can fill out this section with the column headers of the file you want to upload.
  • Field is selected thru a dropdown box. (see the Reference section below for information on standard field names)


IMPORTANT:
The following fields must only be included in the Autoloader field mappings if you are updating existing records. These fields must not be used when using the Auto Loader to create new records.

  • Company ID (Company Auto Loader)
  • User ID (Contact Auto Loader)
  • Resource ID (Resource Auto Loader)
  • Tracking ID (Level 2 / Level 3 Auto Loader)


  • When using the fields above the IDs being Auto Loaded must match IDs of records present within the system.
  • If used the fields should be marked as the Identifier.
  • These fields would normally be used if you have extracted data from SmartSimple including those ID fields, updated the data outside of SmartSimple and wish to import the updated information.


NOTE:  

1. If you wish to deactivate a user using the Autoloader then select the Disable User Login (use value of 1) Field option and ensure that the uploaded file has a value of 1 in the relevant cells.

2. If the Item or App Level option is set to one of the Association options then you can only have Associations ID as identifier. The system we will force the Identifier when Associations ID is selected in mapping, and will only alert them when they try to set any other columns as identifier.

Manual/Automated Autoloader

Triggered by sending a file to the SmartFolder/Configuration Folder

Automated Schedule

Here is a more detailed look at the automated Scheduler.

Decryption

PGP decryption is available in the autoloader and occurs when the file is picked up for processing. PGP encryption and decryption keys should be added in the Integration_Key_Management section. To configure this setting, go to the autoloader configuration page and under the General tab go to the Decryption section, select the desired PGP decryption key to use before the autoloader processes the file.

Uploading Specific Files to Specific Records in Bulk

The autoloader also allows you to upload specific files to specific records in bulk. Previously, if you had several files across multiple records that needed to be updated within a specific UTA, user, or company, finding each record and manually uploading those files would be tedious, especially if the files needed to be updated on a regular cycle. Now you can upload all the files in bulk and the system will unpack and distribute those files to the fields you specified accordingly.

To use this feature, follow these steps:

  1. Create a CSV file containing mandatory columns (File Name and Custom Field ID) and the relevant identifier columns for the record type.
  2. Create a ZIP file that contains the files you want to upload or replace. Make sure the ZIP file and the CSV file have the same name.
  3. Go to Menu Icon > Autoloaders > Create or edit an autoloader > Select the desired Processing Folder.
  4. Set the File Type to “CSV, Text”.
  5. Under Record Type, select the desired record type you want to target ("UTA", "People", or "Company").
  6. Under Item, select the new “File Upload” option. If you selected a UTA as a your Record Type, you will be prompted to select the desired Level 1, 2, or 3.
  7. Configure the column mappings as usual.
  8. Upload the CSV and ZIP file to the processing folder you selected in Step 3.

Note: This new option does not create new records or update other columns. The “File Upload” item only uploads files.

Use the new "File Upload" option to have the autoloader unpack a ZIP file and upload/replace files to specific upload fields as specified in a CSV file.
The CSV file must have the same name as the ZIP file and it must specify the record ID, upload field ID, and file name.

External Data Source

This section allows for the configuration of an external file source if the user does not want to manually upload the file.

Autoloader DS1.png

The External Data Source is a drop-down list with 5 options available:

Additional fields are exposed when one of these options is selected, with the exact number and type of fields dependent on the selected option.

FTP and SFTP

AutoFTP2.PNG

  • Disable processing. Download file only - if checked then the system will only pull the file across into SmartSimple, but will not load the contents of the file
  • Delete File from Data Source After Downloaded - file will be deleted from the sftp site
  • Append Datetime Stamp to File Name - will append date and time when the file picked up from the sftp do not have a datestamp
  • URL - the URL where the file(s) can be retrieved from. e.g. my.sftp.com or my.sftp.com:2222
  • Login Name - username to allow access to the system
  • Login Password - password to allow access to the system
  • Use Private Key - enable this setting when using keys as credentials to authenticate
  • Private Key File - upload the private key file
  • Path - exact folder location on the system where the files can be found i.e. "/" for root directory or "/in" or "/out"
  • File Names - name of the files to be loaded via the Autoloader. e.g. HRFeed.csv, Payment_*.csv (Use * for wildcards in filenames. < br/>

Note that when settings are saved, a Verify Connection button will appear next to the Login Name Textbox. This button can be used to verify that the url, username, password are correct.

HTTP

AutoHTTP.png

  • Disable processing. Download file only - if checked then the system will only pull the file across into SmartSimple, but will not load the contents of the file
  • URL - the URL where the file(s) can be retrieved from
  • File Names - name of the files to be loaded via the Autoloader

SOAP

AutoSOAP.png

  • Disable processing. Download file only - if checked then the system will only pull the file across into SmartSimple, but will not load the contents of the file
  • URL - the URL where the file(s) can be retrieved from
  • Login Name - username to allow access to the system
  • Login Password - password to allow access to the system
  • Function - The function called by the Web Service call.
  • Request Body - The content of the Web Service message to be sent to the external service.
  • Callback - Method to sends the response back to the client.

OData

External-OData1.PNG

  • OData Version - Version of OData available from the endpoint
  • URL - the endpoint of OData
  • Login Name - username to allow access to the system
  • Login Password - password to allow access to the system

File Preparation

This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.

It is only available when the File Type is either CSV,Text or Fixed Length Text.

It is not available when File Type is XML.

See Autoloader File Preparation for configuration instructions.

AutoFilePreparation.png

Settings Explained

This section describes the detailed steps required to use the Autoloader. Carefully examine, using a file editor, the file that will be uploaded.

Ensure that the file meets the file layout criteria outlined in the previous section:

  • Choose the Configuration, Global Settings menu.
  • Stay on the System tab and scroll down the page.
  • Click the Autoloader link.
  • Click the New Autoloader tab.
  • Go thru each of the three tabs.


General tab

Auto6b.PNG

Setting the Autoloader Name


As you can define any number of autoloaders each autoloader process must be uniquely named.

  • Set the name for this autoloader process.
Auto3.PNG

Setting the Folder Type

  • Select Folder Type: SmartFolder  or Configuration Folder
  • Click the Folder Lookup button to display the list.
  • Select the folder where you plan to add the file.
Auto4.png

The folder name will be added to the Folder box.


Setting the File Properties


The File Type can be a CSV,Text file, an XML file, Fixed Length, or JSON data.

The File Encoding can be UTF-8, UTF-16 or ANSI. This option is hidden if the File Type is XML.

AutoFile.png

Defining the Record Type


The Record type can be Company, Contact, Resource or a Universal Tracking Application.

If the selected Record type is Company or Contact then the user is required to populate an additional Item field. The possible options here can include Company or Contact, Event,Transactions, Address and Associations (User or Company) .

If the selected Record type is a UTA then the user is required to populate an additional Level field. The possible options here can include Level 1, Level 2, Level 3, Transactions, L1 Company Association, L1 Contact Association, L2 Company Association and L2 Contact Association.

Auto5.png

If the selected Record type is a UTA and the selected Level is Transaction then the user is required to populate an additional drop-down field reflecting all the levels that the transactions are enabled against.

Auto5b.png

Activate Autoloader


Enable this check box for the Autoloader to execute.


External Data Source


Autoloader can be connected an external data source to pull the file and move the file to the SmartFolder.

Connectiontypes.PNG

Select a connection type and complete the Schedule section below to specify the launch times of the autoloader everyday.


File Layout Tab

The File Layout section is used to determine the number of columns in the file and the Field Delimiter.

Auto8.png
  • Number of Columns - The number of columns you select is used to define the number of columns in the upload file, and the number of fields that can be updated in the database.
    • The maximum number of fields that can be updated is 100 fields in a single file.
    • If more than 100 fields are required, then you will need to create two upload files.
  • Ignore Lines - You can specify the number of rows to ignore from the beginning of the file. This can be used to ignore column headers, if they exist as the first row in any file.
  • Field Delimiter - drop-down list showing the delimiter options used to separate different values in the file into different columns. Will not be presented when the File Type is XML or JSON.
  • Record Node Name - Only presented when the File Type is XML.
  • Record Node Filter - Only presented when the File Type is XML.

Process tab

Auto6.png
  • Workflow before loader starts – drop-down list that contains a list of all workflows in the system. It defaults to No and if users wants to have a workflow execute before the Autoloader adds the data to the system they should select the relevant workflow.
  • Workflow after loader ends – drop-down list that contains a list of all workflows in the system. It defaults to No and if users wants to have a workflow execute after the Autoloader adds the data to the system they should select the relevant workflow.
  • Run report after loader ends - drop-down list that contains a list of all reports in the system.  It will ran the configured Scheduled Export of the report. Frequency on the report should bet set to On Demand.
  • Run sequencial Autoloader after loader ends – Linking Autoloaders
  • Trigger Formulas – enables the triggering of any template formulas that may exist for the selected record type and item/level. If this check box is not enabled then these formulas will not execute.
  • Send email with log when Autoloader is done – Sends email every time the Autoloader executes with a report of the upload process.(Note: The log file is also stored in the same folder as the uploaded file.)
  • Send email notification when Autoloader failed to complete – Sends email when ftp/sftp connection failed, or when autoloader process was terminated unexpectedly.
  • Send email with log when Autoloader has any failed records - Sends email when autoloader fails to complete.
  • Email To – Sets the email address who will receive a message. Multiple email addresses can be included separated by commas.


Create Options

This section of the Autoloader is used to determine how new records in the upload file should be handled.

The exact fields that are displayed vary depending on the Record Type and Item or List values previously selected.

Company Record / Company Item
Auto7.png
  • Create/Update Behavior - If you wish to create new record, then enable the Create new or update matching record check box. If you enable the other check box Update only, do not create new, existing records will be updated but new records will be rejected.
  • Duplicate Match Behavior - behavior when multiple records are matched by the same identifier key. Drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).
    • Update Unique Match Only - will only update when one matching record is found
    • Update All Matches - will update all matching records
  • Account – determines where in the organisational hierarchy new records should be added.
  • Is External – choose this option when creating external companies under the root company.
  • Category – determines the organisational category that you wish to be associated with the new records.
  • Trigger Workflow – drop-down list of all workflows related to the Record Type. It defaults to N/A.
  • Trigger On All Records - check-box that is populated is you wish to have a workflow execute against all records, whether new records or updated records. This can be used to create companies with multiple categories.
Contact Record / Contact Item
Auto7b.png
  • Create/Update Behavior - If you wish to create new record, then enable the Create new or update Matching record check box. If you do not enable the other check box Update only, do not create new existing records will be updated but new records will be rejected.
  • Duplicate Match Behavior - behavior when multiple records are matched by the same identifier key.
  • Account – determines where in the organisational hierarchy new records should be added.
  • Role – determines a user role that you wish to be associated with the new records. If you wish to have multiple roles associated with a user then you will need to trigger a workflow against each record (see below for further details).
  • Trigger Workflow – drop-down list that of all workflows related to the Record Type. It defaults to N/A.
  • Trigger On All Records - check-box that is populated is users wants to have a workflow execute against all records, whether new records or updated records. This can be used to activate users with multiple roles and to e-mail a password to newly created contacts automatically.
Company or Contact Record / Event or Transaction Item
Auto7c.png
  • Create/Update Behavior - If you wish to create new record, then enable the Create new or update Matching record check box. If you do not enable the other check box Update only, do not create new existing records will be updated but new records will be rejected.
  • Duplicate Match Behavior - behavior when multiple records are matched by the same identifier key. Drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).
    • Update Unique Match Only - will only update when one matching record is found
    • Update All Matches - will update all matching records
  • Type – drop-down list of all Event or Transaction types, depending on the exact Item selected. It defaults to N/A
  • Status – drop-down list of all Event or Transaction statuses, depending on the exact Item selected. It defaults to N/A
  • Trigger Workflow – drop-down list that of all workflows related to the Record Type. It defaults to N/A.
  • Trigger On All Records - check-box that is populated if users wants to have a workflow execute against all records, whether new records or updated records.
UTA Record / all Levels
AutoloaderNewRecordUTA.png
  • Create/Update Behavior - If you wish to create new record, then enable the Create new or update Matching record check box. If you do not enable the other check box Update only, do not create new existing records will be updated but new records will be rejected.
  • Duplicate Match Behavior - behavior when multiple records are matched by the same identifier key. Drop-down allows user to specify the behaviour whenever multiple records are matched using the Identifier field(s).
    • Update Unique Match Only - will only update when one matching record is found
    • Update All Matches - will update all matching records
  • Type – drop-down list of all Level 1, Level 2, Level 3 or Transaction types, depending on the exact Level selected. It defaults to N/A
  • Status – drop-down list of all Level 1, Level 2, Level 3 or Transaction statuses, depending on the exact Item selected. It defaults to N/A
  • Trigger Workflow – drop-down list of all workflows related to the Record Type. It defaults to N/A.
  • Trigger On All Records - check-box that is populated if you wish to have a workflow execute against all records, whether new records or updated records.

Updating Account Owners

When you are updating company type information you can automatically associate an account field to a user field in order to establish “account ownership”. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and Item to company.

Auto10a.png
For example, if the imported account file contains an employee ID field, then this can be matched to the user table – providing each user with an employee ID field.

Updating Account Parent

When you are updating company type information you can automatically associate an account field to a parent account field in order to establish “company hierarchy”. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and Item to company.

Auto10b.png
For example, if the imported account file contains a parent ID field, then this can be matched to the account table – providing each company with a parent company.
Example: Updating Account Parent AutoLoader Configuration:
Update Account Parent Example.JPG
The AutoLoader Parent ID field is an Account field that will get populated with the second value (Parent ID) from the CSV record below.
Example: AutoLoader Source File:
Update Account Parent Example - Source File.JPG
Action: A two column report with the Company ID and the Parent ID needs to be created, exported to a CSV file and uploaded to the associated AutoLoader SmartFolder.
Result: The Account records with a Company ID IN (278520,297733,316703) will have their Parent ID changed to 349238.

Updating Event / Transaction Parent Company

When you are updating company type information related to an Event or a Transaction you can automatically associate an account field to a transaction or event field in order to establish a parent for the transaction. This includes using Account standard fields. If no matches exist then you can select the required action using the No parent matched action drop-down.

  • Reject Record - record is not loaded into the system
  • Add as orphan record - record is loaded into the system but without being attached to a parent company
  • Attach to dummy account - record is loaded into the system and is attached to the parent company specified in the Default Parent ID field.


This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to Company and the Item to either Event or Transaction.


Auto10c.png

Updating Parent Company

When you are updating people type information you can automatically associate an account field to a user field in order to attach the user to the correct account. This section is only displayed at the bottom of the Autoloader page if you have set the Type to People.

Auto10f.png

Updating Event / Transaction Parent User

When you are updating user type related to a Transaction information you can automatically associate a user field to a transaction field in order to establish a parent user for the transaction. If no matches exist then you can select the required action using the No parent matched action drop-down.

  • Reject Record - record is not loaded into the system
  • Add as orphan record - record is loaded into the system but without being attached to a parent company
  • Attach to dummy account - record is loaded into the system and is attached to the parent company specified in the Default Parent ID field.


This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to People and the Item to either Event or Transaction.

Auto10f.png

Branch, Company and Owner Custom Field Matching

When you are updating UTA information you can automatically associate a standard field on the UTA with user and company fields in order to establish individual or organizational ownership. This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to UTA and the Level to Level 1.

Auto10e.png

Updating Tracking Parent

When you are updating UTA Level 2 or 3 information you can automatically associate a field on the level 2 with a field on the level 1, or associate a field on the level 3 with a field on the level 1, in order to establish a relationship between the entities.

If no matches exist then you can select the required action using the No parent matched action drop-down.

  • Reject Record - record is not loaded into the system
  • Add as orphan record - record is loaded into the system but without being attached to a parent company
  • Attach to dummy account - record is loaded into the system and is attached to the parent entity specified in the Default Parent ID field.

This section is only displayed at the bottom of the Autoloader page if you have set the Record Type to UTA and the Level to Level 2 or Level 3.

Auto10g.png

Parent record match field ids

This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.

It is only available when the File Type is XML.

AutoXML.png

Ad hoc upload url

This is an Autoloader setting that provides a more friendly and accessible user interface for importing data into the system as well as allowing users to verify the file before uploading it to a SmartFolder for processing by the Autoloader. This feature is intended to be used when External Data Source is not configured.

100.px

History Tab

Available after February 2019 upgrade.
A new Autoloader feature that shows a new History information tab. The list includes the file name, user who uploaded the file, start time, end time, records added/updated and status of last run.

Autoloader-History.PNG

  • # - item number
  • File - filename that was processed. If the file is deleted from the SmartFolder, the filename will not be displayed on the list.
  • Uploaded By - name of the user who uploaded the file
  • Start Time - date and time the file was processed
  • End Time - date and time the file was completed
  • Added - number of records that were added
  • Updated - number of records that were updated
  • Failed - number of records that failed to update/add
  • Status - status of the process.   
    • Processing - the file is currently being processed and is not yet complete
    • Completed - the file was successfully processed and was completed
    • Interrupted - the process was terminated i.e. GORT was terminated abruptly (someone restarted the server)
    • Data Problem - the file has data problem i.e. pound sign at the start of the column . data with # should be enclosed in double quotes e.g. ,"another column","#111 Peter Street","another column",
    • Connection Failed - external data source connection failed
    • No file / download file failed - connection was established but no file was existing in the external data source subfolder
    • File problem 
    • File failed pre-processing 
    • Error processing file 
    • Wrong file type 
  • Process ID - unique identifier of the process

Example autoloader configurations

Organization autoloader

HR Feed autoloader

UTA L1 autoloader

Example how to use ad hoc upload url

When you click on the Upload File button then you will be presented with page one of the Ad Hoc Autoloader screen. Use the 'Choose file' button to select the file to be loaded and then click the 'Upload' button.

100.px

You will be presented with page 2 of the Ad Hoc Autoloader screen, which provides users the option to review the file being uploaded, including what data us being mapped to what field.

100.px

If there is no issue with the data and the mappings then click on the 'Add to SmartFolder and Autolader queue' button. You should be presented with page 3 of the Ad Hoc Autoloader screen, which confirms that the file has been successfully added to the SmartFolder.

100.px

Other considerations

Important note about XML Files

If the contents of any the elements in an XML file being autoloaded contains certain special characters it will disrupt the XML structure, making the message unreadable by the receiving server. The less than symbol (<) and ampersand (&) are two primary special characters to be concerned about. When including fields that may contain those characters you should use a special wrapper so the receiving understands that they are data content and not part of the XML structure:

  • <![CDATA[ CONTENTS ]]>

For example, an address or company name could contain ampersands, so should be included as: 

JaneSmith
<address> </address>
  • It is advisable to use CDATA for any field containing text since the contents are often unpredictable.

Note about JSON Files

JSON format should start with a data node, as shown in the example below 

{
"data":[{
"Name":"Acme Corporation",
"Phone":"416-123-4567",
"CompanyID":"496246"
},
{
"Name":"Super Banana Ltd.",
"Phone":"800-555-1234",
"CompanyID":"496244"
},
{
"Name":"Red Apple Supply",
"Phone":"917-123-0000",
"CompanyID":"496248"
},
{
"Name":"Orange you Glad",
"Phone":"410-222-2030",
"CompanyID":"496242"
}
]
}

Reference: Standard Field Names and Variables

The below table can be used to compare Level 1 Standard Field terminology found in the UTA, on the Autoloader, in the Database, and Variable syntax]]

UTA L1 Standard Field Name  Autoloader Standard Field Name Database name Variable List
n/a Level 1 ID opportunityid @opportunityid@
Application Type Type Name opportunitytypename @type@
  Type ID opportunitytype @typeid@
Application Name Name name @name@
Customer Client company @client@
  Client ID companyid @clientid@
  Client Custom Field companycfield  
Status Status stagename @status@
  Status ID stageid @stausid@
Revenue Revenue revenue @revenue@
Currency      
  Currency (USD,CAD etc.) currencyid @currencyname@/ @currencycode@
  Currency Exchange (USD,CAD etc.) excur @excurrencycode@/ @excurrencyname@
  Exchange Rate excur_rate @exrate@
Close Date Close Date (yyyy-mm-dd) closedate @closedate@
Probability n/a   @probability@
Description Description description @description@
Requirement n/a   @requirements@
Owner      
  Owner ID updatedby @ownerid@
  Owner (firstname lastname) updatedbyname @owner@
  Owner Email updatedbyemail @owner.email@
  Owner Custom Field updatedbyfield  
Branch Branch branch @branch@
  Branch ID branchid @branchid@
  Branch Custom Field branchcfield  
Workflow n/a n/a n/a
Contacts n/a (added Import Contacts on UTA) n/a n/a
Activity List n/a (needs separate autoloader) n/a https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
End Date End Date (yyyy-mm-dd) oenddate @enddate@
Start Date Start Date (yyyy-mm-dd) ostartdate @startdate@
Modified Date n/a   @modifieddate@
Institutions n/a (needs a workflow)   https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
End Time n/a   @hour(fullenddate)@:@minute(fullenddate)@
Start Time n/a   @hour(fullstartdate)@:@minute(fullstartdate)@
Person      
  Person ID oppeopleid @personid@
  Person (firstname lastname) oppeoplename @person@
  Person Email oppeopleemail @person.email@
  Person Custom Field oppeoplefield  
UTA Providers n/a (needs a workflow task)   https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
Status Indicator n/a   n/a
Transactions n/a (needs separate autoloader)   https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
UTA Providers (L2) n/a   https://wiki.smartsimple.com/wiki/Web_Page_View_Field_Variables
Modified By n/a   @modifiedby@/ @modifiedbyid@
Created Date n/a   @createddate@
Created By n/a   n/a
Invitations n/a   n/a

See Also

Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Auto_Loader&oldid=43151’