Smartstaff
1,385
edits
Changes
no edit summary
==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.
* 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.
* 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.
==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.* 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:
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,
* 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
==Instructions==
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 New Autoloader tab.
===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.
:[[Image:Auto3.png]]
===Setting the SmartFolder===
* Click the Folder Lookup button to display the [[SmartFolders Overview|SmartFolder]], list.
* Select the folder where you plan to add the file.
: [[Image: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 Type Encoding can be a CSVUTF-8,Text file, an UTF-16 or ANSI. This option is hidden if the File Type is XML file, Fixed Length, or JSON data.
===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 and Transctions. 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]]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. :[[Image:Auto5b.png]]
: [[Image:Auto5b.png]]
==Additional Settings==
You must set the following additional settings:
* '''Delete First (use with caution)''' – all existing data will be deleted from the [[UTA]] level selected ('''This setting only applies to UTA data''').
* '''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. Multiple email addresses can be included separated by commas. (Note: The log file 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.
===New Record===
The New Record 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====
:[[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 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====
:[[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 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====
:[[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 if users wants to have a workflow execute against all records, whether new records or updated records.
====UTA Record / all Levels ====:[[Image:AutoloaderNewRecordUTA.png|link=]]* '''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
* '''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.
===File Layout===
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.
* 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.
* 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.
* 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 or JSON. * 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 or JSON.
* 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.
* The number of columns you select determines the number of fields you can map.
* The field name is selected from the Field combo box.
* 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.
* The Table header field is only required if you are using the HTML Table feature or when loading an XML or JSON file.
* The '''Duplicate Match Beahviour''' 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 <span style="font -size="3: medium;">'''IMPORTANT:''' </fontspan><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.
* When using the fields above the IDs being ::* '''Company ID''' (Company Auto Loader)*::* '''User ID''' (Contact Auto Loaded must match IDs of records present within the system.Loader)* If used the fields should be marked as the Identifier.::* '''Resource ID''' (Resource Auto Loader)* These fields would normally be used if you have extracted data from [[SmartSimple]] including those ::* '''Tracking ID fields, updated the data outside of [[SmartSimple]] and wish to import the updated information.''' ({{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: 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.
==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.
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]]*: ''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:'''*: [[File: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:'''*: [[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.
==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.
==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.
*
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.
*:[[Image:Auto10d.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==
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.
*:[[Image: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'''.
*:[[Image:AutoXML.png]]*
==File Preparation==
This section allows for manipulation of the data prior to the Autoloader actually loading the data onto the system.
See [[Autoloader File Preparation]] for configuration instructions.
*:[[Image:AutoFilePreparation.png]]*
==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:
===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
<!--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** 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
e.g. HRFeed.csv, Payment_*.csv (Use '''*''' for wildcards in filenames.
===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.
==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:
*:<span style="font -size="2: small;"><![CDATA[ '''''CONTENTS''''' ]]></fontspan>*
For example, an '''address''' or '''company name''' could contain ampersands, so should be included as:
** 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>{
"data":[{
"Name":"Acme Corporation",
==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.
[[Image:AdhocAutoload.png|border|100.px]]
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|border|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.
[[Image:AdhocAutoload2.png|border|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.
[[Image:AdhocAutoload3.png|border|100.px]]
==See Also==
** [[How to Add Users and Contacts]]** [[Autoloader File Preparation]]
[[Category:Data Import]]
[[Category:Global Settings]]
