Guide: Working with SharePoint Listitem Attachment...

Jay-Encodian · ‎01-31-2019

We've been helping lots of folk recently to build Flow's which extract SharePoint list item attachments and perform some form of processing, typically to convert to a single PDF document and share with a third party, or add to a records repository.

The following tutorial is built around a very simple scenario but should serve as a good guide for:

How to extract ‘X’ number of SharePoint list item attachments and process
How to use Flow variables to iteratively build a JSON array
How to merge 'X' number of documents (any format) into a single PDF document
How to dynamically create an HTML fragment and insert into a PDF document

Scenario:

A SharePoint list is used to store and track orders, containing all relevant order data and document attachments. When an order is completed, a summary sheet of the order should be compiled and added to a PDF document formed from the document attachments associated with the order, finally the completed PDF document should be sent to the customer via email.

Flow Creation Steps:

1 - Setup a SharePoint list to store order information, an example configuration is detailed below:

2 - Create a new Flow from a blank template

3 - Add the ‘When an item is created or modified’ SharePoint trigger and associate to the list created in step #1

4 - We need to create a few variables which we will use to store data used later within the flow. Firstly, to store the HTML summary of the order and secondly, to store an array of the attachments which will be merged into a PDF document.

Create a new variable using the ‘Initialize variable’ action, applying the following configuration:

Note: I recommend re-naming the actions post creation to something meaningful… helps Flow authoring as the Flow becomes more complex.

5 - Create another new variable using the ‘Initialize variable’ action, applying the following configuration:

6 - Add a ‘Condition’ action, applying logic to determine whether the Flow should continue to execute. I’ve set the condition to check whether the ‘Order Status’ value is equal to ‘Paid’ for the current SharePoint item.

7 - Checkpoint: Your new Flow should look similar to the following:

8 - The next objective is to obtain all of the attachments attached to the current SharePoint list item and merge them into a single PDF document.

Add a ‘Get attachments’ SharePoint action applying the following configuration:

Site Address: Set as per the ‘Site Address’ value set in #3
List Name: Set as per the ‘List Name’ value set in step #3
ID: Insert the ‘ID’ property from the ‘When an item is created or modified’ action result
Note: If you cannot see the ID field, click ‘See more'

9 - Add an ‘Apply to each’ control action, applying the following configuration:

Select an output from previous steps: Insert the ‘Body’ property from the ‘Apply to each’ action result.

10 - Inside the ‘Apply to each’ loop, add a ‘Get attachment content’ action, applying the following configuration:

Site Address: Set as per the ‘Site Address’ value set in #3
List Name: Set as per the ‘List Name’ value set in step #3
Id: Insert the ‘ID’ property from the ‘When an item is created or modified’ trigger.

File Identifier: Insert the ‘Id’ property from the ‘Get attachments’ action result

11 - Checkpoint: Your new Flow should look similar to the following:

12 - The next step is append the attachment information to the previously created ‘FilesForMerge’ array property which will later be used to send the files for PDF conversion and merging.

Add an ‘Append to array variable’ action, applying the following configuration:

Name: Select the ‘FilesForMerge’ variable
Value: Enter the following JSON:

{
  "fileName": "@{items('Apply_to_each')['DisplayName']}",
  "fileContent": "@{body('Get_attachment_content')['$content']}"
}

Note: If you have changed the default names of the actions, you will need to amend the expressions accordingly.

13 - Underneath the ‘Apply to each’ action, add a ‘Merge document array to PDF’ action, applying the following configuration:

Detailed guidance: Merge document array to PDF

PDF Filename: Enter ‘Order confirmation.pdf’
Click the ‘Switch to input array icon’

Documents: Insert the ‘FilesForMerge’ variable.

14 - The next step is to compile the HTML summary which should be inserted into the merged PDF document.

Add a ‘Set variable’ action, and configure as follows:

Name: Select the ‘HtmlSummary’ variable
Value: Enter the following sample HTML fragment or please create your own as required

<div>
      <img src="https://www.encodian.com/assets/img/Encodian_Logo.png">
</div>
<div>
      <ul>
            <li><b>Order Number:</b> @{triggerBody()?['Title']}</li>
            <li><b>Customer Name:</b> @{triggerBody()?['Customer_x0020_Name']}</li>
            <li><b>Customer Address:</b> @{triggerBody()?['Customer_x0020_Address']}</li>
            <li><b>Purchase Order:</b> @{triggerBody()?['Purchase_x0020_Order']}</li>
            <li><b>Order Details:</b> @{triggerBody()?['Order_x0020_Details']}</li>
            <li><b>Order Placed:</b> @{triggerBody()?['Created']}</li>
            <li><b>Order Paid:</b> @{triggerBody()?['Modified']}</li>         
      </ul>
</div>

Note: The HTML fragment contains expressions to inject properties from the ‘When an item is created or modified’ trigger, simply update as required by selecting different properties.

15 - The next step is to insert the HTML fragment contained within the ‘HtmlSummary’ variable into the PDF document generated by the ‘Merge document array to PDF’ action (Step #13).

Add an ‘Insert HTML to PDF’ action, applying the following configuration:

Detailed guidance: Insert HTML to PDF

HTML Data: Select the ‘HtmlSummary’ variable
Filename: Insert the ‘Filename’ property from the ‘Merge document array to PDF’ action result.
File Content: Insert the ‘File Content’ property from the ‘Merge document array to PDF’ action result
Click ‘Show advanced options’
HTML Insert Location: Select the ‘Prepend’ option, this will set the HTML fragment to be added to the start of the PDF document.

16 - Add a ‘Send an email’ action, applying the following configuration:

To: Insert the ‘Customer Email Address’ property from the ‘When an item is created or modified’ trigger, or manually enter an email address.

Note: You will only see properties for list columns present on the source SharePoint list.

Subject: Enter ‘Order Confirmation’
Body: Enter ‘Order Confirmation’
Click ‘Show advanced options’
Attachments Name - 1: Insert the ‘Filename’ property from the ‘Insert HTML to PDF’ action result.
Attachments Content - 1: Insert the ‘File Content’ property from the ‘‘Insert HTML to PDF’ action result

17 - Complete: Your flow is complete and now ready for testing!

18 - I’ve added the following list item to the SharePoint list for testing, upon setting the ‘Order Status’ field to ‘Paid’, the Flow will be fully executed!

19- The executed flow and relevant results are depicted below:

I hope you’ve found this short(ish) tutorial useful, please let me know if there are any specific scenarios you would like to see documented solutions for!

All the best

Jay – Encodian

@encodian