Question

Exporting Person Communication Method Email Address

J
Rank
Badge +2

I need to export all employee Private Email Addresses but cannot see this ‘value’ available to pull through to an overview screen to export the data - any help would be greatly appreciated please.

3 replies

A
Rank
Badge +1

 Thanks for the information you shared. Loved the way you explained everything in this blog.

J
Rank
Badge +2

Hi @JMusson,

There could be many approaches. If you are looking to fetch the work email which shows in the ‘Employee File’ window summary tab, then you can use a custom filed in the ‘Employees’ window to create a custom column to fetch data via the existing DB function Company_Person_API.Get_Work_Email(COMPANY_ID, EMP_NO).

With that you can extract the details from the employees window with the work email.

 

Otherwise, if you have a specific requirement & need to fetch all the emails for an employee, perhaps you could try out in the ‘SQL Query Tool’ window. A sample query could be written as below. you could modify it to filter by Address type to be Home & default methods etc, depending on your requirement - direct SQL output.

SELECT CPA.COMPANY_ID, CPA.EMP_NO, CPA.FNAME, CPA.LNAME, PC.COMM_DATA, PC.COMM_DEFAULT_FLAG, PC.METHOD_ID, PIA.ADDRESS_TYPE_CODE, PIA.Address_id
FROM PERSON_INFO_ADDRESS_TYPE PIA, COMPANY_PERSON_ALL CPA, PERS_COMMS2 PC
WHERE 
PIA.PERSON_ID = CPA.PERSON_ID AND
PC.PERSON_ID = PIA.PERSON_ID AND
PC.METHOD_ID = 'E-Mail' AND
--PIA.ADDRESS_TYPE_CODE = 'HOME' AND --change to WORK if need to filter or comment this line if need all emails
--COMM_DEFAULT_FLAG = 1 -- 1 for true, 0 for false
CPA.COMPANY_ID ='COMPANY_ID_VALUE_HERE';

 

Hope this will be helpful.

 

Thank you for your reply, it is the Private Email Addresses held in Private Contact Information.

Unfortunately I don’t have the IT skills to try out the SQL Tool (indeed I don’t even know where that is :( ) - I will try and locate someone who does.

N
Rank
Userlevel 7
Badge +11

Hi @JMusson,

There could be many approaches. If you are looking to fetch the work email which shows in the ‘Employee File’ window summary tab, then you can use a custom filed in the ‘Employees’ window to create a custom column to fetch data via the existing DB function Company_Person_API.Get_Work_Email(COMPANY_ID, EMP_NO).

With that you can extract the details from the employees window with the work email.

 

Otherwise, if you have a specific requirement & need to fetch all the emails for an employee, perhaps you could try out in the ‘SQL Query Tool’ window. A sample query could be written as below. you could modify it to filter by Address type to be Home & default methods etc, depending on your requirement - direct SQL output.

SELECT CPA.COMPANY_ID, CPA.EMP_NO, CPA.FNAME, CPA.LNAME, PC.COMM_DATA, PC.COMM_DEFAULT_FLAG, PC.METHOD_ID, PIA.ADDRESS_TYPE_CODE, PIA.Address_id
FROM PERSON_INFO_ADDRESS_TYPE PIA, COMPANY_PERSON_ALL CPA, PERS_COMMS2 PC
WHERE 
PIA.PERSON_ID = CPA.PERSON_ID AND
PC.PERSON_ID = PIA.PERSON_ID AND
PC.METHOD_ID = 'E-Mail' AND
--PIA.ADDRESS_TYPE_CODE = 'HOME' AND --change to WORK if need to filter or comment this line if need all emails
--COMM_DEFAULT_FLAG = 1 -- 1 for true, 0 for false
CPA.COMPANY_ID ='COMPANY_ID_VALUE_HERE';

 

Hope this will be helpful.

 

- Nalaka -

Reply


Terms & ConditionsCookie settings