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.

Add New Item link not displaying on List View Web Part Added to Page

Symptom: A List View Web Part does not show the +New Item link although the Toolbar Type has been changed to a type other than No Toolbar.

Cause:  Unknown. User reports the displayed view was edited using the Edit Current View option rather than starting from a Named View in the list.

Workaround:  Create a Named View, delete the existing list view web part, and re-add the list view web part. Change the Current View option to use the Named View.

  1. From the List, create a view with the required fields, sorted and filtered as you wish it to appear in the List View Web Part.
  2. Name and Save the view. Ensure that the New Item link is visible.
  3. Delete the existing List View web part from the Page.
  4. Re-add the List view web part to the Page.

SharePoint Import from Spreadsheet Option Data Type Problem

SharePoint offers an “Import from Spreadsheet” option to create a custom list. The user names the list, identifies the range of cells required, and SharePoint imports the data, using the top row entries as the column names.

While most of the time this process is seamless, an issue may arrive when SharePoint chooses the data type for each of the columns. Any column containing only numbers will be given a data type of number, which may not be the desired result, particularly when number formatting is applied. For example, a column containing invoice numbers should be formatted as text:




When formatted as numbers, the leading zeroes may be stripped, and commas or periods added to differentiate the thousands places:




To prevent this incorrect data type assignment, temporarily add an alpha character to the first entry in any column where the data contains only numbers, but where the desired data type is Text.




SharePoint will conclude that any column containing even one alpha character should be imported as type Text. You may then remove the temporary alpha character to restore the first record to its original data.

SharePoint Designer Workflow Intermittently Fails to Update an Item in Another List

SharePoint Designer Workflow Intermittently Fails to Update an Item in Another List

Symptom: Workflows periodically fail with the following error message in the Workflow History Log:

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

Possible Data Causes

The data used in the update is invalid, such as putting text in a number field or not providing a value for a required field.

The data used in the update does not provide a unique value in a field that requires unique values.

The data string used in the update is too long, such as putting a string that is more than 255 characters in a field with a data type of “single line of text”

Workaround: Compare the data type and validation rules between the two lists to find the mismatch. You might also add one or more Log to History steps to capture the value of certain fields prior to the Update step. Correct the validation rules in the list from which the data is being transferred to match the validation rules in the list receiving the update.

Possible Permissions Cause

The workflow initiator does not have the level of permissions required to perform the list update. In this case, you may see that some initiators always have their workflows complete, while other initiators always have their workflows fail.

Workaround: In a 2010 Workflow, use an impersonation step to have the update complete using the permissions of the workflow author. In a 2013 Workflow, use an App Step to allow the update to be performed with App level permissions, which allows read and write access to all items on the site.

Possible Performance Cause

On updates that require complex data manipulation or lookups, or updates that require a secondary workflow to complete before the next step in the current workflow, the workflow may error out if the dependent actions have not yet completed when the workflow attempts the Update action. This may also be a problem with high numbers of simultaneous workflow initiations or in environments with known bandwidth issues.

Workaround: Add a step to pause for X number of seconds to allow the dependent actions to complete before attempting the Update step.

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.

People Picker in SharePoint 2010 Throws “Unexpected Error” on Some Client Machines

Symptoms: Some users report that attempting to use the People Picker in a SharePoint List Item results in an “Unexpected Error” message.  The affected users have either recently upgraded to IE9 or have new client hardware. Other users are able to use the People Picker without error.

Cause: Security settings in IE9 prevent the tool from fetching the required user information.

Solution: The SharePoint site should be added to a trusted or intranet zone in the browser settings, as described in this post on avoiding multiple authentication prompts in SharePoint.