Bre Huston

User Profile Service in O365

Bre Huston — Thu, 12 Feb 2015 21:39:00 GMT

Hello everyone!

Here's a trick I use in O365 and SharePoint 2013 to get around some rather nasty Web Service query errors in the browser. I'm sure some of you have tried to use this service, and experienced the error.

Many times data connections are set up within your for to query on load. This is not best practice for 2 reasons.

1. Querying data before you need it can create poor form performance
2. In certain cases, such as the user profile service, your query will fail if set to automatically query on open.

So then, how do you get around this behavior if you want to use the service?

ANSWER: Delay the querying of information from the service by unchecking the option to automatically query.

In this instance, It becomes very important to set the query value of the data connection. On the form Load rules in the designer, set the query accountName value to the userName() function, and hen add a rule to query. Publish your form to your site and watch the magic happen!

I have attached the sample for some experimenting. Simply change the user profile service url to your own site and publish.

If using 2013 there's one extra step. You'll need to change the AccountName query value to not include the claims identifier.

Use this formula to set the username without the claims identifier:

substring-after(userName(), "i:0#.w|")

Adding email Recipients to forms Part 3 - Advanced Techniques

Bre Huston — Sun, 03 Nov 2013 20:48:00 GMT

These email recipient techniques go even further to provide more in-depth solutions for your forms.

qRules GetUserProfileByName command – Retrieves user information from a SharePoint’s GetUserProfileByName web service.

Pros: It works both on Office 365 and browser.
Cons: It requires qRules and site collection admin for browser forms.

On the image above, qRules uses the GetUserProfileByName Command to query data corresponding to the email recipient selected.

Clicking the Get User Properties button triggers qRules Command, and then populates the GetUserProfileByName Secondary data source with the use of the formula shown below.

Qdabra Active Directory Web Service (ADWS) – It uses Query Active Directory to get information.

Pros: It gets email and other properties. It can also search for users via substrings, enumerate all users, get user groups, get manager info and synchronized with AD. No SharePoint needed.
Cons: ADWS web service required (doesn’t work with O365)

This method has a People Picker-like ability but instead uses an Active Directory. The image below shows a search using a substring, and it displays all possible user accounts containing the substring value via Qdabra’s Web Service FindUsersByName.

At the data connection of the web service, you can set the Search Type to value “Contains” so that querying allows you to retrieve the entire data that matches the value or criteria you are searching for.

The image below shows all users having the name “Patrick”.

In this form, the recipients are added to a repeating table (Approver’s List) using qRules’ Insert command - this allows you to insert values onto a repeating table when you click the Add to Approvers button .

This is how the data is displayed:

Bonus:

Copying Multiple Emails

One potential problem is when you need to send email to multiple people. You can merge the values when promoting to a SharePoint column. But, how do you merge in the form.

A solution for that is the Double Eval Technique (People Picker sample)

Pros: It works
Cons: It’s tricky

The image above shows that three recipients have been selected, however if you use the Email field, the value will still be inside a repeating table rendering the data unusable for sending. In order to have this working, you would need to concatenate these values on a different field, and use this value instead.

Steps:

1. Add a new field which will contain the concatenated value.

2. Click the fx button to add a formula.

3. Add the formula below:

eval(eval(Email[. != ""], 'concat(., "; ")'), "..")

Highlight Email and click the Insert Field or Group button.
Select the Email field from the Main Data source

Additional notes:

If you double click the Email field and check the Filter Data, you will notice that this value will only run on the condition that Email is not blank.

The formula used here is a Double Eval technique which returns and links together all Email values chosen from the People Picker group.

Your form should now have a field containing the concatenated e-mail values which you can use for bulk sending.

This technique can also be directly used with the AccountId from the People Picker group which works perfectly with SharePoint Designer Email workflows.

Another solution would be the Table Trigger Technique (Active Directory sample)

Pros: it’s easier to implement than Double Eval
Cons: it requires default values (blank rows)

Going back to the Active Directory Web Service sample, after inserting the list of recipients or the Approvers, you would need to concatenate these by performing a concat formula which combines all the values inserted onto the repeating table.

You can also integrate this technique with other approaches provided that the data is inside a repeating table.

Once you have the e-mail addresses set, you can now perform this method:

Steps:

Add a triggerCopy field to the recipient group.
At the home tab, click Manage Rules.
Add an action rule which Sets a fields Value.
For the Field, select EmailTo (this is the field which hold all the recipient’s addresses).
For the Value, click the fx button and add the formula:

concat(EmailTo, Email, "; ")

Click the Insert Field or Group button and associate the fields accordingly.

7. Click OK twice to exit the rule details.

Your rule should somehow look like this:

8. Add a button which will create the Email List.

9. Add an action rule which sets the EmailTo field to a blank value.

10. Add another rule which Sets a fields Value.

11. For the Field, select the triggerCopy field.

12. For the Value, click the fx button and add the formula:

triggerCopy = "false"

13. Click the Insert Field or Group button to associate the triggerCopy field to the formula.

This is what your rule should look like:

Test your form!

Adding Email Recipients to forms Part 2 - Intermediate Techniques

Bre Huston — Sun, 03 Nov 2013 20:21:00 GMT

As the second part of the adding email recipients post, these techniques for adding email fields to your forms take a step up from the beginning techniques in part 1.

People Picker – The users select Global Address List (GAL). This special InfoPath control was first added in InfoPath 2007.

Pros: It integrates with workflows. It’s also easy to add and flexible.
Cons: It won’t give you e-mail addresses out of the box but you can use it with GetUserProfile to extract the email addresses. It can’t control, extend nor can use rules.

The image above shows the control’s properties configured to query information from a SharePoint site. In preview mode, you can already use this control and search for users or email recipients.

However, this control does not have the recipient’s email address outright for display; rather it would give you the Display Name, Account ID, and the Account Type.

A workaround is to query the information using the SharePoint User Profile Service.

SharePoint User Profile Service – The form queries a web service. It retrieves the user profile information from SharePoint’s GetUserProfile web service.

Pros: It gets email and integrates with the People Picker.
Cons: It doesn’t work with Office 365. It can’t search for user. It doesn’t synch with AD quickly. It can’t enumerate users and it can’t get all groups for users.

This is one of the commonly used techniques for sending email, but a lot of users encounter difficulty in retrieving the actual email addresses. In order to obtain the actual address, you will need to query the GetUserProfile web service using a data connection and set the email value once the account has been populated with the selection.

If you already have the People Picker all set, the next step you need to perform is to add the GetUserProfile connection. First add a data connection which receives data from a SOAP Web service and enter the User Profile Service URL using the pattern below:

http://<myserver>/_vti_bin/UserProfileService.asmx?WSDL

Select GetUserProfileByName and click next until you reach the last page of the wizard.

Remember to un-tick the check box since you will only query using this connection if the People Picker has been populated.

The image below shows that there are two groups, one is for the People Picker Group, and the other for the email Look up.

To proceed with this, create two fields. One field for the AccountId look up field, and another email field which contain the addresses.

At the AccountId look up field, set its default value to the value of the AccountId from the People Picker group.

Then create a rule which sets the email field to blank whenever the AccountId look up field changes.

Add another rule which will perform three actions:

1. Set a field’s value

Set the AccountName field of the GetUserProfileByName Secondary Data connection to the value of the AccountId look up field.

2. Query for data

Select the GetUserProfileByName data connection.

3. Set a field’s value

For the Field, select Email.
Click the fx button and select the Value field from the GetUserProfileByName secondary data connection.
Filter the data with the condition that the Name is equal to “WorkEmail” (use Type text to enter this value).

Note: WorkEmail may not always be the value of the email, to check or this value, drag the repeating table of the secondary data source, preview the form, and enter a value for the People Picker.

Scroll down and look for the instance which displays an email.

Here is another blog which thoroughly discusses InfoPath’s Get user information without writing code.

Adding Email Recipients to forms Part 1 - Beginning Techniques

Bre Huston — Sun, 03 Nov 2013 20:01:00 GMT

What can you do by involving email addresses in your InfoPath forms?

You can use them to send e-mails from your form. Emails are also commonly used in workflows. Having this feature in your form will save you the time and effort, especially when you need to notify someone that a form has been submitted, approved, or rejected.

Another important benefit of email forms is that they allow any user to download the form sent via email, open it, fill it out, and submit it back as an email form.

Two scenarios:

Single email – sequential notification/workflow
Multiple emails – broadcast/parallel workflow

Various Ways of Adding Email Recipients

There are various ways to add an email recipient to an InfoPath form. We will tackle these and give a few simple methods of how to do so.

Manual Entry – The users type in the emails. One or more text fields hold email values.

Pros: It’s easy, flexible and use patterns to validate an email entry.
Cons: It can’t validate that the email actually exists.

This technique only requires the user to enter a valid email.

Add an email pattern validation rule which should have the ability to match an email pattern, and set a condition for the email address field to match an Email Custom Pattern as shown in the image below.

You will see several preset pattern selections and you will need to use the email pattern in this case. Although this technique verifies that the email entered has the format of an email address, it does not validate if the recipient’s email is correct.

Static Selection in Form – The users select from a list in the form. A dropdown or checkbox inserts the address into one or more text fields in your form.

Pros: This technique is easy and it has valid email addresses.

Cons: It uses a fixed list. Updating this list requires republishing the form.

This technique uses hard coded entries on the form. To add these entries, right-click dropdown field and configure the properties to Enter the choices manually, and Add the email recipients.

Since the list of recipients is hard coded and entered manually by the form creator, it is certain that the email addresses listed are valid. However, changes or updates will require you to re-publish the form. You may also use this technique for check boxes or optional/radio buttons.

Dynamic List Query – The form loads a list of values queried from an XML file from a SharePoint site. The drop down list, which displays the recipients, is bound to an external data source which inserts email addresses into one or more text fields.

Pros: It’s easy, the addresses are valid, and you can update the list without republishing the form.
Cons: It uses a fixed list and requires list maintenance.

This approach involves looking up values from an XML file in your SharePoint site. Unlike the first technique, which uses values stored in the form, this method allows you to add, edit, and remove recipients from the list since it uses a secondary data source from SharePoint. To proceed with this method, you would first need to have a library with repeating data containing the list of email recipients.

If you view this form in design mode, you will notice that it is just a simple form with a repeating table used for storing data. Once you have published this form, you may proceed adding more content.

To use this list, you will need to get the XML link by following the steps below:

Click on the library or list from the Navigational Links
Copy the URL of the list or library - http://<MyServer>/<MySite>/
Note: Only copy the server and site name.
Specify the XML Form's name
Add .xml at the end
Your link should look like this: http://<MyServer>/<MySite>/<FormName>.xml

Use this list and add it as a data connection to an XML file.

Add a Data Connection which recieves data from the SharePoint Library or List which contains the email list. Create a Data Connection which Receives Data from an XML Document in SharePoint. Specify the XML data using the XML link created earlier and click Next.

Select Access the data from the specified location. Click Next.

Specify a name for the connection

Optional: If you decide to uncheck the Automatically retrieve data option, you may instead use a Form Load rule located at the Data tab to Query the Dynamic List when form is opened.

Now that the Data Connection has been established, you can proceed with configuring the drop-down list.

This is quite similar to the first technique; however, instead of encoding the recipients onto the form, you will query the list from the secondary data source from SharePoint.

Right click the drop-down list
Click properties
Select Get the list from an external data source
Select the Repeating Group for the Entries
Configure the Value and Display Name as preferred

Though this technique may seem to be a bit intricate, it allows you to quickly update the list of email recipients without having to re-publish the form (since it is not hard coded) at any given time.

InfoPath Dashboards - Displaying Data in Real-Time

Bre Huston — Sat, 02 Nov 2013 04:22:00 GMT

InfoPath Dashboards

Dashboards provide an at-a-glance view of KPIs; it is a real-time user interface, showing a presentation of the current status of a particular objective or a business process like sales, marketing, productions, and other essential items. The data presented normally consists of repeating tables.

Aside from that, there are interesting InfoPath techniques which can be used for dashboards, like InfoPath’s ability as a web page editor, creating overlays, and other features. The processes are simple and ideal for easy deployment.

To display the report, you will first need to have data submitted to a SharePoint site.

In a previous webinar without using qRules, we have discussed mapping a library to a SharePoint list using CAML.

If you already have a library or list containing a number of data, you can already use these values and display them in a dashboard.

The image above displays a parent and the child table set up. The header is inside the child or the repeating section from the secondary data source, and we’ve just dragged the fields inside the repeating group onto the repeating section.

How do we pull repeating data from SharePoint?

First, you would need to have data connections which retrieve data from SharePoint. The example above uses a regular SharePoint connection, one connection from a Library, which is the Parent table, and the other table is for the child which is from a SharePoint list.

The header section or the Parent table resides inside the other section which is the repeating table or the child table.

Parent Table– SharePoint Library

Child Table– SharePoint List

To present this data, you must first create a Repeating Section for the child table. In this case, the Expense Items repeating table.

Create a Section inside the Repeating Section using the same repeating group from the Child Table – ExpenseItems.

Now that you have the repeating sections arranged, distribute the desired fields of the Child Table -ExportItems into the Repeating Section.

Note that using a Custom Table located at the Insert tab helps you keep your design neat and tidy.

Your form should now look similar to this design:

Perform the same procedure with the Parent Table fields.

To keep the borders hidden, you may opt to change the control and use a Calculated Value instead of a Text Box.

Once you’re done, label the fields accordingly.

In this diagram, you will notice that the labels for the Child Table are nesting within the Parent Table –Header section. Using a formatting rule will prevent the labels from re-occurring on each instance of the repeating data.

If you preview your form at this stage, you will notice that the header appears for every child table instance. You will need to add a formatting rule which hides the Parent Table for every repeating Parent ID.

Hide the section if it meets the The Expression:

preceding-sibling::d:SharePointListItem_RW[d:ParentId = current()/d:ParentId]

Preview your form! If there is no displayed data, check both data connections and tick the check box where it says Automatically retrieve data when form is opened.

This approach does not require any complex coding, it’s out of the box and easy to deploy. However, there are scenarios wherein the need for a code is necessary. Like this complex form sample below, you will notice that it similarly has a parent-child table relationship, but if you look more closely, you will find that the form contains several layers of nesting.

Aside from the layered nesting, the form also has several data connections. One of which is an OData Service (Query Items connection) which performs a complex query for multiple lists, and two other connections querying the Products and Products Groups tables from the SharePoint Lists. It has the ability to query the current user (Get Current User connection). A Form Logic which carries out several tasks, and lastly, it is qRules injected.

Search Function

What’s interesting about the complex form is that it has the ability to perform a search function which is very useful if you have a lot of data. It makes the dashboard a lot more readable and user friendly.

The technique is actually quite simple. What this function does is that it filters out the querieFields of the secondary data source before querying the data – check this webinar for more information on filtering queries.

Hyperlinks

Another InfoPath feature you can make use of is a hyperlink:

The hyperlink can be a variable field like the Item ID below which is a unique ID, and also being used as the Display Name for each entry.

Assigning the Link Address is a bit tricky. Using a formula similar to this pattern below will direct you to the xml file.

Concat(“http://<MyServer>/<SitePath>/_layouts/FormServer.aspx?XmlLocation=/<SitePath>/<LibraryName>/”, FormName, “.xml&DefaultItemOpen=1”)

Substitute MyServer, SitePath, and LibraryName with actual link values of the SharePoint Library. The FormName uses the SharePoint list’s Title, which is the File Name when submitting to the Library via Expense Report form.

Overlays

You can also use InfoPath to create overlays. An overlay works like a magical illusion which makes a pop-up appear like they’re floating from your page whenever a part of your website is clicked.

InfoPath as a Web Page Editor

Another great thing is that InfoPath can be used as a Web page editor. To use InfoPath as a web page editor, you need to add the Info Path web part to your page where you can directly modify the content, appearance and behavior of a SharePoint site page by using a browser.

To create a web page, navigate to the SharePoint Library, go to Site Actions and click on Edit Page.

Click on the tiny inverted triangle. This allows us to edit the page. After clicking the triangle, click on Edit Web Part. At the right side, the editing section will appear.

Here you can hide the library at the Advanced section. Tick the check box to hide the library.

To create one, click Add a web part > Forms > InfoPath Form Web Part > Add. Then select a form. In this case, you can select your dashboard.

All the editing you do here will appear on the web page as a web part.

Performance-related publishing issues

When you publish a form from SharePoint, InfoPath sends a whole lot of request to SharePoint. This is where you can use Fiddler. Fiddler is a great tool to use for troubleshooting publishing issues. It allows you to see and inspect what’s going on behind the scenes between your programs and the outside world such as the web.

How to Watch the Process in Fiddler

It is also a free tool which you can download and install.

Start Fiddler.

For first time users, enable the Server_ThinkTime column in the left pane.

Perform publishing process.

Wait for publishing process to either finish, or for the requests to stop for a while.

Use Ctrl+F to search for the text BrowserEnable.

Observe the web request that is located, particularly looking at the XML tab for the response.

Browser Enable User Form Template

When publishing a form to the Sandbox, SharePoint will only allow that to run by only 30 seconds. That’s by default. It is advisable to keep that time as low as possible.

Safe: <2 seconds

Caution: ~60 seconds

Danger: >4 minutes

What are some things that cause timeout issues in publishing?

1. Forms with Large views

A very large form may take up to 10 minutes to publish. To solve this, we should split the page into smaller views so it could publish in a smaller amount of time. Another way to see how large a file is, is to check the size of the file when you export the source file – for InfoPath 2010, go to File > Publish >Export Source Files.

2. Heavily-Nested Sections

Nested Sections are convenient for hiding and showing data, depending on what you need to see or show. However, forms with a lot of nesting become too complex thus will take a longer time to publish. Even Path Designer would be having trouble with heavily-nested forms. It would take at least 10 seconds to switch from one view to another. So we should avoid nesting sections too deep.

Improving Publishing Performance

Avoid huge views.

Split large views up into smaller views, or even a single view combining toggled sub-sections.

Try to keep the views within 3-4 screen lengths.

Avoid heavily-nested section controls.

Use sections where they are needed for show/hide, etc., but don’t overuse them.

Consider splitting your view up into smaller views.

Avoid nesting sections more than 6 levels deep.

Avoid large forms.

Consider having a separate form templates for separate data entries.

Watch the publishing process to see what it’s doing.

http://fiddler2.com/

The 30-Second Sandbox Timeout

Sandbox allows people to publish code to SharePoint without going through an administrator. In order to prevent these codes from performing poorly, you can set limits, and by default, Sandbox allows it to only run for 30 seconds.

The timeout limit can be changed by running a script on the SharePoint server:

Log into the SharePoint server as an administrator.

Modify IncreaseTimouts.ps1 script to set desired timeout lengths.

WorkerProcessExecutionTimeout is the Sandbox timeout.

$cpu.AbsoluteLimit should be at least as long as this

Run IncreaseTimeouts.ps1 in SharePoint Management Shell

Restart SharePoint User Code Host (Sandbox) service

People Pickers inside sections bound to secondary data sources will cause forms to show errors in the browser. Even if the section directly containing the People Picker is bound to the main data source, this issue will occur, so ensure that none of the sections around your people pickers are bound to secondary data sources.

On a side note, if you are working with tens of thousands of rows of data, the most scalable solution is to use a database. And you can use a BCS or Web Service to pull data. But if you don’t want to create your own webservice, you can use Querydb, and you can download our free Web service trial for that.