How do i get the phone numbers in Accounts fromn the database?

[murugappan]

Hi,

My users use the multiple phone numbers feature in Espocrm and encode as many as 3 email numbers in the Accounts e.g Mobile, Office and Whatsapp. When they export the phone numbers in the Accounts they only get the first one in the list. I was told that, unfortunately, Espocrm can only export only one number. I checked out at the DB level and could not find the phone number field in the Accounts table.

Can anyone advise how to get the phone numbers from the DB tables for Accounts?

[Eblasoft]

Hi murugappan

You can find all phone numbers that related to Account in the DB at table named phone_number, and it is linked with the Accounts using entity_phone_number table.

This query could help you:

SELECT epn.entity_id AS account_id,
GROUP_CONCAT(pn.numeric ORDER BY pn.numeric SEPARATOR ', ') AS phone_numbers
FROM entity_phone_number AS epn
JOIN phone_number AS pn ON epn.phone_number_id = pn.id
WHERE epn.entity_type = 'account'
AND epn.deleted = 0
AND pn.deleted = 0
GROUP BY epn.entity_id;

[murugappan]

@eblasoft,

Thank you so much for the help. I needed to get all Whatsapp numbers for all the accounts. I followed your sample and i managed to extract the needs using the following SQL:

SELECT
ac.name as account_name,
pn.name as phone_number
FROM crmemast_emascrm.phone_number as pn
JOIN crmemast_emascrm.entity_phone_number as acpn on acpn.phone_number_id = pn.id
JOIN crmemast_emascrm.account as ac on ac.id = acpn.entity_id
WHERE pn.type = "Whatsapp";

[Eblasoft]

@eblasoft,

Thank you so much for the help. I needed to get all Whatsapp numbers for all the accounts. I followed your sample and i managed to extract the needs using the following SQL:

SELECT
ac.name as account_name,
pn.name as phone_number
FROM crmemast_emascrm.phone_number as pn
JOIN crmemast_emascrm.entity_phone_number as acpn on acpn.phone_number_id = pn.id
JOIN crmemast_emascrm.account as ac on ac.id = acpn.entity_id
WHERE pn.type = "Whatsapp";

Great!

You are welcome