Introduction

3CX provides integration with SQL databases. The configuration is done server side, in an easy and straightforward way. This integration enables 3CX PBX to connect to Microsoft SQL Server, MySQL and PostgreSQL databases, providing these benefits:

  • Contact Synchronization – Inbound calls from external numbers trigger a contact lookup in your database, and contact details are added to 3CX Contacts. This way, the caller name is automatically shown in your phone display when you receive the call.
  • Call Pop-ups – When using the 3CX Web Client, the customer record is brought up to you automatically when you receive an inbound call.
  • Call Journals – Calls are logged in the database.
  • Create a new contact automatically when a call is received from an unknown number.

This guide takes you through the steps required to setup your SQL database with 3CX.

3CX Configuration

General Settings

  1. Login to the 3CX Management Console, go to “Settings” > “CRM Integration” > “Server Side” tab and select your SQL database type from the dropdown list.

2. Enter the connection information:

    • Server: the server name or IP address. For MS SQL Server can include the instance name or port number when needed, e.g.:
      • myServerName
      • myServerName\myInstanceName
      • myServerAddress,Port
    • Port: the port number on which the connection must be established. Not applicable to MS SQL Server.
    • Database: the name of the database to use.
    • Username: the username to use to connect to the database.
    • Password: the password to use to connect to the database.

3. Enter the Lookup SQL Statement. This must be a SELECT statement returning the following case-sensitive columns: contactid, firstname, lastname, companyname, email, phonemobile, phonemobile2, phonehome, phonehome2, phonebusiness, phonebusiness2, phoneother, faxbusiness, faxhome, pager, photourl. If your database table has different column names, you can change the name returned as follows:

SELECT id as contactid, first_name as firstname, last_name as lastname, email as email, phone as phonebusiness, mobile as phonemobile, fax as faxbusiness FROM contacts WHERE phone LIKE '%[Number]%' or mobile like '%[Number]%' or fax LIKE '%[Number]%'

4. Enter the Contact URL prefix and suffix. These are required to create the Contact URL, which is the concatenation of the Contact URL Prefix, the ContactID returned by the database, and the Contact URL Suffix. This Contact URL is considered the key for the contact in 3CX Contacts, and is used to update the contact if any change is detected in the database. Also, this Contact URL is shown in the 3CX Web Client, so if you have a web page to show the contact record, use these settings to create the URL to point to that page.

Call Journaling

  1. If you want to report external calls to the database, check the Enable Call Journaling checkbox and configure the Call Journaling SQL Statement.
  1. Please note that you can use variables in the Call Journaling SQL Statement. Variables are specified between [], for example the external number is specified as [Number]. The available variables are:
    • CallType - The type of call, it can be “Inbound”, “Outbound”, “Missed”, or “Unanswered”.
    • Number - The external contact number (the number dialed for outbound calls or the caller number for inbound calls).
    • Agent - The extension number of the agent handling the call.
    • Duration - The duration of the call in “hh:mm:ss” format.
    • DurationTimeSpan - The duration of the call as a TimeSpan object, which can be formatted as the user wants.
    • DateTime - The start date & time of the call, in local time zone, formatted using the local culture from the 3CX server.
    • CallStartTimeLocal - The start date & time of the call, in local time zone, as a DateTime object, which can be formatted as the user wants.
    • CallStartTimeUTC - The start date & time of the call, in UTC time zone, as a DateTime object, which can be formatted as the user wants.
    • CallEndTimeLocal - The end date & time of the call, in local time zone, as a DateTime object, which can be formatted as the user wants.
    • CallEndTimeUTC - The end date & time of the call, in UTC time zone, as a DateTime object, which can be formatted as the user wants.
  2. Your Call Journaling SQL Statement should be an INSERT statement, for example:

INSERT INTO calls (contactnumber, agentextension, description, calldatetime, callduration) VALUES ('[Number]', '[Agent]', '3CX PhoneSystem Call', '[DateTime]', '[Duration]')

Contact Creation

  1. To create new contacts in the database when the caller number can’t be found, check the “Enable Contact Creation” checkbox and configure the “Contact Creation SQL Statement”.
  2. Please note that you can use variables in the Contact Creation SQL Statement. Variables are specified between [], for example the external number is specified as [Number]. The available variables are:
    • Number - The external contact number (the number dialed for outbound calls or the caller number for inbound calls).
  1. The Contact Creation SQL Statement must return the following case-sensitive columns for the contact created: contactid, firstname, lastname, companyname, email, phonemobile, phonemobile2, phonehome, phonehome2, phonebusiness, phonebusiness2, phoneother, faxbusiness, faxhome, pager, photourl. You can change the name of the columns returned using the same technique explained above for the Lookup SQL Statement.
  2. For the Contact Creation SQL Statement, you can use a combined statement, inserting the record first, and querying it later. For example:

INSERT INTO contacts (first_name, last_name, phone) VALUES ('New', '3CX Contact', '[Number]');SELECT id as contactid, first_name as firstname, last_name as lastname, phone as phonebusiness FROM contacts WHERE phone = '[Number]'