Answered

Repeat an action until it satisfied certain condition and output certain information in each flow

(0) Share

Report

Posted on by kuzzy

Hi all, I am trying to build a flow of approvals for employees' leave request. I have stored the information of employees (including name, email and reporting lines) in Power App's dataverse (the image is a demo). (Office 365 User is not available for my organization)

What I want to achieve: 1)when someone fills a Form to initiate a leave request——2)obtain the emails of this employee's +1 manager, +2 manager and +3 manager, which are stored in dataverse——3)send emails to these managers to inform them to approve the request.

The problems I am facing: I'm stuck at 2. Because the reporting lines are not all the same, some are long, and some are short. My initial solution was to repeat the action of "list rows" to continuously filter out different levels of managers' emails until "the highest level of manager equals to the boss", which can solve the problem I mentioned at the start of this paragraph. BUT, I don't know how to achieve it. Are there any better solutions? (Dataverse, power app, power automate) Thank you very much.

All responses (7)

Answers (2)

kuzzy 15 on at

Like (0)

Report
Re: Repeat an action until it satisfied certain condition and output certain information in each flow

Hi, sorry to bother you again. I am trying to build up a query myself and I have troubles deciding the expressions of the Dataverse output.
I have one table(cr2bf_employeeroll）stores employees' information, (includes name, email, level 1 department, level 2 department and level 3 department). I have other three tables(cr2bf_ms_dept1, cr2bf_ms_dept2, cr2bf_ms_dept3）that store the department name and department managers. The department(e.g cr2bf_dept_2) of the employee table uses lookup to other three department tables(e.g cr2bf_deptname_2). And the department managers(e.g cr2bf_approver_dept2) uses lookup to the employee table(e.g cr2bf_name). I want to get the different levels of department managers' emails of a person. Here is the query I use Fetchxml to write:

<fetch top="50"> <entity name="cr2bf_employeeroll"> <attribute name="cr2bf_mail" /> <link-entity name="cr2bf_employeeroll" from="cr2bf_employeerollid" to="cr2bf_mgr" link-type="outer" alias="mgr"> <attribute name="cr2bf_mail" /> </link-entity> <link-entity name="cr2bf_ms_dept3" from="cr2bf_ms_dept3id" to="cr2bf_dept3" link-type="outer" alias="dept3"> <attribute name="cr2bf_deptname_3" /> <link-entity name="cr2bf_employeeroll" from="cr2bf_employeerollid" to="cr2bf_approvers_dept3" link-type="outer" alias="dept3_approver_mail"> <attribute name="cr2bf_name" /> <attribute name="cr2bf_mail" /> </link-entity> </link-entity> <link-entity name="cr2bf_ms_dept2" from="cr2bf_ms_dept2id" to="cr2bf_dept2" link-type="outer" alias="dept2"> <attribute name="cr2bf_deptname_2" /> <link-entity name="cr2bf_employeeroll" from="cr2bf_employeerollid" to="cr2bf_approvers_dept2" link-type="outer" alias="dept2_approver_mail"> <attribute name="cr2bf_name" /> <attribute name="cr2bf_mail" /> </link-entity> </link-entity> <link-entity name="cr2bf_ms_dept1" from="cr2bf_ms_dept1id" to="cr2bf_dept1" link-type="outer" alias="dept1"> <attribute name="cr2bf_deptname_1" /> <link-entity name="cr2bf_employeeroll" from="cr2bf_employeerollid" to="cr2bf_approvers_dept1" link-type="outer" alias="dep1_approver_mail"> <attribute name="cr2bf_name" /> <attribute name="cr2bf_mail" /> </link-entity> </link-entity> </entity> </fetch>

When I was trying to get the level 2 manager's email, Here is the expression I wrote:

items('Apply_to_each')?['cr2bf_dept2/cr2bf_deptname_2/cr2bf_approvers_dept2/cr2bf_name/cr2bf_mail']

The result ended up to be null. Could you please help me take a look at the expression? I don't know what's wrong.
kuzzy 15 on at

Like (0)

Report

Re: Repeat an action until it satisfied certain condition and output certain information in each flow

Got it! Thank you again!!!💓
Linn Zaw Win 2,982 on at

Like (1)

Report

Re: Repeat an action until it satisfied certain condition and output certain information in each flow

That is possible but you need to loop and call the List Rows action multiple times until it has looped x3 times or the manager's email is the same as the variable (it would be better to store it in the environment variable to make it configurable).

With Expand Query, you will only need to call the List Rows action once and process the data within the flow, so maybe a better performance.
kuzzy 15 on at

Like (0)

Report

Re: Repeat an action until it satisfied certain condition and output certain information in each flow

Got it! Couldn't appreciate more. I still have one small question if it doesn't bother🙏. When trying to get manager's emails, since whatever the reporting line is, the highest level of manager would always be our one and only boss, is it possible to make the manager's email a variable, and loop inquire until its value equals to the boss's email? In this way, we can save the process of using conditions to filter out empty items.
Verified answer

Linn Zaw Win 2,982 on at

Like (1)

Report
Re: Repeat an action until it satisfied certain condition and output certain information in each flow

@kuzzy

It depends on the scenario. If you want to create the approval for one manager after another (e.g. Manager 1 first, only when Manager 1 approves, create another one for Manager 2), then there is no choice but to use Condition three times to check the null value.

But if the scenario is to request approval from all in a single Approval, you can use the expression below to check if the value is empty() and concat() with ';' if the value is not empty. Then, concat all x3 manager emails.

concat ( if(empty(items('Apply_to_each')?['parentsystemuserid/internalemailaddress']), '', concat(items('Apply_to_each')?['parentsystemuserid/internalemailaddress'], ';')) , if(empty(items('Apply_to_each')?['parentsystemuserid/parentsystemuserid/internalemailaddress']), '', concat(items('Apply_to_each')?['parentsystemuserid/parentsystemuserid/internalemailaddress'], ';')) , if(empty(items('Apply_to_each')?['parentsystemuserid/parentsystemuserid/parentsystemuserid/internalemailaddress']), '', concat(items('Apply_to_each')?['parentsystemuserid/parentsystemuserid/parentsystemuserid/internalemailaddress'], ';')) )

If you are creating an approval inside the loop, make sure to increase the Degree of Parallelism or else, the approval for the 2nd employee will be created only after the first one is completed.
kuzzy 15 on at

Like (0)

Report

Re: Repeat an action until it satisfied certain condition and output certain information in each flow

Thank you so much!!! This solution works perfectly. Referring to your last step, "use empty() to check if the value is empty", could you please elaborate more? I only think of using condition for three times to check if a single value (manager's email) is empty. Is there a smarter way?
Verified answer

Linn Zaw Win 2,982 on at

Like (1)

Report
Re: Repeat an action until it satisfied certain condition and output certain information in each flow

You don't need to store the +1 manager and +2 manager columns in the database because changing the manager for one employee will require updating all employees reporting to that one.

Just keep one lookup "Manager" and you can retrieve the email address of x3 level managers with a single List Rows action by using the nested expansion in the Expand Query.

In my example, I am using the out-of-the-box User table but you can replace it with the table logical name, lookup and email column names with the ones from your employee table.

This is how it would look like

Here is the sample expand query for one level manger. Basically, it means get the internalemailaddress column value of the related table of the parentsystemuserid lookup column.

parentsystemuserid($select=internalemailaddress)

Then, you can do nested expand to get x2 level.

parentsystemuserid($select=internalemailaddress;$expand=parentsystemuserid($select=internalemailaddress))

This is the parameter of the Expand Query for x3 level managers.

parentsystemuserid($select=internalemailaddress;$expand=parentsystemuserid($select=internalemailaddress;$expand=parentsystemuserid($select=internalemailaddress)))

If you are afraid to get a syntax error or not sure what to use for the lookup or email address column, you can use the FetchXML Builder for XrmToolBox tool to generate.

This is the FetchXML that I used to generate the Expand Query above.

<fetch> <entity name='systemuser'> <attribute name='internalemailaddress' /> <link-entity name='systemuser' from='systemuserid' to='parentsystemuserid' link-type='outer' alias='Manager1'> <attribute name='internalemailaddress' /> <link-entity name='systemuser' from='systemuserid' to='parentsystemuserid' link-type='outer' alias='Manager2'> <attribute name='internalemailaddress' /> <link-entity name='systemuser' from='systemuserid' to='parentsystemuserid' link-type='outer' alias='Manager3'> <attribute name='internalemailaddress' /> </link-entity> </link-entity> </link-entity> </entity> </fetch>

In the flow, you can select the email address of the primary manager from the Dynamics Value list.

But for Manager 2 and Manager 3, you need to write an expression.

The expression would look something like this but you need to replace the lookup column and email address column with your own logical names.

Level 2 items('Apply_to_each')?['parentsystemuserid/parentsystemuserid/internalemailaddress'] Level 3 items('Apply_to_each')?['parentsystemuserid/parentsystemuserid/parentsystemuserid/internalemailaddress']

You can read more details in my blog post about it.

https://linnzawwin.blogspot.com/2020/08/get-output-data-from-expand-query-of.html#n1twolevel

Finally, when you run the flow, you can see Manager 1/2/3 Email addresses are empty for some of the users due to different reporting lines. All you need is to check if the value is empty() and send approval emails for those non-empty email addresses.