Some SQL questions

[UnConundrum]

From watching the logs, it appears that all non-numeric characters (such as a dash or paren) as well as the country id are removed from [Number]. Could you kindly confirm that observation?

Am I also correct that any formatting done in Sip Trunks -> Edit -> Caller ID -> Inbound is ignored when passing [Number] ?

Are Agent, CallType, Duration, Number, and 6 time/date fields the only variables available to us? Most specifically, I'd like to save a call-id so that I can associate the various hops for a single call. If other variables are available is there a list somewhere?

 

[edossantos]

From watching the logs, it appears that all non-numeric characters (such as a dash or paren) as well as the country id are removed from [Number]. Could you kindly confirm that observation?

The number sent to the CRM considers the settings you have in Contacts > Options. This can be configured to send only the last N digits, or the entire number. Non-numeric characters are removed as you mentioned.

Am I also correct that any formatting done in Sip Trunks -> Edit -> Caller ID -> Inbound is ignored when passing [Number] ?

I would have to check this, but probably you're confused by the configuration that causes that only the last N digits are sent. Please check the settings you have in Contacts > Options.

Are Agent, CallType, Duration, Number, and 6 time/date fields the only variables available to us? Most specifically, I'd like to save a call-id so that I can associate the various hops for a single call. If other variables are available is there a list somewhere?

The variables you have available are described here:
https://www.3cx.com/docs/server-side-crm-template-xml-description/#h.vcivldjsa1lz

The Call ID is not available at the moment.

 

[UnConundrum]

No, I have the Contacts -> Options set to 14 digits, allowing for the 1 digit country code and three hyphens. In the formatting trunk options for incoming calls I have the source pattern set to

(1)(...)(...)(....)

and the replace pattern set to

\1-\2-\3-\4

but according to the logs, [Number] is 10 digits with no country code and no hyphens.

 

[edossantos]

Non-numeric characters will be stripped out, that's for sure.

You should consider sending only the last N digits, and matching the number against that in your CRM.