Create a Word brochure generator with Power Apps and Power Automate: Part One

I was asked by my Marketing colleagues whether I could build an application in SharePoint that would generate and store marketing brochures from Word templates and organise them by region and line of business.

In the old world of SharePoint Designer I would be able to do that quite easily, but as we're all aware, Microsoft started deprecating core SPD functions, like custom input forms, around the beginning of June 2020, leaving us SPD workers a bit stranded.

Though I haven't seen any posting about it anywhere, my work was further limited by being unable to connect Data View Web Parts to Data Sources in SPD. This may be an internal problem at my company, but the effects are just as real.

So, with no DVWPs, no custom forms in SPD and 2010 workflows being turned off in November 2020 I didn't have a lot to work with. My company was able to arrange a course for me on Power Platform and Power Automate, though these turned out to be quite entry-level and lacking the depth I'd need for this brochure project. But with the help of some more knowledgeable colleagues, I was able to find a way through and create quite an effective application.

How it works

Here's how the marketing team wanted the Brochure Generator to work.

1. The user creates a new document from a template
2. A form pops up for the User to add the customer's name, the date of the brochure as well as region, Line of Business and, behind the scenes, the name of the Supervising Manager. 
3. The user then adds the sales pitch into the Word document and saves and checks in. This initiates an automated approval process
4. The Supervising Manager receives an alert that a new brochure has been created. They review and approve (or not approve)
5. If approved, the system converts the Word doc to a PDF
6. The user receives notification of the manager's approval, with the PDF as an attachment, ready to send out to the customer
7. If not approved, the user makes amendments and saves and checks in and the Approval process kicks off again (repeat until approval obtained).

What you need

• A SharePoint Modern Experience Teamsite
• A Document Library (set to "Require documents to be checked out before they can be edited", in versioning settings)
• A Custom List with four columns - Region, Dept, Line of Business, Approving Manager
• One or more Word templates, configured with Quick Parts to hold the dynamic data
• Access to Power Apps and Power Automate
• Access to OneDrive

How I did it

I started with a Modern Experience Teamsite. You may be able to use an old-school Classic Teamsite, but I was told on my course that some Power Platform functions don't work properly on Teamsites that were created in Classic mode.

You don't have to have a nice-looking Landing Page, but it does give the whole thing more of a professional look, don't you think?

I believe all Teamsites come with some basic components, like a Document Library. In our site farm, they do, anyway. 

Navigate to Site Content Types, click on create and follow the onscreen prompts. If you need detailed instructions for this, a quick Goggle search will help.

My first task was to create custom Content Types to hold the metadata for the Documents in the Document Library. I think it's possible to use List/Library Columns in Content types, but why do that? Using Site Columns means the Content Types can be used with other Libraries in the site, something that saved me a bunch of work later, as you'll see. In addition, since we're planning to import list column values into the Word Document, I believe that the Quick Parts we'll be using for that only recognise Content Types and not List Columns.

You may want to use more than one template, so make sure you give your Content Types simple and descriptive names, so your users know what they're getting. 

So I got to work to create some Site Columns that would make up my first Content Type.

As an inexperienced Power Platform student, my first thought was to create Site Columns as Lookups. That's what we'd do if we were using SharePoint Designer, right? Then add a bit of JQuery to get the cascades to work? You don't do that with Power Apps.

I wasted a whole lot of time searching via Google to find a way to do cascading menus in Power Apps. I found several "solutions". None of them worked. That could have been my fault, but I don't think so. I'm fairly good at following instructions. The working solution came from my friend and colleague Ernani, who showed me how to do great drop-down cascades in Power Apps ... but I'll get to that later.

So, like I say, normally, I'd do Look-Up columns to achieve a cascading menu set. But in this case, we're going to Single Line of Text columns to hold the cascade info. Bear with me, it'll become plain.

This is what my finished Content Type looked like when I added all the necessary Site Columns.

Once you've added all the new Site Columns to the Content Type, navigate to the Library Settings and add the Content Type to your Library.

This is what it looks like with your Content Type in place. Note that I've used two different Content Types to handle two different templates - you can only attach one Word Template to a Content Type.

Now we can create the Custom List (I called mine "metadata") that will power the cascading drop-downs. The columns I used were:

• Geography (single line of text)
• Dept (single line of text)
• Line of Business (using the Title Column, single line of text)
• Approver (name field)

This is how I set my metadata list up. Make sure you use the same column types.

The final list looks like this, though obviously, I'm not showing all the rows here.

Here's a snapshot of the populated metadata list. I used the Title column for the unique "Line of Business" values, but it doesn't matter if you want to create a new column.

We'll use Power Apps to make the Custom List and the Document Library talk to each other.

This is what the Properties input form (EditForm.aspx) looks like at this stage of the build. Quite a few of the fields/Content Types are missing.

Even though the Content Type has been added to the Library and the Site Columns should be available, when you try to amend the Properties of an uploaded document, you don't get all the fields in the default Properties window, so the next thing I did was to start up PowerApps to create a custom input form. 

It's pretty easy to fire up Power Apps from your main Library. You may prefer to work from a Canvas App, but that's up to you.

I chose Customise Forms so that I had a basis to work on. Even so, what's available is a bit sparse. All I had on the EditForm stage was "Title". 

A bit of a blank canvas, really ... 

Before we can go any further, we'll need to add the custom list to the Power Apps form so that any custom field we add can pick up the data from the list. This is essential to get our cascades working. So, click on the Data icon in the very far left of your screen

Now click on Connectors and select SharePoint. Sign in as yourself, if prompted.

Over on the right hand side, you'll now see a list of all the SharePoint sites available to you. Click on the appropriate site to select it. 

The view now switches to display all the Libraries and Lists in the selected site. Choose the List to connect to, in my case, I chose the list "metadata" that holds my cascade options.

So, that done, we can now begin adding some more fields to get the cascades working. This is how it's done.

If you can't see the Fields panel, then click on the text link Edit Fields in the right hand panel. (I spent ages looking for this!)

This was a tricky little sucker to find, if you're new to Power Apps. If you get lost, just highlight the SharePointForm1 heading in the left hand column first, then on the Properties link in the right-hand column.

Once the Fields panel displays, you can click on the Add Fields link to grab more columns from your Doc Library and place them on the EditForm stage.

Once the field is on the stage, you can manipulate it as required. For example, you can access the ellipsis in the Field's panel and select the Move Up action to change the field's position on the stage. I'll be removing the Title field at some point in the process, but for now, I'll just move the "Geography_txt" field up to sit above the Title field.

I'm not going to make you sit through the entire process, but I'll do enough so that you can see how the cascade works. So the first thing to do is to make the text field for Geography_txt a little smaller, as we're going to squeeze another custom "Drop down" input in this "card". To do that, highlight the card by clicking in it, then select Insert tab at the top left of the page. Click on Input and select Drop down from the drop-down menu.

The new Drop down input should appear within your card. If it doesn't, then you didn't have your card selected correctly. Delete the rogue Drop down and try again. A successful insert should look like this.

You can resize the new Drop down field so it doesn't overlap the existing text box. Before you can make any changes to the new field you first have to unlock the card in the Advanced panel on the right.

Now you can start making changes. First, rename the label of the card from "Geography_txt" to "Region". Power Apps is picking up the label "Geography_txt" from the list. To change the text, we simply over-write the "Parent.DisplayName" call in the fx field with "Region" (include the quotes).

Next - and this is where it starts to get a bit more complex, we're going to change the underlying programming of the card so that the custom Drop down picks up data from the custom list "Metadata" and sends it to the "Geography_txt" text field.

Highlight the custom drop-down field by clicking on it. Click on the Advanced tab. Add this code into the Items window:

Distinct(metadata,Geography)

Now add this code to the Default window:

ThisItem.Geography_txt

Finally, to complete the sequence, you need to click the entire card to highlight it and then add this code:

Dropdown2.SelectedText.Result

... to the Update field in the Advanced section (you may have to click the More options button to see the Update field):

I'll leave it entirely up to you whether you rename the field "Dropdown2" to something a bit more friendly and descriptive. I didn't do that when I built my original application, but in hindsight, I probably should have. For this example, I'm renaming the field to "ddGeog". Even if I do it at this stage, Power Apps automatically updates the above formula to "ddGeog.SelectedText.Result".

Next, we have to add the dependent drop-down, "Department". So, same method as before. Click on the Add Fields link, and select "Dept_txt".

Highlight the card by clicking in it, then select Insert tab at the top left of the page. Click on Input and select Drop down from the drop-down menu. Highlight the custom drop-down field by clicking on it. Click on the Advanced tab. Add this code into the Items window:

Distinct(Filter(metadata,Geography =ddGeog.Selected.Result),Dept)

And add this code to the Update window:

ddDept.SelectedText.Value

I found as I went through adding these formulae that the Default field usually seems to default to the appropriate formula. In this case it would be "ThisItem.Dept_txt". But if it doesn't, you'll have to add it manually

At this point, it'd be a good idea to test the form and make sure your cascade is working as expected.

Right, final cascading drop-down for this form. But before we do, let's just hide that Title field, as it's getting in the way:

Now add the final cascading drop down in the same way. I've called my "LOB" (Line of Business), and changed the name of the field to ddLOB. Use these formulae to bind the drop-down to the main library:

Items: Distinct(Filter(metadata,Dept =ddDept.Selected.Result),Title)

Default: ThisItem.LOB_txt (usually filled in by default)

Update: ddLOB.Selected.Result

So that's the cascade sorted out. What I did was add another field - which I'll make invisible - to automatically select the Manager responsible for approving each request according to the Line of Business they are responsible for. This is neat, because it prevents users making mistakes and sending Approval requests to the wrong manager.

So we add another field, for "Approver" and add the following formulae:

Items: Distinct(Filter(metadata,Title =ddLOB.Selected.Result),Approver.Email)

Default: ThisItem.Approver_txt

Update: ddApprover.Selected.Result

It's a little trickier if you have a choice of two or more approvers, but I didn't have that requirement and won't be covering it here.

Test and make sure it works. You could hide this field now, but I would leave it visible so you know it's working. Plenty of time to hide it at the end of the build.

It'd probably be a good idea to Save at this point.

The rest of the fields are pretty straight forward to add, and will work fine out-of-the-box. These are:

• DocType (renamed as "Brochure" in the form)
• Client
• Due Date
• Revenue

One useful tip I have is to add a currency sign to the Revenue field. Simply change the Hint Text.

OK ... so we're all done on the Input Form. Time to Publish the form and test it in the Library.

Ready for the next bit? Well, you'll have to wait a few days for that. Pop back soon and find out how to:

• create the Word Template
• attach it to the Content Type
• then insert the Quick Parts that will hold the data or text from the Library columns
• ... and lots more.

Next: Word shenanigans and building the Approval Flow in Power Automate