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

I have the same question (0)

All responses (5)

Answers (0)

Blue Wang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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();

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
MohammadAB 20 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

ergun sahin 8,826 Moderator on at

Like (0)

Report
Copy link

Link copied!

semihcelikol.com/.../

Was this reply helpful? Yes No