Look Up and Compare Value from 2 different lists

[_kikilalaaa]

Hi All.

I have 2 sharepoint list called Employee and Approver. Both list have a column "department". The approver list containes 2 columns: department and approvers. The approvers column data type is group/person. I allowed multiple selection. For example:

Department                                         Approvers

HR                                                        HRAdmin1 , HRAdmin2

Finance                                                 FinanceAdmin1

Everytime user submit a form, the approver will be assigned to approve/reject the form. If any of the approver has approved / reject the form, a column "Approver" in Employee List will be update the name of the approver.

My question is, how to do the flow to detect the department, and then compare the approver based on the department? 

I hope anyone can help me.

Thankss!!

[efialttes]

Hi!
Not sure I understood your req.
Are elegible approvers always the same, no matter the dept the employee belongs to? I mean, when you send the Approval, did you restrict the list of approvers based on the Department?

[_kikilalaaa]

Hi @efialttes ,

The eligible approvers are different based on which departments employees belong to.

[efialttes]

Hi again!

So, Sharepoint List 'Employee' includes at least three columns:

-Employee (Group/person, right?)

-Department (single line of text, right?)

-Approver (Group/Prrson, right?)

Sharepoint List Approvers includes at least two columns:

-Department (single line of text, right?)

-Approvers (Group/person - multiselect, right?)

If so, I guess once you identify the requestor employee, you perform a 'SP Get items' in SP List 'Employee', filter it to get the item representing the Employee, and get its Department.

Then, I guess you will perform a 'SP Get items' in SP list 'Approvers', filter it to get the item representing the Department, and get list of Elegible Approvers

I think we are close to the solution once you confirm my assumptions on Sharepont column types

Thanx!

[_kikilalaaa]

Hi @efialttes !

To answer your question,

So, Sharepoint List 'Employee' includes at least three columns:

-Employee (Single text of line) - store employee's name

-Department (single line of text)

-Approver (Group/Person)

Sharepoint List Approvers includes at least two columns:

-Department (single line of text)

-Approvers (Group/person - multiselect)

I think I get your idea on how the flow should be. But I am not sure on how to filter to get the department from both list and compare it together, to come out with eligible approvers. 

I hope you can help me!

Thanks.

[efialttes]

Hi!
Since Department is a single line of text you can apply an ODATA filter when 'Get items' from Approvers SP list, then apply first() expression to the output to get the item instead of the array.

Hopefully this afternoon I will get some time to send you an example
Hope this helps

[efialttes]

Hi again

I've found this excellent post from @PieterVeenstra explaining the use of ODATA filters in the context of Sharepoint Get items:

https://veenstra.me.uk/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/ 

In your case, assuming that you already stored the Deptartment name in a string variable (let's call it 'TargetDpt'):

I am also assuming there is a single item per Department, I check this via condition action block, if true, this means we got an array with one object (our target item), so we can apply first() function to extract it from the array

Hope this helps

[_kikilalaaa]

Hi!

Thanks for the examples. However, I am a bit confused. How the workflow showed to get the approver name/email based on the department? I could not see it from your example.

I really appreciated it if you could explain more about it.

Thanks!

[efialttes]

Hi
Can you share a screenshot of the flow you already built so far?
If so I can try to complete it

[_kikilalaaa]

Hi @efialttes ,

This might sounds stupid(yes, it is) but I don't know how to attach the screen shot in here. I tried to use the camera icon to upload but i do not know about the source, dimension etc. While I'm still figuring out, I'll make the flow here, I hope you understands!

1) When Item is created or modified

2) Initialize variables : 

    Name:ApproverEmail

    Type: String

    Value : [Approver] - from Employee List

( "[ ] " symbols indicates dynamic content) 

 //  my logic is ApproverEmail now has Approver value from Employee List, which is blank/null/none.

3) Get Items from Approver List

   Filter ODATA: Department eq [Department] - from Employee List

4) Set variables

   Name: ApproverEmail

   Value : [Approvers] - from Approver List

//my logic is when it filter the Department, it will get the approvers for that department

5) Send email

To : ApproverEmail; //this is a variable

Currently, the error I get is from the third step. It said:"

"Column 'Department' does not exist. It may have been deleted by another user.

I hope you get the idea.

Thanks Again!

[_kikilalaaa]

It's weird because I have "Department" column in both list