Solved SQL and CRM integration "CRM Integration is working OK but contact number does not exist."

[DanThor]

I'm trying to check contacts in an SQL database. There doesn't appear to be an issue connecting and finding records but I get a message the contact number does not exist even though the record is returned.

Does anyone have thoughts on what I can check?

Thank you!

 

[edossantos]

That's weird, I can't spot what's wrong. The contact lookup is somehow not accepting the response, but it has all the required fields from what I can see. Can you send me that log as text in a private message? Please remove your credentials.

 

[greychain]

Here is what I found that solved my problem. Don't use the CRM template generator generated XML . Use the default MySQL template and cut and paste your commands from the CRM template generator to the default template.

 

[DanThor]

Here is what I found that solved my problem. Don't use the CRM template generator generated XML . Use the default MySQL template and cut and paste your commands from the CRM template generator to the default template.

OK. I'm not sure if that will fit the issue I am having. I didn't use the template generator. I saw there was an SQL template already so I did use that one. The command I used came from this 3CX page.

https://www.3cx.com/docs/sql-database-pbx-integration/

It's a very basic SQL Statement. SELECT contactid, firstname, lastname, phonebusiness FROM contacts WHERE phonebusiness LIKE '%[Number]%'

Any other thoughts on things to try?

Thank you for your help!

 

[greychain]

No other ideas, sorry, spent many hours working out this problem with a large amount of help from edossantos.

As he said there's nothing wrong with the statement.

 

[greychain]

I think there may have been an update as on my older server there was no option for incoming or both now there is after an update

 

[edossantos]

It's very weird, I just tested the same SQL query here and works fine. Are you using the latest version of 3CX (v16 Update 8)?

 

[DanThor]

No other ideas, sorry, spent many hours working out this problem with a large amount of help from edossantos.

As he said there's nothing wrong with the statement.

OK. Thank you for your time and effort. I'll keep looking.

 

[DanThor]

It's very weird, I just tested the same SQL query here and works fine. Are you using the latest version of 3CX (v16 Update 8)?

Hi Ernesto, Yes, I believe so. It says 16.0.8.9. Is it possible I need to change the way I created the table in the database?

 

[edossantos]

Hi Ernesto, Yes, I believe so. It says 16.0.8.9. Is it possible I need to change the way I created the table in the database?

View attachment 22544

The table is fine. From the log I can see that the database is returning the data, but the template is not properly identifying the contact record.

Are you using the standard template we provide or you made changes? Can you paste here the XML you see when you go to the 3CX Console > Settings > CRM > MsSQL > Show template?

 

[DanThor]

The table is fine. From the log I can see that the database is returning the data, but the template is not properly identifying the contact record.

Are you using the standard template we provide or you made changes? Can you paste here the XML you see when you go to the 3CX Console > Settings > CRM > MsSQL > Show template?

I selected the "Database MsSQL" Template.

Here is the copy and paste.

Thank you!

<?xml version="1.0"?>
<Crm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Country="US" Name="Database MsSQL" Version="4">
<Number Prefix="AsIs" MaxLength="[MaxLength]" />
<Connection MaxConcurrentRequests="2" />
<Parameters>
<Parameter Name="Server" Type="String" Parent="General Configuration" Editor="String" Title="Server:" />
<Parameter Name="Database" Type="String" Parent="General Configuration" Editor="String" Title="Database:" />
<Parameter Name="Username" Type="String" Parent="General Configuration" Editor="String" Title="Username:" />
<Parameter Name="Password" Type="String" Parent="General Configuration" Editor="String" Title="Password:" />
<Parameter Name="LookupSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Lookup SQL Statement:" />
<Parameter Name="ContactUrlPrefix" Type="String" Parent="General Configuration" Editor="Sql" Title="Contact URL Prefix:" Default="https://mycrmurl/contacts/" />
<Parameter Name="ContactUrlSuffix" Type="String" Parent="General Configuration" Editor="Sql" Title="Contact URL Suffix:" Default="/show" />
<Parameter Name="ReportCallEnabled" Type="Boolean" Editor="String" Title="Enable Call Journaling" Default="False" />
<Parameter Name="ReportCallSQLStatement" Type="String" Parent="ReportCallEnabled" Editor="Sql" Title="Call Journaling SQL Statement:" />
<Parameter Name="CreateContactEnabled" Type="Boolean" Editor="String" Title="Enable Contact Creation" Default="False" />
<Parameter Name="CreateOnCallDirection" Type="List" Parent="CreateContactEnabled" Editor="String" Title="Create Contacts on Call Direction:" Default="Inbound" ListValues="Inbound,Inbound/Outbound" />
<Parameter Name="CreateContactSQLStatement" Type="String" Parent="CreateContactEnabled" Editor="Sql" Title="Contact Creation SQL Statement:" />
</Parameters>
<Authentication Type="No" />
<Scenarios>
<Scenario Type="SQLDatabase">
<Query DatabaseType="MsSQL" ConnectionString="Server=[Server];Database=[Database];User Id=[Username];Password=[Password];" StatementPasses="2" Statement="[LookupSQLStatement]" />
<Rules>
<Rule Type="Any" Ethalon="">contactid</Rule>
<Rule Type="Any">id</Rule>
</Rules>
<Variables>
<Variable Name="ContactID">contactid<Filter /></Variable>
<Variable Name="FirstName">firstname<Filter /></Variable>
<Variable Name="LastName">lastname<Filter /></Variable>
<Variable Name="CompanyName">companyname<Filter /></Variable>
<Variable Name="Email">email<Filter /></Variable>
<Variable Name="PhoneMobile">phonemobile<Filter /></Variable>
<Variable Name="PhoneMobile2">phonemobile2<Filter /></Variable>
<Variable Name="PhoneHome">phonehome<Filter /></Variable>
<Variable Name="PhoneHome2">phonehome2<Filter /></Variable>
<Variable Name="PhoneBusiness">phonebusiness<Filter /></Variable>
<Variable Name="PhoneBusiness2">phonebusiness2<Filter /></Variable>
<Variable Name="PhoneOther">phoneother<Filter /></Variable>
<Variable Name="FaxBusiness">faxbusiness<Filter /></Variable>
<Variable Name="FaxHome">faxhome<Filter /></Variable>
<Variable Name="Pager">pager<Filter /></Variable>
<Variable Name="PhotoUrl">photourl<Filter /></Variable>
</Variables>
<Outputs AllowEmpty="false">
<Output Type="ContactUrl" Passes="0" Value="[ContactUrlPrefix][ContactID][ContactUrlSuffix]" />
<Output Type="FirstName" Passes="0" Value="[FirstName]" />
<Output Type="LastName" Passes="0" Value="[LastName]" />
<Output Type="CompanyName" Passes="0" Value="[CompanyName]" />
<Output Type="Email" Passes="0" Value="" />
<Output Type="PhoneMobile" Passes="0" Value="[PhoneMobile]" />
<Output Type="PhoneMobile2" Passes="0" Value="[PhoneMobile2]" />
<Output Type="PhoneHome" Passes="0" Value="[PhoneHome]" />
<Output Type="PhoneHome2" Passes="0" Value="[PhoneHome2]" />
<Output Type="PhoneBusiness" Passes="0" Value="[PhoneBusiness]" />
<Output Type="PhoneBusiness2" Passes="0" Value="[PhoneBusiness2]" />
<Output Type="PhoneOther" Passes="0" Value="[PhoneOther]" />
<Output Type="FaxBusiness" Passes="0" Value="[FaxBusiness]" />
<Output Type="FaxHome" Passes="0" Value="[FaxHome]" />
<Output Type="Pager" Passes="0" Value="[Pager]" />
<Output Type="PhotoUrl" Passes="0" Value="[PhotoUrl]" />
</Outputs>
</Scenario>
<Scenario Id="ReportCall" Type="SQLDatabase">
<Query SkipIf="[ReportCallEnabled]!=True" DatabaseType="MsSQL" ConnectionString="Server=[Server];Database=[Database];User Id=[Username];Password=[Password];" StatementPasses="2" Statement="[ReportCallSQLStatement]" />
<Variables />
<Outputs AllowEmpty="false" />
</Scenario>
<Scenario Id="CreateContactRecord" Type="SQLDatabase">
<Query SkipIf="[CreateContactEnabled]!=True||[IIf([CreateOnCallDirection]==Inbound,[CallDirection]!=Inbound,False)]==True" DatabaseType="MsSQL" ConnectionString="Server=[Server];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[CreateContactSQLStatement]" />
<Rules>
<Rule Type="Any" Ethalon="">contactid</Rule>
<Rule Type="Any">id</Rule>
</Rules>
<Variables>
<Variable Name="ContactID">contactid<Filter /></Variable>
<Variable Name="FirstName">firstname<Filter /></Variable>
<Variable Name="LastName">lastname<Filter /></Variable>
<Variable Name="CompanyName">companyname<Filter /></Variable>
<Variable Name="Email">email<Filter /></Variable>
<Variable Name="PhoneMobile">phonemobile<Filter /></Variable>
<Variable Name="PhoneMobile2">phonemobile2<Filter /></Variable>
<Variable Name="PhoneHome">phonehome<Filter /></Variable>
<Variable Name="PhoneHome2">phonehome2<Filter /></Variable>
<Variable Name="PhoneBusiness">phonebusiness<Filter /></Variable>
<Variable Name="PhoneBusiness2">phonebusiness2<Filter /></Variable>
<Variable Name="PhoneOther">phoneother<Filter /></Variable>
<Variable Name="FaxBusiness">faxbusiness<Filter /></Variable>
<Variable Name="FaxHome">faxhome<Filter /></Variable>
<Variable Name="Pager">pager<Filter /></Variable>
<Variable Name="PhotoUrl">photourl<Filter /></Variable>
</Variables>
<Outputs AllowEmpty="false">
<Output Type="ContactUrl" Passes="0" Value="[ContactUrlPrefix][ContactID][ContactUrlSuffix]" />
<Output Type="FirstName" Passes="0" Value="[FirstName]" />
<Output Type="LastName" Passes="0" Value="[LastName]" />
<Output Type="CompanyName" Passes="0" Value="[CompanyName]" />
<Output Type="Email" Passes="0" Value="[Email]" />
<Output Type="PhoneMobile" Passes="0" Value="[PhoneMobile]" />
<Output Type="PhoneMobile2" Passes="0" Value="[PhoneMobile2]" />
<Output Type="PhoneHome" Passes="0" Value="[PhoneHome]" />
<Output Type="PhoneHome2" Passes="0" Value="[PhoneHome2]" />
<Output Type="PhoneBusiness" Passes="0" Value="[PhoneBusiness]" />
<Output Type="PhoneBusiness2" Passes="0" Value="[PhoneBusiness2]" />
<Output Type="PhoneOther" Passes="0" Value="[PhoneOther]" />
<Output Type="FaxBusiness" Passes="0" Value="[FaxBusiness]" />
<Output Type="FaxHome" Passes="0" Value="[FaxHome]" />
<Output Type="Pager" Passes="0" Value="[Pager]" />
<Output Type="PhotoUrl" Passes="0" Value="[PhotoUrl]" />
</Outputs>
</Scenario>
</Scenarios>
</Crm>

 

[edossantos]

This is not the standard template, and that's the problem. You have an additional rule, which is not matching:

            <Rules>
                <Rule Type="Any" Ethalon="">contactid</Rule>
                <Rule Type="Any">id</Rule>
            </Rules>

The standard template only has the rule for contactid. Revert to the default template and this will start working.

 

[DanThor]

This is not the standard template, and that's the problem. You have an additional rule, which is not matching:

            <Rules>
                <Rule Type="Any" Ethalon="">contactid</Rule>
                <Rule Type="Any">id</Rule>
            </Rules>

The standard template only has the rule for contactid. Revert to the default template and this will start working.

Very strange. I'm not even sure how that would have happened. I deleted that one but there was also an update available. I downloaded it as well and now all is good! Thank you so much for your help!