SharePoint Duffbert

Recently, Sandra (my SharePointBuddy) and I built a peer-to-peer recognition site on SharePoint, and it’s really cool. However, we had a couple scenarios where the notification workflow would error out with the following message:

The workflow could not update the item, possibly because one or more columns for the item require a different type of information.

If you’ve run into this issue and done any research, you know that there is no set answer for how to resolve it. Furthermore, many of the answers are “I did this and it started working again, but I don’t know why.” And of course, none of these answers addressed our problem.

After a few days of trying to figure out the “why” behind our particular variation of this, we finally stumbled across the issue.

The recipient of this particular “High Five” had a regular and a test AD account. Unlike most of the test account scenarios, his test account had his regular name with all the associated data fields filled in. When the workflow got to a point where it was trying to build a new list item, we were using the “Display Name” variation of the Recipient People Picker field for the email address. Unfortunately, the Display Name for this person’s account was not unique, so SharePoint Designer was choking on a multi-value data field that was only supposed to be a single-value occurrence.

Once we figured that out, we changed all the usages of that field to use the Login Name (which *was* unique), and then the workflow worked just fine.

I put this out here so that anyone trawling for an answer has at least one more option to choose from…

In short, you make sure there’s a page out there named… Home.

I had a question today related to clicking on a SharePoint wiki library link. The customer wanted the library to open up to a home page, not a view of all the pages in the wiki library. Sounded easy enough, especially when she also sent me a link to a site where someone was doing just that.

Being a dev/tech person, I’m looking for settings for the library to set her Intro page to be the library home page. However, everything I tried would end up setting the Intro page to the SITE home page, not just the wiki library home page.

After considerable searching, I found a passing reference to a behavior that I didn’t know (and I admit I’m not a huge wiki fan). Apparently, if you open a SharePoint wiki library, it will attempt to open a page actually called Home. If it doesn’t find one, it opens the AllPages view of the wiki library.

I tossed a Home page out there, and voila… the library opened to that page. I then asked her to change her Intro page and rename it to Home. Worked like a dream…

Sometimes, we techies over-complicate simple things. 🙂

Another one of those “I never noticed that before” questions today…

One of my customers wanted to get their pictures out of a Picture library, and said that they couldn’t find the Open In Explorer option in the Ribbon Bar. My first guess is that they were having a problem with the Internet Explorer SharePoint add-ons, but no… there’s basically NOTHING in the Ribbon Bar for a Picture library!

We have Sharegate, and I know I could have exported the Picture library to a file share, and it would have been fine. But still… how could I allow the customer to do that themselves?

A little digging around found an answer in the form of a nice little hack (elegant solution to a problem).

Open a Windows File Explorer window, click in the address bar at the top of the screen, and enter the SharePoint site address in the following format:

\\domain\site\

For example: \\sharepoint.foo.com\sites\MyTestTeamsite

That opens up the site in Windows File Explorer, and you can drill down into any library that’s out there… including Picture libraries.

I passed along the information, the customer tried it, it worked, and everyone was happy.

On a recent “Open Office Hours” call we do in our SharePoint group, we had a customer with an Excel/SharePoint issue. They had an Excel spreadsheet they needed to copy/paste into a datasheet view several times a day, and one of the fields was a “numeric text” field. In other words, the field should be treated as a text value, but it was basically a 12 character field with all numbers. The problem is that when they would import that field, it would change the value to something like 1543E+12, regardless of whether you defined the SharePoint column as text or numeric.

I’ve looked into this in the past, and all the material I found online said basically the same thing… importing a number column from Excel will always have the field being treated as numeric in the translation, even if you define the column in Excel as text. Normally I’d suggest that they pad the field with some text character to get the translation to work as text, but the group that gives them the data from Access were unwilling to make any changes. Basically, we had to do something on the SharePoint side to help them.

Fortunately, I found a workaround that took care of the condition in this particular situation. It’s not perfect for all cases, but perhaps you’ll find this useful if your situation is similar.

In the SharePoint list, define the incoming field as a number, and make sure it’s set for zero decimal places:

Next, create a calculated field in the list where the text transformation will occur:

In the calculated field, set the formula to append a non-numeric value onto the original imported field. In this example, I’m just using &”” so that it thinks there’s a text character at the end, which then makes the whole field a text value:

Here’s what the view looks like when I use the original number field (note the comma separators):

And here’s what the new calculated text version of the field looks like:

This worked great for my site. The customer tried it out, and they were ecstatic with how much time and effort it saved them.

The only two drawbacks that I can see are:

• Leading zeroes – It would likely trim off leading zeroes during the import, so converting it to a text field would likely do so without those zeroes. In my case, that wasn’t an issue.
• Editing – If you had a reason to edit that text-based value, you’d have to have them edit the numeric field as you can’t edit the calculated text field. That might confuse some people, but again… not an issue in either case here.

Hopefully this technique will be helpful if you find yourself in the same situation. All the Google searching I saw says there’s no way to “fix” an Excel import to treat it properly as a text value coming in. This approach at least gets you almost all of the way there, especially if you have no control over the spreadsheet data you’re receiving.