Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / How to get Employee Na...
Finance forum
Suggested answer

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

Posted on by Microsoft Employee

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 

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: How to get Employee Name , Employee Department and Employee Company from D365 F&O DB

    semihcelikol.com/.../

  • MohammadAB Profile Picture
    MohammadAB 20 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 ). 

    Error95.PNG

    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 Profile Picture
    Community Member Microsoft Employee on at
    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.

    Error94.PNG

  • Blue Wang Profile Picture
    Blue Wang on at
    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();

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans