Dynamics 365 Project Operations: Importing time entries, Part I

Tested on:
Project Operations solution version 4.4.0.70 (UR 4), CE-only deployment

Time entries play an important role in Dynamics 365 Project Operations. They are instrumental in the creation of business transactions as both the cost and sales components of Actuals are created based on approved time entries. Sometimes organizations have a time tracking system that they prefer to keep, instead of implementing time tracking in ProjOps. In these scenarios, time entries from a 3rd party system need to be imported to ProjOps, so that the required business transactions are created.

This blog post focuses on importing a batch of time entries to ProjOps so that business transactions are created for tracking a project’s financials. A follow-up to this blog post will cover recalling those time entries in cases where the import has to be rolled back.

Importing time entries

The process of getting time entries from a 3rd party system to ProjOps naturally varies from customer to customer. Time entries could be brought in with an Excel import, Dataflows, or a custom integration. The technologies used to bring them into ProjOps are beside the point. What’s important is that when time entry records from a 3rd party system are in ProjOps, out-of-the-box logic has to be fired off so that all required business transactions are created.

While in the past in PSA it was possible to import time entries with an Entry Status of Approved, this isn’t possible anymore in ProjOps. It hasn’t been possible in PSA either for a while. I’ve not kept tabs on when exactly this changed as projects, where PSA/ProjOps would not be used for time tracking, have been almost non-existent for me. This naturally means that we have to find a way to fire off the OOTB logic so that business transactions are created.

From Draft to Submitted to Approved

On a high level the task of firing off the OOTB logic sounds very simple: Import time entries into ProjOps as Draft, change their Entry Status to Submitted, and then to Approved. When the Entry Status of a time entry changes, the OOTB logic creates all the business transactions that are needed. There’s nothing else that needs to be done to get Journal Line, Project Approval, and Actual records in ProjOps.

Getting records into ProjOps

My approach to getting time entry records to ProjOps is a good old Excel import. I don’t have an actual 3rd party time tracking system so a custom integration is out of the question. I did try Dataflows but for some reason, I don’t seem to get along with that tool. No matter what I tried, I couldn’t get my Dataflow to work in my tenant. It did in another one but that missed everything else I would have needed for my tests. So Excel it is.

A custom view and a column I’ve created help keep track of import batches. A column for import batches also helps in identifying time entries and approvals in cases where an imported batch has to be recalled and rolled back. It’s also easy to create an Excel import template for time entries based on the custom view. Imports can be view from Data Management on the classic web client or from PPAC.

From Draft to Submitted and Approved

This is where things get tricky. I’ve specifically wanted to try imports with volumes no less than 10 000 records. My thinking is – and this is very rough math – that if a person submits time for three different projects/activities every day for a week, 10 000 entries could reflect time entries of a company with roughly 650 employees. Rough math but something to compare 10k weekly entries with.

Classic workflows – strike 1

Now that we’ve covered importing 10k time entry records to ProjOps, the next task is to change their status from Draft to Submitted and then to Approved. This sounds pretty simple but in reality, it isn’t. As a non-developer, my tool of choice for this job is naturally Power Automate. Classic workflows won’t keep up when there are that “many” records to work on. I tried a couple of import batches with classic async workflows and they resulted in thousands of errors when a workflow was fired off on create of records in D365. Strike 1 and let’s cross classic async workflows off the list.

Power Automate with CDS CE trigger – strike 2

Time to look at Power Automate to get the job done. As our batch size is at a minimum 10k records, it’s worth considering consumption limits. MVP Jerry Weinstock has written a really good article about them and I warmly recommend you read through it here. As we’re talking about 10k records with the number of API calls possibly being tens of thousands (there’s no easy way to really count how many API calls a Flow uses), it’s safe to deduce that the optimal solution is to use a service principal. When a Flow is owned by a service principal, the consumption of API calls will be taken from a tenant’s base capacity of 100 000 API calls per 24 h. This all means that such a Flow has to use the CDS CE trigger.

As the thinking in this approach is to use the CDS CE trigger, we need to think of a way to fire the Flow off. The most logical option is to fire it off when a record is created in D365. I ran tests for several import batches and unfortunately, I could not find a way to make my Flow work reliably. Two consistent problems I ran to were:

1. Firing off 10 000 Flows without concurrency control from created records will cause Flow to throttle. Once it does, the result will be an X number of approved time entries in ProjOps and a Y number of time entries with an Entry Status of Draft. Definitely not an acceptable outcome. Whatever is imported must go through without any additional tricks needed. Throttling is expected behavior though. It can be overcome by setting concurrency control on a Flow’s trigger. I did just this and set it to 10. This gave me a few good test imports, before I started seeing problem number 2.
2. Firing off 10 000 Flows will eventually cause a problem where the trigger simply doesn’t always fire off. This is something I spent several days trying to figure out. I even created two different Flows that each handle different chunks of a 10k import batch. As I’m writing this post, I’m still not entirely sure if this is intended behavior or not. So how do we know how many Flows never fired off? When we go to System Jobs in the classic web client and filter System Job Type based on Flow Notification, a list of all the Flows that didn’t fire off is displayed. It’s possible to resume waiting jobs but at least a few always seem to fail. At that point, it seems to be game over. Strike 2 and let’s cross Power Automate with CDS CE trigger off the list – for now at least.

Power Automate with a manual trigger – success

As the CDS CE trigger doesn’t provide consistent successful results, it’s time to abandon the idea of processing time entries on create. I’m not a fan of having to go to D365 and manually fire off Flows because in a real-life scenario, time entries would most likely be integrated from a 3rd party system to ProjOps and logic should fire off automatically. However since Power Automate’s CDE CE trigger seems to be unable to handle the requirement, we need to look at a manual trigger. After spending countless hours banging my head against the wall, I’m happy to say using a manual trigger provides consistent successful runs. Let’s dissect the Flow.

The Flow to submit and approve time entries

The Flow is triggered manually and to get any time entries from draft to submitted and approved, a value for import batch has to be given. If the Flow for an import batch has already been run and some time entries are returned, the Flow can be rerun to resubmit such time entries.

A list records action is used next to list all time entries that match the submitted batch. If returned time entries are resubmitted, the Flow runs on the Yes side of the condition. That will return both draft and returned time entries. A select query is used to limit the properties returned in both list records actions. Sometimes Flows may fail if returned properties are not limited and a lot of records are being returned.

The next two actions are used to update time entries. The first action updates a time entry’s Entry Status to Submitted and the second one to Approved. The update to Approved is where things get interesting. In PSA and ProjOps, a time entry can only be approved when the approver is on the related project’s project team and the Project Approver (msdyn_projectapprover) column (previously known as field) has a value of true. The connection reference used in the Flow’s update record action will define the user in whose context the approval will happen. If the action has me as the connection reference and I’m not set as a project approver on the related project, the Flow will fail. To overcome this, I’ve decided to use a universal project approver that will approve all imported time entries. That approver is a service principal application user. I’ve created a Bookable Resource record for the service principal and I’ve added it as a project approver on all relevant projects.

The action that updates the record to Approved also has an expression to set the Target Entry Status column to null as when time entries are submitted manually, the Target Entry Status column is null. Unless this is done with an expression in Flow, the column will have a value. This more a cosmetic thing but it bothered me so I added an expression. , despite it consuming an API call (yes, using an expression consumes an API call!). If you want to save 10 000 API calls when looping through 10k records, skip the expression.
Edit: This turned out to be a misunderstanding and the expression in the action does not consume an API call. Only the action does.

This is all that is really needed to build a Flow that submits and approves time entries. But what if the approved batch of entries needs to be rolled back? I’m covering such a Flow in an upcoming blog post and in light of that, the import batch identifier needs to be stored on project approval records for the imported batch. Even if a rollback would never be necessary, updating the approval records is still a good idea so that it’s easy to track which approvals have resulted from an import.

To get our hands to the project approval record that is created when a time entry is set as Approved, a list records action is needed. Select query is used to limit the properties returned. The next get records action has an expression that gives us access to the first returned record from the list records action. The expand query seen in the get records action is part of some tests I made for changing the owner of the record. I’ve left it in as an example of an expand query. The final step is to update the project approval record with a value for import batch from the trigger.

Stay tuned for part II, which covers rolling back imported time entries. A link to an unmanaged solution that contains the Flow from this post can be found here. If the issues with the CDE CE trigger are resolved, I’ll also update this post with the latest news.

Edits:
Updated on December 1st 2020. Expressions in the described scenario do not consume API calls.

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