Creating a Word document with project data

[#1]

First a client expressed this desire, then a community member asked about something similar so I wanted to see how/if this works. 

And it does!

Set up the template

I wanted to create a project overview page, so I just started with the fields I wanted to display on the page. Not an actual Word template but simply a docx file with placeholders for data. 

Open up MSWord (or an editor that writes docx files) and let's get started. Note the field names don't have to be related to the actual Workfront field - you set the template placeholder name and map it to the value you want to display (more on that later). For now, We Just write out what we want to see, and format it accordingly. Each placeholder is wrapped with double-curly braces.

Example:

In some cases, we don't want to have extra space or a label on the page if we don't have a value. We can use a rudimentary IF tag. you determine the tag name, and the start gets a pound sign, the end gets a slash: 

If you want to display a list of items, we create the start/end tags in the same way as the IF tags above. For example if I want to display a list of project users, I create a table with the following placeholders:

The ppl tag shows the "iterator" start and end, and inside I put the tags for name and jobRole.

My overall template looks like this, and because I can, I added a the WF logo as Watermark.

OK we're done with the easy part. We have a template that has placeholders for all the fields we want to see on the form, even allows for a list of projectUsers, next-up tasks and documented risks.

In my example I uploaded the template to my own Documents area by clicking on the main menu and selecting Documents. That means these documents are associated only with my user. You may want to store it elsewhere though!

Set up the Fusion scenario

For the purpose of this example, I start the scenario by setting a specific projectID - in the real world, you may do a search, or an event trigger that starts this scenario. 

Then I am going to read that project record. 

Based on the template I need the following project fields: 

actualCompletionDate,actualCost,actualDurationMinutes,actualRevenue,actualStartDate,actualWorkRequired,billedRevenue,budget,description,durationMinutes,name,percentComplete,plannedCompletionDate,plannedCost,plannedRevenue,plannedStartDate,priority,status,workRequired

So I can show names instead of IDs (e.g. owner), and populate the tables in the template I need some more data. 

owner:name,risks:*,projectUsers:*,projectUsers:user:userRoles:role:name,portfolio:name,program:name,group:name,company:name,risks:riskType:name,currency,tasks:plannedStartDate,tasks:name,tasks:canStart,tasks:work,tasks:plannedDuration,tasks:actualStartDate,tasks:status,allStatuses,allPriorities

In my case I didn't include custom fields but any field custom, or core, can be used.

We get the project's statuses and priorities so we can display human readable labels, not just a number. We map the list of allStatuses to get the labels, and we filter to the project's status field. That will give us an array of length 1. The same applies to the project's priority. We'll just set them here for later use.

Sadly same is not true for tasks or risks: The stored value is numeric. In the unsupported API the task object has a statusLabel property, but risks don't and a manual mapping is fine. We'll use a Switch module like so:

But wait - I get ahead of myself. A sidenote on scenario structure. I personally dislike scenarios that are a mile long - I'm impatient and lazy so any click-n-drag I can avoid is good. Instead I use routers to break up the flow. This may not be to your liking and that's OK. 
For me it helps me separate processing that you might otherwise put in a separate function/class. 

From the docs you can see that the "populate template" module requires the list data to be in a certain format:

The key is the placeholder name you used in the Word template. The value will be the actual value from the task or user, or risk.

For each of our lists (risks, next-up tasks, and projectUsers) we need to iterate through its records, and create an entry array that contains a name=value collection for each field of the record. 

I use a parseJSON module to create this entry object directly. One caveat: Since we're literally writing out the JSON as text, there's a possibility that this fails when a value contains a quote, or a line break. In this case I'm reasonably certain that none of these values are at risk; otherwise I could wrap a replace() function around the name, for example. 

The JSON module creates a variable called entry that is a list of key-value objects. In the aggregator we tell it to collect that entry variable for each respective list item (task, user, risk).

Finally, we set a variable so we can retrieve it when we're back on the "main" path. 

With all that pre-processing done, the main route puts the pieces together. First we retrieve the 3 list variables we set 

Then we download the "template" - that Word doc we uploaded earlier.

Next, we tell the Word module that populates the template which placeholder we want to map to what value. 

There are 3 types of values:

• Condition defines the tags for IF described above. They don't output any value. 
• Value is for a single value placeholder
• Loop if for list data. 

A note on the field values: a number of fields come with formatting that we would want to change. For example,

• dates look like this 2024-02-01T14:07:56.058Z
• numbers, eg. budget don't come formatted with thousands-separators: 320000
• etc

The Word module needs to get the data exactly as you want to see it in the document - there's no formatting like in Excel - every value comes out exactly like it went in.

The final step is the document upload - but it could be emailed, too. I chose to upload it to the project from which I created it. As name I used the project name plus a short version of today's date. 

After I download it and open it in Word I see this:

If you want to take this further, attached are the template, the example result document and a ZIP with the scenario blueprint.

Have fun! 

See the docs

example output.docx ‏41 KB

Project overview template.docx ‏49 KB

blueprint.zip ‏6 KB

[#2]

@Sven-iX 

Thank you for posting this detailed explanation!!  After a month of experimentation and outside help, I got this figured out.  For those that are familiar with json formats and array functions, this may be easy.  Fusion help only provides the highest level of info then *a miracle happens* and everything is supposed to work.  I appreciate you putting in the effort to explain and providing the blueprints.

TeamPPAK

[#3]

Very nice @Sven-iX,

When native Workfront Reports are insufficient, this is a good alternative to our Magic Reports solution, for those with Fusion (and time).

Regards,

Doug 

[#4]

Very nifty! Thanks for sharing

[#5]

Hi @Sven-iX ,
Thank you for this beautiful explanation.  One question though, when I go to define my upload module it is looking for a ID but my "Fill out a Document" module doesn't provide one.  If I email it I can see the template is getting filled out and created.

Any suggestions what I might have done wrong?

Thanks

Frank

[#6]

Hi

I want to update multiple word tables using excel data without any API because of limitation of license. Can you help how to achieve this.