Create a Simple SharePoint 2013 Employee Directory on Office365 – Part 4 – Search Schema
- Create a Simple SharePoint 2013 Employee Directory on Office365 – Part 1 – Introduction
- Create a Simple SharePoint 2013 Employee Directory on Office365 – Part 2 – Set up an Employee Directory Page
- Create a Simple SharePoint 2013 Employee Directory on Office365 – Part 3 – Create Display Templates
- Create a Simple SharePoint 2013 Employee Directory on Office365 – Part 4 – Search Schema
- Create a Simple SharePoint 2013 Employee Directory on Office365 – Part 5 – Sorting & Refiners
In the prior posts in the series, we’ve seen how to set up a page for our employee directory and then create Display Templates to render the information we want for each person, along with a nice alphabetical filtering capability.
Up to this point, things have worked pretty much the same as they would in an on premises installation of SharePoint. With Office365, though, this is where things get a lot more complicated. I want to thank my search guru Mikael Svenson (@mikaelsvenson) for his assistance with this part of the work. This is another instance with SharePoint where the steps should be simple, but they aren’t when you add Office365 into the mix: it only gets harder.
In the listing we’ve made so far, we’ve had little trouble displaying the User Properties we want to see in the directory. Adding in some slicing and dicing requires a bit more fancy footwork. After all, if we can’t find people, it’s not much of a search-based solution, is it?
Each property we want to use in a filter we build needs to be “Sortable”. If we want to use a property in a refiner – those lists of values on the left side of the Search Results page – those properties must be “Refinable”.
For some unfathomable reason, out of the box the LastName property is not “Sortable”, nor is it “Refinable”. The FirstName is “sortable”, but to me it’s a lot less likely that you’d want to sort or filter on FirstName than LastName. If we want to sort by Lastname, we’re out of luck. In Ari Bakker’s (@aribakker) post that shows how to set up a simple a Employee Directory on SharePoint 2013: How to: Create a Simple SharePoint 2013 People Directory, he shows how to tweak the Lastname property to make it sortable.
If you go to the Search Schema settings (Admin / SharePoint / search / Manage Search Schema), you’ll see that this is the case.
Update 2016-11-02: Thanks to eagle eyed reader David (see his comment below), the LastName Managed Property is sortable on Office 365 now, at least in one of the tenants I checked. If that’s the case in your tenant, you won’t need to jump through this set of hoops.
In Office365, we’re not able to change the attributes of the out of the box User Profile properties, though. If we try to, they are all simply grayed out; we can’t touch them. This is where our path diverges from on premises installs.
On Office365, there is a very big set of dummy properties named RefinableString00, RefinableString01, etc. There are 100 of these String properties. There are also sets for Date (20), Decimal (10), Double (10), and Int (50). If you need any more of any of these, you’re stuck, so use them wisely.
Because the Lastname is a a string-valued property, we’re going to use one of the RefinableString dummy properties. What we do is map the RefinableString property to a crawled property. Here I’ve chosen RefinableString00 because I haven’t used it yet. Here are the steps to set up the mapping:
- Click on the RefinableString00 property in the search schema listing
- Scroll down to the section for Mappings to crawled properties
- Click on the Add a Mapping link
- Find People:LastName by typing “Lastname” in the search box and clicking “Find”
- Select the People:LastName property and click OK
- You can only map to one Crawled Property, even though the UI will allow you to select several. As much as I wanted to include People:SPS-PhoneticLastName to match the LastName Managed Property, I couldn’t. I had to settle for just People:LastName, (which should be fine).
- Scroll to the Alias setting and give the property a name you’ll recognize. I’ve used LastnameSortable.
- Save the RefineablerString00 property by clicking OK
Now you still have a property named RefinableString00, but it has an alias of LastnameSortable (if you used the same name as I did) and it is mapped to the People:LastName property, meaning that RefinableString00 will get the same values as People:LastName.
Perfect, right? Now we can just use that LastnameSortable property in our slicing and dicing tools and we’ll be all set!
Not so fast, Kemosabi. On Office365, we have no control over search crawling. We can’t just fire off a crawl to update the index like we can on premises. (In either case, we have to be admins, but that’s not the difference here.)
A User Profile will only be re-indexed if a value in that profile changes. For example, if I change my MobilePhone or a new value syncs over from Active Directory, then the next crawl will pick up that change and the value will be available in the search index. We’ve mapped the People:LastName property to the RefinableString00 property, but since no User Profiles were changed in the process, it makes no difference. We can’t just push the re-index button on Office365.
The only way we (Mikael, and therefore I) know to change every User Profile so that it will be indexed is to run a Powershell script that “touches” every profile. This is down and dirty stuff, folks, and not for the squeamish. You might want to enlist your local Admin Superhero to help you with this part.
Mikael built a script that loops through all of the User Profiles; copies the SPS-Birthday property value; sets the SPS-Birthday property to an arbitrary value; saves the profile; sets the SPS-Birthday back to the original, saved value; and saves the User Profile. Yes, for every single User Profile in the User Profile Store.
The one problem I had was that SPS-Birthday was almost never available in the User Profiles in the organization I was working with. Mikael adapted his Powershell script to also work with Department, which ought to be there more often. For the company I originally muddled through this with and their 100 or so employees, this wasn’t a big deal; the Powershell script ran through in a few minutes. If you are in a larger organization, the script might take hours and could possible timeout along the way. That said, it will work. Eventually.
I’m not going to go into all of the details on how to run the Powershell script. Instead, head on over to Mikael’s post “How to trigger re-indexing of user profiles in SharePoint On-line” and follow his instructions.
Once you’ve run the script, you’ll need to wait for some period of time – we don’t have any way of knowing when these crawl jobs actually happen. Experience shows that this will be 2-8 hours, but it can depends on the load in your tenant’s hardware. Once the values are indexed, you can start to use them in your Employee Directory.
The next step is to add some of the slicing and dicing capabilities. We couldn’t do that before we set up the RefinableString00 aka LastnameSortable property. If we had tried to use the Lastname property, we’d just get errors in the page I know this from experience). Errors that tell us precious little about what the actual problem is. Correlation ID!
Ari’s post shows some slick additional sorting capabilities that it would be nice to add at the top of the page. Sorting by Lastname or Firstname might make finding the person in the middle of the list by default a little easier to find. In the next article in the series, I’ll show you how to set those sorting capabilities up.
Another great post, Marc! Just wanted to clarify. This post is only about setting up a Refinable String and re-indexing the User Profiles. It is not expected to actually deploy the Refinable String (we modified) now, we will do that in the next post. Is this correct?
Right. Now that we have the Sortable/Refinable managed property set up, we can do some things with it.
You’ve got to wait for the indexer, anyway!
M.
Hi Marc I have noticed that the results within a letter are not in alphabetical order. Will this be fixed in the next installment ? (Hint, Hint) I control the indexer on my On-Premises farm so I don’t have to wait for it ! none of this waiting foir the Office 365 Indexer to decide its the cat’s birthday and therefore it should do some indexing.
Nigel:
If you’re on premises, then you’re sort of working too hard. You could just follow The post I’ve mentioned from Ari Bakker:
http://www.sharepointconfig.com/2013/05/how-to-create-a-simple-sharepoint-2013-people-directory/
M.
Marc,
Great post. For the life of me I don’t understand why MS doesn’t have an employee directory out of the box. I’ve never had an intranet that didn’t have one…
BTW, in the question above nigelpwordpress asked if the next installment of your series will fix the sorting. I’m on O365 and of course the names aren’t sorted properly.
Will the next installment cover that?
Thanks for the series it was exactly what I was looking for!
Hi Marc as requested I am feeding back my experiences. The People search as described here and Ari’s blog worked fine. The problem I had was if I added custom attributes to the Users profile and updated the Display template accordingly to display those attributes, uploading them to the _EmployeeDirectory area and publishing them, they did not display. After Bing’ing the issue, the one thing which kept repeating was that you had to get some feedback from SharePoint that the display template had been updated in SharePoint. So in the end I added the Display Template to the Results Type. Then when I updated the Display Template, I got the yellow bar on Result Types Page saying the Display Template had been updated. I added a few more things in like the person’s picture and Matt McDermott’s presence indicator and again when I uploaded the display template into the _EmployeeDisplay area, saved it and published it and then went to the Result Types page and the yellow bar was there. I then ran people search and the attributes were there.
I don’t know why the original Display worked and subsequent ones did not until I added the Display Template to the Result types. Perhaps I did something wrong somewhere.
Nigel
Hi Marc,
Will there be another installment to this series?
Yes, several more. I’m just busy!
Are there specific things that you’re waiting for?
M.
Marc,
I know the last thing I’m looking for is the sorting by last name. In Ari’s post he has a dropdown shown. With your solution that wouldn’t look good.
Thanks!!!
Will:
How do you mean it “wouldn’t look good”?
M.
Mark,
We’ve got table headers that could be clicked for sorting. Adding a drop down to change the sort that is used doesn’t seem to fit the design. In my case the company isn’t that big. Users just need to click the last name letters and have the results sorted by Last Name (or in this case the RefinableString00)
I think there are two levels of additions for your post:
1. Simple: Add sorting to the results so people show up in LastName order
2. Home Run: Add #1 and make the table headers clickable so you can sort by LastName, FirstName, or any other table header column.
But, you’ve already created a great post and and beggars can’t be choosers :)
Will:
That’s just the sort of input that’s helpful. Thanks for sharing your thoughts!
M.
I would agree with Will that sorting ability would be very useful. After messing around with the solution to date, I found that by messing around with the “Available Sort Orders(JSON)” property that I could get it to sort based on the LastNameSortable field which is very helpful. But it would be great if the ability to sort on the fly on some of the other fields was included as well.
Can you share what you did in the Available Sort Orders to get it sorted by last name?
Sure, not wanting to take anything away from any of Marc’s upcoming posts though. Also, there may be more elegant ways to do this. As I wrote in an earlier comment I was just messing around. And finally, I did this a few weeks ago so I may be missing something but this is what I believe to be the main parts of how I got the results to be sorted.
If you go into the Web Part Properties for the People Search Core Results and select the Settings option. In there you will see a section titled “Results Control Settings”. Check the box which says Show Sort dropdown.
If click Ok and then save the page you will see that it places a dropdown at the top of the list. In this there should be three possible sort orders (Relevance, Social Distance and LastNameSortable). You can select any of these three options and it will sort the results by the selected sort order.
Initially, I thought I would select the LastNameSortable order and then uncheck the box for the “Show sort dropdown” the Web Part properties and that would keep it sorted. It did not work though. It would simply take away the dropdown box and revert back to the Relevance sort order.
Underneath the “Show sort dropdown” checkbox you will see the “Available sort orders (JSON)” text box. With the dropdown “Show sort dropdown” checkbox checked you are able to edit this entry. So it was a matter of copying the text in the field (Make sure you save this original someplace safe in case you run into issues and need to revert) and pasting into notepad and reordering the options.
I ended up having the entry for LastNameSortable as the first entry (my guess being that this is the placement of what the web part uses as the default sort order). Then pasted it back into the “Available sort orders (JSON)” field, uncheck the “Show sort dropdown” box, click OK and Save the page
BTW Exnav29 your solution worked like a charm. Now my list is sorted by last name. I’d love to have the headers sortable but this works for now. Thanks for posting your solution.
Thanks for a great guide!
I am curious to learn whether it will be an easy way of filtering out users altogether? For example, we have a lot of users in AD that are not actual employees (test users, “guest” users, former employees, etc.) For an internal list of employees, it would be great to not display these at all. For example, if Department = blank then do not include, or something like that..?
Paal:
You can add any filtering you want to the query in the People Search Core Results. In my specific client instance, we used this:
{searchboxquery} (Department<>"Consultant" AND -department:"ex-employee") AccountName:membershipThis excludes employees who are consultants or no longer work with the company.
Since everyone seems to make up their own rules with AD (why, oh why???), your filters will be different but this is where you’d add them.
M.
Paal, You can also set filter on the “user profile service application” and then “configure Synchronization Connections”. From there you can select your AD connector and then click on “Edit Connection Filters” and then you can exclude users or groups by selecting specific attributes.
I used a “Poor mans” workaround on this and simply leveraged the “About Me” Field, entered ~VirutalAccount~ no space into the About Me field and then entered an exclude statement like Marc suggests below but used the About Me Field since our company doesn’t really leverage that field for information. Hardest part is making sure you communicate to all of your administrative users through Business process that it must be entered this way to get the Exclude statement to reflect properly. For Example to get a list of All Folks in IT Department across the company without these garbage user accounts my statement looks like this: “{searchboxquery} contentclass:spspeople Department=”it” OR Department=”information technology” OR Department=”IT” OR Department=”Information Technology” -AboutMe:~VirtualAccount~” It’s not sexy but it gets the job done.
Because of all the variations in the way people use AD (why oh why?) there’s no “right” way. As long as your way works given your AD data, you’re a winner.
M.
Marc, any chance of getting a part 5 where you can show how to limit filter non user accounts and or remove people from the list? This is a great series and we are almost full circle. Thanks for all your work on it.
David,
You can limit employees by changing the query on the People search core results webpart. Just click on the Change Query box and use the property filter to limit the results using fields in your AD.
Because everyone’s AD is different the fields you will use are unique to you. In my case my non-user accounts didn’t have an office so I limited based on office locations. You could easily add some specific text to one field for your non-user accounts and say property not equal to your custom text.
Hope this helps.
OK. I get that. Although my AD setup is odd (I don’t see Department as a refiner, for example) I can live with this and tweak as I go. But NOW, it works for me (I’m an admin) but whenever I try it with another user, i get an error that says the Control_searchresults_Table.js can’t be found or has a syntax error. If it works for me, why not for someone else? I’ve checked file/list.library permissions and I don’t think that’s it. Has anyone come across this before? Thanks.
David:
Make sure the file is checked in and published. It sounds like others may be getting an earlier version of the file.
M.
Marc already beat me to it. Make sure everything you uploaded to the (html and js files) are checked in and published.
Thanks guys. You were close. Also have to add “Approved” to that list. Wow that was buried deep. So after following directions exactly, had to open _EmployeeDirectory properties in browser. Had to make sure each folder AND file was APPROVED. Now it appears to work!
Next issue is what controls the sort order? My people are listed by last name but not sorted by it (in this case refinerString00). It looks like they may be sorted by the order they were entered into the system. Not a deal breaker but would be nice if it sorted simply.
Thoughts? Thanks again for the assist.
David,
ExNav29 put in a long post up above with how to get it to sort by Last Name. I replicated what he did and it worked like a charm. My list is now sorting by the lastnamesortable property and looks great.
Great advice and it seems to have worked. So is there a way to affect the “refiners” on the left side? Currently I only get 7 departments (I’m guessing it’s the top 7 by population?) plus some other categories in small chunks. Any way to get some sort of tree view with all departments? I’m thinking not as all that stuff is “under the hood”. But I figure it can’t hurt to ask. =)
David,
The refiner panel is a webpart on the page. You can edit and change the refiners by editing the webpart and clicking the choose refiners button. It’s pretty easy and allows you to preview the results. Also, if you need, you can map any of your properties to one of the presetup refinablestringXX properties (if you’re in O365).
Thanks Will. I understand that and can see the list but the issue is that for the “Departments” refiner, I get one automatically but it only shows 7 and not the full list. ideally, I’d like a list of all departments so that a user can select “Marketing” and get a list of all marketing Employees (God help them). =)
David:
The Refiner Web Part actually uses a Display Template “under the hood”, so you can change its behavior as well. However, the point of the refiners is really to show the top n values for the managed property so that you can refine the result set. If Marketing isn’t showing up, then there must be far fewer people there than in other Departments.
You could also consider adding a different navigation mechanism at the top (like the alphabetic filtering) that filters by Department of that is a significant enough filter for your organization.
M.
Marc, thanks for sharing these great articles.
Might be tricky but would it be possible to have a printable version??
Marc, also, any hints on how to add more fields like say, Preferred Name to the table? I’ve tried adding it to the properties list in Items_Person_Table page but I get to data. Thanks.
Shoot, I mean like adding the LastNameSortable field. Pref Name is obviously already in there.
Marc,
I would think that in the REST call we should be able to add $orderby=LastName. Hweer when I try that it seems to be ignored. If we could get that to work, then we should be able to avoid all the work with the sortable last name. Any thoughts on why Microsoft is ignoring “$orderby=LastName”?
Marcel:
The problem with LastName is that it isn’t set up to be sortable on Office365. That’s why we’re jumping through all of these hoops with LastNameSortable.
M.