Dynamic View Access help

Luke L

Hi all,

I have a sheet called performance appraisal tracking that holds a bunch of information about a dept, an employee in that dept and the step they are currently on in the performance appraisal workflow. Now that the review period is over I've been asked to create a report where department heads can see (next time we go through this exercise), what step all their direct reports are on for the given week. Rather than adding a dept head "contact list type" column to the original smartsheet (because that data doesn't come from the original report I'm importing from), I created a DeptAccess smartsheet that lists the Dept, head of dept and Dept Head Email (which is a contact list type field).

I then created a Weekly Status Report report that groups direct reports under their department and shows their name, the step they are on and the dept head email.

In Dynamic view I created a new view that looks at 'Weekly Dept Status Report' report but when I go to setup the Restrict View by current User, the only option is 'No restriction - all rows visible to everyone shared'.

I would like the dept head to login and only be able to see the rows where the email they logged in with matches with the Dept Head Email.

I tried creating a dynamic view just using DeptAccess smartsheet as the source and that allows me to select 'Dept Head Email' from the Restrict view by current user option, so why can't I do the same when referencing the combined report?

Thanks in advance for any help/instruction.

Paul Newcome

That option doesn't exist when referencing a report. You would need to set the filter in the report, and it will automatically pull through on the back end.

Luke L

Thank you very much Paul. I read a couple of other community posts last night and it occurred to me I may have to put that filter on the report. I'll give it a go. Have a good one and I'll update the discussion later after some testing.

Luke L

Hi @Paul Newcome I tried to set the following filter on the report, thinking Dept Head Email should be one of and then I could select 'Dept Head Email' from the dropdown, but instead I get the option to 'select all', 'blank' or check off the emails individually.

Again, I want the report to filter the data based on if you are listed as a dept head for that department. The associations of which are in my DeptAccess smartsheet. Instead of 'is one of' should it be something else? I tried 'Select All', but when I test the view I see every department even though I associated myself to 1 of 27 departments.

Thanks

Luke L

Is there a way on the report to filter by logged in user? I'm thinking my filter should be the equivalent of 'Dept Head Email is equal to <Session.loggedinUserEmail>' but I don't see how you can choose session variables.

Luke L

I found this article: https://help.smartsheet.com/articles/2483384-current-user-report which suggests exactly what I want to do is possible, but when I select 'is equal to' or 'is one of' (because the recommended 'has any of' option doesn't exist), I don't see the 'current user' as a selection. I only see, 'Select All', 'Blank', or a list of all contacts in that contact list.

Paul Newcome

@Luke L Make sure the column is set as a contact type column.

Luke L

Hi @Paul Newcome, I just double checked and it is a Contact List type column. See very first screenshot I posted.

Paul Newcome

Is there an option for "Has any of"?

Luke L

@Paul Newcome No such option, like the support article stated.

Luke L

The only options I see in the dropdown are:

drop down options include
is blank
is not blank
is equal to
is not equal to
is greater than
is not greater than
is less than
is not less than
is a number
is not a number
contains
does not contain
is between
is not between
is one of
is not one of

Paul Newcome

When you add the Dept Head Email column to the report, is there more than one listed?

Luke L

Ok, I think I know what part of my problem was. I have been doing so many different things to try and get this to work, I forgot what I had done. I've since torn a lot of that proof of concept work down. What I have now is performance appraisal tracking (PAT) sheet. On that sheet I have columns like:
* IsMaxDate (Is this the latest record for this person)
* employee
* employee email
* supervisor
* supervisor email
* Department
* awaiting step

Then I have another report DeptAccess (DA) that has the following fields:
* DA Department
* DeptHead
* DeptHead Email (contact list, allows multiple contacts)

I created the Weekly Dept Status Report, which looks at both sheets and displays PAT.Department, PAT.Employee, PAT.Awaiting Step and DA.DeptHead Email. I had initially created a vlookop formula in PAT Dept Head Email field so that the report would group by dept and show the associated dept head email, and I think that's why I wasn't able to filter appropriately. So now that I removed that vlookup column, my Weekly Status Report doesn't group right AND I can't filter on the report OR the PAT sheet. I'm going to call this one done and seek help from support.

Thanks so much for your assistance @Paul Newcome I really appreciate it.