Third party integration

You can find documentation and step-by-step guide on how to integrate EspoCRM with the following tools:

• Kobo
• Power Query (Excel & Power BI)
• 121
• RedRose
• SendGrid (emails)
• Twilio Messaging (SMS, WhatsApp)
• Twilio Flex (DEH)
• Africa's Talking (SMS)

Integrate espoCRM with KoBo

This article explains how to integrate espoCRM with kobotoolbox. In this example we are using the IFRC server. There are two options for doing this:

1. Make a direct connection between KoBo and espoCRM through the REST functionality of KoBo
2. Use KoboConnect
3. [DEPRECATED] Use an Azure Logic App

For option 1 you don't need additional resources, but it has the following drawbacks:

• There is no support for images or other attachments
• It can take a lot of time to configure in the xlsform
• Debugging can be difficult and time consuming

Option 2 only requires some extra configuration of the Kobo REST Service.

Option 3 requires some basic knowledge of python and Azure and costs money (although it is inexpensive).

All options are explained below, but first universal preparation steps are listed:

Preparation

• Create all the fields in EspoCRM that you will need
• Create the corresponding layout(s)
• Create an API user in EspoCRM with a corresponding role

Option 1: Direct connection through KoBo REST service

Below is a very short list of steps, this will be improved in the future

• Create your form in your preferred way
• Download the xlsform
• Create hidden fields with the "name" corresponding to the fieldnames in espoCRM
• Use calculations to "map" the answers to the questions of the survey, for example:
• Upload the updated xlsForm in KoBo
• Go to your form, click on settings and then REST Services
• Fill in the following things
  • endpoint: this is the API endpoint for the entity that you want to make an entity for
  • Select fields subset: type all the fields you want to push to espoCRM (tip: you can paste a comma separated list)
  • Custom HTTP Headers: X-Api-Key - copy & paste the api key from the api user in EspoCRM
• Test and debug

Option 2: use KoboConnect

See KoboConnect docs. Note: if debugging is needed, this can be done by checking the logs of EspoCRM

Warning

Be aware that boolean values are not send over the REST service by Kobo Also, fields in EspoCRM cannot be Read-only, because it doesnot allow the REST service to enter information

Option 3: use an Azure Logic App

This option is DEPRECATED in favor of KoboConnect, due to ongoing issues with docker containers.

• Clone this repository
• Create the mapping of fields from KoBo to EspoCRM: .csv uder data/, example here
• Add all necessary credentials: create a file called .env under credentials/, example here, and fill out neccesary information:
  • TOKEN, your kobo api token, as retrieved here, if logged into KoBo
  • ASSET, the asset id of your form, can be extracted from the url. Go to kobo, click on the form, the ASSET is in the url: https://kobonew.ifrc.org/#/forms/`ASSET`/landing
  • ESPOURL, the url of your EspoCRM instance (make sure not to include a trailing /)
  • ESPOAPIKEY = the API key can be copied from the API User in EspoCRM
  • ESPOENTITY = The name of the entity that you want to push your information to
• Configure the Logic App as explained here
• Go to your form in kobo, click on settings and then REST Services
• Fill in the following things
  • endpoint: copy and paste the generated endpoint in the logic app workflow after you save the workflow
  • Custom HTTP Headers: X-Api-Key - copy & paste the api key from the api user in EspoCRM
• Test and debug

Integrate EspoCRM with PowerBI via API

This article explains how to integrate Espo CRM with Power BI. The steps are the same to integrate Espo with Excel (through Power Query)

Preparations in Espo:

1. Create an API user and role

Preparations in PowerBI:

1. Select get data, and then select web. Select Advanced.
2. Enter in the URL section: name of the Espo instance/api/v1/name of the entity
3. In the HTTP request header, add “X-API-Key” and add your api key
4. Make sure to select connecting Anonymous in the "Access Web content" pop-up
5. Repeat that for all entities that you would like to add to PBI

Tips

1. If you want to only export specific fields, add “?select=[field1,field2] after entity (f.e. name of the Espo instance/api/v1/name of the entity?select=[field1,field2])
2. Providing an API key makes PBI store it and it’s visible in an advanced editor, which can cause security risks. An option is to store the key in a text file on SharePoint in a password-protected folder. This is how it can look like from the Advanced Editor

   Source = Json.Document(Web.Contents(URL, [Headers=[#"Content-Type" = "application/json",
        #"X-Api-Key" = List.First(Table.ToList(Table.FromColumns({Lines.FromBinary(Web.Contents(
            "https://rodekruis.sharepoint.com/sites/team-XXXX/key.txt"),
            null, null, 1252)})))], RelativePath=path])),
   

3. Espo API has a limit of 200 results. The document has to change it is here
4. Instead of changing the limit, you can also apply pagination within PowerBI to make sure all records in Espo are loaded into the dashboard. To do so, you will need two Powerbi queries for each entity that you want to load.

• The first query defines a function that will be used to load 1000 records from the entity in Espo:

    (offset) =>
    let
        URL = "<base_url>",
        path = "api/v1/<entity>?maxSize=1000&offset=" & Number.ToText(offset),
        Source = Json.Document(Web.Contents(URL, [Headers=[#"Content-Type" = "application/json", #"X-Api-Key" = <api_key>], RelativePath=path])),
        #"Converted to table" = Table.FromRecords({Source})
    in
        #"Converted to table"
  

• The second query will keep on calling this function until all records of the entity in Espo have been loaded. To be able to call upon above function, we should name the query. Assuming we call the first query getRecords, the second query would look like:

    let          
        Source = List.Generate( 
                    ()  =>  [ offset = 1000, records = getRecords( 0 ) ], 
                    each not ( Table.RowCount([records]) = 1 ),
                    each [offset = [offset] + 1000 , records = getRecords( [offset] ) ],
                    each [records]
        ),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table"
  

121

This article explains how to integrate espoCRM with 121. In this example we are using the APIs of the 121 staging server. For espoCRM we will use placeholder url https://yourespourl.org, so make sure to replace this when copying urls

Preparation

• There is an espoCRM entity (for this manual we assume the Contact entity to be used) that is used for registrations, with following fields:
  • enum status121, with options: notimported and imported, default should be set tonotimported
  • datetime importdatetime121
• There is an espoCRM entity called transfers, linked many-to-one to the registrations entity with following fields:
  • date dateOfIssue
  • integer paymentNumber (this should be auto-incremented per registration for each consecutive payment)
  • enum status, with options: ordered, approved, pending, success, failed, retry
  • integer transferAmount
  • datetime transfer121

Steps

1. Create a 121 User with permissions to create registrations and execute payments
2. Create a global variables entity in EspoCRM
3. Create a flowchart for obtaining the auth Cookie
4. Create a flowchart for creating the registration in 121
5. Create a flowchart for doing payments in 121
6. Create a flowchart for showing 121 transfer status in espocrm
7. Create a flowchart for retrying failed transfers in 121

Create a 121 User with permissions to create registrations and execute payments

• Go to the 121 swagger UI
• Make sure you are logged in with an admin account, if not use the login endpoint
• Create a new user via the aidworker endpoint, with following body (make sure to replace the password with a complex, generated password):

{
  "email": "[email protected]",
  "password": "password"
}

• Write down the user.id of the API response
• Create a new user-role via the roles endpoint, with the following body:

{
  "role": "espo",
  "label": "Create user accounts and execute payments",
  "permissions": [
    "payment.create",
    "payment.read",
    "payment:transaction.read",
    "registration.create",
    "registration:attribute.update",
    "registration:fsp.update"
  ]
}

• Assign the espo-user to the espo role for the program in 121 you will use, via the assign Aidworker endpoint, with userID as written down and programId of the corresponding 121 program (can be found in the url if you login to the portal) the following body:

{
  "roles": ["espo"]
}

Create a global variables entity in EspoCRM

• Create a new entity called GlobalVariable in the entity manager
• In this newly created entity create 2 varchar fields, with following names:
  • key
  • value
• Make sure key and value are the only fields of the detail layout
• Go to formula and set the name equal to the key: name = key;
• Add a new Global Variable record, with following attributes
  • key: [email protected]
  • value: set this to the password you had generated for the 121 user
• Add another Global Variable record, with following attributes
  • key: 121Cookie
  • value: leave this empty

Create a flowchart for obtaining the auth Cookie

• As preparation we need to create a report, with the 121Cookie on the report
  • create new report for the Entity GlobalVariable
  • set name to 121 - Get auth cookie
  • with 2 Filters:
    • Value: Is Empty OR Value: Modified at: Older than X days: 10
    • Name: Equals: 121Cookie
• In espoCRM, go to Admin -> Flowcharts
• Create a new flowchart, with following attributes
  • name: 121 - get auth Cookie
  • Target Entity Type: Global Variable
  • Then for the flowchart itself:
    • Create a Timer Start Event, for which you select the report created earlier as the Target Report and the set the scheduling on 30 1 * * *
    • Create an action with name Get token and save, and following actions:
      • Execute Formula Script: $key = record\attribute("GlobalVariable", "[email protected]", "value"); (this retrieves the password for the [email protected] account)
      • Send HTTP Request
        • Request Type: POST
        • URL: https://staging.121.global/api/user/login
        • Headers: leave empty
        • Content Type: application/json
        • Payload:

        {
          "username": "[email protected]",
          "password": "{$$key}"
        }

      • Execute Formula Script, to save the response body to the 121Cookie record:

      $response = $_lastHttpResponseBody;
      $token = json\retrieve($response, 'access_token_general');
      value = $token
      

    • Create an end event and draw arrows

Create a flowchart for creating the registration in 121

• First create a report, with all registrations that are not yet in 121.

• Create a new flowchart, with following attributes

  • name: 121 - create registration in 121

  • Target Entity Type: Contact (or the entity you are using for registrations)

  • Then for the flowchart itself:

    • Create a Timer Start Event, for which you select the report created earlier as the Target Report and the set the scheduling on */5 * * * * (every 5 mins)

    • Create an action with name Create registration in 121, and following actions:

      • Execute Formula Script: $token = record\attribute("GlobalVariable", "IdOf121CookieRecord", "value") (this retrieves the token to authenticate the [email protected] account)

      • Send HTTP Request

        • Request Type: POST
        • URL: https://staging.121.global/api/programs/3/registrations/import (change the 3 for the correct program of 121)
        • Headers: Cookie:access_token_general={$$token}
        • Content Type: application/json
        • Payload (this depends on the 121 program configuration):

        [
            {
                "preferredLanguage": "{$preferredLanguage}",
                "paymentAmountMultiplier": {$paymentAmountMultiplier},
                "firstName": "{$firstName}",
                "lastName": "{$lastName}",
                "phoneNumber": "{$phoneNumber}",
                "fspName": "{$fspName}",
                "whatsappPhoneNumber": "{$whatsappPhoneNumber}",
                "addressStreet": "{$addressStreet}",
                "addressHouseNumber": {$addressHouseNumber},
                "addressHouseNumberAddition": "{$addressHouseNumberAddition}",
                "addressPostalCode": "{$addressPostalCode}",
                "addressCity": "{$addressCity}",
                "referenceId": "{$referenceId}"
            }
        ]
        

      • Execute Formula Script, to save the response body to the Contact record:

      $response = $_lastHttpResponseBody;
      $countimported = json\retrieve($response, 'aggregateImportResult.countImported');
      ifThen($countimported == 1, importdatetime121=datetime\now());
      ifThen($countimported == 1, status121='imported');
      

      Please note that importdatetime121 and status121 should be fields in Contact (or the entity you are using for registrations)

    • Create an end event and draw arrows

Create a flowchart for doing payments in 121

• Create a report, with all transfers that due today or in the past and have status approved
• Create a new flowchart, with following attributes
  • name: 121 - do payment
  • Target Entity Type: Transfer
  • Then for the flowchart itself:
    • Create a Timer Start Event, for which you select the report created earlier as the Target Report and the set the scheduling on 0 9 * * * (09:00 every morning)
    • Create an action with name Transfer money in 121, and following actions:
      • Execute Formula Script: $token = record\attribute("GlobalVariable", "IdOf121CookieRecord", "value") (this retrieves the token to authenticate the [email protected] account)
      • Send HTTP Request
        • Request Type: POST
        • URL: https://staging.121.global/api/programs/3/payments (change the 3 for the correct program of 121)
        • Headers: Cookie:access_token_general={$$token}
        • Content Type: application/json
        • Payload (this depends on the 121 program configuration):

        {
        "payment": {$paymentNumber},
        "amount": {$transferAmount},
        "referenceIds": {
            "referenceIds": [
            "{$contactId}"
            ]
        }

        If your using a different entity for registration data, make sure to replace contactId
      • Execute Formula Script, to save the response body to the Transfer record:

      $response = $_lastHttpResponseBody;
      $countimported = $response
      ifThen($countimported == 1, transfer121=datetime\now());
      ifThenElse($countimported == 1, status121='pending', status121='failed');
      

    • Create an end event and draw arrows

Create a flowchart for showing 121 transfer status in espocrm

• Create a report, with all transfers that have pending status
• Create a new flowchart, with following attributes
  • name: 121 - Get transfer status
  • Target Entity Type: Transfer
  • Then for the flowchart itself:
    • Create a Timer Start Event, for which you select the report created earlier as the Target Report and the set the scheduling on *\30 * * * * (every 30 minutes)
    • Create an action with name Update transfer status, and following actions:
      • Execute Formula Script: $token = record\attribute("GlobalVariable", "IdOf121CookieRecord", "value") (this retrieves the token to authenticate the [email protected] account)
      • Send HTTP Request
        • Request Type: GET
        • URL: https://staging.121.global/api/programs/3/payments/transactions?minPayment={$paymentNumber}&referenceId={$contactId} (change the 3 for the correct program of 121)
        • Headers: Cookie:access_token_general={$$token}
        • Content Type: application/json
        • Payload: can be empty
      • Execute Formula Script, to save the response body to the Transfer record:

      $response = $_lastHttpResponseBody;
      $status = json\retrieve($response, '0.status');
      ifThenElse($status == 'success', status='success', status121='failed');
      

    • Create an end event and draw arrows

Create a flowchart for retrying failed transfers in 121

• Manually set failed transfers to status: retry
• Create a report, with all transfers that with status retry
• Create a new flowchart, with following attributes
  • name: 121 - retry payments
  • Target Entity Type: Transfer
  • Then for the flowchart itself:
    • Create a Timer Start Event, for which you select the report created earlier as the Target Report and the set the scheduling on *\5 * * * * (09:00 every morning)
    • Create an action with name Retry transfer in 121, and following actions:
      • Execute Formula Script: $token = record\attribute("GlobalVariable", "IdOf121CookieRecord", "value") (this retrieves the token to authenticate the [email protected] account)
      • Send HTTP Request
        • Request Type: PATCH
        • URL: https://staging.121.global/api/programs/3/payments (change the 3 for the correct program of 121)
        • Headers: Cookie:access_token_general={$$token}
        • Content Type: application/json
        • Payload (this depends on the 121 program configuration):

        {
        "payment": {$paymentNumber},
        "referenceIds": {
            "referenceIds": [
            "{$contactId}"
            ]
        }

        If your using a different entity for registration data, make sure to replace contactId
      • Execute Formula Script, to save the response body to the Transfer record:

      $response = $_lastHttpResponseBody;
      $countimported = $response
      ifThen($countimported == 1, transfer121=datetime\now());
      ifThenElse($countimported == 1, status121='pending', status121='failed');
      

    • Create an end event and draw arrows

SendGrid

SendGrid is a cloud-based mass email service owned by Twilio. It offers a free plan with 100 emails/day; 510 has a paid account, contact Maarten to get access.

Set up:

1. Sign up and check the Setup Guide
2. Create a sender identity, i.e. the “from” email address your recipients see in their inbox
3. Select SMTP relay and create an API key
4. in EspoCRM, go to Administration -> Outbound Emails, and configure as shown on SendGrid -> SMTP Relay
5. select Send Test Email and verify that it works

Twilio Messaging integration

It is possible to send SMS and/or Whatsapp messages through Twilio Messaging from EspoCRM. We need:

1. An active Twilio Account, with an active Messaging Service.
2. The EspoCRM Advanced Pack, to use flowcharts.
3. A dedicated entity in EspoCRM, e.g. Message.
4. [OPTIONAL] A dedicated webhook for status callbacks, e.g. an Azure Function.

Simple setup (without status callbacks)

The Message entity needs to contain at least the following fields:

• text (text)
• to (varchar)
• twiliosid (varchar)
• deliveryStatus (enum), with values e.g. sent, delivered, failed

First recover your Twilio Auth Token and Twilio Account SID (find it in Twilio > Account > API keys & tokens) and use them to generate an encoded authentication header, for example using this website

username: Twilio Account SID
password: Twilio Auth Token

the header should look like

Authorization: Basic <encoded key>

The messages can then be sent via a dedicated Flowchart, which needs to contain at least:

1. One script task that sets the variables $auth, containing the encoded authentication credentials, and the $messagingservicesid

$auth = 'Basic <encoded key>';
$messagingservicesid = '<my twilio messaging sid>';

You can find <my twilio messaging sid> in Twilio > Messaging > Services.

2. One task that first Send HTTP Request to the Twilio Messaging API then Execute Formula Script to save the twilio SID and update the delivery status

Request Type
POST
URL
https://api.twilio.com/2010-04-01/Accounts/<my twilio account sid>/Messages.json
Headers
Authorization: {$$auth}
Content Type
application/x-www-form-urlencoded
Payload
{
    "To": "{$to}",
    "MessagingServiceSid": "{$$messagingservicesid}",
    "Body": "{$text}"
}

Execute Formula Script
$sid = json\retrieve($_lastHttpResponseBody, 'sid');
twiliosid = $sid;
deliveryStatus = 'sent';

Advanced setup (with status callbacks)

TBI

Twilio Flex integration (DEH)

Important

The Digital Engagement Hub is developing a product which is a standardised engagement platform for use across the Red Cross movement. This is not to be confused with Twilio Messaging which is another product of Twilio and can also be integrated with EspoCRM as explained in the previous section. Please refer to the latest docs

An omni-channel contact center with CRM integration for use across all Red Cross National Societies. This is a product for deploying digital services worldwide and leverages Twilio's communication capabilities mainly via Twilio Flex with EspoCRM as an underlying data storage and agent interface. The backend of this product is partly made possible via shared resources in Azure.

The product comprises two main tangible components:

• A Twilio Flex instance providing agents with functionality for receiving calls, Facebook Messenger, and WhatsApp via their device. (channels dependent on availability in country)
• An EspoCRM instance (embedded into Flex) allowing agents to retrieve and store relevant information relating to a person affected (PA) such as personal details, case information, interactions, etc.

Glossary

Terminology	Definition
EspoCRM	An open source CRM solution
Azure	Microsoft's cloud computing platform serving as backend
Twilio Flex	Cloud-based contact center solution provided by Twilio
Twilio Console	UI of Twilio to manage accounts, buy and configure Twilio products such as Flex
Agent	Role of a user in Twilio Flex who interacts with PAs
Supervisor	Role in Twilio Flex who manages agents and can see internal dashboards
Admin	Role in Twilio Flex with privileged access. Admins can login via SSO and via the Console
Person Affected (PA)	Person in need of assistance from the Red Cross, or more broadly speaking, these are the persons who engage with the Red Cross
Case	A request, pertaining to a PA, which relates to a particular service that the Red Cross can assist with
Interaction	Details contact between a PA & a Red Cross agent. This may be a call, SMS, WhatsApp, etc.
Contact	The entity name for the a PA in EspoCRM

Deployment

See the Deployment documentation.

User Manuals

Agent manuals can be found here (in progress)

Supervisor and admin manuals can be found here (in progress)

Operation

See the Architecture documentation for an understanding of the product's components.

See the Troubleshooting guide for known issues and common problems.

If you want to adjust the Worker Skills configuration please see this guide

See Life Cycle Diagrams for informaiton about the life-cycles of conversations and calls.

If the Azure Resources Stack profiles need different settings, this can be done by updating a scaling profile. In a similar way, the Terraform (State) Files could be adjusted here.

AfricasTalking

It is possible to send SMS messages through AfricasTalking from EspoCRM. We need:

1. An active AfricasTalking Account, with an active App.
2. The EspoCRM Advanced Pack, to use flowcharts.
3. A dedicated entity in EspoCRM, e.g. Message.
4. [OPTIONAL] A dedicated webhook for status callbacks, e.g. an Azure Function.

Simple setup (without status callbacks)

The Message entity needs to contain the following fields:

• text (text)
• to (varchar)
• sentAt (datetime)
• messageId (varchar)
• cost (varchar)
• status (varchar)
• statusCode (enum), with values 100, 101, 102, 401, etc. see complete list in the AfricasTalking API docs

Recover your AfricasTalking username and API key (find it in AfricasTalking > Home > Apps).

The messages can then be sent via a dedicated Flowchart, which needs to contain at least:

1. One task that first Send HTTP Request to the AfricasTalking API then Execute Formula Script to save the AfricasTalking message ID

Request Type
POST
URL
https://api.africastalking.com/version1/messaging
Headers
Accept: application/json
apiKey: <your API key>
Content Type
application/x-www-form-urlencoded
Payload
{
    "username": "<your username>",
    "to": "{$to}",
    "message": "{$text}"
}

Execute Formula Script
status = json\retrieve($_lastHttpResponseBody, 'SMSMessageData.Recipients.0.status');
statusCode = json\retrieve($_lastHttpResponseBody, 'SMSMessageData.Recipients.0.statusCode');
cost = json\retrieve($_lastHttpResponseBody, 'SMSMessageData.Recipients.0.cost');
messageId = json\retrieve($_lastHttpResponseBody, 'SMSMessageData.Recipients.0.messageId');
if (status == "Success") {
    sentAt = datetime\now();
}

Advanced setup (with status callbacks)

TBI