3

I have a list view that should only show when an admin adds the person's username to an "allowed viewers" username column. So, I am thinking to create a calculated column that compares the current-viewing user to the username in the "allowed viewers" column. Then, I will filter the list by the result. If it compares to true, the list item will show. If it compares to false, it will not show.

So my question is, what do I put in the Calculated Column field to compare my "allowed viewers" column (which contains a username) to the current user?

[waaaay later]

Well, I've never come to a satisfactory solution to filtering a list by the current username, and got into more trouble here. I'm adjusting the title of my question to better reflect my intention.

How can I use a username column and filter the view by the matching current user?

Community
  • 1
bgmCoder
  • 3,581
  • 16
  • 61
  • 102
  • Can't you just set the permission on the list? SharePoint will handle permission trimming for you. – Hugh Wood Nov 20 '12 at 15:32
  • No, because I am using the list as a template to be set on 100 sites where each site has a different group assigned with rights to manage everything but views. – bgmCoder Nov 20 '12 at 15:38

2 Answers2

3

Okay had a play and basically use "Me".

Either:

// Include domain name
Me

// Remove domain name
RIGHT(Me, LEN(Me) - FIND("\", Me))

Note: Column names are in the format [column]

Hugh Wood
  • 6,285
  • 1
  • 26
  • 45
  • can we use calculated columns with multi-select person field ? – Arsalan Adam Khatri Nov 20 '12 at 15:52
  • Yes, I have seen it done, filter the data perhaps with find? since it is stored as plain text, "Name; Name1; Name2", this part is a little fuzzy but I think it should be fine. – Hugh Wood Nov 20 '12 at 16:19
  • Uh, how do I use that to compare Me with the username value in the "allowed users" column? The "allowed users" column will be a multi-select field, by the way. – bgmCoder Nov 20 '12 at 16:23
  • The logic would be something like "if(FIND(ME, [column]) > 0, TRUE, FALSE)" I can't test this on my current setup however. – Hugh Wood Nov 20 '12 at 16:26
  • Ah, it says I can't use columns defined as username data types for a calculated value... – bgmCoder Nov 20 '12 at 16:33
  • If I change the other column to a text field, it will let me add it to the calculated column, but it says I can't use "Me" in the calculated column. I thought I read that somewhere, actually. – bgmCoder Nov 20 '12 at 16:35
  • This should fix it for you, http://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/ (I didn't test the code in a calculated column, so it didn't warn me) – Hugh Wood Nov 20 '12 at 16:52
  • That is a hack, actually. I already tried it on my Sharepoint Enterprise 2010, but it didn't seem to work. I'm afraid of that anyway, because I have to create a template from these columns. – bgmCoder Nov 20 '12 at 17:08
  • Hack/Fix yadda yadda :P! Seriously, that is the only way I can work around to do what you want to do without, maybe perhaps writing something custom like a feature which will set up the permissions on all the sites for you on the lists.. That would be really complicated. – Hugh Wood Nov 20 '12 at 17:14
  • Don't you have to use brackets, as in "[Me]" ? I know that's how you do it when you filter on a user field. – Cameron Verhelst Mar 04 '14 at 19:27
  • Yes, I was just proposing logic, the column names are indeed in square brackets – Hugh Wood Mar 05 '14 at 09:04
  • This doesn't really work. Read this: http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ – bgmCoder Dec 15 '15 at 21:25
  • This is for usernames, that blog post is about [Today] which is an updated value. Yes this will not work for values which can change. But yes it does really work you just have to know what you are doing. – Hugh Wood Dec 18 '15 at 16:29
  • In the page you've linked to, in the comments, he says it doesn't really workd: Apparently this doesn’t work dynamically. As Bill said above, the [Today] value doesn’t change the next day, and likewise, the [Me] trick doesn’t work for the next person. see the Jan 16 comment. – bgmCoder Dec 23 '15 at 21:41
  • No you misunderstand, it works in 2010 and upwards. That comment is 2007 related. – Hugh Wood Dec 25 '15 at 02:49
  • Since Calculated Formulas only recalculated on item change, [Me] in a (hacked) Formula is the same as [Modified By]. You can not use [Me] in a Formula for a reason. The [Today] hack has been debunked long long time ago, and is the same effect as using [Modified]. And square brackets or none do exactly the same, only in Column names with spaces do they matter. – Danny '365CSI' Engelman Dec 28 '15 at 11:55
1

After having spent hours and hours trying to get columns and calculated columns and workflows to come up with some combination that might match the current user against a value in a list-view field, I finally managed to come up with something.

In the end I used a Current User Filter. The idea came from here. But here is my take on the procedure:

The List-View page is already a Web-Part Page with a list-view web-part, so I:

  1. Entered Edit Mode
  2. Added a Current User Filter - you will find this when you go to add a new web-part to the page. It is under Filters.
  3. Using the drop-down arrow for the Current User Filter web-part (in the upper corner) I used 'Create Connection' and set the list-view as the recipient and I chose which column I wanted to filter - which is a regular person field.
  4. Then, in the Current User Filter's web-part options, there is a place where you can specify how the value is returned. I set it to Name. This way it will return a Display Name instead of a domain user account name (see screenshot below).

Here is an image:

Options for Filter Web Part

That's it!

Summary

I added a Filter Web Part to the Web-Part Page and connected it to the List-View and set the return value. This works like a charm. Now I have a list filtered to show only those items where the username column matches the currently logged in user. And it's all OOTB and scriptless.

Community
  • 1
bgmCoder
  • 3,581
  • 16
  • 61
  • 102