Tag : data connection

InfoPath – Hyperlinks with ‘Page not found’ or how to deal with URL’s with spaces

InfoPath will allow you to display links to external files (such as attachments in a list accessed through a Data Connection), but if those URL’s have spaces in them (as many user files do), it could be that when you click on the link you get the ‘Page not found’ message as shown below

110-1

This could be happening because you have used the Hyperlink found in the Control Tools instead of the Hyperlink found in the ‘INSERT’ tab in InfoPath. Pretty weird! Anyway, use this method to make sure your linked content displays correctly

Create a Data Connection link to the table that has the attachments.

Make sure to select the attachment in the list of fields as shown below

110-2

Drag the table onto your form to create a Repeating Table control

110-3

Arrange the columns

Arrange the columns in the format you want and delete any unneeded columns like the ID AND the attachment column (You delete the attachment column as we will add it later and you do not want duplicate links, especially ones that do not function correctly). Insert a column to the Left or Right at the point where you want the Hyperlink. In the image shown below, you will notice that the last column, which I titled ‘Attachment link’ is empty. Your form should look similar to the following

110-4

Create the Hyperlink

Place your cursor in the empty cell in the repeating table, switch to the INSERT tab and select the ‘Hyperlink’ button …110-5

This will open up a dialog box for you to complete as follows

110-6

In the dialog box for the Link to section, select ‘Data source’ and link the Data Source to the attachment as shown above (in most cases the system will automatically default to the attachments

In the display selection you will probably want to select ‘Data source’. You can then use the Field selector to display the attachment URL or some other detail available in the data connection, or you could use a formula similar to the one below to reduce the size of the URL and only show the last section

substring-after(.,”Attachments/”)

This formula will show the portion of the attachment that follows ‘Attachments/’ – in this case the file name associated with the attachment. This is useful since you do not want to display a URL that is a mile-long and that the users will have problems understanding

Publish the form

Publish the form, and when you click on the link, the image should display with no error

7-08-2016 |

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

7-07-2016 |

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.

|