CRM: Contact Creation Error MySQL DB

[CISCOX]

Hello,

I'm having an issue with 3CX CRM and my external SQL database. I can perform queries using the "Test" button and receive a result when manually inserting it into my MySQL database. Call journaling is working fine.

Now, onto my problem: Unfortunately, I'm unable to add contacts to my external MySQL database using the 3CX web client. I receive the following error message in the event log:

Exception during contact creation. FirstName=Test, LastName=Test, Company=, Number=01234567, Email=. Exception: System.InvalidOperationException: CommandText must be specified at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 345 at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 337 at Integration.Crm.Engine.SqlDatabaseClient.Execute(String statement, CancellationToken token) at Integration.Crm.Engine.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token) at Integration.Crm.Engine.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token) at Integration.Crm.Engine.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token) at Integration.Crm.Engine.CrmProcessor.CreateContactRecord(IValueManager customManager, String scenarioId, Func`2 filterResultsFunctor, CancellationToken token) at Integration.Crm.Engine.CrmProcessor.CreateContactFromClient(String firstName, String lastName, String company, String contactNumber, String email, CancellationToken token) at MyPhoneServer.Common.ClientManager.ServerMessageProcessor.ProcessUpdateContact(RequestUpdateContact request, IClient client, CancellationToken token, ICrmProcessorFactory crmProcessorFactory, IGraphServiceClientFactory graphServiceClientFactory, IPhoneSystem ps, IMicrosoft365ConfigurationRepository configurationRepository)

The Error Message in web client:

Contact Creation Error
Problems occurred while adding the contact
Contact state:
CRM
Unable to create the contact due to an error. Please contact Administrator.

I use the Contact Creation SQL Statement as follows:

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]'

I know this docu (does not work for me): https://www.3cx.com/docs/server-side-crm-template-xml-description/

 

[CISCOX]

So you should use:
INSERT INTO contacts (first_name, last_name, phone, mobile , companyname, email) VALUES ('[FirstName]', '[LastName]', '', '[Number]', '[Company]', ''); SELECT id as contactid, first_name as firstname, last_name as lastname, email as email, phone as phonehome, mobile as phonemobile, fax as faxbusiness FROM contacts WHERE phone LIKE '%[Number]%' or mobile like '%[Number]%' or fax LIKE '%[Number]%'

Does that mean it's not possible to add a phone number?

Based on your statement, no phone number is being added to the database under "phone," and the "phone" field in 3CX is also empty (as shown in the screenshot).

You should manually create the contact from the Web Client. Then the first call will be logged.

That's unfortunate; that's not the purpose of "Inbound/Outbound" Contact Creation...

 

[edossantos]

Does that mean it's not possible to add a phone number?

Based on your statement, no phone number is being added to the database under "phone," and the "phone" field in 3CX is also empty (as shown in the screenshot).

When 3CX creates a contact, only 1 phone is provided, and it's available in the [Number] variable. I mean, you receive a call, no contact is matched, in the 3CX Web Client you click "Add contact", you enter a first name and last name, check the "CRM" option, and you create the contact. In this case, we will invoke the SQL statement you define, but only 1 phone number is provided, and it's the number that is calling you. In the SQL statement I proposed, this number is set to the mobile field in your database, and the phone field remains empty. If you later want to add a second number, you can do it by editing the database.

That's unfortunate; that's not the purpose of "Inbound/Outbound" Contact Creation...

Most users don't want that we create contacts automatically for every single call. They want to be able to speak with the customer, and add them when they really need, providing a first name and last name at that point. In previous versions of 3CX we created contacts automatically for every call using a default name, but this is not the case anymore since v18 Update 8.

 

[CISCOX]

Then I will probably have to downgrade to update 7A and use MySQL template version 5. I think it's bad that you can only use mobile when creating a contact and otherwise have to edit the database. That does not make sense. This makes 3CX CRM pointless in my opinion.

 

[enabled79]

I also need the function to create a contact in my CRM on MS SQL server, which used to work, but now it doesn’t, I don’t use the web version of 3cx and I don’t plan to, only a mobile application for Android and my CRM, then make the setting with a checkmark to enable or disable creation of all contacts, this would be correct for all clients without exception, otherwise, at the request of some clients, the function was disabled, but they did not take care of those who used the function and did not even think about it!

 

[amzachoudhary]

I am facing the similar issue.
How can i get the template version 6 for the mysql.
Can you provide the same?

i tried from test
INSERT INTO contact (first_name, last_name, phone_number, mobile ) VALUES ('[FirstName]', '[LastName]', '', '[Number]');SELECT id as contactid, first_name as firstname, last_name as lastname, email as email, phone_number as phonehome, mobile as phonemobile FROM users WHERE phone_number LIKE '%[Number]%' or mobile like '%[Number]%'

but not able to create contact!

 

[amzachoudhary]

When 3CX creates a contact, only 1 phone is provided, and it's available in the [Number] variable. I mean, you receive a call, no contact is matched, in the 3CX Web Client you click "Add contact", you enter a first name and last name, check the "CRM" option, and you create the contact. In this case, we will invoke the SQL statement you define, but only 1 phone number is provided, and it's the number that is calling you. In the SQL statement I proposed, this number is set to the mobile field in your database, and the phone field remains empty. If you later want to add a second number, you can do it by editing the database.


Most users don't want that we create contacts automatically for every single call. They want to be able to speak with the customer, and add them when they really need, providing a first name and last name at that point. In previous versions of 3CX we created contacts automatically for every call using a default name, but this is not the case anymore since v18 Update 8.

Can you please help how you resolved the issue?

 

[edossantos]

The Test button only lets you run a contact lookup. To test the contact creation you will need to make a real call, and create the contact from the Web Client.

 

[CISCOX]

If you got a yellow background, everything works fine.

 

[amzachoudhary]

The Test button only lets you run a contact lookup. To test the contact creation you will need to make a real call, and create the contact from the Web Client.

Thanks for your reply.

I have one more query!
Can i get the call recording/even recording url, chat also with their id in my crm?
Using the intigration with mysql database if yes how?

 

[edossantos]

I have one more query!
Can i get the call recording/even recording url, chat also with their id in my crm?
Using the intigration with mysql database if yes how?

No, the recording URL is not provided to the CRM.

 

[greychain]

Is there any chance the recording url could be made available in the CRM?

 

[Evolute IT]

Is there any chance the recording url could be made available in the CRM?

Not yet and there's no ETA for it to happen.

We do have a small PHP script that can be used with U8+ to grab the recording during the call reporting, and send it either as file or URL to the CRM.

 

[amzachoudhary]

When 3CX creates a contact, only 1 phone is provided, and it's available in the [Number] variable. I mean, you receive a call, no contact is matched, in the 3CX Web Client you click "Add contact", you enter a first name and last name, check the "CRM" option, and you create the contact. In this case, we will invoke the SQL statement you define, but only 1 phone number is provided, and it's the number that is calling you. In the SQL statement I proposed, this number is set to the mobile field in your database, and the phone field remains empty. If you later want to add a second number, you can do it by editing the database.


Most users don't want that we create contacts automatically for every single call. They want to be able to speak with the customer, and add them when they really need, providing a first name and last name at that point. In previous versions of 3CX we created contacts automatically for every call using a default name, but this is not the case anymore since v18 Update 8.

Hi i need help for this :- I am creating a contact in 3cx as an agent i do have uploaded a template for the custom crm but the contact is not getting saved this was in crm not even api is getting called(as im checking the in my crm any request is received or not with postman it is also working) the same template was working in the previous version where i tested the crm by clicking on test button it called the api and created the contact in my crm but in lasted im not able to do this. But im able to fetch the already created record by clicking the test button. Attaching the crm template also.

 

[amzachoudhary]

Not yet and there's no ETA for it to happen.

We do have a small PHP script that can be used with U8+ to grab the recording during the call reporting, and send it either as file or URL to the CRM.

Can you help with the same. How can write the script for the same? Where we can run these scripts?