Home

Tag : email

Workflow – Link from email to list item

In many emails it is helpful if the user can click on a link to open the document directly. To enable this functionality is a simple process in SharePoint

Adding a link to display the document

Insert a ‘Send an Email’ action in the workflow

Insert the Current items ‘Encoded Absolute URL’ into the body of the email as shown below

213-1

Make the link user friendly

While the link will work, the actual information displayed does not entice the user to click on the link and it may be necessary to include some small HTML coding to make it easier on the eye. Change the Properties of the email by Right clicking on the Action and selecting Properties as shown below

213-2

A screen similar to the following will be displayed.

213-3

Click on the ellipsis (…) to display the detail (mess) of the body of the email (I have highlighted the ‘Encoded Absolute URL’

213-4

Now, change the highlighted section as follows

213-5

<A href=”[%Current Item:Encoded Absolute URL%]”>Click here to see the Document</A>

After you have made the change, click OK and then OK again to save your changes and exit from the ‘Properties’. Then Click on your email action again and it should now display a friendlier invitation as shown below

213-6

(Note the line above the ‘Click here to see the document‘ link. This is useful and can be added using the <HR> tag)

Adding a link to edit the document

To add a link to the email that will open the list item directly in Edit mode, follow the same procedures covered above, but substitute …

‘[%Workflow Context:Current Site URL%]Lists/DOC/EditForm.aspx?ID=[%Current Item:ID%]‘ for ‘[%Current Item:Encoded Absolute URL%]‘

… in the body of the email, where ‘DOC’ represents the name of the list you are referencing. For example, if the name of the list was ‘Item Prices’, the URL would look as follows:

‘[%Workflow Context:Current Site URL%]Lists/Item%20Prices/EditForm.aspx?ID=[%Current Item:ID%]‘

Note that the spaces in the list name need to be substituted by ‘%20’. You may also want to change the hyperlink label to ‘Click here to edit the document’

7-08-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 list 106-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

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

7-07-2016 |

Workflow – Useful REST calls – Attachments in email

From a workflow, you may want to find the attachments that are linked to a list item or even display these attachments within the body of an email. The next steps will guide you through this process. For those among us who are impatient, most of the entire process is shown in the figure below

203-1

Build a dictionary variable

Select the ‘Build a dictionary’ action and click on ‘this’ and use the ‘Add …’ button to define the dictionary names ‘Accept’ and ‘Content Type’, both og which are defined with exactly the same value: ‘application/json;odata=verbose’

202-3

Change the name ‘dictionary’ to specify a new dictionary variable ‘Header’. The end result looks as follows

Define the REST call

Set a variable (String01 in this case) to the HTTP Web Service Call (REST Call) that will get the attachments. This is set to a combination of your site, list and list item as follows

<site>_api/web/lists/getbytitle(‘[%Workflow Context:List Name%]/items([%Current Item:ID%]/AttachmentFiles

203-4

This will return all attachments related to the current item. If you were looping through all items in the list, you could leave out the ([%Current Item:ID%])

Call the web service

Use the REST string previously defined in the ‘String01’ variable to call the web service, setting the ‘RequestHeaders’ in the Properties to the dictionary variable previously defined

203-7

203-6

Data Returned from HTTP Web Service call

When the HTTP Call executes, it will return a packet of data to the ResponseContent variable ‘Response’ that looks similar to the following (Note that is your ‘RequestHeaders’ are set as defined previously, the system will return values in JSON format:

XML (Multiple iterations of ‘value’ removed to save space):

<?xml version=”1.0″ encoding=”UTF-8″ ?>
<odata.metadata>https://contoso.sharepoint.com/sites/dev/_api/$metadata#SP.ApiData.Attachments</odata.metadata>
<value>
 <odata.type>SP.Attachment</odata.type>
 <odata.id>https://contoso.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)</odata.id>
 <odata.editLink>Web/Lists(guid’f2c6ac85-9999-222a -a306-2433922ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)</odata.editLink>
 <FileName>Ferrari 2.jpg</FileName>
 <ServerRelativeUrl>/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg</ServerRelativeUrl>
</value>

JSON:

{
    “d”: {
        “results”: [
{
                “__metadata”:
{
                    “id”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)”,
                    “uri”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari 2.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg”
},
{
                “__metadata”: {
                    “id”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a -a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “uri”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari.jpg”
            },
            {
                “__metadata”: {
                    “id”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “uri”: “https://contosa.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Maserati.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Maserati.jpg”
            }
        ]
    }
}

Note: If you forgot to specify that your ‘ResponseHeaders’ are in JSON format, then the above values will be returned with ‘value’ instead of ‘d/results’ as shown below. This will cause the following commands not to work:

{
    “odata.metadata”: “https://contosa.sharepoint.com/sites/dev/_api/$metadata#SP.ApiData.Attachments”,
    “value”: [
        {
            “odata.type”: “SP.Attachment”,
            “odata.id”: “https:// contoso.sharepoint.com/sites/dev/_api/Web/Lists(guid’f2c6ac85-9999-222a-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)”,
            “odata.editLink”: “Web/Lists(guid’f2c6ac85-9999-222a-a306-2433922ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)”,
            “FileName”: “Ferrari 2.jpg”,
            “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg”

If you look closely at the JSON data above, you will notice that there are multiple iterations of the ‘value’ data element. Since we need to know how many attachments there are, we must move all these iterations into a separate data dictionary to count them. Add a ‘Get’ action to move the ‘’d/results’ elements from variable ‘response’ into the dictionary variable ‘ItemData’

203-9

Count the number of items (Attachments) returned

Once the ‘d/results’ iterations are in the ‘ItemData’ dictionary variable, we count them with the ‘Count Items’ action and place the value in the integer workflow variable ‘CountOfItems’

Initialize the variables for the loop

Initialize the workflow variable (Count) that will indicate which iteration we are working through in the list. While the kneejerk reaction is make this an integer variable, I recommend making it a ‘Number’ variable since it will save a workflow step when this value is incremented later (Don’t concern yourself that it sets the value to 0.0 – it is still 0)

Since we will be creating a ‘special’ HTML string that will contain the images of the attachments (In this case I know the attachments are images), we need to empty this string first. Use the following command to set ‘String03’ to empty

Loop through the attachments (items) returned

Insert a ‘Loop n Times’ action that will loop the number of times indicated by the attachment count you set earlier in the ‘CountOfItems’ variable. This loop will be used to iterate through the ‘values’ returned by the call and build up the data that we wish to include in an email

203-13

Read each attachment

Use a Get statement to read the ServerRelativeURL from the data returned by the HTTP Call for the current iteration indicated by [%Variable:Count%] (the first iteration will be 0, the second 1 and so on). You will notice that I included this statement in a step to make the coding more readable

203-14

It is also possible to get this value from the ItemData dictionary variable defined earlier to get the count from the ‘ItemData’ dictionary instead of the ‘Response’ dictionary using the following action:

Get ([%Variable:Count%])/ServerRelativeURL from Variable:ItemData (Output to Variable:String01)

While this possibly makes it easier to read, I decided to use the structure from the top down to provide more understanding about what is going on

Use a Get statement to read the ‘FileName’ from the data returned by the HTTP Call for the current iteration indicated by [%Variable:Count%]. Follow the same process as you did for the ServerRelativeURL

203-15

‘Fix’ the data by replacing special characters

Replace any special characters (such as spaces and exclamation marks) in the ServerRelativeURL to ensure that if the user clicks on the link it will open up correctly. In my example I replace commas (,), exclamation marks (!) and spaces ( ) with the appropriate encoded characters (%2C, %21 and %20)

Thereafter, log the result (optional) if you wish to view the final URL

203-16

Add the image into the final string that will go into the email

Now that we have the Server Relative URL and the File Name of the attachment, we can construct the body of the email. In my case, I wish to embed the pictures within the email but you could also define a link to the image instead

203-17

As a convenience, I have included the text values here so you can cut and paste if necessary (Note that the [%Variable:String01%] and [%Variable:String03%] will have to be substituted with actual look-ups

[%Variable:String03%]
<tr>
 <td style=”width: 193px”><a href=”[%Variable:String01%] “>
 <img src=”[%Variable:String01%] ” width=”175″/></a></td>
 <td style=”width: 9px”> </td>
 <td style=”width: 600px”>[%Variable:String02%] 
 
</tr>

‘String03’ is made up of String03 and the new image contained in ‘String01’ together with the title of the attachment in ‘String02’

Increment the counter

Once we have finished with the list item, increment the counter and restart the process for the next iteration. You will notice that because the variable ‘Count’ is a number (as opposed to an integer), you can simply increment its value by adding 1. If the count variable was created as an integer, you would have to first increment a number value and then set the integer value to the incremented number value, requiring two steps

203-18

Define the email

The last step is added AFTER the end of the loop and emails the filenames and images to someone. In this case, the system will email the individual that is maintained in the people picker field ‘Current Item:User’. This is a very simple email but should serve to illustrate what to do. Feel free to add any additional details

203-19

Final result

You should be good to go and your code should then send an email similar to the following (The Maserati was too expensive and was trimmed off to save space)

203-20

7-04-2016 |