Category Archives: Technology

InfoPath- Formula to find the text between two characters

The following formula finds the string value between the characters “=” and “%” in a column called ModifierPercent.

substring-before(substring-after(ModifierPercent, “=”), “%”)

To convert the string to a number and make it a percentage so you can use it in calculations, use this formula

(number(substring-before(substring-after(ModifierPercent, “=”), “%”))) * .01

Set InfoPath Date Picker Field Using String Data Type

Symptom: Attempting to set the value of a DateTime field in InfoPath by using data concatenated from string data results in an error “Only Date or Date  and Time allowed”, although the date appears to be formatted correctly, and typing anywhere in the field causes the data to be accepted as a date.

Cause: The underlying DateTime field stores the data in YYYY-MM-DD format. Typing in the field causes the DateTime field to attempt to reformat the text entered into any acceptable format, such as 01/22/2016 or 14-03-2016. Setting the field’s value via InfoPath rule does not trigger this reformatting check.

Solution: Concatenate the date and time string in a format acceptable to the DateTime Field.


Create fields Month, Day, and Year as type Choice. List 01, 02, 03 or 2015, 2016, 2017, etc. as the choices.

Create a field DateAsDateTime as type DateTime.

Set an action rule on each of the fields (Month, Day, and Time) to set  the DateAsDateTime value* using the following formula

concat(Year, “-“, Month, “-“, Day, “T00:00:00Z”)

*be sure to validate that the Month Day and Year are populated before the action rule runs.

This will add the date with time of 12 AM. To change the time, convert the time to military time and replace the 00:00:00 in the concat formula.

Thanks to this blog for tips on the proper format.


SPD Workflow Email Failing with Invalid Email Address Error

Symptoms: A SharePoint Designer workflow that sends an email to a recipient fails with an error stating that the email address is not valid, although the name appears to resolve correctly within the People Picker field in an InfoPath form.

Cause: The user selected has more than one account with the same display name. In our case, it was a user with a normal AD account (with an email address) and an elevated system admin account (without an email address). Both accounts had the same display name.

Solution: The display name for the admin account was changed. After the next profile sync, the user was able to receive emails sent by the workflow.

Site Owner of Sub site Cannot Add Web Parts

Symptoms: A user with design or full control permission attempts to add a web part to a page, but finds that there are no web parts available for her to select. The  dialog box provides only App Parts in the selection list.

Problem: The user does not have read permission to the top level site of the site collection. Read permission to the site collection is required because there is only one Web Part Gallery per site collection, and it is housed on the parent site.

Solution: Grant at least Read permission for the site collection to the user with the issue.

SharePoint Slow Performance or Size Warning on Excel Files

Symptoms: Users report slow upload or download of Excel Files, or report that SharePoint refuses to upload an Excel file, prompting the user to save the file locally instead.

Cause: Formatting or adding formulas to whole rows or columns causes Excel to recalculate or reformat thousands of empty cells on each save and makes the size of the Excel file grow exponentially. While technically not a SharePoint issue, users first notice the disproportionate file growth when the workbook size reaches a point that causes performance issues when communicating with the SharePoint server or when the default SharePoint upload limit of 50MB is exceeded.

Solution: Clear unneeded formatting and calculations from the workbook’s empty rows, as described in this post Users may also wish to save the file as an ODR file, which will remove the blank cells. The user can then save the file back as an xlsx with the same file name. In one instance, the size of the workbook was reduced from 52MB to 235K.

SharePoint 2010 Export to Excel 2010 Fails to Export All Fields in Some Views

Symptoms: Users report that on some views in a SharePoint 2010 document library or list, the export process results in an Excel table with the first column missing. Additionally, the first column in the table is Content Type, regardless of the view’s original column order. No error is reported by the application. Users of Excel 2007 are able to export the list without missing data and without errors.

Cause: Bug. Possibly because Excel 2010 is not able to sucessfully convert the object data in the Content Type field to text.

Resolution: Remove the Content Type field from the columns to be displayed in the view. (Switch to the view>Modify View> uncheck the box next to the field “Content Type”>OK).  Note that you will still be able to filter, but not sort, the results by Content Type, even if the Content Type field is not displayed.

SharePoint “Open with Explorer” Button Intermittently Disabled

Symptoms: Users find that in some instances of Internet Explorer, they are able to use the “Open with Explorer” button on the SharePoint List or Library tab.  In other instances, the button is disabled. Closing and reopening the library sometimes corrects the problem.


Cause: The “Open with Explorer” button is not available in the 64-bit version of IE. When the problem is intermittent, it usually means that the client has both 64-bit and 32-bit versions of IE available on the same machine, perhaps with the shortcut to one on the task bar and a shortcut to the other on the start menu or desktop. The button availability changes based on which shortcut the user chooses.

Solution: Delete any shortcuts to the 64-bit version of IE. Replace them with shortcuts to the 32-bit version of IE.  Note that with system generated shortcuts, the 64 bit version will be titled, “Internet Explorer (64-bit). If needed, search for “Internet Explorer” from the start menu to find both versions in the search results, right click the result which does not say “(64-bit)”, and choose “Pin to task bar” or “Pin to Start Menu”.

Error on Word Check In to SharePoint: Web Server Copy is Open

Problem: When a user is editing a checked-out version of a Word document and leaves the cursor in an edited comment field or in the header/ footer editing area, the check-in fails with the following error: “This document cannot be checked in while the Web server copy is open. Close the Web server copy and try again.” This occurs even though no other user has the document open.

Cause: Bug

Resolution: Have the user place his/her cursor in the main body of the document. Save and check in normally.
Thanks to the following thread for this solution.

Quick and Dirty: Make PowerPoint Slides Default to Print on Standard Paper


When printing slides in PowerPoint, users have the option to “Scale to fit paper” in order to make the slides fit on standard paper.  This option is per print setup, however, and the setting is not retained from one user to the next.  Users often are not aware of this option, or forget to choose the option at print time, causing paper waste.


By default, PowerPoint slides are laid out with a size that optimizes display on the standard on-screen show ratio of 4:3. This ratio does not correspond exactly to standard paper sizes. To correct the layout to the standard 8.5 x 11 inch (or other) paper size, choose Design>Page Setup  and either change the drop down option from On-screen Show (4:3) to Letter Paper (8.5×11) or manually set the Width and Height. Save, and the settings will be saved with the presentation, so the next user will have their print job default to standard paper size without having to adjust the settings or select “Scale to fit paper”.

Clicking on a Link to a Document in a Document Library does not Prompt for Check-Out

As our organization slowly upgrades from one Office version to another, some users found that behavior in SharePoint changes. On document libraries for which Check-Out is required, some users find that clicking on a link to a document opens the document in the client application as expected, but they are not prompted to choose Read Only or Check-Out and Edit. Moreover, the document that opens is a copy of the original, so when the user attempts to save, the user is prompted to save the document locally. Meanwhile, the original document in SharePoint may have been edited by another user, since the check-out process of locking the document for editing did not occur.  

This problem appears to be caused by either incorrect security settings for the IE Zone in which SharePoint is placed or a faulty (missing or corrupt) copy of the Windows SharePoint Services Support file which guides SharePoint’s behavior regarding interaction with Office files.

The following steps resolved the issue.

  • Verify that the URL for SharePoint is in the Intranet or Trusted Sites zone in IE. Do not place the site URL in both places. (While IE guards against having a single URL in both places, it may be possible if one URL uses a wildcard: and https://*
  • Verify that the Windows SharePoint Services Support file is installed and set to run from the client machine. Control Panel> Programs and Features> Select (but don’t double-click…this will start the uninstall process) Microsoft Office [Version]>Use the Change option at the top of the list and Add or Remove Features> Continue to check what features are installed.  If the Windows SharePoint Services Support file is not set to run from computer, change it to do so and complete the Add process.
  • If the file is present, it may be corrupt or may be a file version from a prior Office version.  Run a repair of Office.  Control Panel> Programs and Features> Select (but don’t double-click…this will start the uninstall process) Microsoft Office [Version]>Use the Repair option at the top of the list. The repair operation requires a restart.

This issue is similar to the “A Windows SharePoint Services-compatible application could not be found” error (addressed here), but these later version of Office, IE, and the OS resulted in a change in opening behavior rather than an actual error message.  Problems with the Windows SharePoint Services Support file are especially common in a mixed Office version environment, such as when a client is running Office 2007, but has later versions of individual Office programs like Visio 2010, Access 2010, or SharePoint Designer 2010 also installed on the same machine.

Thank you to the following forum and blog discussions used in troubleshooting: