How to retrieve worker's private email address in reporting ?

(0) Share

Report

Posted on by Community Member

Hi Everyone,

Can someone tell me How to retrieve worker's private email address in reporting using Report builder or MVS ?

i am able to retrieve worker's primary email address but our requirement is to show any additional emails attached to worker in the report.

thnk you

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Each worker is created as an entry in the Global Address Book, for which the main table is DirPartyTable. The DirPartyTable is extended by DirPerson.

HcmWorker.Person == DirPerson.RecId == DirPartyTable.RecId

The e-mail is stored in LogisticsElectronicAddress, which is tied to the Global Address Book party. So this way you could directly go as filtering out for contact details of type Email, where the Private checkbox is set. Or if there are multiple e-mails defined, you could just go as isPrimar == NoYes::No instead to return all secondary e-mail addresses.

select * from logisticsElectronicAddress where logisticsElectronicAddress.PrivateForParty == HcmWorker.Person && logisticsElectronicAddress.IsPrivate == NoYes::Yes && logisticsElectronicAddress.Type == LogisticsElectronicAddressMethodType::Email

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for you response.

Should i use inner join in select clause ? actually i am using report builder right now this query is showing errors on below code :

after modification :

select * from logisticsElectronicAddress

where logisticsElectronicAddress.PrivateForParty = HcmWorker.Person

and logisticsElectronicAddress.IsPrivate = NoYes::Yes

and logisticsElectronicAddress.Type = LogisticsElectronicAddressMethodType::Email

error : The multi par identifier "hcmworker.person" could not be found.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

FYI privateforparty is also showing limited records.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

This code pasted above is for X++. I have seen you are doing a lot of posts on the forums mostly using SQL and Reporting, I think it is time for you now to learn the actual AX syntax for select statements to be able to at least read and understand it, since many of the reports could be easily made by tools provided by AX. Such as Query, View, Dataset for data, and class objects as Data provider/Controller/UI Builder for parametrization/data retrieval/execution of reports.

Inner join depends on what do you put first. Since you did not state how do you select your worker, here I assumed you already have it returned and available in the HcmWorker buffer.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Obviously PrivateForParty only contains values if the contact details are Private, of specific Type, and for specific Party from the Global Address Book. LogisticsElectronicPostalAddress table holds contact details for any GAB entries including customers, contact person, vendors, workers, employees.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have used the below sql query to state the worker and their description,department and email(primary) :

SELECT hcmworker.PERSONNELNUMBER as Code, dirpartytable.name as "Worker Name", LGS.LOCATOR as Email ,dimattributeomdepartment.name as Department,hcmpositiondetail.description as description,LGS2.LOCATOR as "Phone(other Email)" from HCMWORKER

inner join DirpartyTable on hcmworker.PERSON = DirpartyTable.RECID

inner join LOGISTICSELECTRONICADDRESS AS LGS

on LGS.RECID = dirpartytable.PRIMARYCONTACTEMAIL

inner join LOGISTICSELECTRONICADDRESS AS LGS2

on LGS2.RECID = dirpartytable.PRIMARYCONTACTPHONE

inner join HcmPositionWorkerAssignment on HcmPositionWorkerAssignment.Worker = hcmworker.RECID

inner join HcmPositionDetail on HcmPositionWorkerAssignment.Position=HcmPositionDetail.Position

inner join dimattributeomdepartment on dimattributeomdepartment .RECID=HcmPositionDetail.department

where DirpartyTable.KnownAs = 'Active'

and LGS.LOCATOR LIKE '%@eaworld.com%'

AND HcmPositionDetail.VALIDFROM <= getdate()

and HcmPositionDetail.VALIDTO >= getdate()

-------

please if you can share sql query to fetch private email as well.

upon your suggestion i will try to move on to X++ soon :).

thank you so much

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (1)

Report

You could make my select statement into an X++ Job in AX (with adding the additional joins if you want to), and add the generateOnly keyword to get the T-SQL statement, like this:

www.agermark.com/.../check-sql-statement-generated-from.html

It is too much extra effort to provide T-SQL statements on an AX forum when we blindly type solutions, so I would really recommend to get used to the AX syntax:

https://msdn.microsoft.com/en-us/library/aa656402.aspx

https://msdn.microsoft.com/en-us/library/aa848113.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am going through your blogs and links for further learning. By profession i am a IT Professional which knows little programming and SQl.

Improving day by day

thanks alot for your time.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

Please take your time to verify answers by ticking the checkbox next to each helpful answers in order to close the topic and assist other users with similar problems.

Was this reply helpful? Yes No