CRM Lookup display fields

[diego.zandolin]

Good morning,

I am new regarding 3CX and now I am testing the functioning of the CRM integration with our custom ERP based on SQL SERVER.

To be clear I set inside CRM Integration the following Lookup SQL Statement:
SELECT contactid, firstname, lastname, companyname, email, phonemobile, phonemobile2, phonehome, phonehome2, phonebusiness, phonebusiness2, phoneother, faxbusiness, faxhome, pager, photourl, IDAnagrafica FROM CX_Contacts WHERE phonebusiness LIKE '% [Number]%' or phonebusiness2 like ' % [Number]% 'or phonemobile LIKE'% [Number]% 'or phonemobile2 LIKE'% [Number]% 'or faxbusiness LIKE'% [Number]% 'or faxbusiness LIKE'% [Number]% 'or faxhome LIKE' % [Number]% '

I have the following questions.
Question 1.
In order to correctly write the WHERE clause of the previous query I would need to understand better the format of the [Number] parameter:
for example my telephone number is 049633656 but as far as I know I do not know which value the parameter [Number] could assume, infact
it could have the Italian prefix 39 with or without the prefix '+' or '00'. Moreover can the [Numbre] have intermediate spaces or other not-digit characters or separator like '+', ' spaces' or separators '-'?
So the combinations for one numbre like '049633656' are many: '049633656' or '049 633-656' or '39049633656' or '0039049633656' or '00 39 049633656'.
On the other hand when I query the database I need to know the format of the [Number] parameter.
So I would like to know if 3cx "cleans" the input number in some way from non-numeric characters and expect only digits.

2. If there were multiple records meeting the WHERE clause criteria of the query, would 3cx take only the first record, correct?

3. Which fields are shown on the display according to the various apps (web app, android app and windows app)?
When I create a new contact besides FirstName and LastName there are other fields that I would like to manage or show during a call, like for example Company, but also Businness (equivalent to department or job?) Or Other (in Other I'd like to insert some useful info in a short text format to the telephone operator such as "PAY ATTENTION TO THIS ONE!").
So how do you know which fields will be shown and how to set their visibility at the time of the call?

Thanks

 

[edossantos]

Question 1.
In order to correctly write the WHERE clause of the previous query I would need to understand better the format of the [Number] parameter:
for example my telephone number is 049633656 but as far as I know I do not know which value the parameter [Number] could assume, infact
it could have the Italian prefix 39 with or without the prefix '+' or '00'. Moreover can the [Numbre] have intermediate spaces or other not-digit characters or separator like '+', ' spaces' or separators '-'?
So the combinations for one numbre like '049633656' are many: '049633656' or '049 633-656' or '39049633656' or '0039049633656' or '00 39 049633656'.
On the other hand when I query the database I need to know the format of the [Number] parameter.
So I would like to know if 3cx "cleans" the input number in some way from non-numeric characters and expect only digits.

The [Number] variable will only have numbers, and eventually the + symbol at the beginning. This is the number that you receive from your SIP trunk. So if your SIP trunk sends it with country code, then the variable will have the country code. 3CX only removes anything that is not a number or the + symbol.
In addition, you control how many numbers you want to send from the 3CX Console > Advanced > Contacts > Options. If you select "Match exactly", the number is sent exactly as it arrives. If you select to match the last 7 digits, then only the last 7 digits are sent, and when you receive a call from +39123456789 the SQL query will be created using only 3456789.

2. If there were multiple records meeting the WHERE clause criteria of the query, would 3cx take only the first record, correct?

Yes, only the first record returned is considered.

3. Which fields are shown on the display according to the various apps (web app, android app and windows app)?
When I create a new contact besides FirstName and LastName there are other fields that I would like to manage or show during a call, like for example Company, but also Businness (equivalent to department or job?) Or Other (in Other I'd like to insert some useful info in a short text format to the telephone operator such as "PAY ATTENTION TO THIS ONE!").
So how do you know which fields will be shown and how to set their visibility at the time of the call?

You can't put that kind of information in the phone fields. The phonebusiness, phoneother, and so on are phone fields and must have numbers only. These fields are not shown to the agent, only the first and last name are shown.

 

[diego.zandolin]

Thanks
Regarding point 3, are there other methods to give access to Agent a detail form of incoming contacts (example a link to a url or else that could be proposed to the agent)?

 

[edossantos]

Regarding point 3, are there other methods to give access to Agent a detail form of incoming contacts (example a link to a url or else that could be proposed to the agent)?

The Contact URL that you provide is automatically opened by the Web Client, so that could be a way to pass information. However, please note that you should keep the same URL for the same phone number, otherwise 3CX will start duplicating contacts, and the first contact returned will be always shown to the agent.

 

[diego.zandolin]

What do you mean for "duplicating contact"?
Which "rules" follow 3cx to duplicate a contact or vice versa which rules do not duplicate the contact?
If my CRM update the phone number of an already imported contact what happen to the same 3cx contact after that this one was alrady imported? It will be duplicated or updated?

 

[edossantos]

The Contact URL returned by the CRM is considered the key. So, if you return the same Contact URL, the details will be updated with the information returned (updated the name, phone numbers, etc.).

On the other hand, if you return a different Contact URL but the same phone number, 3CX will create 2 contacts, each one with a different Contact URL, and both with the same phone number. This is because the Contact URL is considered the primary key. And when this happens, you're returning a second URL, but 3CX will match the phone from the first contact, so the first URL will be sent to the agent...

 

[diego.zandolin]

1. When I create a new contact from 3cx I do not find such contact URL field, correct?
2. Where I may find an exemple of contact URL format?
3. Which contact list have precedence between 3cx defauld one and CRM if one number is present in both?

 

[edossantos]

When you use the integration with databases, the Contact URL is created concatenating the Contact URL Prefix, then the contact ID, and finally the Contact URL Suffix that you configure in the console. Please check this:
https://www.3cx.com/docs/sql-database-pbx-integration/#h.4xf3snf20461

So, if your database returns contact ID = 123, and you configured the prefix as https://mycrmurl/contacts/ and the suffix as /show, then the full Contact URL will be:
https://mycrmurl/contacts/123/show

When you have an existing contact in 3CX contact that matches the phone number, that contact will take precedence. Always the first contact created in 3CX will be considered as the match.