intelligentcontract uses CSV files to Export and Import data.  This article explains the aspects of a CSV file and how the data must be presented to ensure a successful Import. An example populated CSV file is attached to this article for reference.


CSV Data

When you generate a populated or example CSV file from intelligentcontract, you adjust the data to reflect your required changes.  Please note that a CSV file type will NOT remember any adjustments made so you will need to save the CSV as an Excel file, adjust accordingly and then save the adjusted Excel file as a CSV file type, this adjusted CSV file can then be used for the Import.


Columns

Each column that is presented in the CSV file represents a field that can be placed on the corresponding form, for example, if you have generated a Contract CSV file, then every field available for placement on the Contract form will be presented.  You may find that there are columns corresponding to fields that you have removed from your Contract form, these columns can be deleted from the file.  


If you are adding, deleting or modifying data that is only applicable to certain columns then all the other columns can be optionally removed from the CSV file. For example, if you were just updating the End Date then the columns within the CSV file can be: 


Transaction Type | Original System Reference | End Date

Note: The Transaction Type and Original System Reference columns must be included on every CSV file

Rows

If you have generated a populated CSV file then all your existing records will be presented on a separate row.  If you only wish to adjust specific records then delete the rows that are not applicable from the CSV file.


Column Names

Please note that the column names presented in the CSV file will be the original field names, if you have overwritten the field name in Form Layouts then this will NOT be presented. For example, if you have changed the name of the field 'Organisational Unit' to 'Department' then 'Organisational Unit' will be the column name on the CSV file.


We recommend that fields should only be renamed to something similar such as 'Active Start Date' to 'Commencement Date', renaming a field to change it into something completely different such as 'Description' to 'Clause Details' will create difficulties during the Import mapping process or when creating a New View.  Any requirements for a new field should be made as a new Custom Field.


If a field has been marked as a mandatory field (either by the User or the system) these will be represented by a star character * in the column name.  If you include mandatory fields in your CSV file and the column contains no values then the Import will fail and an error will display informing you that mandatory field data is missing.


Transaction Type

The Transaction Type field is where you tell the system what you want it to do, this is represented by a A (Add), M (Modify), D (Delete), ARM (Archive and Modify), UAM (UnArchive and Modify).  This column value is mandatory for all CSV files and must contain only one value.


ID and Original System Reference

The ID and the Original System Reference are unique identifiers for each record, this column is where you stipulate which records require either Addition, Deletion or Modification.  When adding new records the User can manually enter a Original System Reference value, or if left blank the system will generate a unique reference.  Please refer to the article for more information regarding ID & Original System References.


Hierarchy Fields

Hierarchy fields are fields in the system where a value can be a sub value, such as Contract Type, Department or Category.  The value held within the column must reflect how to find the correct level value, this is achieved by entering the characters 'equal' and the 'right arrow' => by entering these characters you tell the system that it must drop to a lower level to find the correct value.


The example below shows that we want a Contract Type of Gas, but this value is held under a value of Energy and Utilities


Fig 1 - Hierarchy field

To enable the system to assign the correct Contract Type value of 'Gas' then the column value should be entered as:


Energy & Utilities => Gas


If you have multiple sub levels the each drop to a level should be reflected by the =>.  The example below shows that we need to assign the value of 'Purchase' which is 4 levels down.


Fig 2 - Multiple levels


To enable the system to assign the correct Contract Type value of 'Purchase' then the column value should be entered as: 


Customer => Europe => Property => Purchase


Value Set Fields

Fields with drop down options (Value Sets) must have column values that match exactly with what is stipulated in the corresponding field Value Set, if any column value does not match due to spelling or a completely different value then the Import will fail and an error will be displayed informing of the mismatched value.


Fig 2 - Value Set fields


Multi-select Fields

Multi-select fields allow you to select multiple values for a field.  The column value must reflect how add multiple values in the field.  This is achieved by entering the pipe | character between each value. By entering this character you tell the system that each value entered is a separate value.


The column value should also adhere to the Value Set rules for exact value match.  


Fig 3 - Multi-select Fields


To enable the system to assign the correct values above then the column value should be entered as: 


Asia | Europe | UK


Multi-Select Hierarchy Fields


Multi-Select Hierarchy fields combine the feature of allowing a user to enter multiple values that use a hierarchical structure such as Department levels.  This type of column value must adhere to all three rules for Hierarchy, Value Sets, and Multi-Select.


So if you had a hierarchy field that required multiple values then you should complete the column entry as below, here we are wanting to upload 3 values into an Organisation Unit (Department) field.

Fig 4 - Multiple hierarchy fields


To enable the system to assign the correct values above then the column value should be entered using a combination of the => and | characters mentioned previously:  


London => #3431 London |  North West => #2334 Manchester | North West => Liverpool => #3434 Liverpool


When uploaded into the record field the values would be displayed like this:



Contract Value Display

The Contract Value display stipulates what type of contract value you wish to pull through and display on the Contract Summary screen.  The default if left blank is 'Lifetime' but if you wish to amend this then you must specify the value in the Summary Display Option column.  


Only one of the 4 choices can be specified, you can either use the defaults set by the system as detailed below or use the corresponding Value Sets to build your display value separated by the 'pipe' | character where required..

  • Lifetime - Displays the Contract Lifetime Value
  • Frequency - Relates to 'Display Average...' (default is 'Annual')
  • In Period - Relates to 'Display value Relating...' (default is 'This' 'Year')
  • To Date - Relates to 'Display value up to...' (default 'Start Of' 'This Year')


If you wish to stipulate a specific Display value then the column value should reflect the correct values separated by the 'pipe' | character where required.  Please refer to this Contract Value article to see all the options within the available value sets.


Fig 5 - Contract Value Display Options 


To enable the system to assign the correct values above then the column value should be entered as:  


To Date | End of | Last | Year


Dates

  • Dates entered as a column value should be entered at DD-MMM-YYYY or MMM-DD-YYYY always using the dash - separator, values such as DD/MM/YY will cause the Import to fail.


  • The system validates that the correct day values are used, entries such as 30-Feb-2019 will cause the Import to fail


  • Start Date and End Date column values should always be logical, the system will verify if a Start Date or End Date is correct, if for example, the End Date year value is before the Start Date year value the Import will fail


  • For fields that allow a Date Range the column value should use the dash - separator character between the two dates, if you do not enter the second date then only the first date will be entered into the field:


DD-MMM-YYYY - DDD-MMM-YYYY


  • For fields that allow multiple date values the column values should be entered using the pipe | character:


DD-MMM-YYY | DDD-MMM-YYYY | DD-MMM-YYYY


  • For fields that allow multiple date ranges then the column values should use the dash - separator and the pipe | characters:


DD-MMM-YYYY - DDD-MMM-YYYY | DD-MMM-YYYY - DDD-MMM-YYYY | DD-MMM-YYYY - DDD-MMM-YYYY


Checkbox's

Checkbox column values are represented by a Y or N value, if left blank or removed from the CSV file, the default will be set to N


Security Groups

If you are using Security Groups then you can specify the Security Groups that have access to the specific records. The column value should be entered following the Multi-Select Hierarchy Field rules above, if left blank or removed from the CSV file the default will be set to Everyone (rw).