Finance | Project Operations, Human Resources, ...

How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

(0) Share

Report

Posted on by Community Member

Hello Experts , i have one requirement to set look up for employee so once employee name will be chose respected department and company will come automatically. i was trying to figure out the relation between the table to get those details but couldn't find anything. please advise i am, using below quarries.

Not Working :

SELECT A.PERSONNELNUMBER, E.NAME AS 'WORKER NAME', D.NAME AS 'DEPARTMENT'
FROM HCMWORKER A LEFT OUTER JOIN HCMPOSITIONWORKERASSIGNMENT B ON B.WORKER = A.RECID
LEFT OUTER JOIN HCMPOSITIONDETAIL C ON C.POSITION = B.POSITION
INNER JOIN DIRPARTYTABLE D ON C.DEPARTMENT = D.RECID
INNER JOIN DIRPARTYTABLE E ON A.PERSON = E.RECID

For getting employ name i am using this and its working

select DP.NAME,* from HCMWORKER h, DIRPARTYTABLE DP where h.PERSON = DP.RECID

Thank you

All responses (5)

Answers (0)

Suggested answer

ergun sahin 8,816 Moderator on at

Like (0)

Report

RE: How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

semihcelikol.com/.../
MohammadAB 20 on at

Like (0)

Report

RE: How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

Hi Sachin,

I am looking to create a similar report to get the department of the reportedbyperson. Did you manage to find the query?

Kind Regards,

MohammadA
Community Member on at

Like (0)

Report
RE: How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

Hello Guys, I can see in Human Resources > Employee while we are creating employee we have option to add company and department in employment > financial Dimension (TAB ).

After investigating i found below table are responsible for this.

DimensionAttributeValueSet

DimensionAttributeValueSetItem

DimensionAttributeValue

For Vendor table i wrote below query and it is giving me the department and company name.

select dimensionAttributeValue.DISPLAYVALUE,* from HCMEMPLOYMENT join dimensionAttributeValueSetItem on dimensionAttributeValueSetItem.DimensionAttributeValueSet = HCMEMPLOYMENT.DefaultDimension join dimensionAttributeValue on dimensionAttributeValue.RecId = dimensionAttributeValueSetItem.DimensionAttributeValue

But i am confused which table i should use for Employee department and company. I tried with HCM worker but it does not have DefaultDimension column also i tried with HCMEMPLOYMENT table but not getting correct result please advise.

So i will write a manual lookup to show only the department and company which is linked to this employee
Community Member on at

Like (0)

Report
RE: How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

Hello Blue,

Thank you so much for the response actually i am already able to pick current logged in user name and logged in date by using initValue() method

public void initValue() { super(); this.ReportedDate = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()); this.ReportedByUser=HcmWorker::worker2Name(HcmWorker::userId2Worker(curUserId())); }

Now i want respected department of that user and respected company should also picked automatically , once i chose the ReportedByUser. Please find the screen shot below for more clarity. Same scenario i want for assignee but assignee field i am getting value by using HcmWorkerRecId EDT in table field.
Blue Wang on at

Like (0)

Report
RE: How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

Hi Sachin,

Have you tried this?

community.dynamics.com/.../current-user-and-its-employee-name

HcmWorker::find(DirPersonuser::findUserWorkerReference(UserId)).name(); HcmWorker::find(DirPersonuser::findUserWorkerReference(UserId)).primaryDepartmentName();