Tested on:
Dynamics 365 CE version 9.1, PSA solution version 3.3, Unified Interface
There was a very interesting question on the Dynamics Community forums about updating Expense Estimates (which are really Estimate Lines related to an Estimate) on a Project based on approved Expense Entries. OOTB this is a bit challenging as Expense Entries are never related to a Project Task nor are they in any way related to Estimates or Estimate Lines.
Estimate Lines should be considered a high level estimate (notice the word “estimate” before the word “lines”). As approved Expense Entries lead to Actuals for concrete financial data, I do understand the need to at least compare these two concepts. I would normally encourage for Power BI to step in for reporting on these, however this time I simply wanted to see if we can easily update Estimate Lines based on our Expense Entries.
I don’t want to count the hours I spent figuring and testing this out but I eventually got my Flow and the whole scenario working as a “Minimum Viable Product”. Despite this being a very interesting learning scenario, I do lean towards recommending that Expense Entries are kept separate from Estimate Lines. But because the power of Flow makes building scenarios like these possible for a no-code consultant, I just had to give this a try.
Let’s see how I succeeded in pushing Expense Entries to a Project’s Estimate Lines. Before we jump in the Flow, there are some things that need to be considered:
- An Order (Project Contract) and a Project are mandatory when invoicing Expenses. The Flow covered on this blog post doesn’t work properly if an Order and a Project are not present.
- A Project should only have a single Estimate Line on which the combination of (Project) Task and (Transaction) Category are unique. Example: If there is an Estimate Line with a Task called Implementation and the Estimate Line’s Category is Travel, a second Estimate Line with that unique combination should not be created. Otherwise this Flow will cause cost and sales prices to be rolled up to both Estimate Lines.
- Despite countless tries, the Expense Estimates custom control grid CC_EstimatesGridControl didn’t consistently render correctly when new Estimate Lines were created. This is why the Flow covered on this blog post is only for updating Estimate Lines.
- There may be some extra actions (such as Get record) in this Flow due to it having been rebuilt several times to diagnose issues mentioned in the previous paragraph.
A Flow to update Estimate Lines
The Flow covered on this blog post can be downloaded from the TDG Power Platform Bank here. The Flow is provided “as is” and should be tested in a development environment before implementing it in a test and a production environment.
Scope 1 – Transaction Origins and Actuals
The Flow is fired off when the msdyn_approvedby field on a Project Approval record changes. The goal of this scope is to update Project Approval with a Transaction Category from an Actual related to the approval record. Transaction Category is needed later on in scope 3.1 to compare a Transaction Category of an Estimate Line with that of a Project Approval record.
As a Project Approval has no direct relationship to an Actual, a Transaction Origin is leveraged. A Transaction Origin with an Origin Type of msdyn_expense and a Transaction Type of msdyn_actual will point to the original Expense Entry record.
At the end of scope 1, there is a condition to check if a record is approved and if its Entry Type equals Expense. This condition has been build prior to Flow’s new condition builder and is thus built using the advanced mode. The expression for this condition is as follows:
@and(equals(triggerBody()?[‘msdyn_recordstage’], 2), equals(triggerBody()?[‘msdyn_entrytype’], 1))
Scope 2 – Getting Expense Entry and Expense Category
Scope 2 is fairly simple and straightforward. In this scope, the Expense Entry is fetched with the Get record action. This gives us access to the Expense Entry so the next step is to fetch the Expense Category related to the Expense Entry.
Scope 3 – Estimate Lines
If the thrill of looping through Transaction Origins wasn’t enough, this scope is where the fun starts. To determine if Estimate Lines related to a Project’s Project Tasks exist in PSA, a List records action with a Filter Query of _msdyn_task_value eq @{body(‘Get_Expense_Entry_record’)[‘_cust_projecttaskc_value’]} is used.
Next it’s time to validate if there are any records in PSA that the List records action has gotten for us. This can be done with a expression length(body(‘List_Estimate_Lines’)?[‘value’]) and by checking if it is greater than 0. Next we will cover the scenario where the condition results in a true. The false side is also covered towards the end of this blog post.
Scope 3.1 – Update Estimate Lines
In this scope the condition from the previous action resulted in true. This means that we have at least a single Estimate Line for the related Project and for one of its Project Tasks. It’s now time to update this/these Estimate Line(s) with values from the related Expense Entry (cost side of expenses) and Project Approval (sales side of expenses) records.
Apply to each is used as we’re looping through all Estimate Lines based on a Filter Query. A condition is used to check that the Transaction Category on an Estimate Line matches the one on Project Approval. If the condition results in a false, an email is sent to both the Expense Entry submitter as well as the Project Approver. As we are inside an Apply to each and we will have both Cost and Unbilled Sales Estimate Lines, two emails will be sent. Feel free to edit the Flow further if two sent emails bothers you.
The expression used in the send email action and displayed in image 6 is as follows:
concat(‘https://YourInstanceHere.crmX.dynamics.com/main.aspx?appid=YourAppIdHere&pagetype=entityrecord&etn=msdyn_projectapproval&id=’,triggerBody()?[‘msdyn_projectapprovalid’])
If the condition is met, a Switch is used and all existing Estimate Lines that match the Filter Query are updated. The value for the Switch is Transaction Type and it’s used from the previous Get Estimate Line record action. This way both Cost and Unbilled Sales Estimate Lines can be updated. The default case in the Switch should not occur. Nevertheless there is a send email action there just in case.
The fields that need to be updated on the update record action are Amount and Price. Updating the custom Expense Category field is optional. If you want the Amount and Price fields to change based on Quantity, then naturally Quantity needs to be updated as well. You may need to take this into account in the expressions for the Amount and Price fields. In this example, quantity is always 1. The expressions used for the fields are:
Unbilled Sales
Amount: add(triggerBody()?[‘msdyn_salesamount’],body(‘Get_Estimate_Line_record’)?[‘msdyn_amount’])
Price: add(triggerBody()?[‘msdyn_salesprice’],body(‘Get_Estimate_Line_record’)?[‘msdyn_price’])
Cost
Amount: add(body(‘Get_Expense_Entry_record’)[‘msdyn_amount’],body(‘Get_Estimate_Line_record’)?[‘msdyn_amount’])
Price: add(body(‘Get_Expense_Entry_record’)[‘msdyn_price’],body(‘Get_Estimate_Line_record’)?[‘msdyn_price’])
Values from the Project Approval record are added to the values on the Unbilled Sales Estimate Line. This is because the sales price of an Expense is set on Project Approval. For Cost, the values from the Expense Entry record are added to the values on the Cost Estimate Line.
Scope 3.2 – Notify submitter and Project Manager of missing Estimate Lines
If no existing Estimate Lines are found in PSA, a send email action will notify the Expense Entry submitter as well as the Project Approver of missing Estimate Lines. This scope is nearly identical with the send email action displayed in image 5. The only difference is that as the scope is not inside an Apply to each, it can be terminated as cancelled.
I hope this blog post gives you some ideas about how values can be passed in PSA’s end to end process. We can update Estimate Lines based on what our Expense Entry has been but this approach isn’t perfect. It should be carefully evaluated if a project’s estimates should change based on actual cost and revenue from Expense Entries and Project Approvals.
Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.
Hi Antti,
Recently I did some plugin work around Project and Estimate Lines. My requirement was to create Expense Estimates from a Project under another project (from a small project considered a CR into the so called Original Project). I also faced the problem of displaying the Estimate Lines under the Expense Estimates custom control.
I did some investigation and I realised PSA generates an “Estimate” record for every project you create. In order for the programmatically created Estimate lines to appear in that control, you must link them to the existing Estimate record.
There is one more catch: when you create Estimate lines, the system automatically generates a record in the entity called “Transaction Connection”. You must generate a record in this entity to connect the Estimate lines (unbilled sales and cost) otherwise you will not be able to delete the Expense Estimates you created.
I hope this will help somebody in the future.