Solved: Re: How to get the department for the worker ?

Ksepahman · ‎04-23-2021

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

Ksepahman · ‎04-23-2021

Hot off the press >>>For finding out about the root cause, I removed the second step (Apply to each) and ran the flow and here is the result:

Basically, the odata produced by the Xrmtoolbox does not help me for some reasons. So I started testing FetchXML query and I succeeded. These are the steps:

1- Started the flow, checked the raw data results and noticed the variable is now assigned a value.

2- Modified the variable 'cdm_description' to 'cdm_department3.cdm_description'

3- Succeeded printing the data.

4- Problem is now solved.

Thank you @EricRegnier @dpoggemann .

View solution in original post

kierian · ‎04-23-2021

Power Apps LookUp Function - YouTube

Hope this can help you

Have a good day.

Ksepahman · ‎04-23-2021

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 · ‎04-23-2021

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

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Ksepahman · ‎04-23-2021

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 · ‎04-23-2021

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

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Ksepahman · ‎04-23-2021

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 · ‎04-23-2021

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

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

EricRegnier · ‎04-23-2021

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!