Category : InfoPath

InfoPath – Using the filter option

Querying and retrieving data within InfoPath can be confusing mainly because the ‘filter…’ option for data retrieval is sometimes used but not always. This makes it seems like two queries are being used and the beginner will not understand which one takes precedence or when the ‘filter’ option should be used.

The ‘Query using a data connection’ action is the query that populates the data connection, while the ‘filter…’ button used when assigning values to a field assigns a value to a field from a Data Connection that has already been populated. Only the rows returned by the query are available to controls within your form. Rows that have been excluded from the Data Connection (through the setting of a query field) are not available even though they are in the SharePoint list

For example, if in a SharePoint list we have the following table:

Row ID Key Department First Name Last Name
1 P001 IT John Smith
2 P006 IT Lynette Lee
3 Q001 IT Mark Charter
4 Q002 IT Kevin Jenzen
5 Q003 HR Joe Bloggs
6 Q004 HR Sue-Anne Harding
7 Q005 Sales Mike Smith

 

… and you set the data connection query field ‘Department’ to ‘IT’ and query the table, the system will make the following records available to any control that uses this list

Row ID Key Department First Name Last Name
1 P001 IT John Smith
2 P006 IT Lynette Lee
3 Q001 IT Mark Charter
4 Q002 IT Kevin Jenzen

 

The other list items (ID’s 5, 6 and 7) will not appear and are not available to any of the controls that access this list. If you want them to be available, you would need to set the query field of ‘Department’ to blank and redo the query

Now consider that we have 4 rows of data in the Data Connection and we wish to display the first and last names of the approver ID selected from a combo. In this case we would need to filter the 4 rows in the data connection to get the correct names to display

You do NOT need to always filter and a good rule of thumb is that if your Data Connection returns a single record, or you do not care about the value, then no filtering is needed. If multiple rows can be returned by the query and you care about the data displayed, then you need to filter

In short, though:

If the connection will return a single record available, do a straight assign

If the connection returns multiple records, do a filtered assign

Straight assign

In the example below I assign the email address of the user from the ‘User Information List’ to a field called ‘CurrentUserEmail’

107-1Here I set the query field Account (which strangely enough displays as ‘name’ in the Rule Details box) to the function ‘userName()’.

Next, I query the User Information list. Since the query will be based on the name of the current user, only a single row from the User Information list will be returned as there is always only one account with the value in ‘userName()’

107-2

Now, I want to set and display a field that contains the users email. Since I know the ‘user Information List’ will only be populated with a single row, I can do a straight assign

107-3

I use the ‘Insert Field or Group’ button to select the ‘Work Email’ field from the list of available fields in the ‘User Information List (Secondary)’. Since I know that there is only a single record in memory, I do not need to use the ‘Filter Data …’ option. You would click OK all the way through to save your changes

107-4

Filtered assign

In the next example, I am working with a form that allows the user to select a Location from a list of locations. I then want to default the Entity value on the form to the default Entity value for that location

Add an action rule to perform the assign statement

107-5

Click on the function button to open up the ‘Insert Formula’ dialog box and then click on the ‘Insert Field or Group …’ button

106-18Once you click the ‘Insert Field or Group …’ button, a ‘Field’ selection box will be displayed, initially set to ‘Main’. I use the drop-down arrow to select the list I want – in this case ‘Locations (Secondary). This will display fields similar to the following

107-6

I want to get the value of the Entity in my form, however, which location record should I use to get Entity as the system has multiple location records in memory? I had previously selected a location I needed from the list of locations and saved it to the field called ‘CapexLocation’, so now I will use that value to make sure I get the correct entity in my form

107-7

To specify which record I need to use, I select the ‘Filter Data…’ button which opens up a form similar to the following. I select ‘Add…’ from the dialog box

107-8

This opens yet another window where I specify the criteria to use to select the correct Location record as shown below. I click on the drop down box and select ID (I select ID because my ‘CapexLocation’ field on my form contains Location ID but displays the location name. This can cause some confusion to new users of InfoPath)

107-9

I leave the condition as ‘is equal to’ and set the last combo box to ‘Select a field or group …

107-10

… and another dialog box opens up allowing me to select the ‘CapexLocation’ from the ‘Main’ group

107-11

After clicking on ‘OK’ [more than] a few times, we end up with the following in the Formula dialog box

107-12

The system will now return the Entity related to the Capex Location that I had previously selected. You can now click OK once again to finalize your setting. The system will now return the correct entity from the location list based on the location ID in the form


InfoPath – User Information List

You can use the ‘User Information List’ in InfoPath to access details related to the current or other users, such as the users first and last name, email address, title and other useful details.

To get this information do the following:

Add a data connection to the ‘User Information List’

Add a new data connection to Receive Data from a SharePoint library or list106-1

Select the site that that contains the User Information List (iin most cases this is the current site)

106-2

The system will display a panel that details current lists and libraries in the site. Scroll down through the lists and libraries and select ‘User Information List’. Click Next

106-3

Select the fields that you want to use – mostly this will be the ones indicated in the image below. Click Next

106-4

Make sure the ‘Store a copy of the data in the form template’ is unchecked and click Next

106-5

Uncheck the box ‘Automatically retrieve data when form is opened’. We uncheck this box because this particular list can be very large and the system would attempt to load all users into the form even though we only require the user information for the person who is currently opening the form. If checked, your form would take longer to open or may even fail to open

106-6

The system will default the name. My practice is generally to make two data connections to the UIL – the one to contain the current users information and the other to contain information related to other users. In this case, the second UIL would of course have a different name

Create a ‘Form Load’ rule to read the user information list

In the  InfoPath data area, select ‘Form Load’ to open the pane that shows the rules that execute when the form is opened

106-7

Create an action rule with a meaningful name, like ‘Get user details’. This rule should probably be the first rule that executes since you may want to use the information it returns in subsequent rules

106-8

We now require two or possibly three separate actions:

The first action specifies the data we are searching on (the query data)
The second performs the search
The optional third action stores the data in another field on the form

Set the Query fields value

The first action of the rule specifies the data we wish to use to query the UIL (If we did not do this, the query data would be empty and ALL records would be returned). Click on the ‘Add’ button and select “Set a field’s value”. A dialog box similar to the one below will be displayed. Click on the ‘Fields’ box highlighted in Red below

106-9

A screen like the following will appear. In the top drop-down list, select ‘User Information List’ and then explode the fields below the ‘queryField’ item as shown below. The fields displayed are the fields that you can base your query on.  Since the InfoPath userName() function returns the ‘Account’ data, select ‘Account’ to perform the query based on the current user (In this instance, a single query field is being used, but you can use multiple set field values to query based on any number of fields)

106-10

The system completes the name in the Field fill-in as shown below (Do not be alarmed that the system returns ‘Name’ instead of ‘Account’. This seems to be a Microsoft nomenclature problem with the list as the ‘Account’ is the actual field being queried

106-11

Click on the fx button to specify a formula to set the Account query field to. A screen similar to the one below will be displayed. Use the ‘Insert Function…’ key to insert the ‘userName()’ function into the formula. Click on the OK button

106-12

Your ‘Set a field’s value’ dislog box should now look as follows. This is telling the system to set the Name (Account) in the lists query parameters to the current user name

106-13

Perform the query on the User Information List

Now we need to query the data and return the user details to the form.  Select the ‘Query for data’ action from the Add button as shown below

106-14

In the screen that is displayed, select the ‘User Information list’ as your ‘Data connection’ as shown below

106-15

The system will now query the user information list based on the current user name (account) to make the first name, last name and any other user attribute available to the InfoPath form

Set an InfoPath field to the current users name

Now let’s set a field’s value to the name of the user. Once again choose ‘Set a fields value’ from the ‘Add’ button

106-16

 In a similar way to before, click on the ‘Fields’ box highlighted in Red below to enable you to choose a field from the selector

106-9

Select the field whose value you want to set, by selecting ‘Main’ and then choosing the field. In the example below I am setting my CurrentUserName (which I subsequently access through a workflow so that I know who the last person was to update the form)

 106-17

Once again, click on the Function button fx  to open up the formula box and then select ‘Insert Field or Group’ so that we can move the value in the query that was completed previously into this field

106-18

A dialog box similar to that shown before is displayed. In the ‘Fields’ section select the User Information List and explode the tree so that you can see the fields under ‘dataFields’ as shown below (The ‘queryFields’ only contains the data you used for the query, while the dataFields contain all the rows returned by the last query)

106-19

Select the ‘Name’ of the user. A note on names: In many cases, the SharePoint data comes in the not so friendly format of ‘Last Name, First Name’. You can use the ‘concat()’ function in your formula box with the ‘First name’, ‘ ‘ and ‘Last name’ to create a more user friendly version of the name, but beware: External users (Those signing onto SharePoint through other accounts) will have empty ‘First name’ and ‘Last name’ fields

Your ‘Formula’ box should now look like this (you will notice that the name says ‘Title’ instead of ‘Field’, but don’t be concerned as this is that nomenclature thing again)

106-20

Click OK to save your Formula

Notes and tips

  1. Once you perform a Query action, the rows of the list that are returned remain in memory, so you can refer to them directly without having to run another Query action
  2. I suggest that you always use the data connection to return the single row of data based on the current userName(). If you need to select a user from a list of users, I suggest you create another Data Connection to the same ‘User Information List’ (give it a different name of course) and then link your combo-box or repeating table to this new list. Be sure to set your query parameters to only return the rows you want
  3. I believe it is good practice to save the current user name in the form so that it is possible to get the identity of the user who last changed the data, in either the form, workflow, or SharePoint view.
  4. If you require additional user information that is not in the User Information List, you may need to create a User List for those particulars. This would be a standard SharePoint app, keyed on the email address of the user so that it is a simple process to access the user details in InfoPath through a Data Connection to this table

UserName() function

The userName() function returns the identity of the user currently accessing the formExamples of the data format returned:

Internal users: ‘i:0#.f|membership|melsmith@contoso.com

External users: ‘i:0#.f|membership|msmith_gmail.com#ext#@contoso.onmicrosoft.com

In preview:      ‘136909

Note that in preview, this function may return a value in a very different format from the one it returns when not in preview, which means that you will not be able to properly test user related functionality in preview.


InfoPath – Close window automatically

If you are accessing a SharePoint related InfoPath form directly through a URL in a form or an email, you may notice that when you exit from the form, a message ‘The form has been closed.’ is displayed. This is painful especially when you are accessing the form directly from a mobile device and you want the active screen to return to the calling application

105-1

After much searching online (and forgetting where I found this advice), here is a method to avoid this message

  • Create a site page in the SharePoint call CloseWindow.aspx (actually, any name could be used, but this makes it easy to understand the function of the site page)
  • Edit the page and embed the following code. Do not be surprised when the page closes automatically after you have embedded the code

<script language=”javascript” type=”text/javascript”>
    window.open(“”, “_self”);
    window.close();
</script>

105-2

  • In the URL that opens the page, add the following statement as a suffix to the URL that is used to access the form

?source=<SiteUrl>/SitePages/CloseWindow.aspx

  • The complete URL should look similar to the following

https://contoso.sharepoint.com/sites/Test/Lists/Attachments/Direct1.aspx?source= https://contoso.sharepoint.com/sites/Test/SitePages/CloseWindow.aspx

  • The window should now close automatically when you exit the form. Note that this may not work if the page you accessed has additional web parts defined

InfoPath – Temporary fields in InfoPath form

When creating InfoPath forms directly from the SharePoint list toolbar, all fields that you create in the form are added to the SharePoint list. When you only require a field to hold a temporary value for the duration of time that the form is open, adding theis field to the SharePoint list can complicate the list item with unneeded data. Instead of defining these fields in the SharePoint list, you can create a Data Connection to an XML file that defines the temporary fields you wish to use (I picked this tip up from ‘The Tinkerer’s Journal’ – many thanks Peter Northcott). The values in these fields will NOT be saved with the form. This is how this works

  1. Create an XML file

The first step is to create an xml file that contains the values needed. Create a file using Notepad called, for example, ‘temporary.xml’ that has the following contents (you can set these to initial values if you need to by following the previous xml example):

<?xml version=”1.0″ encoding=”utf-8″?>

<TemporaryValues>

 <ItemCost />

 <ItemPrice />

</TemporaryValues>

  1. Add a data connection to this xml file and set ‘Automatically retrieve data when form is opened’ to true
  1. Reference the fields within the xml file as you would any other field by using the ‘Insert Field or Group …’ button in your formulas or using an Action to set the values as needed

Note.

The values of these fields are refreshed everytime the form is opened


InfoPath – Getting multiple values from a field

When dealing with fields that are defined as containing multiple values in SharePoint it may be necessary within the form (and in any workflows that could be defined) to know all the values that were selected. Use the magical ‘eval’ function to provide you with a delimited field that contains the values (if you intend to only use the combined values in a SharePoint workflow, you can use the ‘Merge’ function when promoting the fields instead of following these instructions)

Create a new field that will contain a semi-colon separated list of the values selected

104-1

Set the Default value of the field to a value similar to the one shown below to get a semi-colon delimited field (ensure that the box ‘Refresh value when formula is recalculated is checked)

104-2

If you wish to only bring specific values into the field, you can use the filter option on the field when ‘Insert Field or Group …’ is selected

104-3


InfoPath – Adding special characters to text boxes

To separate comments or data within a text field you can use a line feed and carriage return. You can enter these special characters into the field directly but I have found that a data connection to an xml file that defines these values makes it simpler to use and understand

Create an XML file

The first step is to create an xml file that contains the values needed. Create a file using Notepad called, for example, ‘characters.xml’ that has the following contents (You can add more fields if you need to – just stick to the format):

<?xml version=”1.0″ encoding=”UTF-8″?>

<characters

    cr=”&#xD;”

    lf=”&#xA;”

    crlf=”&#xD;&#xA;”

/>

Add a data connection to the file

Add a data connection to this xml file and set ‘Automatically retrieve data when form is opened’ to true

103-2

Use the fields in the file

Reference the fields within the xml file as you would any other field by using the ‘Insert Field or Group …’ button in your formulas

103-1

The concat() command above will produce the following result:

Hello

World


InfoPath – How to clear out a repeating table

On occasion you may wish to clear out a local table completely every time you visit the form, for example when you want the user to enter new details in a table format and do not want previously entered rows to display. Since there is no [easy] method to delete all the rows in the table in the form load (which is where you would most likely want to perform this action), there is a small workaround that allows you to clear out all rows. Here are the steps you need to follow

Add a text field to your form that is the name of the repeating table that you will later create, say ‘TableXXX’ (The table must not have already been defined)

102-1

In the ‘Form Load’ section, create an action to set this field to blank as shown below

102-2

Delete the field but leave the Action in the Form load

Insert the Repeating Table into your form, configuring the columns as required. Do not concern yourself with changing the group name at this point

102-4

Now, right click of parent group (group 5) of the repeating section, select properties and change the name of the field to the name of the field that you previously deleted. When you have completed this, the field layout should be as shown below

102-5

Since the previous rule of the text box ‘TableXXX’ remains, you have now tricked InfoPath to clear out this table each time the form opens. If you wish to clear out the table on some other action, create the action on the field and set the temporary text value to empty BEFORE you insert the actual table itself


InfoPath – Borders appearing when they should not

When the subsequent row in an InfoPath table has fewer columns than the preceding row, the system may automatically add in a border where none is defined. This can manifest itself when the designer is simulating selection tabs at the top or bottom of the form to allow the user to select a page view. In short, to get around this issue, ensure that both preceding and subsequent rows have the same format and number of columns

For example:

The form is designed similar to the following figure. Notice that the row with the headings (General, Costs, Prices and Other) has 4 columns, while the row below only has 1 column

101-1

When you preview the form it displays correctly, as though the page for ‘General’ is ‘open’

101-2

However, if you open the form in the Browser, it sometimes adds an extra border as shown below under the ‘GENERAL’ text

101-3

To correct this problem ensure that the row following the tab row has exactly the same column layout as shown below (I have found that this problem occurs when you make formatting changes anywhere on the form AFTER you have defined the Tabulated row).

101-4

Correct all your borders and publish the form which will now open with the correct borders as shown below

101-5