How to get the department for the worker ?

[Ksepahman]

Hi all,

I am trying to find an efficient way to get the department description for the worker using the xrmtoolbox. I have not been able to work it out so far. What do you think? What are the alternatives?

More specifically, I need to list all workers and retrieve their departments.

Dynamics 365 for Human Resources

[kierian]

Power Apps LookUp Function - YouTube

Hope this can help you

Have a good day.

[Ksepahman]

Thank you for quick reply but unfortunately this is not addressing the problem at all. The raised issue is about querying dataverse tables. In SQL server I would probably use couple of 'joins'.

Have a good day,

[dpoggemann]

Hi @Ksepahman,

Based on the data model above you can have a worker that could be in multiple departments so you would not be able to do this in a simple query because there could be multiple rows returned.   Am I missing something in my understanding?

Thanks much,

Drew 

[Ksepahman]

Hi Drew, 

Thanks for quick help. Having just had a deeper look, I've noticed the Position Worker Assignment Table has a field called 'Is Primary Position' with Optionset 'Yes' and 'No' as the ONLY values.

I assume this will help us to retrieve the primary assignment and therefore the job position associated and department. What do you think?

If my assumption is correct, then I need to somehow loop this trough and get the department value all the way back for the list of workers. Not sure how to achieve this though.

Thanks you,

Kia

[dpoggemann]

Hi @Ksepahman,

You should be able to utilize this to then connect to the Job Position and then to the Department since both of these are on the "1" end of the 1:Many relationship.  Have you tried to do this with Advanced Find or through the XRMToolbox FetchXML Builder?

Thanks!

Drew 

[Ksepahman]

Hi @dpoggemann,

Thank you very much for your expertise. I have some sort of success but not just there yet. Within the Xrmtoolbox, I seem to be getting good results:

But when I print it out, I do not get the department descriptions:

 Sample data:

Logic seems to be working but somehow the flow not :-s.

Kia

[dpoggemann]

Hi @Ksepahman,

I think you are close but you might want to look at the "Expand Query" option for this to bring back the related entity data with this query.

https://linnzawwin.blogspot.com/2019/12/power-automate-list-records-use-expand.html

Hope this does it! 😀

Thanks,

Drew

[EricRegnier]

Hi @Ksepahman,

Based on your screenshot, the "List Rows" step is not quite right. You pasted the whole OData query in the "Fetch XML Query" section which is not the right type of expression that should go there. FetchXML looks like XML: <fetch><entity name="connection" ><all-attributes/></entity></fetch> and oData is what you pasted.

Here's the mapping:

Hope this helps!

[Ksepahman]

Hi @EricRegnier,

Thanks for your help. Indeed I tried it out correct but while taking the scrshots, I managed to demonstrate it incorrectly somehow.

I made the changes you've mentioned and unfortunately, the flow does not start anymore.