New 3CX v16 Integrations with SQL and NoSQL Databases

[Constantinos Constantinou]

Added Integration for Microsoft SQL Server, PostgreSQL, MySQL and MongoDB​

3CX v16 is really elevating server-side integration with CRM (Customer Relationship Management) systems to maximize your flexibility and potential. Keeping up with our open standards and barrier-breaking tradition, we are also adding integration with the ...

Continue reading the Original Blog Post.

 

[Evolute IT]

Already configured my DB lookup for my CRM database. Awesome!

Can we have docs/examples of SQL queries to use for call journaling and contact creation? I can't find any.

 

[edossantos]

Can we have docs/examples of SQL queries to use for call journaling and contact creation? I can't find any.

Here you have sample queries:
https://www.3cx.com/docs/sql-database-pbx-integration/

 

[ccs1]

Looks great, can we pass infomation such as the call recording or link to the audio (file name) or simlar, so that we can have the call in the CRM as well ?

regards

 

[edossantos]

Looks great, can we pass infomation such as the call recording or link to the audio (file name) or simlar, so that we can have the call in the CRM as well ?

Unfortunately the call recording path is not available as a variable for CRM templates at the moment.

 

[treefiddy]

+1 on the recording integration.

I really hope the API makes a comeback too so CRM integrations can do call control too.

 

[ccs1]

Unfortunately the call recording path is not available as a variable for CRM templates at the moment.

is this in the roadmap (somewhere)

 

[edossantos]

is this in the roadmap (somewhere)

This is a very common need, so it will probably be implemented at some point. However, there are some technical issues related to this, and it's not as easy as it seems...

 

[Constantinos Constantinou]

is this in the roadmap (somewhere)

Hello @ccs1

Updates on feature releases or roadmaps on specific elements of our software announced and will be announced periodically.
Product roadmap is something that we do examine internally based on your valuable input, and plan accordingly. Ideas is the place that you can give priorities to items (@treefiddy) that we should consider on our roadmap. As a result my suggestion is to create an entry for Audio Recording as part of integration with CRM systems and give it a push.

 

[UnConundrum]

Are the sql queries and results logged anywhere? I'm having some difficulties getting it working with MySql, but I don't see any errors in the MySql error.log with verbosity set to 3. I'd like to confirm that 3CX is actually executing the query.

 

[Constantinos Constantinou]

Are the sql queries and results logged anywhere? I'm having some difficulties getting it working with MySql, but I don't see any errors in the MySql error.log with verbosity set to 3. I'd like to confirm that 3CX is actually executing the query.

Hello @UnConundrum ,

You can enable the verbose logging on your 3CX Management Console -> Dashboard -> Activity Log.
With this approach you can check the generated log file for the executed queries.

 

[UnConundrum]

Thank you.
I also enabled the general log on MySql and did find the selects there, verifying that my account is correct and logging in.

What I don't see is any activity relating to the Call Journaling. I have it enabled, and my sql statement is:

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

A

grep -i -e"insert into calls" *

in /var/lib/3cxpbx/Instance1/Data/Logs finds nothing. Any ideas?

Also, although I said the select was working, the test for it has issues. My sql query is:

SELECT  contacts.id as contactid, first_name as firstname, last_name as lastname, '' as email, '' as phonebusiness, '' as phonemobile, '' as faxbusiness FROM contacts  inner join phone_numbers phone on phone.contact_id = contacts.id where contacts.intranet_id in (1, 28)  and contacts.dod is null and replace(phone.number,'-','') like '%[Number]%' limit 1;

The results indicate that no record was found, yet proper results show in the text that follows. I also noticed that it truncates my search term "3237700" to only 6 characters instead of the 7 I submitted. Adding the area code still results in a submission of 6 characters.

 

[Constantinos Constantinou]

Thank you.
I also enabled the general log on MySql and did find the selects there, verifying that my account is correct and logging in.

What I don't see is any activity relating to the Call Journaling. I have it enabled, and my sql statement is:

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

A

grep -i -e"insert into calls" *

in /var/lib/3cxpbx/Instance1/Data/Logs finds nothing. Any ideas?

Also, although I said the select was working, the test for it has issues. My sql query is:

SELECT  contacts.id as contactid, first_name as firstname, last_name as lastname, '' as email, '' as phonebusiness, '' as phonemobile, '' as faxbusiness FROM contacts  inner join phone_numbers phone on phone.contact_id = contacts.id where contacts.intranet_id in (1, 28)  and contacts.dod is null and replace(phone.number,'-','') like '%[Number]%' limit 1;

The results indicate that no record was found, yet proper results show in the text that follows. I also noticed that it truncates my search term "3237700" to only 6 characters instead of the 7 I submitted. Adding the area code still results in a submission of 6 characters.

View attachment 10071

Hello @UnConundrum
We will check it internally and we will revert back to you soon.

 

[Constantinos Constantinou]

Thank you.
I also enabled the general log on MySql and did find the selects there, verifying that my account is correct and logging in.

What I don't see is any activity relating to the Call Journaling. I have it enabled, and my sql statement is:

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

A

grep -i -e"insert into calls" *

in /var/lib/3cxpbx/Instance1/Data/Logs finds nothing. Any ideas?

Also, although I said the select was working, the test for it has issues. My sql query is:

SELECT  contacts.id as contactid, first_name as firstname, last_name as lastname, '' as email, '' as phonebusiness, '' as phonemobile, '' as faxbusiness FROM contacts  inner join phone_numbers phone on phone.contact_id = contacts.id where contacts.intranet_id in (1, 28)  and contacts.dod is null and replace(phone.number,'-','') like '%[Number]%' limit 1;

The results indicate that no record was found, yet proper results show in the text that follows. I also noticed that it truncates my search term "3237700" to only 6 characters instead of the 7 I submitted. Adding the area code still results in a submission of 6 characters.

View attachment 10071

Hello @UnConundrum,

First of all, we can see that your returned columns on the select statement are blank. If those fields (business phone, mobile) are blank then the system is not aware of the phone number returned.
As you can see in the testing scenario above, the set to attempts are blank.
In order to include all 7 numbers in your query, navigate to your Management Console -> Advanced-> Contacts -> Options and Set Match at least X number of characters to 7.

 

[UnConundrum]

Thank you. That helped.