Category: Technology

SharePoint Column Naming Inconsistency

I have been interfacing with SharePoint list data via the REST API and assumed the name I typed onto the column was, well, the column’s name. It’s not!

In the ‘normal’ view, click ‘Add column’ and add a column.

Switch to “Quick edit” view.

Add a column using the “+” symbol.

What do we have? The columns look like they have the names I’ve supplied through either method. But … hover your mouse over the column and check out the URL for the column.

While the ones added using “Add Column” are named exactly what I typed, the ones added in quick edit have four-character pseudo-random strings instead of the name I typed.

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%2MZQ472C3%2F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=AddedViaAddColumn

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%9MZQ472C3%9F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=dwev

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%9MZQ472C3%9F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=fqkm

https://tenant.sharepoint.com/sites/SiteName/_layouts/15/FldEdit.aspx?List=%9MZQ472C3%9F5P5D%2M3M4%2P9T5%2DJ3K5CE52M9I1%7D&Field=NumberViaAddColumn

For GUI access to your data, this is immaterial – the friendly name displays on forms; but, when you are accessing data via the REST API, you must use the internal field name and not the display name you think is assigned to the column. It took me a long time to figure out why my REST call kept saying there was no field “uid” when I could clearly see a column with that name in my list.

 

SharePoint Rest API Does Not Allow Unindexed Queries

I’ve been developing code templates for CRUD operations (that’s a real acronym — Create, Read, Update, Delete) against SharePoint — we need to use SharePoint lists to replace database tables. Retrieving information worked fine until I tried to filter the data through the REST call. SharePoint throws a generic error about exceeding some admin-set limit. (1) I know the limit, I can see the limit. The limit is 5,000, and I know my filtered result set is 121 records. WAY lower than 5,000. Oh, and (2) I can run the query without the filter — I’m paging it! — and read all 29,887 records so what does the limit have to do with anything? Reasoning with an HTTP response … well, doesn’t work. No matter how unassailable my argument is, the API call still returned:

{"error":
    {"code":"-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
     "message":{"lang":"en-US",
       "value":"The attempted operation is prohibited because it exceeds the list 
                view threshold enforced by the administrator."}}}

It is, it turns out, a poorly worded error. I started thinking about the query limits on my LDAP servers — we have hard limits to operations and also require most people perform queries against indexed attributes. It’s computationally expensive to search through unindexed attributes (and the Right Thing To Do, generally speaking, is add an index for something that is a frequent query target). I wondered if there was an analogous “no unindexed queries” setting in SharePoint. Quick enough to test — add an index on the column(s) you use in the filter. In the site content listing, click the sideways hamburger menu by the list name. Select “Settings”

Scroll down to Index Columns and click the hyperlink.

Click ‘Create a new index’

Wait for the index process to complete, then try the filtered request again … I’ve got data! Evidently SharePoint ODATA filter queries to the REST API need to be performed against indexed columns. I’m sure Microsoft has that documented somewhere but quite a bit of Googling didn’t get me anywhere … so I’m posting this in case anyone else encounters the same error.

Did you know … you can archive a Team in Microsoft Teams?

Sometimes a project is done. You’ve used Teams to plan, coordinate, and implement the projected … and there’s a lot of good information in the Teams space … but there’s no need to continue the discussion. Did you know a Team can be read-only? This is called “Archived” – members can search and read content, but no new files or posts can be created.

To archive a Team, view your Teams. At the bottom of the Teams list, click the little gear.

I recommend changing the Team description to let others know it is archived – this is especially valuable if your team is Public as people may join intending to participate in an active discussion. To modify the Team description, select the sideways hamburger menu next to the Team name and select “Edit team”.

I prefix the description with “ARCHIVED:” … hoping people at least glance at the description. Click ‘Done’ to save your change.

To archive your Team, click the sideways hamburger menu again. Select “Archive team”.

You will be asked if you want to make the SharePoint site for the Teams space read-only as well – the answer is generally yes, but if you’re using the SharePoint site for more than just the Team then you do not want to check this box. Click ‘Archive’ to archive the Team.

Should you need to begin accepting new content in your Teams space, you can find the archived teams by expanding the “Archived” section.

Click the sideways hamburger menu and select ‘Restore Team’. This will move your Team back to the “Active” section and allow members to continue posting content.

 

Did you know … Microsoft Teams will mute your microphone when joining a large meeting?

Several offices ago I sat across from a L.O.U.D talker. A loud talker who was constantly on a conference call. I’d try to remember to mute my line before joining a bridge but didn’t always remember. Random background noise isn’t so bad when you’re about to talk to two or three close colleagues, but background noise becomes inundating when the twenty-something people in my department all join a bridge.

Microsoft Teams helps you avoid a deluge of background noise in your call. When you join a “large” meeting –more than five people already in the meeting – your microphone is muted as you join. A notification is displayed prior to joining the meeting to remind you the microphone will be muted.

You can click the microphone button in the meeting control bar to un-mute your microphone and begin speaking.

 

Did you know … you can insert 3D models in PowerPoint presentations?

Select “Insert” from the ribbon bar, then click the drop-down on “3D Models”. You can browse models from Remix 3D (“From Online Sources”) or models stored on your computer (“From a File”).

You can insert Filmbox (fbx), Object (obj), 3D Manufacturing (3mf), Polygon (ply), StereoLithography (stl), and Binary GL (glb) files.

STL files are commonly used when designing objects for 3D printing – here I’ve included a camera/telescope adapter.

Click on a 3D object and there is a rotation control

By changing an object’s orientation, you can use the “Morph” transition to rotate the object during your presentation. Right-click on the slide that contains your 3D object. Select “Duplicate Slide”.

Rotate the object on the new slide. Select “Transitions” from the ribbon bar, and select “Morph” as the transition.

When you play your presentation, the object will rotate as PowerPoint transitions between the two slides.

Now I know there aren’t a lot of uses for rotating 3D unicorns in business presentations, but manufacturers can embed 3D renderings of their products. Many architectural firms are providing 3D building designs that can now be included in PowerPoint presentations.

Using AI For Lead Qualification & Cost Reduction

Microsoft posted an overview of how they use AI (and a bot) to score leads in their sales process — Microsoft IT Showcase/Microsoft increases sales by using AI for lead qualification. My personal ‘most interesting thing’ is that they’re using scikit-learn in Python for some of the analysis — I’m using similar components in a custom-written spam filter at home. Their idea of running the text through a spell checker first is interesting — I want to try running my e-mails through Aspell and see if there are statistically significant changes in classifications.

 

They’ve previously detailed reducing energy usage through machine learning — that’s something I’d love to see more companies doing. Energy can be a significant operating cost, and reducing energy use has a positive environmental impact.

Did you know … you are talking on mute?

Well, probably not *right this second*. But we’ve all heard — err, not heard — someone else do it. We’ve all done it too. The Teams desktop client helps you avoid saying “Sorry, I was talking on mute”.

In the Teams desktop client, you will see an alert when your microphone is muted and sound is detected. Maybe you’re talking to someone who stepped into your office — just ignore the warning. If you’re trying to speak to the meeting, this is a great way to avoid having to repeat your entire thought because no one else heard it the first time!

Did you know … your mouse can be a laser pointer in PowerPoint?

This works both in “Reading View” and when you are playing a presentation. To enable “Reading View”, select “View” from the ribbon bar and click “Reading View”.

To play your presentation, select “Slide Show” from the ribbon bar and click “From Beginning” (or “From Current Slide” to begin the presentation from the current slide).

Hold the CTRL key, then left-click and drag your mouse. You’ll see a red circle which can be used to draw attention to elements of your slide.

If you want a different color “laser”, select “Slide Show” from the ribbon bar and click “Set Up Slide Show”.

Click the drop-down next to “Laser pointer color” and select the desired color. Click OK.

Now when you hold the CTRL key, left-click and drag the mouse … now there’s a blue laser pointer.

 

 

Did you know … you can remove duplicates in Excel?

I use Excel’s COUNTIF function a LOT for reporting. When I want to count the number of transactions that occurred per day (or during a date range), it’s easy enough to get the list of IF’s to count. But when I need to find the occurrence of different text strings, I need a unique list of the strings first. “Remove duplicates” quickly exactly what I need.

In this example, I have a list of all employees and contractor’s departments and titles – I want to know how many people are in each department and how many people have each title. Removing duplicates modifies the data, so the first step is to make a copy of the spreadsheet. Highlight the data. Select “Data” on the ribbon bar, then select “Remove Duplicates”

Select the column(s) where you want to remove duplicate data. This could be exact duplicates across multiple columns (e.g. the unique “City, State” combinations), or (in this case) I just want a unique list of departments. Click OK.

A summary will be displayed showing you how many records were removed and how many unique values remain.

Now that I have a complete listing of departments, I can use my COUNTIF function to show how many employees and contractors are in each department.

Remove duplicates only deletes records within the highlighted data. Here, I have a list of all employee titles next to the department and count info we just created. If I highlight just the ‘Title’ data and click “Remove Duplicates”, the department and count information is left unchanged.

Now I have a unique list of titles as well.

 

Did you know … you can be notified when changed are made to a SharePoint Online Wiki?

My group has started using a SharePoint Online Wiki – it’s a quick way to have an aesthetically pleasing knowledge base, but I’d rather not have to check “Updated Pages” periodically to see if there’s new content. Configuring SharePoint to notify me when new pages are added (or existing pages updated) avoids needing to check for new content.

From the Wiki, click “Page”

On the “Page” ribbon bar, select “View All Pages”

Click “Library” in the ribbon bar, then click the “Alert Me” drop-down.

Select “Set an alert on this library”

Configure your alert – you can receive notifications when new pages changed or added or only when new pages are added.

You can filter out changes you make by selecting “Someone else changes a wiki page”, and you can receive digest updates instead of getting a notification for each individual change. Click “OK” to save the notification, and you’ll start getting e-mails when the Wiki is updated.

If you want to adjust or cease receiving the alerts, select “Manage My Alerts” instead of ‘Set alert on this library’

Click on the alert name to edit the alert (or check the box in front of the alert name and select “Delete Selected Alerts” to remove it).