Dynamic view for person & team

Neil Watson

Hi

I have several DV's set up for individuals to review a task list. These individuals are grouped into several teams. I have had a request to create a view that shows the team members tasks, as well as the tasks of other team members in the same team.

Suggestions on the best way to achieve this?

Philip Robbins

@Neil Watson I've just tested out another option that might work for you.

Set up a separate team list sheet with a row per team and a multi-select contact column for the team members.

On your task sheet you'll need a single-select contact column for your assignee, a column for your team name (text/number is fine) and a multi-select contact column for the team. Assignee should be selectable. Under the team name column add the following column formula:

=INDEX(COLLECT({Team list Name}, {Team list Team}, HAS(@cell, Assignee@row)), 1)

Note, here 'Team list Name' is the name of the team and 'Team list Team' is the multi-select contact column with the team members.

Under the multi-select contact column in the task sheet add the following column formula:

=INDEX(COLLECT({Team list Team}, {Team list Name}, [Team Name]@row), 1)

This should pull through all the contacts for the relevant team and you should then be able to set up a Dynamic View that is filtered on that column that gives you your 'My Team's Tasks'.

Let me know if you get it up and running.

Philip Robbins

@Neil Watson if I've understood you correctly I believe this would only work if you were looking to select just one team. You can't pull back contacts from multiple cells (e.g. contacts from multiple teams) to a single cell and have them retain the contact format. This is a quirk of the contact column type that's discussed in other community threads. For most data types, JOIN/COLLECT works perfectly for this, but with contacts they don't retain their format and hence can't be used to filter a Dynamic View. So selecting one team and INDEX/COLLECT is your only option.

Philip Robbins

Hi @Neil Watson,

If your task sheet has a column with a team selector you can set up sheet filters for each team based on the column value and Dynamic Views based on these filters that you share with each team. Further, you could have a separate sheet which lists each individual as a contact and which team they are in, then on your task sheet perform a lookup in your team column based on the assignee, so you only ever have to select the assignee and the correct team is returned.

Neil Watson

Thanks @Philip Robbins, I will have to go down that path. I am embedding the DV links in a WorkApp and was hoping to reduce the number of links to "My Tasks" and "My Teams Tasks".

Philip Robbins

@Neil Watson I've just tested out another option that might work for you.

Set up a separate team list sheet with a row per team and a multi-select contact column for the team members.

On your task sheet you'll need a single-select contact column for your assignee, a column for your team name (text/number is fine) and a multi-select contact column for the team. Assignee should be selectable. Under the team name column add the following column formula:

=INDEX(COLLECT({Team list Name}, {Team list Team}, HAS(@cell, Assignee@row)), 1)

Note, here 'Team list Name' is the name of the team and 'Team list Team' is the multi-select contact column with the team members.

Under the multi-select contact column in the task sheet add the following column formula:

=INDEX(COLLECT({Team list Team}, {Team list Name}, [Team Name]@row), 1)

This should pull through all the contacts for the relevant team and you should then be able to set up a Dynamic View that is filtered on that column that gives you your 'My Team's Tasks'.

Let me know if you get it up and running.

Neil Watson

@Philip Robbins spot on mate! Works perfectly and as intended. Thanks very much, great example and use of index/collect.

Thanks

Neil

Neil Watson

@Philip Robbins have another sort of related question, I have a form with a multi-select column where one or more teams can be selected. I want to have a lookup column that returns contacts so I can use this in a dynamic view. In the case below, "Team(s)" is the multi select dropdown and "Team Leader" is the lookup from a source admin sheet. is this possible?

Philip Robbins

@Neil Watson if I've understood you correctly I believe this would only work if you were looking to select just one team. You can't pull back contacts from multiple cells (e.g. contacts from multiple teams) to a single cell and have them retain the contact format. This is a quirk of the contact column type that's discussed in other community threads. For most data types, JOIN/COLLECT works perfectly for this, but with contacts they don't retain their format and hence can't be used to filter a Dynamic View. So selecting one team and INDEX/COLLECT is your only option.

Neil Watson

Hi Phil, I feared as much! Thanks for responding, I am going to have to give this some serious thought.

Andrée Starå

Hi @Neil Watson @Philip Robbins

I hope you're well and safe!

If I haven't misunderstood, it's actually possible, and I recently did something similar in a client solution.

If you add a so-called helper column so each team populates in separate cells, you can add a current user filter referencing them all and then filter Dynamic View with that filter.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Neil Watson

Hi Andre

Sounds like it might work! I will try this tomorrow and let you know.

Andrée Starå

@Neil Watson

Excellent!

Fingers crossed!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Philip Robbins

@Andrée Starå Are you suggesting a helper column per team that has a conditional lookup that only pulls the names in if the team is selected in a separate multi-select column?

Neil Watson

@Andrée Starå thanks, your suggestion worked!

Andrée Starå

@Neil Watson

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Philip Robbins

Yes, exactly!

Philip Robbins

@Andrée Starå that's actually a great shout if the columns are only going to be used for filtering. I was thinking they might also be used for other purposes, so was narrowly thinking that all contacts needed to be in a single column. 🙌