Building a #PowerApps app for #MSProject data Part 3 #SharePoint #PowerPlatform #PowerAutomate #LessModeMorePower #NoCode #CDS #Office365

In part 3 we continue looking at the Power Automate Flow that is used to sync the data to the Power Apps Common Data Service (CDS) entities. If you missed part 2 it can be found here: https://pwmather.wordpress.com/2020/04/25/building-a-powerapps-app-for-msproject-data-part-2-sharepoint-powerplatform-powerautomate-lessmodemorepower-nocode-cds-office365/

Continuing where we left off in part 2, I have simplified the flow a lot as I found a way in my flow to get UPSERT to work for the Common Data Service “Update a record” action. An updated flow summary can be seen below:

Looking at the details – the flow is still triggered on a schedule then the next two actions are to initialise some variables:

These are used later in the flow where we trim off the opening and closing curly braces from one of the values returned from SharePoint – you will see this further on. We then have the “GetAllProjectSites” SharePoint HTTP action:

The process does assume that the projects to add are all synced with SharePoint project sites in the same site collection in Office 365 – add that site collection into the Site Address property. In the Uri property the REST query is added that returns the site title and site url for all sites that have a web template of PROJECTSITE. The data returned from that action is passed into an Apply to each action that I have renamed to “ForEachProjectSite” as seen below using the expression outlined in red:

Inside that loop there is another SharePoint HTTP action which is renamed to “GetProjectSiteTop1TaskForProjectGUID” – this is to return just 1 task from each project site. This is used to get the SyncedProjectGUID from the task item so that this can be used later as the MPP Project record identifier. The Site Address property is passed in from the items in the “ForEachProjectSite” loop as seen outlined in red below. The Uri is a REST query to return just 1 task from the project site but only return the SyncedProjectGUID property:

The next action inside the “ForEachProjectSite” loop is another apply to each action, this one I have renamed to “ForEachProject”. The data from the “GetProjectSiteTop1TaskForProjectGUID” action is passed in:

We then set the projectGuidInitial variable by passing in the SyncedProjectGUID from the “ForEachProject” loop:

The SyncedProjectGUID contains curly braces around the GUID but I’m using the SyncedProjectGUID as the identifier in the MPP Projects entity so the braces need to be removed which is where the next two “compose” data operations come in. The first one using this expression to remove the opening brace:

Then the second compose action uses this expression to remove the closing brace – notice the firstRemove compose output is used in the expression:

Next the projectGUID variable is set using the output from the lastRemove compose action – which is the cleansed SyncedProjectGUID:

The Flow then uses a Common Data Service (CDS) Update a record action to create or update the project records – this is renamed to “UpdateCreateProjectRecords-Upsert”. If the record exists it is updated, if it doesn’t exist the record is created:

The record identifier is the projectGuid variable which has the value of the cleansed SyncedProjectGUID. The Project Name is the project site Title value passed in from the “ForEachProjectSite” action using an expression outlined in the image above. The Task List URL is set using another expression to concatenate /List/Tasks with Project Site URL as seen below:

Another SharePoint HTTP action renamed to “GetProjetSiteTasks” is used to get the tasks for each project site from the task lists. The Site Address property is passed in from the items in the “ForEachProjectSite” loop as seen outlined in red below:

Then the final apply to each loop, renamed to “ForEachTask”, passing in the task data from the previous “GetProjectSiteTasks” SharePoint HTTP action:

The final action in this flow is another CDS Update a record action, renamed to “UpdateCreateTaskRecords-Upsert”, to create or update the task records just like the project records are updated / created:

Outlined above is the property used as the record identifier, this is the task GUID from the Task list – it is passed in from the “ForEachTask” loop. This is the same for the following fields but mapped to different SharePoint columns:

• Task Name – mapped to Title
• Due Date – mapped to DueDate
• Percent Complete – mapped to PercentComplete
• SharePoint Item Id – mapped to Id
• Start Date – mapped to StartDate
• Status – mapped to Status
• TaskGUIDSharePoint – mapped to GUID
• TaskUIDSharePoint – mapped to TaskUID

The SyncedProjectGUID is set using the projectGuid variable and the Related Project field is set using the output from the previous “UpdateCreateProjectRecords-Upsert” action:

That is it, as you have seen there are quite a few actions in the flow to handle syncing the projects and project tasks but it is quite straightforward.

That’s the Power Automate Flow covered, there are lots changes I would do before this was used in production but for a prototype it works great. Items you would need to look at if you wanted to run this is production would be things like:

• handle deleted tasks – when tasks are a deleted from the Microsoft project plans / the SharePoint Task lists, the Flow should remove those from the CDS or at least maybe flag them as deleted
• only update tasks in the CDS for Project tasks that have been modified since the last flow run – make the flow more efficient
• handle SharePoint REST API paging for more than 100 list items in the SharePoint HTTP actions

I will look to publish this flow in a future post so that it can be downloaded if anyone wanted it.

Next up in part 4 we will look at adding some simple features into the Power App.