Call Journaling doesn't work in SqlServer

[dimis1989]

I have enable Call Journaling. Also i set this query below:

INSERT INTO calls (contactnumber) VALUES ('[Number]')



The problem is that contact creation is work, but call journaling doesn't. I don't get any insert inside my calls table. Is there any configuration which i need to enable?

 

[dimis1989]

Anyone who can help?

 

[jey_007]

Seems Call journal, works only if the contactid and phone number returned from CRM.

Mine is diff i just want the Call history, so i copied the XML template and updated
<Number Prefix="AsIs" MaxLength="15" /> this line.

And then for Look up SQL statement...

SELECT 1 contactid, 'Ignore' firstname, 'Ignore' lastname, '' as email,'[Number]' as phonebusiness, '[Number]' as phonemobile, '[Number]' as faxbusiness

So it always return 1 and Call journal get fired...

Hope i am not breaking any rules..

 

[edossantos]

Exactly, by design the CRM integrations (including the integration with databases) work in the following way:

1. When the call is received, the contact is searched in the configured CRM / database.
2. If not found, a new contact is created (when contact creation is enabled).
3. When the call ends, the call is reported to the CRM / database, ONLY when it is related to a known contact.

So, if your lookup query is not returning any data, then the call journaling will not be fired.

The solution proposed by @jey_007 is valid and doesn't break any rule. It's just the same contact always being updated in 3CX contacts with the new phone number.

 

[dimis1989]

I made the changes that you wrote, but i am still getting the same message.
CRM Integration is working OK but contact number does not exist. Check log for more info or try a different number.

Started scenario with empty id
Request text: ConnectionString: Server=(local)\sqlexpress;Database=mssql;User Id=sa;Password=1234;
Statement: SELECT 1 contactid, 'Ignore' firstname, 'Ignore' lastname, '' as email,'123411' as phonebusiness, '123411' as phonemobile, '123411' as faxbusiness

Response text: [{"contactid":1,"firstname":"Ignore","lastname":"Ignore","email":"","phonebusiness":"123411","phonemobile":"123411","faxbusiness":"123411"}]
Response finished
Variable ContactID set to 1
Variable FirstName set to Ignore
Variable LastName set to Ignore
Variable CompanyName set to
Variable Email set to
Variable PhoneMobile set to 123411
Variable PhoneMobile2 set to
Variable PhoneHome set to
Variable PhoneHome2 set to
Variable PhoneBusiness set to 123411
Variable PhoneBusiness2 set to
Variable PhoneOther set to
Variable FaxBusiness set to 123411
Variable FaxHome set to
Variable Pager set to
Variable PhotoUrl set to
Finished scenario with empty id
Started CreateContactRecord
Request text: ConnectionString: Server=(local)\sqlexpress;Database=mssql;User Id=sa;Password=1234
Statement: INSERT INTO contacts (phone) VALUES ('123411');SELECT phone as phonebusiness FROM contacts WHERE phone = '123411'

Response text: [{"phonebusiness":"123411"}]
Response finished
Finished CreateContactRecord
Result obtained

 

[dimis1989]

Isn't any way to bypass it through xml code?

 

[jey_007]

Test will not create the contact.. it just validate your SQL statement and XML parse...

u can enable call journal and check in SQL server. if you have any issues you can check Activity -> Logs --> Instancce -> 3cxSystemService.log for any issue.

 

[edossantos]

I see that you're testing with a 6 digits number. What value do you have configured in the 3CX Console > Advanced > Contacts > Options > Matching strategy? If you're setting "Match at least X number of characters" to 7 or great, there is a known issue until Update 1 which causes that there is no match.

You can try with larger numbers, in any case, in a real world scenario, external numbers will be longer than that.

 

[jey_007]

Hi Edo,

The integration working fine for me, but its very difficult to co-relate the CRM records without contact id.

If i send a CRM contactid in SQL lookup, anyway to get it in journal.

is there anyway to get more fields in call journal ??

Thank you in advance

 

[edossantos]

You will need to make a small adjustment to the template in order to use the Contact ID in Call Journaling. In the contact lookup scenario, you need to generate a new output:

        <Output Type="ContactID" Value="[ContactID]" />

Once you do this, you can use the variable [Contact::ContactID] in the call journaling scenario, as part of your query.

 

[jey_007]

Hi,

Thanks for quick response, What are the other variables available. I can't find in Template generator.

I want the recording file name. is there any other way can compile the file name using available call journal fields..

Regards.

 

[edossantos]

Here you have the available variables:
https://www.3cx.com/docs/server-side-crm-template-xml-description/#h.vcivldjsa1lz

The call recording path is not available at the moment.