Look Up and Compare Value from 2 different lists

(0) Share

Report

Posted on by _kikilalaaa

353

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!!

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (0)

efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
_kikilalaaa 353 on at

Like (0)

Report
Copy link

Link copied!

Hi @efialttes ,

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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
_kikilalaaa 353 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
_kikilalaaa 353 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
_kikilalaaa 353 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
_kikilalaaa 353 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No