ClicData Integration

There are two parts to setting up your ClicData integration. The first part is to set up the connection between ClicData and your OpenCRM system. The second part of the integration is to add specific data connections that will bring the information from OpenCRM into ClicData. You will need to set up a data connection for each module or report you want to use, depending on whether you want to filter the data in OpenCRM or ClicData, but you will only need to set up the overarching connection once.

Before you get started

When you first sign-up for ClicData, you will need to choose which account you would like to purchase with them. These accounts offer different numbers of administrators, API calls, data refreshes, etc. For the purposes of this FAQ, the important information here is the API Calls and Data Refreshes.

API Call - an API call, in this integration, refers to the number of times ClicData will send information to OpenCRM. This is not possible at the time this FAQ was written.

Data Refresh - a data refresh, on the other hand, speaks to the number of times OpenCRM sends information to ClicData. For example, if you only have 10 data refreshes per month with your ClicData account, but have a Report that  you would like to have sent to ClicData every day, you will quickly exceed this limit. You would need at least 30 data refreshes to meet this schedule.

Quick Tip: To ensure you maximise your data refreshes, if you are going to be sending OpenCRM Reports to ClicData, try to configure your Reports so that they are big enough to cover a number of different widgets, graphs, and dashboards.

Support

If you run into any trouble with setting up the connection between OpenCRM and ClicData, please contact our support team. 

Once you have the connection set up, any assistance you require with creating dashboards or widgets within ClicData will need to be provided by ClicData. You can access their Knowledgebase via the question mark icon in the top right of the ClicData interface. You can also open support tickets with them by going to the Support tab along to top of the screen.

Setup ClicData Connection to OpenCRM

1. The first thing you will need to do is get in touch with our support team, they will need to enable your system to connect to ClicData and provide you with an API key.

2. Once you have received confirmation that this has been done, login to http://www.clicdata.com (or your own ClicData url)

3. Click on Data at the top

4. In the middle of the page click Connect

5. Click Add New Connection

6. Select Web Service by scrolling down the popup list

7. Properties Tab

• Name: Give your connection a name, "OpenCRM" is a good one 😉
• Description: This is optional
• Protocol : HTTPS
• Host : {yoursystem}.opencrm.co.uk replacing {yoursystem} with your OpenCRM system url
• Port: This will auto-populate, do not change it.

8. Go now to the "Headers" tab on the left (you can ignore the Authentication tab)

• If you have not done so already, you should make sure to contact our Support team to request an API key for your system.
• Enter these two keys exactly as below, using the key names "key1" and "key2"

9. Click to Save your Connection

You do not need to "test" the connection - this will more than likely throw an error message if you do, but this can be ignored.

Your OpenCRM system is now connected to ClicData. You can now set up the connections between specific modules and/or reports.

Add Data Using Your New Web Service Connection

1. Click on the Data title in the left menu

2. Click Connect in the middle of the page

3. Select your new Web Service

4. Enter the API endpoint url with any details required (see below for a full list of available endpoints)

    The endpoint url should be /api/rest/ { endpoint function to run }  / followed by options and values eg recordid = 42 will be recordid/42
    Multiple options an values can be appended depending on the endpoint arguments availabe
    Example of a full endpoint url: /api/rest/get_record_list_from_report/reportid/42

5. Click the Test button.  This should respond with a tick with the text "200 - OK".  If it says "401 - Unauthorized" then please check your API keys are entered correctly.  "404 - Not Found" please check your endpoint url is correct.  405 or 500 please contact ClicData or OpenCRM support

6. Go to the "Test" tab on the left, when you click Test, you should see the below "200 - OK" message

7. Go to the Schema tab and, if you want to be able to update records rather than just add new - see below for more information on this, set any unique fields by ticking the key box

8. Go to Preview and make sure the data looks right

9. Click the Next button nd ClicData will now import all your data.  You can also schedule this data to be imported again either daily on hourly (these options can be set later). This is where you need to keep in mind the number of Data Refreshes you have purchased.

Unique Key

You have a number of options with how ClicData brings in new data:

• Reload - Does what it says on the tin, recreates your data with each import. If anything has been deleted or filtered out in OpenCRM, it will no longer appear in ClicData. This option is the easiest, but if you are using a Report from OpenCRM to populate ClicData, just remember that if a record is excluded from the report due to it no longer matching the filters, it will be removed. For example, if your Report filters on items created in the last 365 days, anything created before that will not show up in ClicData. 
• Append - This will keep all data previously brought into ClicData and just add the new information to it. You cannot add new fields or columns to your report if you use the Append option. If you do not have your filters set up correctly, you may end up with duplicates using this option. It can also result in a very large data file in ClicData that may result in a slow down.
• Update - The update option will use a unique key to identify and match up records from OpenCRM with their row in ClicData. It will then update any values that have changed since the last import. It will not, however, add any new records.
• Update and Append - Finally, this option will update existing data with new values and also add any new records from OpenCRM into ClicData. It requires a unique key. Like the Append option, this can give you a very large data file in ClicData that may result in a slow down.

If you want to "Update" or "Update and Append" your data in ClicData, rather than "Reload" or "Append" it, you will need to identify a unique key, i.e. a value that will be different for each row of your data. The CRMID should work well for this purpose. 

To set up the unique key:

1. Select the data you want to edit and choose "Properties" from the bottom of the screen
2. Navigate to "Schema" and tick the box next to the unique key field
3. Click Save - if the values are not unique, you will be notified instantly

List of API Endpoints Available

Here are a list of the various API endpoints you can use to "call" data from OpenCRM into ClicData. If you have any questions about these, please don't hesitate to get in touch with our support team.

Anything with these {brackets} means that you will need to enter specific information from your system. For example, if the endpoint says {report id}, you will need to look at the URL of the report (or hover over the name and look in the bottom right of your screen) to get the id:

   or   

You can also add some variables at the end of the endpoint to filter the information. These are listed here, but explained below:

• Select specific Report: /api/rest/get_record_list_from_report/reportid/{report id}
  • Optional variables to append: filtercolumn, filter, startdate, enddate
• Select Activities: /api/rest/get_activity_list/
  • Optional variables to append: query_string, limit_start, limit_end, keywords
• Companies: /api/rest/get_company_list/
  • Optional variables to append: query_string, limit_start, limit_end, keywords
• Leads: /api/rest/get_lead_list/
  • Optional variables to append: query_string, limit_start, limit_end, keywords
• Opportunities: /api/rest/get_opportunity_list/
  • Optional variables to append: query_string, limit_start, limit_end, keywords
• Projects: /api/rest/get_project_list/
  • Optional variables to append: query_string, limit_start, limit_end, keywords
• Any other module: /api/rest/get_record_list_full/module/{module name}
  • Optional variables to append: query_string, limit_start, limit_end, keywords

Optional Appending Variables

Date and Time Formats

Dates and Times in the format of 2015-08-04 15:04:01 can be converted to standard php date time formats by adding to the url dateTimeOverride/ followed by the format, replacing spaces with "_" underscores and forward slashed with "*" stars eg d_F_Y_H:i

/api/rest/get_project_list/dateTimeOverride/d_F_Y_H:i

Dates without the time in the format of 04-08-2015 can be converted in a similar way using dateOverride/ followed by the format

/api/rest/get_project_list/dateOverride/d_F_Y_H:i

These can be combined in the same endpoint url

/api/rest/get_project_list/dateTimeOverride/d_F_Y_H:i/dateOverride/d_F_Y

Record Limits

04/08/2015 - the record limit is currently set at 50000 records. If you need more records the set the limit_start to 0 and limit_end value to a reasonable amount above the number of records you expect to import

eg. /api/rest/get_record_list_from_report/reportid/4/limit_start/0/limit_end/100000