This is a common use case which is a regular topic within the community!

Encodian is one of our trusted Power Platform connector partners which contains a whopping 33 different actions covering document format conversion, redaction, watermarking, OCR, PDF to PDF/A, data extraction, merging, splitting, securing, search and replace, archiving (ZIP files!) and a whole boat load more!

Flow Overview

Many organisations include the document approval sheet in the document either at the start or end of the document. Using this approach requires the user to manually update the approval table which is difficult to manage especially when using an automated approval process to generate an approved version of the document, i.e. you can’t change the document once it’s approved! And you can’t really complete the approval table (dates, comments) until the approval process is complete.

This Flow will demonstrate how to dynamically generate the approval sheet from Flow approval data and add it to an approved rendition of the document (PDF).

Let’s build the Flow!

Navigate to the SharePoint document library which will store and manage the documents that will be sent for approval.

Click ‘Flow’, click ‘Create a Flow’

Click ‘Show more’

Select the ‘Complete a custom action for the selected file’

Click ‘Continue’

Select the default name of the Flow text and change to ‘Submit for Approval’

Add a SharePoint ‘Get File Content’ action to the Flow

Configure the ‘Get File Content’ action as follows:

Site Address: This must be set as per the ‘Site Address’ value set in the ‘Get File Properties’ action

File Identifier: Select the ‘Identifier’ property returned by the Get File Properties’ action

Add a ‘Start and wait for an approval’ action to the Flow

Select the required approval type – this demo uses the ‘Approve/Reject – Everyone must approve’ approval type

Complete the ‘Start and wait for an approval’ action configuration as follows:

Title: Set to ‘Official Document Approval Request’ or similar

Assigned To: Enter the users who will be asked to approve the document

Details: Enter additional detail if needed using markdown

Item link: Select the ‘Link to Item’ property returned by the ‘Get file properties’ action

Item link description: Select the ‘File name with extension’ property returned by the ‘Get file properties’ action

At this stage the Flow approval process is now complete. The next stage is to create the official rendition of the document, obtain the approval data and build the approval sheet that we will insert info the approved PDF document.

Add a ‘Initialize variable’ action

Name: Set to ‘ApprovalSheetHtml’

Type: Select ‘String’

Value: Copy and paste the contents of the ‘ApprovalsheetHtml-InitialValue.html’ file

              NOTE: This action is required later within the Flow… more details to follow

Add a ‘Condition’ action

Choose a value: Select the ‘Outcome’ property returned by the ‘Start and wait for an approval’ action

Equation: Set to ‘does not contain’

Choose a value: Enter ‘Reject’

This step is optional; it demonstrates how to change document content using Power Automate. In this example an action is added to update the document footer to specify the correct approval date:

Inside the ‘If No’ branch, add some relevant handling to inform the requesting user the approval has been rejected (see example below):

Inside the ‘If Yes’ branch;

Add the ‘Search and Replace Text’ Encodian action

File Type: Select ‘DOCX’

Filename: Select the ‘File name with extension’ property returned by the ‘Get file properties’ action

File Content: Select the ‘File Content’ property returned by the ‘Get file content’ action

Phrases Search Test – 1: Enter [APPROVALDATE]
Phrases Is Expression – 1: Select ‘No’

Phrases Replacement Text – 1: Select the ‘Completion Date’ property returned by the ‘Start and wait for an approval’ action

NOTE: If required you can specify exact font size, colour, etc. otherwise the current format of the word document text will be inherited.

Add the ‘Convert to PDF’ Encodian action

Filename: Select the ‘Filename’ property returned by the ‘Search and Replace Text’ action

File Content: Select the ‘File Content’ property returned by the ‘Search and Replace Text’ action

PDF Filename: Select the ‘Filename’ property returned by the ‘Search and Replace Text’ action. The Encodian action will automatically ensure that the output filename has a PDF extension.

Click ‘Show advanced options’

Remove Markup: Select ‘Yes’, this ensure that any comments / tracked changes are removed from the resultant PDF document.

The next step is to create the approval sheet. We have several options/approaches which would work; if the document already contains an ‘Overview/Title Page’ we could:

Use the ‘Insert HTML to PDF’ action to insert the approval sheet at a specific location within the PDF document.

Use the ‘Insert HTML to PDF’ action to append the approval sheet to the end of the PDF document.

Split the source PDF document into two separate documents using the ‘Split PDF’ action, create the approval sheet using the ‘Convert HTML to PDF’ action and the merge the three files in the correct order using the ‘Merge Document Array to PDF’ action

However, we advise our customers not to include an ‘Overview/Title Page’ or ‘Approval Sheets’ in the actual documents as these can easily be added dynamically when needed, providing enhanced control and options for adding these types of pages in a document. This is what we’ll now do…

Add an ‘Append to string variable’ action

Name: Select the ‘ApprovalSheetHtml’ variable

Value: Select the ‘Responses Approver email’ property returned by the ‘Start and wait for an approval’ action

NOTE: This should automatically place the ‘Append to string variable’ action in a ‘Apply to each’ loop.

Value: Enter the following text

<tr>

<td class=”TdData utilW100″>@{items(‘Apply_to_each’)?[‘responder’]?[‘displayName’]}</td>

<td class=”TdData”>@{items(‘Apply_to_each’)?[‘responseDate’]}</td>

<td class=”TdData”>@{items(‘Apply_to_each’)?[‘comments’]}</td>

</tr>

Underneath the ‘Apply to each’ loop, add an ‘Append to string variable’ action

Name: Select the ‘ApprovalSheetHtml’ variable

Value: Enter the following text

</table></div></div></body></html>

Add a ‘Insert HTML to PDF’ action

NOTE: This screen shots of the ‘Insert HTML to PDF’ action show some configuration options (Page Number, Page Size, Page Orientation) which are currently under deployment and may not reach your region for another 2 to 3 weeks.
Filename: Select the ‘Filename’ property returned by the ‘Convert to PDF’ action

File Content: Select the ‘File Content’ property returned by the ‘Convert to PDF’ action

HTML Data: Select the ‘ApprovalSheetHtml’ variable

HTML Insert Location: Select ‘Prepend’

 
NOTE: If you cannot see the ‘HTML Insert Location’ your region has not yet received the pending updates, simply click ‘Show advanced options’ to update the ‘HTML Insert Location’ option

The approval sheet containing document information and approval details has now been added to the document. You may now wish to dynamically add a document title sheet.

Optional Document Title Sheet Insertion Example

This is already a lengthy post, so we’ve just included a basic example for dynamically creating a document title sheet as a taster!

A Word template is retrieved from SharePoint, the Encodian ‘Search and Replace’ action used to replace placeholder text with the document title and approval date, a PDF rendition is created and then merged with the actual PDF document which already contains the approval sheet.

The resulting title sheet document:

Final Flow Action

Add the resulting approved document to the required location. In this example we’ll simply add the file back to the originating location.

Add a ‘Create File’ SharePoint action

Site Address: This must be set as per the ‘Site Address’ value set in the ‘Get File Properties’ action

Folder Path: Select the ‘Folder Path’ property returned by the ‘Get file properties’ action

Filename: Select the ‘Filename’ property returned by the ‘Insert HTML to PDF’ action

File Content: Select the ‘File Content’ property returned by the ‘Insert HTML to PDF’ action

Let’s Test!

Before you test, we advise reviewing the following blog post which outlines how to share your Flow with users correctly to ensure connections are shared with the correct configuration to negate the need for users having to enter unnecessary connector authentication information such as API Keys:

Reference Guide: Prevent users needing to enter an API Key in Power Automate

The ‘run only users’ configuration for this demonstration flow has been set to the following configuration:

Go to SharePoint and start the Flow

Click ‘Continue’

The approved PDF file with embedded approval sheet should have been created

Closing thoughts…

Although this post has focused on the creation and manipulation of a PDF document, this could also be achieved with a Word document (docx) using the ‘HTML to Word’ and ‘Merge Word Documents’ actions to replace their PDF equivalents.

The post Intermediate | Flow of The Week: Create and insert a document approval sheet using Flow approval data appeared first on Microsoft Power Platform Blog.

Today’s post is written by Michelle Gilbert (former MVP, now Microsoft Teams Technical Specialist) and Melanie Hohertz, MVP Community Superstar!

Onboarding is a painful experience at many organizations. Managers are busy, and welcoming a new hire can fall through the cracks. Thinking about onboarding communication as a business solution means workflows, updates, and notifications galore. But the good news is that you can swoop to the rescue with a smart use of Flow.

The solution below has been tested and verified. It uses Flow, SharePoint and Outlook in ways that do not require admin rights. But we also include an optional, “bonus” Flow that would require “send as” email permissions.

We’ll follow these steps:

Step 1: Outline Your Audiences, Cadence, Messages and Senders

Step 2: Create a SharePoint List to House the Communication Timeframe

Step 3: Build the Flows

Step 4: Create an Input Form with SharePoint

Step 5: Manage Your Flows

Are you ready for automated onboarding communications goodness? Here we go, step by step!

Step 1: Outline Your Audiences, Cadence, Messages and Senders

This business solution will send messages in a pre-set, timed sequence to three different audiences. First is the requestor of the cascade, which may be an admin assistant or manager. Second is the hiring manager, who will receive notices to prepare for the employee’s arrival, and more. Third is the new hire, who should be welcomed, provided resources and prompted for questions and feedback.

Also, while our example below includes timing assumptions such as communications two weeks before start date, on start date, etc., you will want to think through what cadence will best serve your business and colleagues. Thinking about what information should be pushed at particular times leads to drafting your message content.

Last but not least, we had fun thinking through our senders. In our fourth, “bonus” flow, we walk through the steps to send a welcome communication to a new hire from their dynamically generated, geographically appropriate regional leader.

Our solution manages these audiences in four Flows, all pulling data from one SharePoint list to achieve the tasks. This architecture makes use of various SharePoint and Flow features, but of course there are many other possible configurations.

Step 2: Create a SharePoint List to House the Communication Timeframe

First things first! Every time you read “SP list” below, your brain should fill in “SharePoint Online modern list.” Ready?

We created a custom SP list with a column that tracks “hire status” as a trigger for our Flows. The list also stores and serves the new hire’s first and last name, email, region and regional leader options, manager, start date, and the timeframes for our communications to deliver.

As you can see above, many of the columns are simple single line of text, and date and time. We also used person fields, which is a look-up of our organization’s employee directory (Active Directory). Then there are choice fields where we create drown-down options for region and hire status. Last, we used some complex calculated columns, such as for our communication dates and our regional leader. Don’t worry – we will give you those calculations!

Let’s take a look at the columns and their settings, in the order shown above. Note that we made some fields required, as those will trigger our Flows and fuel our calculated columns. These fields will be populated by people filling out the SP input form we create in Step 4.

Ready to start list creation?

1. Rename your list’s Title field to First Name. (This field is required by default. By the way, column names are very important to making the dynamic content work in these Flows, so follow the recipe.)
2. Create a single line of text column. Name it Last Name, and checkmark to make it required.
3. Create a Date and Time column. Name it Start Date, and follow the configuration below.
   
4. Create a Person or Group column. Name it Manager, and follow the configuration below.
   
   Note that in the Manager field shown above, we’ve allowed selection of “People Only,” and we’re showing the work field email. Add the following two columns if you would like to have emails sent to your new hire based on geography and regional leaders.
5. Create a Choice column. Name it Region, and follow the configuration below.
   
6. Create a Calculated column. Name it Regional Leader, and follow the configuration below.
   
   This is one of the columns where we use a calculation. Here is the formula for easy copy and paste:

   ---

   =IF(Region=”APAC”,”apac@domain.com”,IF(Region=”EMEA”,”EMEA@domain.com”,IF(Region=”LATAM”,”LATAM@domain.com”,IF(Region=”NA”,”NA@domain.com”))))

   ---

   Once you copy and paste, swap in the regional email addresses applicable to your company. That, we can’t do for you! You could use a regional leader address, but given changes in personnel, you might consider setting up regional shared mailboxes.

7. Create a Choice column. Name it Hiring Status, and follow the configuration below. Please note that our options here reflect the messy process of many company hires. They cover situations where a manager or administrator knows someone is hired but does not have a start date, or when a hire gets canceled. If you choose other options, or change the wording, later in this process you will need to update the filter query in the flow action.
   
8. Create a Person or Group column. Name it New Hire Email, and follow the configuration below.
   
9. Create a Calculated column. Name it Comm1, and follow the configuration below. In this case we wanted to calculate the “StartDate minus 14 days,” meaning two weeks before the start date. Why? Because this communication date is intended for managers to receive an email two weeks prior to a new hire starting.
   
10. Create a Calculated column. Name it Comm2 and follow the configuration below. This second communication date will help send managers a notification one day before the new hire starts.
    
11. Create a Calculated column. Name it Comm3, and follow the configuration below, including the provided formula. This communication date is intended for managers to receive an email two weeks (“StartDate+14”) after the new hire starts, to support a check-in and feedback.
    

Please note that because Comm1 was two weeks before start date, and Comm3 is two weeks after, the 28-day total span is very close to the 30-day storage retention limit of Flow. So, if you want further communications in this onboarding sequence, such as 60-day and 90-day communications, you’ll need another Flow. Yes, Flows can kick off other Flows, but we did not include that here.

With Comm3 done, our SharePoint list is complete!

Step 3: Build the Flows

Now we’ll build four Flows. That’s one each for the cascade requestor, manager, and new hire; and a “bonus” fourth Flow for a welcome message from the geographically appropriate regional leader. Throughout the Flows, we are using the Office 365 Outlook (not the Outlook.com) to send communications.

Flow 1: Cascade Requestor

This first Flow directs a notification to the person creating the onboarding communications request to confirm receipt of their request for the cascade. As this person may be an administrative assistant, the manager of the new hire is copied on the notification.

This is the simplest of the Flows. You can see that the trigger, as shown below, is “When a new item is created” in our SP list. (Remember that in Step 4, we build an input form to collect new requests. That populates our SP list and triggers our Flows.)

For this Flow, we use v2 of the Send Email connector, but we don’t take advantage of its fancy options, as we do in later Flows.

Also, notice in this Flow we’re bringing dynamic content into the body of the email, such as the name of the requestor (“Created By Display Name”).

Flow 2: Manager

Next, our second Flow directs communications to the hiring manager. When triggered, this Flow will deliver three hiring manager notifications:

• The first will arrive two weeks prior to the new hire start date
• The second will show up one day before start date, and
• The third is sent two weeks after the new hire start date.

Remember our SP list? These three communications are based on the time stamps in our three calculated list columns: Comm1, Comm2 and Comm3. That’s why you see three branches in the Flow below.

Of course, our architecture assumes that this onboarding communications cascade is requested more than two weeks before the new hire’s first day. But what happens if someone is hired with less notice, such as 10 days? The good news is that this Flow won’t fail. But, the first of the three messages designed for delivery in this Flow would not take place.

Just like our first Flow, our trigger is tied to changes in our SP list. In this case, we chose “When a new item is created or modified.” Our choice means that this Flow will be triggered when an item is created or modified, and the action that follows will query whether the created or modified list items meet certain conditions.

It’s important to note in Flows with multiple actions: when pulling in dynamic content, the dynamic content pulls from all actions.

This means we need to choose the dynamic content from the SP trigger “When a New Hire is created or modified on the list” output, rather than from the SP action “Get Items if the start date is final” dynamic output. We are using the “When an item is created or modified” trigger, as this triggers an “Apply to Each” for the “Delay Until” action. We need this for the emails to function correctly.

Also important: When we sent this solution out for testing, our friendly expert copied the subject line from the Comm1 email to Comm2 and Comm3. DON’T DO THIS. The flow will not save. They had to delete the entire Apply to Each container to fix it. Additionally, when the “Delay Until” was tested, it didn’t point to the StartDate column directly. To remedy this, our friend formatted with this expression instead: formatDateTime(triggerBody()?[‘StartDate’],’yyyy-MM-ddThh:mm:ssZ’ ) . You can use this as an alternative if you run into the same snag.

Moving on, as you see below, the action “Get items from a SP list” queries our SP list and gets the items where “Hire Status” = “Start Date Final.” (Remember, “Start Date Final” is one of the options available in the “Hire Status” choice column in our SP list.)

So, when the start date is finalized for a new hire, then (and only then), the manager of that hire will receive the three communications.

Now take a closer look at the three branched communications in this Flow. They all use a “Delay until” action that is waiting for a date from our SP list (more dynamic content!) to send the communication.

Also, these three communications use the Send Email v2 action. We chose that option because, unlike the classic Send Email, the v2 option enables rich text editing. This feature will make your communication draft author happy!

Just as in the first Flow, the messages created here will use dynamic content pulled from our SP list. The subject line of these emails will reflect the name of the new hire and their start date.

You’ll see in this Flow that the body text for each of these manager communications is just a placeholder. You could house this body copy in a second SharePoint list and pull it dynamically into the appropriate messages. Why not?

Flow 3: New Hire

Now let’s move on to the third Flow, which sends two communications to the new hire, with one on the hire date and one a week later. Of course, you can customize the timeframe of these communications, within the 30-day run limit of a Flow.

As you see below, just like the Flow that sent messages to managers, the trigger here is “When a new item is created or modified,” and there is a following action to query our SP list and get the items where “Hire Status” = “Start Date Final.”

Beneath that action are the two branches that drive the two new hire communications:

• The first will arrive on the hire’s start date, and
• The second will show up a week after the start date.

Let’s focus for a moment on the purple variable input step in this Flow. Here, we decided to use Flow rather than SharePoint to set the date of our new hire communications. We’re using a variable input to add 7 days to the start date, with this expression: addDays(triggerBody()?[‘StartDate’],7,’MM/dd/yyyy’)

In this view, and many others, we’ve renamed our triggers and actions to help keep track of our workflow. This can be a helpful practice when Flow admin rights are shared across several people, as we’ll note later.

Looking at the two communication branches in the new hire Flow, the first branch uses the time stamp Start Date directly from the dynamic content in our SP list. But the second branch uses the time stamp based on the output of the variable, which sends our new hire a message a week after they start work.

Both communication branches in this Flow use Send Email v2 to enable rich text editing. Also, both messages generated here make use of dynamic content from the SP list to populate the new hire’s email address, and a personalized greeting in the email subject line.

Flow 4: Bonus: Regional Leader Welcomes New Hire

Now we’re ready to move on to our last, optional (unverified but fabulous) “bonus” Flow, which sends the new hire a welcome message from the geographically appropriate regional leader.

By now you’re familiar with the trigger and first action in the Flow shown above, which fires when an item in our SP list is added or modified, and drives action when a condition is met (“Hire Status” = “Start Date Final”). Specifically, the action sends a communication on the timestamp of the new hire start date.

That communication uses the classic Send Email action, because it offers an advanced options field called “From (Send as).” This feature enables us to send a welcome message to a new hire on behalf of a regional leader, as long as we take care of Outlook mailbox access permissions.

Please note, you may or may not see this action as available in your tenant! If not, this Flow will not work for you, but the good news is your tenant isn’t broken. If the Send Email classic action is not there, you will know it has been deprecated, as Microsoft notified us it would be, back in August, 2019.

However, if you see it, feel free to use it, because as that deprecation notice promises, Microsoft is committed to your Flows continuing to work even as they routinely upgrade actions and connectors.

So, is the action there or not? If it is, great! If not, all is not lost, as we have another option for you. The picture below shows the same Flow, but with one change: we have chosen the Send an Email from a Shared Mailbox v2 action instead.

So far we’ve presented you with a fairly straightforward technological choice. But as we’ll talk about below, this Flow has interesting challenges in terms of people and process, including mailbox permissions or setting up shared mailboxes.

Remember that in our SP list, we created a choice column with options for each region. That enables our SP input form to drive a regional selection. The choice of region then interacts with a calculated column from our SP list, to produce the correct regional leader’s email address in the “From (Send as)” field.

These Flow clicks are quick, but the Outlook access steps to enable “(Send as)” may take time for four leaders. We’ve brainstormed solutions including:

• Setting up regional shared mailboxes
• Partnering with administrative assistants who have leaders’ Outlook delegation rights
• Making administrative assistants with leaders’ Outlook delegation rights owners of this Flow

We also thought about service accounts in this context. If your organization uses them, service accounts would offer at least three benefits here, among others:

• Your leader or administrative assistant with delegation rights might be more comfortable with the idea that a service account shares those rights. All logins to the shared account would be trackable.
• Your Flow would not be deleted when you or any other individual owner left the company.
• If your company has limited Flow licenses, a service account may make the best use of resources.

In all these cases, we will need to make future adjustments as our company structure or leaders or administrative assistants change.

We hope you can understand why we love this Flow, challenging as it may be. It’s loaded with dynamic information from our SP list, including not only the appropriate regional leader as sender, but also the region in the email subject line, the new hire’s name in the message body, and the manager copied on the message.

If you put it to work, even in a scaled-down version for smaller departments, please let us know!

Step 4: Create an Input Form with SharePoint

As many of us are familiar with creating useful and appealing forms with SharePoint, we won’t walk through the setup mechanics here. Note that all fields in this form are required in order to update our SP list, power our Flows, and send our onboarding communications.

At this stage in the process, Melanie looked at our form and noted that the two-word field labels (like “FirstName”) are “squished together.” Michelle responded that she could update the column headers in the SP list to make them “un-squished” in the SP form.

The interesting learning here is that our Flows will be fine even with Michelle’s changes! This is because the Flows refer to the originally created column header names, even when those names are changed later. A fresh Flow run confirmed it.

Melanie promptly suggested another change, as business partners do. If form labels can be renamed, she’d change “First Name” to “New Hire First Name,” to avoid user confusion.

So, the essential points to remember here are about people and process. First, host the form where everyone who needs it can access it. Second, integrate the form into any other pages that administrative assistants or managers use when setting up new employees.

Third, provide training for all “request creators,” so they’ll know what this form offers and delivers. Why training, or at least guidance in context on the page? Well, consider how much activity is powered by the “Hire Status” choice of “Start Date Final.” You’ll want people to choose “Start Date Final” only when it’s final-final!

Plus, you may want to explain the ability to update and even cancel the communications cascade using the “Hire Status” options.

Step 5: Manage Your Flows

All throughout our demonstrated Flows, you’ve seen it: renamed triggers and actions that are there to help us remember later “what on earth we were trying to do when we made this.”

Renaming the elements in your Flow is helpful not only for you, but other owners. You did plan to name other Flow owners, right? Because if you leave a company, and you’re the only owner of one or more Flows, those helpful Flows will disappear along with you. Remember what we said above about the virtues of service accounts!

Plus, let’s think about failures for a moment. It’s good to keep an eye on your Flow runs every now and then, where you can see the history of their success or failure, and whether your Flow is being used at all. (If not, that’s another kind of failure.) You might consider adding error handling to your Flows, so if they fail, you get proactive notification.

Also, monitor the Flow roadmap, because actions and connectors continue to evolve. After all, the Send Email v2 action that we rely on here is still new enough that it carries “(Preview)” notation.

Make It Your Own, and Share It

As we noted in the very first step of this process, there are many possible configurations that could support automated onboarding communications, mixing SP and Flow and maybe even Forms. Your solution may be different and fabulous in its own way.

Creativity and community fuel the joy that powers our solutions. Thanks for coming along!

The post Beginner | Flow of the Week : Automate Onboarding Communications with Flow! appeared first on Microsoft Power Platform Blog.

Today’s post is written by Ed Gonzales; Community Super User and all around superstar!

As part of a series intended to help non-developers (like me) brave some of the intimidating aspects of Power Automate, I wanted to cover one of my favorite expressions in a very simple Flow…split().

Split is used to break up text string into an array, or a ‘table of substrings’ using a delimiter of your choosing.  You can also use a zero-length (blank) string, which will result in each character being broken out individually.

In today’s example, we’ll use the function to extract the first and last name from an Email address which is formatted as firstname.lastname@email.com.  We’ll use a manual trigger with a text input to get started, but you can insert this after any step passing your desired text string.

Click “Add an input” and select “Text”.  Even though we are collecting an Email address, selecting “Email” will cause Flow to try to resolve the address to your specific tenant.  Next, we’ll add a Compose action so we have a place to manipulate our string from the manual trigger.  Click in the input field for the Compose, and then choose “Expression” to start building:

Looking at the sample input, Ed.Gonzales@email.com, we’ll want to first split on the ‘@’ which will give us two elements: “Ed.Gonzales” and “email.com”.  Then, we can further parse the first element on the period, which will produce two more elements, “Ed” and “Gonzales”.  With nested functions, I like to start in the middle and work my way out, so the first iteration will begin by typing “split(“ in the expression box, then clicking back to “Dynamic content” to select the input from the previous step.  Add a comma and your delimiter (‘@’) in single quotes.  It should end up looking like this:

split(triggerBody()[‘text’],’@’)

Flow assigns numbers to each element (beginning with zero), so if I only want the first element of the output array, I’ll add a [0] at the end like this:

split(triggerBody()[‘text’],’@’)[0]

This should result in an output that is only “Ed.Gonzales”.  To further separate this part, I’ll add another split before the original, and then add a new comma at the end with ‘.’ as the second delimiter.  The second iteration should look like this:

split(split(triggerBody()[‘text’],’@’)[0],’.’)

This should give two elements “Ed” and “Gonzales”.  So now, it’s just a matter of assigning one element to a Compose for FirstName, and another for the LastName.  Using my original Compose for FirstName, I just added the [0] at the end of the whole thing to only look at the first element.  Also, to reduce complications later, I’ll convert everything from an array back to a string using the function of the same name:

string(split(split(triggerBody()[‘text’],’@’)[0],’.’)[0])

Copy that step and change the last element selection to [1] to grab the last name:

string(split(split(triggerBody()[‘text’],’@’)[0],’.’)[1])

The results should look something like this:

From there, you can add the data into whatever connector you’d like.

Play around with it, make mistakes, and learn along the way!

Try. Fail. Learn. Repeat.

Ed Gonzales has been working in technology for twenty-five years, covering many roles and developing new ones along the way.

His journey to bring customer relationship management (xRM) solutions to his current employer led to thought-leadership awards and recognition from leading industry experts and his peer community group.

Ever the polymath, his current endeavors include aviation science, aquaponics, and fostering orphaned kittens.

More information and his personal blog can be found at flyingpolymath.com and you can also connect with him on LinkedIn at /in/edgonzales.

The post Intermediate | Flow of the Week : Expressions for Non-Developers Part I appeared first on Microsoft Power Platform Blog.

In this weeks video, Flow Joe aka Joe Unwin teaches us how to use the Microsoft Geolocation trigger to build a fence around a customer site, so that way when your sales people walk in the door… they are delivered a playbook full of customer information from CDS, Social media and news items!

Come check it out in this new Microsoft Flow Tutorial Video

Be sure to follow Flow Joe on Twitter and subscribe to his YouTube Channel! 

Leave a comment below, or on the video itself!

Later Friends!
– Jon

The post Intermediate | Flow of the Week: Geo-Fence Your Customers appeared first on Microsoft Power Platform Blog.

John is a Microsoft MVP and freelance consultant and trainer for hire specializing in Excel, Power BI, Flow, PowerApps and SharePoint. You can find more interesting articles from John on his blog or YouTube channel.

In this post he is going to show you how you can use the new Word connectors in Flow to easily create some really nice looking PDF invoices.

For this example,he will be using a SharePoint list and the Flow button trigger, but many other triggers could be used including from PowerApps.

Please enjoy!

Video Tutorial

Creating A Word Template

First, you’re going to need to create a Word document template for your invoices.

I’m not a Microsoft Word expert and there might be better ways to create an invoice template, but I find using tables to be an easy option.

A couple tips for creating your own template:

1. Use tables to layout the heading or other sections in the invoice. Go to the Insert tab ➜ Table.
2. Remove the borders in your heading table section. Select the table ➜ go to the contextual Table Design tab ➜ Borders ➜ No Borders.
3. Add gridlines to your table so you can see and adjust the table borders in your template. Select the table ➜ go to the contextual Layout tab ➜ View Gridlines.
4. Use merge or split cells to adjust the layout for sections that need more space. Select the cells you want to merge or split ➜ go to the contextual Layout tab ➜ Merge Cells or Split Cells.
5. For the section that will contain the invoice line items, create a table with two rows. One for the column headings and one for the first row of data. You will turn the row of data into a repeating content later.
6. Use the table styles feature to add some style to your table. Select the table ➜ go to the contextual Table Design tab ➜ select a style from the Table Styles
7. If you’re going to have a lot of line items in each invoice, then you might want the column headings to appear on each page of the invoice. Select the row of column headings ➜ go to the contextual Layout tab ➜ Repeat Header Rows.

You should be able to create a pretty awesome looking template with these tips.

Enable The Developer Tab In Word

Unfortunately, the content controls needed to create templates useable by Flow are hidden away in the Developer tab.

These aren’t available in Word Online, so you’ll need to be using Word on Desktop.

To enable the Developer tab right click anywhere in the Word ribbon ➜ select Customize the Ribbon ➜ check the Developer tab ➜ press OK.

Add Content Controls To Your Invoice Word Template

Now you’re going to need to add content controls to your Word template. These are what Flow is going to use to dynamically populate the Word template.

All the content controls can be found in the Controls section of the Developer tab. This example will use 3 types of content controls.

1. Plain Text Content Control for input of a single text value. This will be used with the invoice number and invoice line items.
2. Image Content Control for input of an image. This will be used with an image of a signature stored in OneDrive, but it could also come from the pen input in PowerApps.
3. Repeating Section Content Control for input of variable amounts of data. This will be used with the invoice line items.
4. Properties for the selected content control in your Word document. This will be used to name your content controls.

The Properties is how you can name each of the content controls and the name you give the control will be used in Flow.

Add Content Control For The Invoice Number

Highlight the invoice number in your template and click on the Plain Text Content Control in the Developer tab.

Open the content control Properties and give it a name like InvoiceNumber.

Add Repeating Content Control For The Invoice Line Items

Highlight the entire data row in the invoice template and click on the Repeating Section Content Control in the Developer tab.

Open the Properties and give it a name like InvoiceItems.

Once the repeating section has been added, you’ll also need to add a plain text content controls inside the repeating section for each column. This example has two columns which will be named ItemDescription and ItemAmount.

Add Content Control For The Signature Image

Place your cursor in the location in the Word document where you want your signature image to appear and click on the Image Content Control in the Developer tab.

Open the Properties and give it a name like InvoiceSignature.

Note: You will need to adjust the size of the content control so that it fits your image, otherwise your image will appear stretched. Select the control and go to the Picture Format tab and adjust the Height and Width as needed.

You can add a placeholder image first that will be the same size as your signature image and convert this to a content control. The content control will be perfectly sized this way!

Save Your Word Template To OneDrive Or SharePoint

Once you’re satisfied with your Word template, you’ll need to save it to either OneDrive or SharePoint.

Building Your Flow To Create Invoice PDF’s

Now you can build the Flow to create the PDF file. The invoice line items for this example will come from a SharePoint list called InvoiceList.

1. Manually trigger a flow

This example will use the flow button with a text input for the invoice number as the trigger.

2. Get Items From SharePoint List

Now we need to get the relevant items from our SharePoint list with the Get items action.

You can add a Filter Query to limit the items based on the dynamic content for the invoice number from the trigger step. The invoice number in this example is in the Title column of the SharePoint list.

3. Initialize Variable For Invoice Items

Here you can initialize an Array variable to hold the SharePoint list items. The items will need to be in an array when populating the Word template later.

4. Append SharePoint Items To Array

Now you can populate the array with all the data from the SharePoint list items.

Add an Apply to each action and add the value dynamic content from the Get items step. Then add an Append to array variable action inside.

The Value can then use the dynamic content for the fields from you need from your SharePoint list.

This will be used to populate the Word template.

5. Get File Content For The Signature Image

Add a Get file content action and connect it to the signature image to be used.

6. Populate A Microsoft Word Template

Add the Populate a Microsoft Word template action and connect it to your template saved in either SharePoint or OneDrive.

Since the invoice will have a varying number of invoice line items, you will need to Switch to input entire array mode.

Then you can add dynamic content from the previous steps to populate the various invoice content controls.

7. Create File From Populated Word Template

Populating the Word template doesn’t create the file. You will need to add a Create file action.

Make sure to add the proper docx file extension to the File Name.

The File Content will be dynamic content from the previous Populate a Microsoft Word template step.

8. Convert Word Document To PDF

Now that you have a populated Word document, you can use the Convert Word Document to PDF action to convert the file to PDF.

The file to be converted won’t exist until the Flow runs, so you won’t be able to use the file picker to select the file. Instead, you’ll need to use the Path dynamic content from the previous Create file step.

9. Create PDF File

Again, converting the word document to PDF doesn’t create the file. You’ll need to add a Create file action.

Make sure to add the proper pdf file extension to the File Name.

The File Content will be dynamic content from the previous Convert Word Document to PDF step.

Conclusions

The new Word connectors are a great option to dynamically create PDF’s from Word templates.

But there are a few tricky bits in the process that aren’t quite obvious.

Once you get them figured out, you’ll be able to create some awesome PDF templates in Word and use Flow to dynamically populate them with your data.

The post Intermediate | Flow of the week: Create PDF Invoices Using Word Templates with Microsoft Flow appeared first on Microsoft Power Platform Blog.

This week come along with me and Flow MVP Serge Luca as we introduce you to the Power BI Connector inside of Microsoft Flow.

We cover all of the triggers and actions as well as some future capabilities as well!

Please feel free to comment below, on Twitter or in our community!

The post Beginner | Flow of the Week: Introduction to the Power BI Connector appeared first on Microsoft Power Platform Blog.

This week we are bringing you a video tutorial to show you how to add Microsoft Flow Approvals to Microsoft Teams by using Adaptive Cards with Flow MVP Daniel Laskewitz.

If you have any questions please feel free to leave a comment below or reach out to us on Twitter or in the Microsoft Flow Community

The post Intermediate | Flow of the Week: Approvals in Teams using Adaptive Cards appeared first on Microsoft Power Platform Blog.

Have you ever missed an important meeting because it was before your normal work hours? This happened to me one too many times, so I decided to do something about it! My flow runs every night around 8 p.m. and sends me and email if I have a meeting before 10 a.m. the next day. This gives me time to change my alarm if needed or make other plans. 10 a.m. may not be “early” to a lot of you, but these values are easy to change.

Before I get into the implementation, if you only care about the solution, I’ve created templates for Office 365 Outlook, Outlook.com, and Google Calendar/Gmail. I’ll be walking through the Office 365 Outlook version, but the others are pretty much the same.

In theory this a straightforward flow, given the handy “Get calendar view of events” action. Get current time, add hours, get events, send an email. But of course time zones have to show up and complicate things. So let’s break it down.

Here’s the math-y part. “Get current time” uses UTC, so we have to convert it to local time to work properly with the startOfDay() function. It turns out that even though we specify the “Round-trip [o]” format, it loses the time zone information here, so we have to convert it back to UTC to be useful to the calendar action. We then add a day to get start of day tomorrow, then another 10 (or however many) hours to get the threshold for “early”. These actions are also all available as functions if you’d like to condense this part, but the actions make for a more readable and fillable template.

NOTE: I didn’t specify the minutes in my Recurrence trigger, meaning it will run sometime between 8:00 and 9:00. If you specify exactly 8:00 you can skip the time zone stuff and do +4h, +10h to get the range. This makes for a brittle template though because changing one value requires changing the others, and we have no real need to run exactly on the hour.

This is the easy part. We pass the calculated times to “Get calendar view of events action”. For Outlook, we pass the “isAllDay eq false” filter because all-day events don’t count as early. Google Calendar doesn’t have an equivalent filter, so instead we use the “Filter array” action and check whether the start time has a “T” separator in it, indicating a specific start time vs. an all-day event.

We then check if there are any events in the array. Normal condition stuff.

From here we could send an email that says “Early meeting! Go check your calendar!” and call it quits. But where’s the fun in that? Instead, let’s convert the events to local time and put them in a more readable format for the email. You can skip the “Apply to each” in favor of a “Select” action here, but that doesn’t let us leave the time zone blank for the template.

Finally, we send the reminder email. Outlook.com and Gmail require you to type in your own email address, but Office 365 has a handy “Get my profile” action that saves you the trouble.

That’s it! Hopefully this flow helps keep your schedule running smoothly. Extra credit if you can get it to filter out an 11:00 a.m. Monday through 5:00 p.m. Friday event (not “all-day” but multi-day), or to remind attendees of the early meeting you scheduled. Let me know in the comments if you have any feedback or if you have any cool timing or scheduling flows of your own.

The post Intermediate | Flow of the Week: Early Meeting Reminder appeared first on Microsoft Power Platform Blog.

Written by Anton Khritonenkov.

In this article, I will describe how to automate the creation of complex PDF documents from a template. Let us say we have a sales department. They are tired of filling the same documents with brief changes again and again. We could provide them some form for filling data and generate documents automatically.

Microsoft Flow is a glue between different services. It makes it possible to pick a few products that suite your needs and use them together without writing a line of code.

In this article, I will show how to use SharePoint Online, Plumsail Forms, and Plumsail Documents together for satisfying our requirements for the invoicing process.

We will need to create a form, then trigger a flow on form submission, generate an invoice from a template. Then we will save the generated invoice in the SharePoint document library of our sales department. Then the document will be sent by email to a customer.

Create an invoice form

In this article, I used Plumsail Forms for Web, but you can use any other forms solution that has integration with Microsoft Flow and supports tables.

I designed the form as a wizard to simplify the user experience. Below you can see how it looks.

I will not describe here the implementation of the form. You can download the export file and import it on your side. You will get the same form.

Note: While creating your form assign meaningful internal names to the fields, table and table columns. Those internal fields will be a part of data sent to our Microsoft Flow.

This is how you can specify the internal name:

Prepare DOCX template for invoice

I want to use DOCX template instead of HTML or some other format because it gives more control to end user (our sales department). The person without programming skills has to be able to change the template and adjust the styling of the documents.

Click here to download the sample template. This is how it looks:

You can see that there are some tokens like {{data.bulletFullName}}. We will use data from our forms to replace them.

A bit tricky part is a table with products. There is a single row with tokens like {{data.product.quantity}} in the Word table. The template will automatically understand that the “products” is an array and replicate rows for each product item.

I would recommend you to read more about template syntax in the documentation.

Later in the Flow, you will see the JSON object that is applied to the template. Understanding the structure of this object will help you to create the correct template.

When the template is ready we can save it somewhere in SharePoint. Let us say we saved it in the document library of our sales department site.

Create Microsoft Flow for invoice generation

Now we need to create a Flow that will receive data from the form submission and apply it to the template that we prepared.

Here is how our Flow looks:

Let us review it step by step.

Flow trigger

Flow is triggered when the form is submitted. You need to put form ID into the trigger to enable this. Review this instruction for more information.

Getting DOCX template

We use “Get file content” action from SharePoint connector to get the DOCX template from the SharePoint document library where we saved it earlier.

Initialize invoice number

We use the “Initialize variable” action to generate an invoice ID. Put this expression into the variable. It represents the current date formatted as “yyMMdd”. You can use any other approach for numbering. I just like this one.

formatDateTime(utcNow(), ‘yyMMdd’)
Apply our DOCX template

The action uses Create DOCX document from template action. You just put the DOCX template into “Document content” property. Then specify the JSON object as on the picture below. The JSON object is applied to our DOCX template. There are two fields in this object:

1. invoiceNumber — variable from the previous step.
2. data — data from the Plumsail Forms trigger. The “Body” represents all data received from the form.
3. 

Convert generated DOCX document to PDF

You just need to put the output from the previous action to Convert DOCX to PDF action:

Save result PDF into SharePoint and send email

Then in the last to actions, we save the result PDF file in SharePoint for future reference and send it by email to our customer.

The “billedEmail” is taken from the form submission trigger.

Conclusion

Microsoft Flow allows us to automate a lot of things and integrate various services. One of the common cases is document management automation. I hope this article will help you to save your time and time of your colleagues. Spend time for more valuable tasks than filling out some papers again and again. I think it worth it to spend some time on automation and then delegate it to Microsoft Flow.

Please leave comments and questions below and be sure to Follow Anton on Twitter!

The post Intermediate | Flow of The Week: How to automate PDF document generation from a DOCX template using Microsoft Flow appeared first on Microsoft Power Platform Blog.

Whats up Flow Friends!

This post is written by George Culler and Brian Osgood  from the Azure team. They Contacted me and asked to show me a set of Flows they had made for their team and after seeing this all in action, i asked them to share the post with all of you, as i know we can all use this same concept in many ways!

Anyways, onward and Enjoy!

 Overview:

Hi, Flow Community!

We are excited to share a customized Triaging and Team Assignment Flow which will help speed up your teams processing times and ensure more accurate work.

We built this Flow out of a need to quickly triage to the right teammate and check for errors in tickets coming from a much larger customer facing team. Below we will show you the steps taken to run Flow through five different checks, as well as send TFS tickets to the right team member for processing. This Flow allows your organization to cut down on churn between teams, provide more accurate information and overall improve customer experience. It will be assumed that you have a understanding of how Flow works so we are going to dive right into creation.

Prereqs:

• Basic Expression Function Knowledge
• Advanced Flow Knowledge
• SharePoint list creation
• Intermediate TFS / DevOps Knowledge

Steps:

Create a new Flow with a DevOps “When Work Item is Created” trigger

Fill in your required fields and select your work item type. For our Flow, we are using our in-house “Credit Issue” work item. (this helps the Flow pull the correct data fields if you have multiple work items)

We are going to be checking for five “pain points” on each work item. These five points are:

1. Date issue reported; we have a requirement that issues be reported within a certain amount of days since service start.
2. Is a file attached? We require that a calculation file be attached to some issues so we will need to check the issue category and then check a field for its value.
3. Is this Issue a duplicate category for the same customer? We have a couple of one time use categories that we need to check aren’t given multiple times.
4. Is the dollar amount below a certain threshold? If so, it should be marked by the creator as Pre-Approved.
5. Auto calculate the dollar amount based on the exchange rate.

We will be running our checks in parallel so let’s add some parallel conditions and get this Flow started!

Date Reported:

Starting with a condition action, we will use the dynamic content to pull in the “Date Issue Reported” field. Our conditional statement will check if the field is blank. We used an expression for this with the “empty” function.

empty(triggerBody()?[‘fields’]?[‘Ops_DateIssuewasReported’])

This function returns true if the field is empty. If its empty, we tag it as “BOT: NODATE”. If the field is populated, we will perform some date calculations to check if the reported time is within 120 days of the Date Issue Started. Using the “Date Time” connector and the “Add to Time” action. We can add 120 days to the Date Issue Began field.

We now have two Dynamic date values we can use, but to compare the date difference, we will use another expression function, the “Ticks” function which we can use in a conditional action to compare if our date issue started + 120 days is greater than the date issue was reported.

ticks(triggerBody()?[‘fields’]?[‘Ops_DateIssuewasReported’])

ticks(body(‘120_Day_Addition‘))

If the condition results in a “Yes” then we will tag the ticket as “BOT: 120Days” and leave a message in the Bot Box (this is a field we have added to our TFS template that automatically displays a message when a ticket is created incorrectly). If the condition results in a “No” we do nothing as the ticket is within our criteria.

Calc. file check:

Currently, our check for the file is dependent on if the creator has marked “No” in a dropdown. If so, we tag it “BOT: CF” and then update our custom “Bot Box” to have a message.

Empowerment Check:

For this branch, we will use our SharePoint connector to pull from a custom list. We add a SharePoint action “Get Items” and add our site address and select our List Name. This will return an array that we need to iterate through using the “Apply to Each” action on the “value” dynamic variable from our “Get Items”.

We then run a condition on the ticket item checking for criteria, if its marked correctly, and what the dollar amount is in USD.

If the condition returns a “Yes” we tag the ticket with “BOT: EMP” and leave a message in the Bot Box. We played with the idea of re-assigning the ticket to the creator but that would cause too many headaches for the small amount of time we might save.

Duplicate scenarios:

Since we have three, one time use categories, we need to make sure we are not giving a customer more than once, however we have thousands of customer’s so we cannot just go look over every customer’s request and find certain categories every time. We built a custom query in TFS to pull all tickets where we approved a refund in those categories within the last two months. We then used the DevOps connector “Get Query Results” action to find our custom query and pulled from it.

This returns an array, so we need to use the “apply to each” action again. Inside the “apply to each” we need get the details of each ticket. For this we use the DevOps “Get Work Item Details”. After we have the details, we run a condition, this checks if the customer number on the new ticket is equal to the customer number in an old ticket and if the category is the same for that ticket.

If the conditions are met, we update the ticket, and you get one guess on what we do to the ticket… did you say, “Tag it”? Congrats, we tag it!

Currency Calculation:

This one was tricky to do and involves A LOT of string parsing and variable initialization. Here is the branch overview.

We won’t go through all the individual variables, but I will go over the FX Rate (Exchange Rate) and how we pull from a list and parse the value out. Inside of DevOps, we have a tab that is manually updated by the finance group. It is a long list of semicolon separated values for each of that month’s specific exchange rates for each currency. We will use the split expression function to split on a delimiter. The format of the list is:

USD-1; EUR-0.9;

So we need to split on the semicolon and then the dash. This is the expression we used.

float(split(split(split(variables(‘FX Rates For Current Month’),variables(‘currency value’))[1],’;’)[0],’-‘)[1])

It returns a float that corresponds to the currency that is used in the ticket and the month the ticket was created to then use for a local currency to USD calculation.

Once all the variables are created, we can update the TFS work item with our Tag, “BOT: FX”. All our branches are completed, if there are issues with any of these instances, they will be tagged for the team member to be made aware of and check.

So, let’s go to the final step.

Pre-Triage Tag:

Once all checks have been run, if the ticket passes all previous criteria it will get tagged as BOT: Triage. Signaling the system to send the ticket to its sister Flow where we triage tickets out to individual team members. First, we must determine that this is a new case and is not coming from certain teams already approved. To do this we will use the condition action. Setting the state of the ticket to only run when equal to new cases. Further, we consider certain other teams who are excluded from this action using the or function.

When the parameters for the condition have been set we use the update work item in Azure DevOps for both the yes and no condition trees. Both are, once again, only going to change the tags on the ticket which will later trigger the team triage process.

If the conditions are not met then the Flow will follow the no path and tag the ticket Bot: NA, this signals to the system that the appropriate conditions were not met and to not move forward with the triaging to team members.

If the correct conditions are met it will mark the issue as Bot: Triage, which gives the go ahead to the system to send the ticket to the triage Flow and distribute to the appropriate team member.

This is the end of our initial Flow, to recap, a ticket has been checked for our five initial problems and has been tagged as such to alert the team member of the problem. Further, if a TFS issue has met all the necessary criteria it is marked “Bot: Triage” signaling to the system it is ready to be sent to a team member.

Queue Triage:

This will be a separate Flow in which we take what was done in the previous Flow and distribute TFS tickets to individual team members. As a brief overview, this is what the formatting will look like when done correctly. Below we will dig into what all of this is doing and walk through the process.

First, we need to set up our trigger, we are using a scheduled recurrence to run this Flow. Every five minutes it will run and look for updated tickets with the necessary tag from our previous section. Once it sees a TFS ticket with the tag it will move on with the Flow.

We then built individual queries within TFS for each member of the team and the categories they own. A new feature within Azure Devops called “get query results” allows us to check these queries quickly and as team members come and go be more flexible.

When you have built your query, use the “apply to each” condition and select value as the output. This will allow you to access all data fields in the TFS query we have built.

After we have identified the correct query, we will need to add a separate condition that ensures the issue was tagged by the pre-check Flow talked about earlier.

Once the update condition has been met, we once again need to use the “update work” item in Azure Devops. This will finally send the TFS ticket to the correct team member with the ticket checked for any of our known issues before being received.

Conclusion:

Overall, Flow has helped us cut down our own triage processing times from over 6 hours to 1-2 minutes and given us the flexibility to focus on the core business projects.

We know this post will help you think differently about more creative ways Flow can be used to reduce manual work, speed up your business, and solve redundant problems you run into on a daily basis.

If you have any suggestions or questions, feel free to comment on this post.

We’d love to hear your feedback!

Brian and George

The post Advanced | Flow of the Week: Triage and Team Assignment with Microsoft Flow appeared first on Microsoft Power Platform Blog.