Approvals based on values in contact list containing more than one value

Kimberly Loveless

I have an approval process to ok team members to work on an additional project.

I have a contact list column to enter which team members are being requested for a certain project. I need an approval that will fill in and gather approvals from their directors

My initial thought was to use an index/match function to pull the team member's director but it will only collect this if a single contact is in the team member column.

This is what I mean, if I have 2 team members "IT Help Desk" and "Test" how would I pull the director for each and set up the approval?

It may not be able to be done and that's ok

Jen Lange

Hi @Kimberly Loveless. I think this could be achievable utilizing a combination of, IF, CONTAINS and VLOOKUP formulas to populate the Director column and then creating an automated workflow to send approval requests to the directors.

Here's what I'm thinking...

1) Setup the Directors as contacts

2) Populate your Director column with the following formula (modify references according to your source data sheet)

3) Create an automated approval workflow

Let me know if this insight is on the right path and then I can tweak from there.

-Jen

=IF(CONTAINS([Team Member]1, [Team Members]1) = "True", VLOOKUP([Team Member]1, [Team Member]:[Director Name], 2, false)) + ", " + IF(CONTAINS([Team Member]2, [Team Members]1) = "True", VLOOKUP([Team Member]2, [Team Member]:[Director Name], 2, false))

Jen Lange

Hi @Kimberly Loveless. I think this could be achievable utilizing a combination of, IF, CONTAINS and VLOOKUP formulas to populate the Director column and then creating an automated workflow to send approval requests to the directors.

Here's what I'm thinking...

1) Setup the Directors as contacts

2) Populate your Director column with the following formula (modify references according to your source data sheet)

3) Create an automated approval workflow

Let me know if this insight is on the right path and then I can tweak from there.

-Jen

=IF(CONTAINS([Team Member]1, [Team Members]1) = "True", VLOOKUP([Team Member]1, [Team Member]:[Director Name], 2, false)) + ", " + IF(CONTAINS([Team Member]2, [Team Members]1) = "True", VLOOKUP([Team Member]2, [Team Member]:[Director Name], 2, false))

Jen Lange

UPDATE: I successfully received approval requests for the lines I submitted using the setup I outlined above, which means this seems to be a viable solution to your use case.

Kimberly Loveless

hi @Jen Lange I need to keep everything in the same row. I am thinking that there isn't going to be a way to do this. Your solution would work if it could be done differently.

Jen Lange

@Kimberly Loveless, what do you mean by keeping everything in the same row? The right portion of the screenshot is mirroring data located on another sheet. Is the Team Member - Director relationship not captured in another sheet?

If they are, then ignore the columns that don't match your original spreadsheet, because they won't exist in your main sheet.

Kimberly Loveless

@Jen Lange Ahh i misunderstood. I figured out a way around this. Thank you for your help though :-)

Jen Lange

No worries. Things aren't always easily translatable through a forum. I'm glad you found a solution. When you have some time, it'd be great to learn of your approach.

Also, thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time AND it helps me flex my skills.

If you appreciate my responses, please recognize the effort by with an "Insightful" or "Vote Up" selection. Thanks!

-Jen