Category Archives: SharePoint

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.

Example:

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. http://nabeeldotnet.blogspot.com/2011/11/infopath-2010-date-picker-problem-when.html

 


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 http://www.spreadsheetshoppe.com/2/post/2012/01/how-to-reduce-excel-file-size.html. 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.

Open

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”.