web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Human Resources forum / Manager field in Worke...
Human Resources forum

Manager field in Worker entity returns null in Flow

(0) ShareShare
ReportReport
Posted on by Ciara Lynch 80

Hi all,

Currently working on a Microsoft Flow that retrieves a worker's details in Talent from the Common Data Service, which includes their manager. However, even though the user being tested is assigned to a position and is reporting to someone, the Manager field in their particular Worker entity returns null, as does Manager Type. Is this information stored in a different entity?

Thanks in advance!

Categories:
Leave and Absence
Other HR Tasks
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Hello Ciara,

    Typically the manager field should be populated in the worker entity in the CDS, see https://docs.microsoft.com/en-us/dynamics365/unified-operations/talent/media/hcmcommon-worker-entity-diagram.png

    However I have faced the same issue and the manger field remains empty in our CDS as this is a Vlookup field.

    Alternatively I have created a logic (workaround)  that checks worker > worker position assignment (pickup the position id) > position hierarchy (to get the manager position ID) > worker position assignment (to get the personnel number of the manager) > worker (to get the name of the manager). 

    This might not be the best approach, but I got it working for customer reporting I needed to create.

    perhaps you have an alterntive solution?

  • Ciara Lynch Profile Picture
    Ciara Lynch 80 on at

    Hi Erik,

    Glad to know I wasn't the only one facing this issue, your guidance helped a lot.

    Thanks a million!

    Kind regards,

    Ciara

  • Dudditz Profile Picture
    Dudditz 85 on at

    Hi There, the manager field in the worker entity is also null in our environments.  Are you indicating there should be a position hierarchy entity?  I was not able to locate this one.

  • tommalt Profile Picture
    tommalt 79 on at

    Hi,

    sorry to resurrect this thread, but this is very similar to an issue I'm facing with Power BI - I need to report on a person's manager, but can't get my head around  your solution.

    Do you know how I could use your workaround in Power BI to be able to report on a person's manager please? The _cdm_managerworkerid_value column in the cdm_workers table is blank.

    many thanks,

    Tom

  • Suggested answer
    Parag Chapre Profile Picture
    Parag Chapre 12,254 Most Valuable Professional on at

    Hi Tom,

    First, you have to use Worker > Position Worker assignment > Job position.

    In this way, you can get manager value.

  • tommalt Profile Picture
    tommalt 79 on at

    Hi Parag,

    thanks for getting back to me so quickly - these are the relationships I've got in Power BI.

    pastedimage1619798993612v1.png

    So I've got cdm_workers > cdm_positionworkerassignmentmaps > cdm_jobposition - but I can't see anything in job positions that helps me get the manager name. Sorry if I'm missing something simple, but would you be able to help please?

    many thanks,

    Tom

  • Suggested answer
    Parag Chapre Profile Picture
    Parag Chapre 12,254 Most Valuable Professional on at

    Hi Tom,

    You can Join Worker with Position worker assignment

    You will get Worker and Position assignment

    Then join with Job position

    You will get Position and Parent position

    If you have a parent and child position then you can get Employee and manager names based on the above entities.

  • Suggested answer
    Parag Chapre Profile Picture
    Parag Chapre 12,254 Most Valuable Professional on at

    Hi Tom,

    Try this code in Power BI.

    Add 3 entities to Power BI

    cdm_worker, cdm_jobposition, and cdm_positionworkerassignmentmaps

    Create a merge query and use the below code.

    I put a filter on one record. You can remove the range

    let
    Source = Table.NestedJoin(cdm_worker, {"cdm_workerid"}, cdm_positionworkerassignmentmap, {"cdm_workerid"}, "cdm_positionworkerassignmentmap", JoinKind.LeftOuter),
    #"Expanded cdm_positionworkerassignmentmap" = Table.ExpandTableColumn(Source, "cdm_positionworkerassignmentmap", {"cdm_jobpositionid", "cdm_positionworkerassignmentmapid", "cdm_workerid"}, {"PWA.cdm_jobpositionid", "PWA.cdm_positionworkerassignmentmapid", "PWA.cdm_workerid"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded cdm_positionworkerassignmentmap", {"PWA.cdm_jobpositionid"}, cdm_jobposition, {"cdm_jobpositionid"}, "cdm_jobposition", JoinKind.LeftOuter),
    #"Expanded cdm_jobposition" = Table.ExpandTableColumn(#"Merged Queries", "cdm_jobposition", {"cdm_parentjobpositionid"}, {"cdm_parentjobpositionid"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded cdm_jobposition", {"cdm_parentjobpositionid"}, cdm_positionworkerassignmentmap, {"cdm_jobpositionid"}, "cdm_positionworkerassignmentmap", JoinKind.LeftOuter),
    #"Expanded cdm_positionworkerassignmentmap1" = Table.ExpandTableColumn(#"Merged Queries1", "cdm_positionworkerassignmentmap", {"cdm_workerid"}, {"cdm_positionworkerassignmentmap.cdm_workerid"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded cdm_positionworkerassignmentmap1", {"cdm_positionworkerassignmentmap.cdm_workerid"}, cdm_worker, {"cdm_workerid"}, "cdm_worker", JoinKind.LeftOuter),
    #"Expanded cdm_worker" = Table.ExpandTableColumn(#"Merged Queries2", "cdm_worker", {"cdm_fullname", "cdm_workerid", "cdm_workernumber"}, {"cdm_worker.cdm_fullname", "cdm_worker.cdm_workerid", "cdm_worker.cdm_workernumber"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded cdm_worker", each ([cdm_workernumber] = "000040")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"cdm_workerid", "PWA.cdm_jobpositionid", "PWA.cdm_positionworkerassignmentmapid", "PWA.cdm_workerid", "cdm_parentjobpositionid", "cdm_positionworkerassignmentmap.cdm_workerid", "cdm_worker.cdm_workerid"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"cdm_worker.cdm_workernumber", "Reports to id"}, {"cdm_worker.cdm_fullname", "Reports to name"}})
in
    #"Renamed Columns"

    You will get this.

    pastedimage1619801596546v1.png

  • tommalt Profile Picture
    tommalt 79 on at

    Hi Parag,

    that's amazing - thank you so much!

    I had to update some of the table and column values in the statement (mostly adding s to the end of some of the tables and _xxx_Value to some of the columns).

    Thanks so much again,

    Tom

  • Suggested answer
    Parag Chapre Profile Picture
    Parag Chapre 12,254 Most Valuable Professional on at

    Hi Tom,

    Can you please mark the answer as verified?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Human Resources

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans