Populating complex Word template

[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

[DamoBird365]

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

[CCava]

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

[CCava]

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

[Pstork1]

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

[CCava]

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!

[Pstork1]

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.

[DamoBird365]

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

[Pstork1 · Accepted solution]

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.

[CCava]

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

[Pstork1]

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.

[CCava]

It is one file with a section for each project.  That's what I was trying to do - for each project, add only the titles that are part of that project.  Since the project titles and number of projects for each person are different, I need each of those to be dynamic. 

To check if a book is part of that project, I would need a list of all books in each project - but that is what I am trying to get.  Here's what I've tried:

Now, this does remove the duplicates from my PDF, but still shows all book titles, even if they aren't part of the project.  It keeps appending the varProjBookList for all projects in the array.

I tried "is equal to" instead of "contains" but that didn't work at all.

I know it has to be the logic that I am using that is not producing what I am trying to get - but I'm not sure what I am missing?

[Pstork1]

If you want the list of books for each project in a section with each project then it has to be inside the loop where you are processing each project.  You can't do it in a separate place or the list won't appear at the right point in the file even if you do filter the list of books.

[CCava]

That definitely helped a lot - moving the book list loop inside of where I was getting the other information.  The (hopefully) last issue I am having is that the variable will not clear after each round in the loop. 

I've tried using a condition to see if the variable is null (using the expression editor), checking if the length of the variable is = 0, and in each appending the variable to null (using the expression editor) - and then not using a condition but still appending the variable to be null, '',  "", or even a space.  I sue the compose step to see what is in the variable at various times - and this is happening

I've tried placing the append to null (blank, '', "") at various points in the flow (beginning, end, before appending) - it never quite works.

Is it possible to empty the string value between loops? Or is there another way to only show the active loop's data?

I truly appreciate all of your help - thank you!

Cheryl

[Pstork1]

The first actions in the outermost loop should be actions that set all the variables.  You can clear an array variable by using the set variable action and setting it to null.  You can clear a string variable with the same action and setting it to string('').

[CCava]

Thank you very much, @Pstork1! That was the last piece I needed to finish this - I would not have been able to navigate all the piece to this without your assistance - thank you again!

[Anonymous]

Hi there - could you please post your final flow to help out those who are trying to solve the same challenge?

Thanks!

[AlexEncodian]

@CCava 

If you want to avoid designing / maintaining HTML, then you can use Encodian's Populate Word Document action which supports repeating sections within repeating sections.

The template is plain text so you can put a <<foreach>> tag within another <<foreach>> tag to achieve this. Overview of the template syntax is here: Template Syntax for Populate Word Document