A [[An '''Autoloader''' is a SmartSimple]] feature used to automatically upload files of import data related to [[organization|companies]], [[contact]]s and 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 create/update SmartSimple information with data with data from an external text filesfile. This feature A general overview of the process is vital if you need to keep SmartSimple customer or project data in sync with an existing legacy or enterprise resource planning (ERP) system.as follows:
An Autoloader can be triggered whenever a file is added to a designated [[SmartFolder]]/Configuration Folder. These files can be added manually or uploaded automatically picking by up a file from an SFTP. * 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.
* Each record from the upload uploaded file will update the associated record in the database.* A log file will be created indicates to indicate the success or failure of each record update and an email can be sent to a designated user indicating 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 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 and , [[Universal Tracking Application]] {{l1}} or {{l2}} data, {{l3}},[[Category:Transactions|Transactions]], [[Notes_Overview|Notes]], [[Associations/|Associations]].
Before you can deploy this =Autoloader===Prerequisites==* You must be a {{GUA}} in your [[instance]] of SmartSimple to access the Autoloader feature it and set up an upload process.* The user uploading the file must have write permissions to the folder where the file is necessary 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===* Define the Any [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder where ]] can be used as the upload file will be storedsource for an Autoloader.* Define The folder must be used '''exclusively''' for the format and layout of the data file (the number of columns in the upload fileAutoloader, the delimiter between each field and the field order).* Specify a field in the upload since every file that the system uploaded will use to match an upload record to an existing customer recordbe processed.* Determine if you wish to use an automated process for files by adding a schedule to pick up Incorrect files from an external source (i.e. SFTP or manually add the incorrectly formatted files '''will be processed''' and could lead to the folder)data loss and/or creation of invalid data.
Each record in the '''IMPORTANT: '''Because any file uploaded file is used to update one set of fields in the entity a [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] that is associated to an autoloader will be updatedprocessed, it is extremely important that the SmartFolder/Configuration Folder is permissioned and labelled appropriately.
The existing value in the SmartSimple field will * [[SmartFolders Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_Folders|Configuration Folder]] for Autoloaders should be permissioned such that only contacts that need to be replaced by the value on 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 field in the upload filefolder.* In creating a new [[SmartFolders_Overview#Configuration_Folders|Configuration Folder]], specify "Autoloader/Export Storage" as Folder Type.
==Creating a SmartFolder/Configuration Folder=Preparing the file to upload into the autoloader===* Any SmartFolder can be used as Define the format and layout of the source for an Autoloader.* The folder must be used '''exclusively''' for data file - the number of columns in the Autoloaderupload file, since every file uploaded will be processed.* Incorrect files or incorrectly formatted files '''will be processed''' so could lead to data loss the delimiter between each field and/or creation of invalid datathe field order.
'''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.
* SmartFolders/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 into a parent folder named '''Autoloader''' (with role-restricted access) to help ensure no inappropriate files are uploaded to the folder.
==Preparing the Upload File==
* 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.
* If columns has a number sign after the comma, always add double quotes to avoid data problem when processing the file.
==Configuration - Essentials==
These are the mandatory fields in creating a basic autoloader:
1. Essential settings in the '''General Tab'''
<ul style="margin-left: 50px;">
<li>Set the '''Loader Name'''</li>
<li>Select the '''Folder Type''': [[SmartFolders Overview|SmartFolder]] or [[SmartFolders_Overview#Configuration_Folders|Configuration Folder]]
<li>Click the Folder Lookup button and select the folder assigned to this autoloader</li>
<li>Select the '''File Type'''</li>
<li>Select the '''File Encoding'''</li>
<li>Define the '''Record Type''' for this autoloader</li>
<li>Select '''Item''' of the '''Record Type'''</li>
<li>Activate the autoloader</li>
</ul>
2. Essential settings in the '''File Layout Tab'''
<ul style="margin-left: 50px;">
<li>Select the '''Number of Columns''' - based on the file sample to be uploaded into this autoloader</li>
<li>'''Ignore Lines''' should be 1, if the file has header in row 1</li>
<li>Select '''Field Delimiter''' from the dropdown list</li>
<li>Define the column mapping based on the field columns of the file to the '''Record Type''' fields from the dropdown</li>
</ul>
<p style="margin-left: 50px;">Note that you can toggle the identifier if the file has a unique identifier.
This will ensure integrity of the data being added/updated.</p>
3. Essential settings in the '''Process Tab'''
<ul style="margin-left: 50px;">
<li>Go to the '''Create / Update Options''' section and select '''Create / Update Behaviour''' for the autoloader.</li>
</ul>
<p style="margin-left: 50px;">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.</p>
==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.
===Autoloader Constraints===: [[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 a 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}} / {{GUAl3}} in your 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 [[instanceSmartSimple]] including those ID fields, updated the data outside of [[SmartSimple ]] and wish to import the updated information. '''NOTE:''' 1. If you wish to access deactivate a user using the Autoloader feature 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 up an upload 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 now available in the autoloader.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 from the dropdown list of the pgp keys to use for decrypting files before autoloader processthe 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 must have write permissions 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 where 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 storedretrieved from. e.g. ''my.sftp.com'' or ''my.sftp.com:2222''* '''Login Name''' - username to allow access to the system* You '''mustLogin Password''' not use - 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 any purpose other than uploading root directory or "/in" or "/out"* '''File Names''' - name of the filesto 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, as 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 attempt 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 process any file added 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 folderAutoloader 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. [[Image:AutoFilePreparation.png]]
==Autoloader Configuration PageSettings Explained==
This section describes the detailed steps required to use the Autoloader. Carefully examine, using a file editor, the file that will be uploaded.
Select a connection type and complete the ''Schedule'' section below to specify the launch times of the autoloader everyday.
'''Schedule'''
Here is a more detailed look at the automated [[Scheduler]].
* '''Record Node Name''' - Only presented when the File Type is XML.
* '''Record Node Filter''' - Only presented when the File Type is XML.
'''Defining the 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.
==='''Process''' tab===
* '''Process ID''' - unique identifier of the process
==File PreparationExample autoloader configurations==This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.===Organization autoloader===
It is only available when the '''File Type''' is either '''CSV,Text''' or '''Fixed Length Text'''.===HR Feed autoloader===
It is not available when '''File Type''' is XML.===UTA L1 autoloader===
See [[===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 File Preparation]] for configuration instructionsscreen. Use the 'Choose file' button to select the file to be loaded and then click the 'Upload' button.
[[Image:AutoFilePreparationAdhocAutoload1.png|100.px|border]]
==External Data Source==This section allows for You will be presented with page 2 of the configuration of an external file source if Ad Hoc Autoloader screen, which provides users the user does not want option to manually upload review the filebeing uploaded, including what data us being mapped to what field.
[[Image:Autoloader_DS1AdhocAutoload2.png |100.px|border]]
The 'If there is no issue with the data and the mappings then click on the 'Add to SmartFolder and Autolader queue'External Data Source''' is a drop-down list button. You should be presented with 5 options available:page 3 of the Ad Hoc Autoloader screen, which confirms that the file has been successfully added to the SmartFolder.
Additional fields are exposed when one of these options is selected, with the exact number and type of fields dependent on the selected option[[Image:AdhocAutoload3.png|100.px|border]]
==Other considerations=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 ==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:
For example, an '''address''' or '''company name''' could contain ampersands, so should be included as:
<pre>
JaneSmith<primarycontact><firstname>Jane</
prefirstname>
<lastname>Smith</lastname><address>
<![CDATA[145 Main Street]]></address>
<companyname><![CDATA[Acme & Friends]]></companyname></primarycontact></pre>
* 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
</pre>
===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]]
| style="background-color: #f0f0f0;"|n/a
|}
==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.
[[Image:AdhocAutoload1.png|100.px|border]]
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.
[[Image:AdhocAutoload2.png|100.px|border]]
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.
[[Image:AdhocAutoload3.png|100.px|border]]
==See Also==
** [[How to Add Users and Contacts]]** [[Autoloader File Preparation]]** [[Linking Autoloaders]]
[[Category:Data Import]]
[[Category:Global Settings]]
