Unanswered

Populating complex Word template

(0) Share

Report

Posted on by CCava

Hello,

I have been working on trying to populate a complex Word template with data from several SharePoint lists, but am running into difficulty with repeating rows inside of a larger item. I'm not sure if this is a limitation within Word or Power Automate, but I think my issue is within Power Automate, so I am sharing all info. In Power Automate, the Populate a Word Template does not work within a For each loop or any other control. As long as the final file is a PDF, I do not have to use Word, if there is another option.

For a singular project, I can populate: Title (single line of text), Description (multiple lines of text), Product List (repeating rows of "Product" and "Status"), plus more of the same.

What I cannot do is repeat all the items listed above for multiple projects within the same Word doc: having Title, Description and Product List for Title1, Title2, etc.

Examples

Can do

Can't Do

Title 1

Description 1

Products

Product A, Completed

Product B, In process

Product C, Not started

Title 1

Description 1

Products

Product A, Completed

Product B, In process

Product C, Not started

Title 2

Description 2

Products

Product A, Completed

Product B, Completed

Product C, In process

I have tried creating an HTML document then replacing the fields just like in Word, where [title] is replaced by TitleName - and this works until I need to repeat for all projects. I have also tried writing my own JSON (which has been validated), and it works again until the whole project needs to be repeated for all projects in the SharePoint list.

I'm not sure what specific information you all need to help. I have searched online for similar items and tried to modify Flows from Reza Dorrani , Laura Rogers and many more. Laura has a video on looping through a task list - which is great, but I need more than just the repeating rows and I need it all in one file, emails and not multiple files.

Thanks,

Cheryl

Categories:

Building flows

I have the same question (0)

All responses (16)

Answers (1)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @CCava

I've done an article on creating a dynamic pdf document without using the premium word connector on my blog but it is a faff vs the permium connector so if you have that as an option, let's keep that option for now.

The sample data you have provided:

Title 1
Description 1
Products
Product A, Completed
Product B, In process
Product C, Not started

Title 2
Description 2
Products
Product A, Completed
Product B, Completed
Product C, In process

Is that all dynamic? You could simply have 1 quick part field in your word document and do the data prep in Power Automate, would that work? For instance if I stuck the above in a compose and then called it to the word action, it would complete the file exactly per above, no need to have multiple quick parts, unless there should be some formatting between, but even then, you could create all of this in PowerAutomate.

Alternatively you can convert HTML to PDF via the OneDrive Connector but it's not very realiable.

If the above works for your great, alternatively can you provide a bit more info, like a screenshot of the required Word Doc and the data source(s).

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No

CCava

54 on at

Like (0)

Report

Hi @DamoBird365,

Thanks for your reply. Most of the content is dynamic, aside from "Products" in this case, which is just standard text.

When you say to stick it in a Compose, could you give more information. I have used compose before for a few things such as replacing text - is that what you are thinking of? And sticking that in a for each loop for all titles in the list?

I have a lot of fields and lists in my data (all SharePoint lists), but it essentially boils down to what I'm showing below. I do also have fields (title, quarter, and year) that I already filter the Get Items action using the filter query with an ODATA query - this portion is successful on my end, so I did not include it in my original request, but will here, in case it's needed/useful. Data is populated through a Power App.

Report Request List (when a new item is created here, it triggers the flow)

User	Quarter	Year	Created Timestamp
User 1	1	2021	[auto-generated]
User 2	1	2021	[auto-generated]

Projects List (I need to filter this list by the User field to match the User who initiated the request in the list above to get only their projects)

User	ProjectTitle	Description	AnotherField	AnotherField2	AnotherField3
User 1	Title 1	Lorem ipsum 1
User 2	Title 2	Lorem ipsum 2
User 1	Title 3	Lorem Ipsum 3

Products List (I need to filter this list 1st for the User who requested the report, then batch their products/status together)

Title	User	Product	Status	AnotherField
Title 1	User 1	Product A	In process
Title 2	User 2	Product C	Completed
Title 1	User 1	Product B	Completed
Title 3	User 1	Product B	Not started
Title 1	User 1	Product C	In process
Title 2	User 2	Product A	In process
Title 4	User 2	Product A	In process

The Word template would look like this

Notes

Anything in '{}' is a plain text user control (I use the curly braces to easily and quickly identify the fields)
The products list is a table with no borders
Product Type and Product Status have their own plain text control, plus are together in a repeating section control
I've tried to put everything for the project together in a control, in case that would help, but have been unsuccessful

I really appreciate your help on this - I've been working on this for a few weeks, on and off, but still a few weeks. Thanks again!

Cheryl

Was this reply helpful? Yes No

CCava 54 on at

Like (0)

Report

Hi again @DamoBird365,

I'm still having difficulties with this. I've looked into the Quick Parts, but am still not sure on how to repeat the entire Quick Part within the same file.

@Pstork1and @ScottShearer : I've either taken a SharePoint Virtual Workshop with you and/or seen you present at SharePoint fest (all wonderful!) - do either of you have suggestions on this?

Thanks!

Cheryl

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (2)

Report

I've done something similar for creating an Invoice with repeating sections in HTML and then converting to PDF. The key is to divide the HTML up into fragments. Then fill in each fragment. Finally concatenate all the fragments together and convert them to a PDF. For the Apply to each do the following:

1) have one HTML fragment that is the template

2) at the start of the loop copy the template to another variable

3) replace the values in the variable for that iteration

4) append the variable to the main document. Loop and repeat at step 2

5) after exiting the loop append a variable to the document to close out the HTML

Was this reply helpful? Yes No
CCava 54 on at

Like (0)

Report

Thanks, @Pstork1! I will give this a try and if I am successful, I will mark it as the solution. I appreciate your quick response!

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

Let me know if any of the steps aren't clear. I can provide some screenshots of the example I mentioned if that will help.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Pstork1

I'm interested 😂. I've played with HTML to PDF in OneDrive and found the conversion hit and miss and had better luck with Word or Excel to PDF. If there is a trick to getting the format correct I would appreciate you sharing the tip.

Cheers,

Damien

Was this reply helpful? Yes No
Verified answer

Pstork1 68,697 Most Valuable Professional on at

Like (3)

Report

There isn't really a trick. Its a bit tougher because you are working with RAW HTML. But I've hand coded enough web sites over the years in notepad. Its the same thing. Here's screenshots of the flow.

1) Start the flow and get Base64 code for the two images I use in the HTML.

2) Create a bunch of other variables to hold different HTML fragments.

The top of the document must have this HTML

The rest of the variables are HTML building blocks. For example, here's the HTML fragment that starts the <Body> tag. Dynamic content is applied where needed. This header will only be used once in the document.

3) Now that we have the building blocks for the top and bottom of the document its time to buildout the detail lines. I create a loop counter so I can alternate the background colors of the rows to make them more readable. Then process each row using an Apply to each loop.

Odd versus even rows have slightly different styles but are otherwise identical. Here's what an odd row looks like. This inserts the dynamic content for that row and appends it to the Table Header variable.

4) Once all the rows are processed I close out the HTML table that I've been appending rows to. Then I concatenate the various fragments together, create the HTML file in OneDrive, and Convert it to PDF in memory, and save the PDF file to OneDrive.

Was this reply helpful? Yes No
CCava 54 on at

Like (0)

Report

Hi, @Pstork1 ,

Sorry it has taken me so long to get back on this - other higher priority projects came up. This has mostly helped (I've been able to successfully add most parts into the string variables, concatenate them, create the file and view it) , but I am still not successful with one part of my request though: filtering a list for just the project listed.

In this report, I will have multiple projects that each have repeating rows sections (see example 1 below, data will match the screenshots for ease - please excuse my weird test data) - which I can get with what I created from your suggestions, but the data for the repeating rows section within each project is listing all data for all projects, not just the project it's part of (see image Results), so I tried to get the data a different way which somewhat works.

Example 1
Report for Cheryl C (will have a report for multiple researchers)
ABC Reading Rainbow, active
Some description of the project
Staff on the project

Book List
Book Title Book Author
My Little Pony Cool author
Strawberry Shortcake Another author

Outcomes List
Item Due
Write paper Jul 1
Write book Oct 31

123 Math Stories, active
Some description of the project
Staff on the project

Book List
Book Title Book Author
The little engine that could Rad person
Best math stories ever Math teacher
Bedtime math ParentsR Us

Outcomes List
Item Due
Edit paper Jul 15
Edit book Oct 31

Results

My Flow

Close up on appending book list variables which is the part not working how I want

I tried filtering the data through Parse JSON then using Select to show only the fields I need then using Create HTML table - it still always lists the entire list, not just for that project. Project title is the first column in the lists:

I'd appreciate any help or insight you (or anyone else) can give into this - Thanks!

Cheryl

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

Is the output of the flow one file with a section for each project or one file per project? It sounds like its one section per project. If that is the case I don't think you want the double loop where you are looping through the projects. I think what you want at that point is a condition that checks each book to see if it is part of that project. If it is add it, if it isn't skip it. That should then be part of the overall project loop so you are appending each book list to the project as you are processing the project.

Was this reply helpful? Yes No