Category Archives: InfoPath

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.

InfoPath Form in SharePoint 2010 Error: Root of the Certificate is Not Trusted

An InfoPath 2007 form deployed in 2010 gave repeated errors on opening, including:

“You do not have permission to access a web service.”

“The root of the certificate is not a trusted root authority.”

The InfoPath form in question was an administrator-approved form which called a SharePoint web service called GetUserProfileByName in order to pre-populate several fields with the current user’s data.  Permissions were checked and the data connection (UDCX) files were examined for errors, including incorrect URLs. The form threw no errors in SharePoint 2007.  A search for the correlation ID in the Windows Application Event logs revealed an error regarding the main wildcard SSL certificate for the site. This certificate had a valid date range and was working correctly to authenticate https traffic.

Several blogs (Share-n-dipity Eric Kraus’ SharePoint/.NET Blog ) pointed out that SharePoint 2010 has its own trusted authority store, separate from the Windows certification registry; these blogs were focused on the error when using claims authentication. This forum: InfoPath Form Errors after In-Place Upgrade from MOSS 2007 to SharePoint 2010 was more InfoPath specific. By default, only the “local” trust resides here (Central Admin>Security>Manage Trusts), but additional certificates may be added. Do not delete the “local” connection; it is used internally by SharePoint.

“I trust you, baby. I just don’t trust your friends.”

The error in the event logs meant SharePoint was complaining that the root of the authentication chain was not listed as a trusted certificate-issuing authority in the SharePoint trusts store.  In other words, SharePoint was saying, “This document looks legitimate, but are you sure the person who gave it to you was legit?” Registering the issuing authority’s certificate verifies that the authority is a trusted source. If your SSL cert includes a certificate hierarchy, all the certificates in the hierarchy must be listed in the managed trust store of SharePoint 2010.

Let’s Play “Find the Cert!”

To identify the issuing authority certificate(s) in use, find your SSL cert in the certification management console on the server on which the certificate has been installed. (Run > certmgr.msc) You may have to look under the Personal folder to find the SSL certificate (or if someone else installed the certificate, it may be under their Personal folder when logged in under their profile). In our case, the SSL cert had a 3 tier hierarchy. We noted the certificate names and exported each certificate by right clicking the certificate in the list and exporting it using the default settings to produce a .cer file. We then uploaded them to the Managed Trusts site in Central Admin, giving each a reasonable name and providing no other optional info.

One thing to note: the name displayed in the certificate hierarchy is not the name listed in the certificate list, but rather, the “friendly” version of that name. In our case, they were nothing alike, and we had a difficult time locating one of the certificates because the friendly name did not display in the regular screen display. To see the heirachical name, the user must scroll several fields to the right.

Additionally, the listing of certificates in SharePoint’s Managed Trusts center was required with this InfoPath form because the form was an administratively-deployed form containing code. InfoPath forms which do not contain code and which are published to SharePoint by users will likely not encounter this problem.