With Microsoft Flow overshadowing the SharePoint Designer (SPD) workflow, I, like most of the others, wanted to revisit my SPD tasks if I could achieve the same automated tasks as in the erstwhile application.
To be honest, being a non-programmer, the SPD to me was much more than an approval/rejection workflow mechanism. From modifying list values to iterating through each item of a list to driving a custom template-based document library from a list, I’ve enjoyed the company of SPD to mask my inability to create script plugins, yet smooth sailing through accomplishing my tasks.
Quickly jumping to the point, my blog on dynamically populating custom word documents for a custom list, saw a lot of visitors. With folks moving towards Flow, I attempted to achieve the same and hence my blog with the same goal, but a different Flow.
Where to start from?
Let’s start with a simple use case so that we understand better how this flow may help. Say, I have a product which might have a few variable properties that may keep changing over a period. I’ll take an example from our daily life say, a loaf of bread. Based on the departmental store discount or some special offer, there may be some offers running on a daily or weekly basis on this item with a certain item code.
Let’s list down what could be the variables in this case:
- Date of manufacture/packaging
I assume you should now be able to visualize amongst the above listed properties what kind of variations may happen – it could be 2 loaves at the price of 1, or just a discount on each loaf, the date of manufacture/packaging could vary rendering some near to expiry items on a sale, and lastly a description. I find managing versions (item change history) much easier handled in a document library. At the same time, I find changing item properties can be easier done in a list.
Hence, I create my first creating custom word document flow driven by a custom list.
How to start?
A first task is to identify the variables in the document that may undergo change in values. I’ve labelled it Task 0 because it’s a onetime activity to do similar flows. You could either trust me or check for yourself what I say is true on the below 2 constraints:
Go to Library Settings > Advanced Settings and Edit Template. The default template file is template.dotx, open it on local MS Word application and save in the same place as ‘./<library name>/Forms/<new template name>.docx’
Edit this template file again and go to Insert > Quick Parts > Document Property
Since I said this is a custom template, you could add header, footer and some static text wherever and however you wish to format the document template.
Library columns populated using Flow
The final set of column types that a flow could finally dynamically populate is the intersection of above two constraints.
It wasn’t difficult but a time-consuming process to first create one column of each column type in the document library and the driving list to check which columns can be dynamically populated, to conclude that much like my last blog using the SPD, only the following column types work:
- Single line of Text
- Multiple line of Text
- Date and Time
Note: With a few tests, I just discovered that the Multiple line of text works best as a Plain or Rich text in this case since
Enhanced rich text passes on HTML tags to the document with the Flow.
Once you’ve identified what all are the supported column types, create a mapping between the properties of the item and the column type.
For now, a very basic structure with of the supported column types fits well in the example I mentioned earlier. I could map the variables in a loaf of bread as below:
- Available Offer – Single line of Text
- Offer Description – Multiple line of Text
- Quantity – Number
- Price – Current
- Date of Packaging – Date and Time
- Date of Expiry
Customize the list and document library structure to contain those columns.
List Column Settings
Where Title could just be the item code that remains unique for each item
Document Library Column Settings
Modify the template in the document library from Library Settings > Advanced Settings > Edit Template and name it with a .docx extension in the same path.
In my example, my document library is Test Doc Lib, and my custom template file is template1.docx
Edit Template and format it – you could use color, font, images…just anything that you want in your static content!
Here, all grey text within square brackets are inserted from Quick Parts > Document Property and the text in black is the static text that should remain constant across all documents in the library as a part of the template.
Start your flow with a blank template to trigger a flow When an item is created or modified
Add the next step to create a document in the library as a copy of the library template file (./<library name>/Forms/<custom template name>).
Set the Overwrite Flag as Yes – I’ll explain this later.
Add the next action to the Flow to Get file metadata using path (choose the action with SharePoint icon against it from the list of available actions)
Each step of a Flow provides some Dynamic content to be used. Since this step is using the Name data from the previous action Copy File, expand to see dynamic content underneath when you click in the text box against File Path.
The final step is to update the properties of this file so that the placeholders from the custom document template get filled with real data.
The following screen shot is photo-shopped to show the different Dynamic content used from different actions in the flow.
As a final step, save and test the flow.
I’ve filled in one list item for you to see how it works.
When I check my document library, I see that 1 item is created.
On click of document Name, don’t be shocked to see nothing in your word document when you open in Word Online.
Instead, Edit In Word to open it with your local machine word application.
This is a perfect document the way I wanted it to be.
What else should I know?
As I’d promised in an earlier section, here is the explanation of how setting the Overwrite to Yes benefits us.
Not very common that items/products undergo a change in their unique code or name. Hence, in a case like a departmental store, where the same product can undergo frequent periodic changes, you do not want a new word document to be generated each time with a change.
In this case, one additional setting that you’d be needed to do with setting this flag as Yes is set versioning on the document library to Create major and minor (draft) versions, so that each change adds a new version to the same file.
However, since the Flow starts creating a document from a copy of the document library of template and then change its properties based on the list item changes, hence each time a change is made, if an old word document exists in the document library with the same name, a new version is added which is a copy of the template and then the document properties are modified in the next version of the same Flow.
Hence, if you check the Versions history of the document corresponding to any list item, it looks something similar as in the below screenshot.
I realize this is quite clumsy, however with the Create major and minor (draft) versions turned ON, when you open Versions History, you could quite easily click on Delete Minor Versions on the top and get rid of all those unwanted versions and your document looks neat and clean as in the following image.
I haven’t come across a better workaround than this, so if you readers want to try out this flow, do drop in your comments letting me know how you have improved it!