Dynamics 365 Project Operations: Creating Invoice Attachments With Power Automate – Part I

Tested on Project Operations on Dataverse version 4.25.0.91 (October 2021)

Invoice attachments. That’s a conversation that gets my brain in an infinite loop. Creating invoice attachments has never been a very easy or straightforward task with the native tools available on Power Platform. Simple scenarios can be managed with Word templates or Excel templates but Dynamics 365 Project Operations related requirements are often too complex to solve with either of the two.

So what other options are there? There are actually quite a few different ways to create attachments from Dataverse data. Some of them are simpler than others, and personally for me, Power Automate with Word templates was a quick win. Some of the different options for creating attachments are:

• Power Automate with Word templates. My colleague MVP Timo Pertilä has written a really good walkthough about the solution we use at Forward Forever in his blog here (translate it to English with Google). It was easy to pick up this approach for ProjOps, based on Timo’s post.
• Paginated Reports.
• Fetch-based Reporting Services reports. Now Blue Badge Nick Doelman has a really good post about them here, and the fantastic MVP Megan Walker has a whole course about SSRS reports here.
• Office Scripts in Power Automate. MVP Paul Murana has an interesting blog post about them here.
• HTML in Power Automate. MVP Paul Murana has a video about this approach here, and MVP Terho Antila a blog post here.
• DocumentsCorePack.

An interesting option in the future could be the Electronic Invoicing service from F&O. While the feature seems to have been removed from the 2021 wave 2 plan as of September 30th 2021, we will hopefully see more about it in 2022 wave 1 plan.

Frequent Project Operations related attachment needs

Now that we’ve run through the different options for creating attachments, let’s look at Project Operations specific attachment needs. Some of the following needs also apply to core D365 Sales scenarios.

• Quote printouts and attachments.
• Order printouts and attachments.
• Invoice printouts and attachments. In the integrated deployment, these are normally produced from F&O. This blog post focuses on invoice attachments in a ProjOps Lite deployment.
• Time entry listings as invoice attachments. This will be the focus in part II.

By far the most frequent ask I hear is sending a listing of time entries with an invoice. What is actually required does vary from customer to customer, so I decided to write a two-part series: Part I covers invoice attachments that have an invoice’s Invoice Line Details (later referred to as ILDs in this blog post) in PDF file. Part II covers time entry lists for a given period, similar to my colleague Timo’s aforementioned post.

Invoice attachments based on Invoice Lines and Invoice Line Details

Before looking at Word templates or the flow for the invoice attachment solution, let’s consider the plethora of variables that need to be taken into consideration:

• Does an invoice have Invoice Lines for T&M, Fixed Price or products? Products aren’t supported in the solution we’re dissecting in this post.
• Are multiple different projects being invoiced on a single invoice? This option isn’t supported by the solution we’re dissecting in this post.
• Are T&M based ILDs Chargeable, Non Chargeable or Complimentary? This post focuses on Non Chargeable an Chargeable T&M ILDs.
• Do T&M based ILDs contain time, expense or material type transactions? This post focuses solely on time entries on T&M ILDs.
• Are FP based ILDs for Milestones Chargeable, Non Chargeable or Complimentary? This post focuses on Non Chargeable an Chargeable FP ILDs.

As we can see, there are lots of variables that need to be considered. I’ve probably forgotten some in my listing above so there could be even more variables to consider. Due to the complexity of invoices in ProjOps, using Power Automate to create attachments isn’t a fast solution to build. I guess I could say I learned this the hard way after I had started creating my flow. I realized that invoice attachments containing data from Invoice Lines and ILDs are probably best built using Paginated Reports or fetch-based RS reports. A simple list of time entries is different, as we’ll see in part II.

Word templates

Creating Word templates, from which invoice attachments are created, are pretty simple to create. Timo’s blog post covers creating Word templates in detail, so I’m not going spend much time writing about them. I advise to plan your attachments scenarios well in advance because specific scenarios do impact the way a flow need to be built, and how many Word templates are needed. Small things like the Word template action in flow not picking up any changes to templates without removing and re-adding the action can end up being time-consuming pieces in the big picture. ALM for Word templates can also be tricky. You might want to read this post by MVP Matt “MCJ” Collins-Jones.

For me the most time-consuming part with Word templates in Power Automate was envisioning what I really wanted to put on a Word template. I ended up creating a single template for scenarios including T&M and FP Invoice Lines, and realized that I’d need a fair amount of time if I wanted to accomplish scenarios seen in normal day-to-day business. Hence the solution is only complete for invoice attachments that contain both T&M and FP Invoice Lines.

Images 1 and 2 below show the Word template I’ve created for T&M and FP Invoice Line scenarios. Image 2 displays the Repeating Section Content Controls and Plain Text Content Controls, which are populated with data by using flow. While the templates might not be the prettiest you’ve seen, the point of this post is to show you how Word templates can be used with Power Automate, not how stunning templates are created.

Creating invoice attachments with flow

Let’s look at the flow that creates attachments as PDF documents based on the previously mentioned Word templates. Let’s first consider how such a flow should fire off. Normally, an invoice row’s command bar would be a good place. In this case, I’ve based the flow on a manual trigger as testing needs to be simple for invoices with T&M and Fixed Price Invoice Lines (later referred to as TMFP in this blog post), T&M Invoice Lines (later referred to as TM in this blog post), and Fixes Price Invoice Lines (later referred to as FP in this blog post).

Initial actions

After the trigger, a compose will hold today’s date and time, so that a timestamp can be attached to the resulting PDF file. This is more for testing purposes and is thus optional. Now comes the fun part. As the flow needs to loop through all ILDs of different Invoice Lines, array variables are used to hold arrays for the different options of chargeable T&M, non chargeable T&M, chargeable FP, and non chargeable FP. At this point you might already be realizing the eventual complexity that we’ll run into later. I found it helpful to name the properties in the arrays to match the Content Controls in my Word template.

After the variables are initialized, the invoice chosen in the trigger is retrieved. An account and an order row related to the invoice can also be retrieved, so that the eventual PDF document can be stored in an account and order specific folder in SharePoint. I did abandon this fine tuning in the end as I had already spent more time on the flow than I originally planned I would.

Parallel branches and a switch based on Invoice Lines

The next step is to loop through the Invoice Lines that are on the invoice chosen in the trigger. This flow doesn’t work for scenarios where there are several different projects on an invoice so if you have such a use case, you need to adjust the flow accordingly. Parallel branches separate scopes for both T&M and FP Invoice Lines. Let’s look at both of the branches separately.

T&M and Fixed Price Invoice Lines

On the T&M side, Invoice Lines with a Billing Method (msdyn_billingmethod) of Time and Material (192350000) are listed. A compose action checks whether or not the List TM Invoice Lines action returns anything. If it does, the compose named Compose List TM Invoice Lines result will hold a value of true. The compose named Compose TM or null will then hold TM, if the output of the previous compose is true. This way we’ll know that a Time and Material Invoice Line was returned. If the list rows actions is empty, the previously mentioned compose action won’t have a value at all. This indicates that a T&M Invoice Line was not returned by the list rows action. The expressions for both compose actions are:
not(equals(empty(outputs('List_TM_Invoice_Lines')?['body/value']), true))
if(equals(outputs('Compose_List_TM_Invoice_Lines_result'),true),'TM','')

The Fixed Price branch only differs from the T&M side in a couple of details. The list rows action on the FP side lists Invoice Lines with a Billing Method of Fixed Price (192350001). The compose actions also differ slightly. The Compose FP or null action produces a value of FP or no value at all, depending on what the list rows action returns.

The switch action

A switch action is used to determine the branch the flow will run in, after the parallel branches. If both a TM and an FP Invoice Line are present, the flow will run in the switch’s TMFP branch. If only a TM Invoice Line is present, the branch is TM, and if only an FP Invoice Lines is present, the branch is FP. As the entire flow is a fair amount of work to build, especially as a switch is used, I’ve only finished the TMFP branch for the sake of example. Next, we’ll dissect the contents of that branch.

TMFP branch in the switch

Let’s do a quick recap on how we ended in something called TMFP. We listed T&M and Fixed Price Invoice Lines. Both were found on an invoice so an attachment needs to include details from them both. A switch was used to determine how the flow should proceed, depending on the Invoice Lines found. In the case of both T&M and FP Invoice Lines, the switch runs in a branch named TMFP. It’s naturally short for Time and Material, Fixed Price.

As the flow runs in the TMFP branch, parallel branches inside TMFP are needed to process the logic more quickly. As we need to process both T&M and FP Invoice Lines, this branch in the switch was a bit time-consuming to build. The nice thing about parallel branching is that it allows us to split the logic neatly so that it’s easier to understand what’s going on in the flow. Both sides of the parallel branches are similar but there are some important differences. Let’s look at the T&M side of the parallel branches first.

T&M parallel branch – Non Chargeable transactions

This parallel branch processes the T&M based Invoice Line for Chargeable and Non Chargeable transactions, and the first part that we’ll look at is for Non Chargeable transactions. For the sake of example and brevity, Complimentary transactions are not processed. Support for Complimentary is possible by simply duplicating the steps of Non Chargeable transactions but for Complimentary transactions. The Word template would also need to be updated to support Complimentary transactions.

Looking at the Non Chargeable T&M transactions, the first action is a get row action based on the listed Invoice Lines in image 5. As only a single row is returned, the expression used is first(body('List_TM_Invoice_Lines')?['value'])?['invoicedetailid']. Another get row action is then used to get the project related to the Invoice Line.

The scope, which follows the actions, and is named TMFP – Scope for Non Chargeable TM ILDs, is where the fun starts. Invoice Line Details (later ILDs) hold the true details of an invoice. They are the evolution of the entire Time Entry -> Approval -> Actuals -> Invoicing process and essentially define what we are invoicing. As the Word template is used to sum up all Non Chargeable transactions, the total quantity of Non Chargeable hours from the ILDs needs to be summed up. I’ve used FetchXML in a list rows action to sum up all hours in the msdyn_quantity column by using the query below. Note the SumOfQuantity alias that I’ve used.

<fetch aggregate="true">
  <entity name="msdyn_invoicelinetransaction">
    <attribute name="msdyn_quantity" alias="SumOfQuantity" aggregate="sum" />
    <attribute name="msdyn_project" alias="Project" groupby="true" />
    <filter>
      <condition attribute="msdyn_invoicelineid" operator="eq" value="@{outputs('TMFP_-_Get_TM_Invoice_Line_row')?['body/invoicedetailid']}" />
      <condition attribute="msdyn_billingtype" operator="eq" value="192350000" />
    </filter>
  </entity>
</fetch>

A compose action with the following expression is then used to compose the value for SumOfQuantity : first(outputs('TMFP_-_List_Non_Chargeable_TM_Invoice_Line_Detail_rows')?['body/value'])['SumOfQuantity']. A format number action is then used to format the value to my region’s format. The final action before the apply to each in image 8 is to list all Non Chargeable Invoice Line Details again – this time without using FetchXML. Next, we’ll loop though those rows.

The final piece in the Non Chargeable TM ILDs scope is to loop through all relevant Non Chargeable ILDs. Get row actions for Project Tasks and Bookable Resources will let us fill the Word template with relevant values, as all ILDs can be traced to a project, a project task, a resource or even back to the very original time entry. I had a hard time using an expression with dynamic content for External Description so there’s a compose to hold the value of the aforementioned column. Another compose is used to format the msdyn_documentdate column so that it will be in a day/month/year format used in my region, and without a timestamp. The expression used is formatDateTime(items('TMFP_-_Apply_to_each_listed_Non_Chargeable_TM_ILD')?['msdyn_documentdate'],'dd.MM.yyyy').

The final action in the apply to each is an append to array variable. We’re appending to variable varNonChargeableTmIld, which is initialized in image 3. This var is later used in the Word template action so appending to it will eventually result in the Word template’s Repeating Section Content Control to display data on Word/PDF documents. The PersonNonChargeable property in the array’s JSON has the External Description column from the ILD table and the msdyn_subject column from the Project Task table coalesced, in case the value in either of the columns is null. The coalesce expressions used are:
coalesce(outputs('TMFP_-_External_Description_for_Non_Chargeable_TM_ILD'),'-') coalesce(outputs('TMFP_-_Get_Project_Task_row_for_Non_Chargeable_TM_ILD')?['body/msdyn_subject'],'-').

T&M parallel branch – Chargeable transactions

Let’s look at Chargeable transactions next. The idea is the same as with Non Chargeable transactions but there’s one notable difference. With Chargeable transactions, we need to account for both the sum total of hours (msdyn_quantity) as well as the sum total of ILD amounts (msdyn_amount). This is where FetchXML comes into play again. As seen in image 10 below, I’ve used the following fetch to aggregate the values.

<fetch aggregate="true">
  <entity name="msdyn_invoicelinetransaction">
    <attribute name="msdyn_quantity" alias="SumOfQuantity" aggregate="sum" />
    <attribute name="msdyn_amount" alias="TmIldAmount" aggregate="sum" />
    <attribute name="msdyn_project" alias="Project" groupby="true" />
    <filter>
      <condition attribute="msdyn_invoicelineid" operator="eq" value="@{outputs('TMFP_-_Get_TM_Invoice_Line_row')?['body/invoicedetailid']}" />
      <condition attribute="msdyn_billingtype" operator="eq" value="192350001" />
    </filter>
  </entity>
</fetch>

Compose actions then store the sum totals for quantity and amount and format number actions convert the outputs to a format that matches my region. A list rows action with an example of filtering rows kicks off an apply to each, where ILDs are looped through.

The apply to each for Chargeable ILDs is similar to the one for Non Chargeable ILDs. The variable at the end of the loop is different and is eventually used to populate the Word template with Chargeable ILDs.

FP parallel branch – Non Chargeable transactions

Now that the T&M branch is covered, it’s time for the Fixed Price branch. The logic is very similar to the the T&M branch but with Fixed Price there’s no quantity for Milestone ILDs. Non Chargeable Milestones don’t even need a sum total for amount so this entire branch is a bit more straightforward. A variable is again present at the end of the apply to each loop so that Non Chargeable Milestones can be populated to the Word template later on.

FP parallel branch – Chargeable transactions

The Chargeable Milestones are summed up with FetchXML so that the sum total of Milestones can be conveniently displayed on the Word template. Just like with Non Chargeable Milestones, quantity is not summed because the amount of Milestones per se is of little value on the Word template. Image 13 below displays the Chargeable Milestones section of the flow in its entirety. Again, the logic doesn’t change from the previously mentioned sections too much. The fetch used to aggregate the Milestones is:

<fetch aggregate="true">
  <entity name="msdyn_invoicelinetransaction">
    <attribute name="msdyn_amount" alias="FpIldAmount" aggregate="sum" />
    <attribute name="msdyn_project" alias="Project" groupby="true" />
    <filter>
      <condition attribute="msdyn_invoicelineid" operator="eq" value="@{outputs('TMFP_-_Get_FP_Invoice_Line_row')?['body/invoicedetailid']}" />
      <condition attribute="msdyn_billingtype" operator="eq" value="192350001" />
    </filter>
  </entity>
</fetch>

Word template and attachment creation

The final scope in the flow. In this scope the Word template is populated and the actual attachment document is created. Lesson learned here was that if the layout of the Word template stored in SharePoint changes, the entire Populate a Word template action has to be removed an re-added. All the dynamic content naturally goes away so I ended up clicking them back quite a few times.

The actions in this scope are pretty simple. Word template is populated, a Word document is created in SharePoint, the Word document is converted to PDF, and the PDF is stored in SharePoint. Like I stated in the beginning, the output is as pretty as one’s eye for template design, as we can see in image 15.

With the attachment created, we’re finally done! If you want to take the flow out for a spin, feel free to download it from my GitHub repo here. I’ve made it available for the community “as is”. In part II we’ll look at a simpler approach of listing time entries as an attachment.

Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.