Data Loader Guide | | chillidb

Contact Us

Polymorphic Solutions

Level 13, 97 Creek Street
Brisbane, QLD, 4000

Australia


info@chillidb.com

Tel: 1300 65 72 43

About

Newsletter

Video

Partners

Pricing

Terms of Use

Privacy

Security

ChilliDB is a Registered Trademark of Polymorphic Solutions © 2019

Nov 2, 2018

Data Loader Guide

0 comments

Edited: Jul 22

The purpose of this utility is to allow ChilliDB users to import multiple records retrieved from a list of values stored in an external CSV, XML, XLS, or XLSX file. Currently it is possible to import data into Contact, Organisation, Note, User, or Client Administration.

 

The use of this utility requires special formatting of files and possible post migration cleanup activities and any attempt to use the utility should be performed carefully ensuring database backups are taken before commencing. For clients who are using Polymorphic Solutions hosted service for their ChilliDB systems will have their database backed up nightly.

 

The data loader is acessed underneath the System > Data Management > Data Loader menu. You must have the data loader user ticket to see it.

 

Input Data File Format

If you are importing using CSV files, they must be formatted as follows:

 

  • Line Delimiter (Line Separator) - each CSV row should be separated by a delimiter character or string.

 

By default, a carriage return (new line character) will be used as the line delimiter.

 

Polymorphic Solutions recommends the user to use "#!!#" (without the double quotes) as delimiter for each CSV row as it is unlikely to be present within the data itself.

 

The user can however specify other delimiter format as long as it does not match the common used delimiters such as commas ( , ), slashes ( / ), dashes ( - ) or hashes ( # ).

 

  • Column Delimiter - each CSV column should also be separated by a delimiter character or string.

 

By default, a comma ( , ) will be used as the column delimiter. The user can however specify other delimiter format to separate the CSV fields. The use of commas as column delimiter is a standard within CSV file, however there are some cases when the comma is being used as a valid content. In this case, the user may want to specify another character as column delimiter to be able separate the correct values.

 

Despite the utility considers commas which are contained within the CSV cell values as delimiter, Polymorphic Solutions recommends the user to use pipe character "|" (without the double quotes) as delimiters for each CSV column value as sometimes commas can be used as valid values within the file.

 

The example below shows how the CSV file content can be formatted. It is permissible for data to span rows provided the row delimiters are present

 

Firstname | Lastname | Organisation | Phone | Fax | Email #!!# Joe | Blogs | Polymorphic Solutions | 07 3274 2356 | 07 3274 5101 | joeblogs@polymorphicsolutions.com.au #!!# Mary | Jane | ACME Solutions | 07 3274 2345 | 07 3275 5111 | mjane@acme.org.au #!!#

 

Processing Rules

 

Entity Dependency

 

The data import tool does not currently check for dependency within the uploaded data. In order to ensure that the Contact, Organisation and Note data linked properly, the following steps must be followed in order when processing the data using this tool:

 

1. Upload all organisation data including all Organisations linked to the contact Data

 

If there are some identifiers linked to the old system, they can be uploaded as external identifier for the organisations and can be used to identify duplicates when updating existing data

 

The external identifiers can later also be used to link the Organisations with the Note data

 

2. Upload all contact data including all Contacts linked to the Note data

 

If there are some identifiers linked to the old system, they can be uploaded as external identifier for the contacts and can be used to identify duplicates when updating existing data

 

The external identifiers can later also be used to linle the contacts wit hthe note data

 

3. Upload all note data

 

By default, the Note data will be able to be linked with existing Organisation records using their name. If the user wants to link the Note against the existing Contact records, then the users will need to keep the external identifiers of the Contacts and have them linked to the proper Note row in the file. The user should ensure that the external identifiers have been uploaded along with the Contact data. The user can also link the Note with the Organisation external identifiers. In doing so, the user should also ensure that the external identifiers have been uploaded along with the Organisation data.

 

Not following the above steps in order may cause the Contacts not to be associated with the correct Organisations, or Notes not to be associated with the correct Contacts and Organisations.

 

Reference Data

 

Hierarchical Data - Type, Category, and Sub Category

Type, Category and Sub Category contains hierarchy linkage which requires them to be inserted appropriately according to their parent-child relationship. The hierarchical order starts from the Type at the highest, then followed by Category and the last bottom one would be the Sub Category.

 

Whenever the data import utility processes each record row from the input file, the text values defined for those Type, Category and Sub Category will be collected first at application level before checking the existing Type, Category, and Sub Category in the database.

 

If the Type does not exist in the database, the Type will be automatically created as the new reference data. The same process will also apply to the Category and Sub Category relationship. If a Category does not exist in the database, it will be created as a new reference data.

 

General Reference Data (Other than Type, Category, and Sub Category)

Whenever the data import utility processes each record row from the input file, the text value will be checked if it exists in the database. If the checked value does not exist in the database, the value will be automatically inserted into the database.

 

Organisation

Inserting or updating an Organisation requires a set of mandatory parameters to be able to insert/update the data properly. These mandatory parameters are described as follows:

 

  • Type: The Type of the Organisation

  • Organisation Name

 

Notes:

 

  • Organisation Status will only accepted two values i.e. "A" for Active and "I" for Inactive. When assigning a mapping column for Organisation Status, if the status value of the input file is stated as "Active" or "Inactive", the data import utility will pick up the first character of that word and converted to "A" or "I". Everything else will be converted as empty text. The utility will consider any word beginning with letter of "A" or "I" to treat it as "Active" or "Inactive"

 

Duplicate Handling

The duplicates which may occur during insertion of Organisation records can be prevented by assigning a key field/column in the input file to an external identifier field in ChilliDB database. The key value from the input file will be verified against the existing values in the external identifier field for any record to be inserted and if the value matches, an update function will be executed instead of the insert function.

 

If external identifier is not defined in the mapping list, the Organisation's name will be used to match for existing Organisation records in the system.

 

Contact

Inserting or updating a Contact requires a set of mandatory parameters to be able to insert/update the data properly.

 

These mandatory parameters are described as follows:

  • Type: The Type of the Contact

  • Contact First Name

  • Contact Surname

  • Organisation Name

 

Duplicate Handling

The duplicates which may occur during insertion of Contact record can be prevented by assigning a key field/column in the input file to an external identifier field in ChilliDB database. The key value from the input file will be verified against the existing values in the external identifier field for any record to be inserted and if the value matches, an update function will be executed instead of the insert function.

 

If external identifier is not defined in the mapping list, the combination of Contact's first name, middle name, and surname will be used to match for existing Contact records in the system.

 

Note

Inserting or updating an Note requires a set of mandatory parameters to be able to insert/update the data properly. These mandatory parameters are described as follows:

  • Type: The Type of the Note

  • Organisation Name

  • Contact External Identifier: This is the original Contact unique identifier in the client external database.

  • Organisation External Identifier: This is the original Organisation unique identifier in the organisation external database

Notes

 

  • Note Status will only accepted two values i.e. "C" for Closed and "O" for Open. The utility will consider any word beginning with letter of "C" or "O" to treat it as "Closed" or "Open". When assigning a mapping column for Note Status, if the status value of the input file is stated as "Closed" or "Open", the data import utility will pick up the first character of that word and converted to "C" or "O". Everything else will be converted as "C" as Closed is the default status for Note item within ChilliDB.

 

Duplicate Handling:

The duplicates which may occur during insertion of Note records can be prevented by assigning a key field/column in the input file to an external identifier field in ChilliDB database.

 

The key value from the input file will be verified against the existing values in the external identifier field for any record to be inserted and if the value matches, an update function will be executed instead of the insert function.

 

Address

The data import utility supports Business Mail Address, Business Physical Address, Home Mail and Home Physical Address to be inserted or updated.

 

Communication

The data import utility supports Business Phone, Personal Phone, Business Fax, Personal Fax, Business Email and personal email Address to be inserted or updated.

 

Custom Fields

Currently the data import utility supports a number of types for creating new Custom Field within ChilliDB. The following list describes the available types, and the expected input values:

  • Text - expected to receive any characters.

  • Integer - expected to receive absolute numbers only. Any decimal point number will be rounded down to the nearest absolute number.

  • Decimal - expected to receive floating point numbers only. Any integer number will have additional .00 appended to the number.

  • Date - expected to receive the following format as text:

 

- 30-Nov-52

- 30/11/1952

- 30 Nov 1952

- Any time value appended to the date will be dropped when storing

- When using csv files, DD-MMM-YY format must be used

 

Creating Custom Fields

  • Whenever the data import utility creates a new Custom Field into ChilliDB database, it is stored in a custom section called “Migrated Data”.

  • The data loader will check if there is already an existing Custom Field with the same name.

Was this article helpful?

New Posts
  • Q: When contacts are expired, does it automatically remove them from a distribution list? A: When a contact is expired all records related to them are expired too. ChilliDB does automatically unsubscribe them during the expiring procedure. The reason for being unsubscribed in the distribution list is shown as 'Contact expired from system' . Q: What happens if they have been expired by accident? How would they be un-expired and resume their subscriptions? A: This is most easily done by clicking 'Subscribe to other lists' from the distribution list tab on the contact page and selecting the distribution lists you would like to subscribe to. For other records such as memberships, you can filter for expired records and un-expire from there Was this article helpful?
  • In many areas of ChilliDB such as custom fields, contacts, organisations, and most other modules, we provide the expire concept. Expiring data is a way to effectively delete something but with the option to bring it back again if needed. Deleting data permanently deletes it from ChilliDB. An expired contact for example is hidden from the system like they are deleted but they can be viewed from the search if filtering for expired contacts, and be unexpired if needed. If you are unable to see the expire option for a record, please check your user role tickets using System > Users > Maintain Roles + Tickets, or contact your system administrator if you don't have access to the Users menu. Most areas of ChilliDB don't provide a delete function, if you would like to delete something but are unable to do it yourself, we can do so using helpdesk support hours. Was this article helpful?
  • The Sensitivity feature allows the user to restrict access to some information within the ChilliDB system based on certain Types or according to custom selected User Roles. At the moment, this feature is applied to Contacts, Notes, and Membership Packages. This Article covers different levels of sensitivity, how different areas of ChilliDB are affected by sensitivity settings, and managing sensitivity. Table of Contents Levels of Sensitivity Finding Information if the Record is Sensitive Reporting Views Web Service Outlook Plug-in Managing Sensitivity Settings Bypassing Sensitivity Settings Frequently Asked Questions Levels of Sensitivity The ChilliDB system currently offers three levels of sensitivity: 1. No sensitivity – the record can be viewed or modified by any logged-in user as long as that user has the security ticket to view or modify the record. 2. Sensitivity based on Type – the record can only be viewed or modified by those User Roles defined at the Type level. In this level, the Type will contain selected User Roles that can access (view or modify) the record associated with the Type. This is managed through the Reference Table Maintenance screen when creating or editing the Type. Where a record has no sensitivity settings applied and then those settings have changed for the Type, the record will automatically apply the Sensitivity based on the Type. This is because the Sensitivity based on Type will override the “No sensitivity” condition. 3. Sensitivity based on selected User Roles – the record can only be viewed or modified by the User Roles defined to access that specific record. Even though some User Roles contain the security ticket to view or modify the record, they won’t be able to access the record if the User Roles are not included in the allowed User Role list for that specific record. This is managed through the Manage Sensitivity screen which is accessible when viewing the record via the Quick Action called “Manage Sensitivity” or when creating or editing a record via a link in the Sensitivity field. This will only appear if the User has the security ticket “Sensitivity Modify”. The sensitivity based on selected User Roles will override sensitivity rules based on Type. The user can also enable all User Roles to access the record. This will ensure the record will stay accessible to all User Roles even though the Sensitivity settings for the record Type have changed. Finding Information if the Record is Sensitive When the records have Sensitivity settings applied, the user whose User Role is not included in the Sensitivity settings, experiences the following conditions: View/Modify Records The user is not able to access the record display page. The page indicates that the record is sensitive and redirects the user back to the home page. Some of the links in the system such as in the Search pages show the Sensitive records in a different colour. The default colour is orange; however the user can customise this colour setting at the database level. The links are also disabled from clicking and are showing a tooltip to notify that the sensitivity rules apply to the record. The address details, phone, fax, email, and some other details such as gender, date of birth, job title and department which are specific to the Contact record are hidden from the export and print list when doing Export All or Print All from the search results grid. The sensitive Contacts are not included in the Bulk Update for Contacts. When selecting the sensitive Contacts to be included in the Bulk Update, a warning message is shown to notify that the sensitive Contacts will not be included in the Bulk Update process. Send Message Custom fields and the Contact communication details for phone, fax, and email are hidden when refining the Contact recipients of the message upon performing the “Send Message” Quick Action from some screens like Contact Search, Contact tab in Organisation Display screen, Distribution List Display screen, and Membership Display screen. When viewing notes from screens such as Contact note listings and Note searches, all custom fields and the details field are hidden Within the Refine Message Recipient screen, the sensitive Contact name is shown in a specific colour. The default colour is orange; however the user can customise this colour setting at the database level. Since the communication details are hidden, the sensitive Contact shows the “Not Available” message in the Communication Address area. The Refine Message Recipient screen also shows the warning message to indicate how many sensitive Contacts are shown in the recipient list and notify the user that those sensitive Contacts will not be included when sending the message. Furthermore, these sensitive Contacts are not included in the Send Message screen. The sensitive Contacts do not appear on the search result when the user tries to search for the message recipient from the Find Recipient popup screen (accessible from the Send Message screen by clicking Find link). When sending message to a Distribution List, any sensitive Contacts contained in the Distribution List are not included as the message recipients. A notification message appears on the Send Message screen to notify that the sensitive Contacts contained in the Distribution List will not be included in the message. Distribution List The Contact communication details for phone, fax, and email are hidden when they are displayed in the Members or Unsubscribed Members tabs within the Distribution List Display screen. The Contact communication details for phone, fax, and email are hidden when assigning the Contact’s communication item to the Distribution List upon performing the “Subscribe to Distribution List” Quick Action from some screens like Contact Search and Contact tab in Organisation Display screen. The communication items are still shown in the Communication address area however the details of those communication items are marked as “** HIDDEN **”. Within the Subscribe to Distribution List screen, the sensitive Contact name is shown in a specific colour. The default colour is orange; however the user can customise this colour setting at the database level. The Subscribe to Distribution List screen also shows the warning message to indicate how many sensitive Contacts are shown in the subscriber list. Event The phone, fax, and email of the sensitive Contact are hidden from the Event Attendee list screen which is accessed from selecting the “Show/Export Full Attendee List” Quick Action item or by clicking the “Show List” link on the Session tab under “Registrations/Attendance count” column within the Event Display screen. The phone, fax, and email as well as the address details of the sensitive Contact are also hidden from the export list and print list when doing Export All or Print All from the Event Attendee list screen above. Membership The Contact communication details for phone, fax, and email will be hidden when assigning the Contact as Member of a Membership package which manages the Distribution List. The communication items will still be shown in the Address field however the details of the communication item will be marked as “**HIDDEN **”. The drop down list which is showing available communication for the Contact is also disabled and the rule for automatic communication subscription into a Distribution List is used in here to automatically select the preferred communication item to be assigned into the Distribution List managed by the Membership Package. As for the user whose User Role is included in the Sensitivity settings experiences the following conditions: The user is able to view the record. A padlock icon is displayed on the top right corner of the panel next to the Online Help icon to indicate that the record applies sensitivity settings. Bringing the mouse icon over the padlock icon shows the tooltip with information showing the level of sensitivity applied to the record as well as the User Roles which are allowed to view/modify the record. Clicking the padlock icon shows the Sensitivity Display popup screen showing the same information in the padlock icon tooltip. When the user has the security token/ticket to modify the sensitivity settings, the user is able to see the “Manage Sensitivity” Quick Action from the record display screen, or has the Sensitivity field shown as a hyperlink from the record maintenance screen. Clicking on the “Manage Sensitivity” Quick Action or the Sensitivity field hyperlink will enable the user to manage the sensitivity settings of the record. The user is able to modify the record, send message to the sensitive Contact, export/print the sensitive Contact, or view the communication details of the sensitive Contact as if the sensitivity settings were not applied. Reporting Views The Reporting Views will ignore the sensitivity rules and will only indicate if the record contains sensitivity settings or not by showing the information within the field called “Is Sensitive”. The field returns a value of true or false . Web Service The Web Service will ignore the sensitivity rules at this stage, and will update it in the future to include the flag to indicate the record contains sensitivity settings or not (similar to the “Is Sensitive” field in the reporting views). Outlook Plug-in As the current Outlook plug-in relies on the web service, the plug-in will also ignore the sensitivity rules at this stage. Managing Sensitivity Settings The user will be able to manage the sensitivity settings where their User Role contains the SENSITIVITY MODIFY ticket. The sensitivity setting itself is managed by selecting the “Manage Sensitivity” Quick Action from the record display screen (this is after the record has been created), or else through the Sensitivity field hyperlink from the record maintenance screen (this is when creating a new record or modifying an existing record). As for managing the settings for the Sensitivity based on Type, the user can create or modify the settings from the Reference Table Maintenance screen by creating a new Type or modifying an existing Type. Bypass Sensitivity Settings The user can have a special privilege to treat all sensitivity rules as nothing (bypassing the sensitivity rules) by assigning a special ticket called “SENSITIVITY OVERRIDE” to the User Role. With assigning that ticket to the User Role, the users associated with that User Role are able to view and modify any records in the system regardless of any sensitivity settings applied to the records. Frequently Asked Questions 1. How do I overwrite a record when Type based sensitivity is applied?\ Answer: You can modify the Sensitivity settings of the Type from the Reference Table Maintenance screen to include additional User Roles. Another way is to assign the “SENSITIVITY OVERRIDE” ticket against your current User Role so you can bypass the sensitivity rules for the sensitive record. 2. What happens if a new Role has been created and I would like to associate this new Role to the Sensitivity settings of a Type? Answer: You can modify the Sensitivity settings of the Type from the Reference Table Maintenance screen to include your current User Role. If your User Role has “SENSITIVITY OVERRIDE” ticket, then you will be able to view the record and then modify the sensitivity settings of that record to set it to be based on user selected User Roles and then include your new Role in the User Roles list. 3. What happens if a Role which is included in the Sensitivity Settings is expired? Answer: You will be asked to assign an existing User Role as a replacement for the expired Role. Any sensitivity settings belong to the old User Role will not be deleted and will be marked as “[EXPIRED]”. 4. Can I filter the sensitive records in my reporting view? Answer: You can filter the records based on the information if the record contains sensitivity or not. However, you cannot filter the sensitive records based on specific User Role. 5. Can the sensitive Contacts be restricted through the Web Service? Answer: The current web service does not allow the user to restrict the sensitive contacts. 6. Can the sensitive Contacts be restricted within the Outlook plug-in? Answer: The current Outlook plug-in does not allow the user to restrict the sensitive contacts. 7. What is the precedence of sensitivity rules? Answer: Below is the order of precedence from the lowest importance to the highest importance within the sensitivity rules: (Lowest) No Security > Restricted based on Type > Restricted based on selected User Roles > Visible to Everyone (Highest) Note: The highest order of precedence can override the lower one. 8. What are the Rules for determining which email address, fax number, or mobile numbers to use for refining the send message recipients? Are they impacted if that Contact is sensitive? Answer: All sensitive Contacts are not receiving any message from restricted users. This means the user is prevented from sending message to them. The sensitive Contacts shows the communication details as “Not Available” and do not include the sensitive Contacts into the To/Cc/Bcc fields when they are transferred from Refine Message Recipients screen. 9. What are the Rules for determining which email address to use for auto-subscription to Distribution List? Are they impacted if that Contact is sensitive? Answer: All sensitive Contacts mark the communication details like email addresses, fax numbers, and mobile numbers as “** HIDDEN **”. However, the users are still able to allocate specific communication item to be assigned to the Distribution List based on the communication item type. When adding a member into a Membership Package and the membership package maintain distribution lists, the auto-subscription rules for subscribing communication item into Distribution List will apply. Was this article helpful?