ACE360 Connect DocumentationEPA Components Report
This example will show you how to use Microsoft Power Automate to automatically get all paginated apprentices and their component attempts with grades and build an Excel report.
At the end of this example you should be able to create your own scheduled Power Automate flow to query the ACE360 Connect API, get apprentice data, an apprentice's corresponding TPO, Standard and Components data and add that to an Excel report. This is just an example feel free to try different things and pick data that would be relevant to you.
Contents
Getting Started
Before you can create a Power Automate flow, you'll need access Power Automate itself. It is included with some Office 365 licenses, so if you're already an Office 365 customer check to see if access is included. Otherwise visit here and sign up. Keep in mind that this guide makes use of premium Power Automate features, if you don't have access to Power Automate Pro, you won't be able to follow along.
Your Flow
Once your account is set up and you've logged in you should see something similar to this:
This dashboard makes things look pretty complicated. There's a lot going on! In this demo, we're only going to focus on setting up a single automated flow. We'll cover some of the other features and options in later demos.
Configuring Your Flow
Click "Create" from the navigation bar on the left and under the start from blank heading, select "Scheduled cloud flow".
Add a name for your flow, for this example we'll use "Apprentice EPA Components". (If you skip this step, Power Automate will generate a name for you)
Set the date, time, and the frequency you'd like this flow to run at. For this example we'll go with every week, on a monday, at 10:00 AM. Then at the bottom of the dialog click "Create".
Getting Apprentice Data
To handle pagination, you need to initialize a variable that will be incremented for every page.
Note: ACE360 Connect returns paginated responses. This means we limit the amount of data returned in each request. To get all of your data you'll need to request each page of results.
Select "New Action" or the plus icon to add a new step to your flow.
Search for "variable" and select the "Initialize variable" option under the Variable heading.
Fill out the Name, Type and Value of the variable as follows.
The name of the card can also be edited for better readability, especially when there are multiple variables that need to be declared. In this case "Initialize variable" is changed to "ApprenticePageNumber".
To increment the initialized variable for every page retrieved, another variable must be created to denote how much to increment by.
Select "New Action", search for "increment" and select the "Increment variable" option under the Variable heading.
Under the name dropdown, select the initialized variable "ApprenticePageNumber" defined previously and fill in the value as 1.
To request data from Connect you'll need to select "New Action", search for "http" and select the "HTTP" option under the HTTP heading.
Fill out the HTTP step as follows. You'll need a method, URI, and some headers. In the form under the HTTP section, fill out the URI, Method and Headers as follows.
Be sure to enter your own token like so "Bearer 2|abcd...." (don't include the quotes).
You also need to set the page to the previously defined initialized variable "Apprentice Page Number" and set how many apprentices you need perPage.
Before you can work with your data, you'll need to do a bit of processing. This involves selecting the data that the API has sent us, and parsing that response so we can manipulate it. Don't worry, Power Automate can do this for us.
Select "New Action", search for "json" and select the "Parse JSON" option under the Data Operation heading.
You need to tell the JSON parser to collect the data or "body" from the Http request.
TIP: Use the little lightning bolt to select available options.
You'll also need to generate a schema for it to interpret the data. Click the link under the big empty box that says "Use sample payload to generate schema" and paste the following into the window that will have opened.
{
"total": null,
"currentPage": null,
"perPage": null,
"totalPages": null,
"data": [
{
"id": null,
"ulnNumber": null,
"firstName": null,
"lastName": null,
"dateOfBirth": null,
"standardId": null,
"status": {
"id": null,
"text": null
},
"tpoId": null,
"epaoRegisteredDate": null,
"estimatedStartDate": null,
"estimatedEndDate": null,
"esfaCertificateClaimedAt": null,
"estimatedGatewayAtEpaoRegistration": null,
"approvedForEpaDate": null,
"provisionalGrade": null,
"epaOverallGrade": null,
"gradeDate": null,
"createdAt": null,
"archivedAt": null,
"withdrawnAt": null,
"archivalStatus": null,
"epaoInternalId": null,
"epaoId": null
}
]
}It should look something like this:
Clicking done will fill the previously empty box with a schema that Power Automate can understand.
To iterate through all the apprentices you need to loop the API call for each page. Select "New Action", search for "do until" and select the "Do Until" option under the Control heading.
Set the loop to run until the increment variable you defined earlier is equal to the total pages.
Drag the Increment variable, HTTP and Parse JSON actions within the DO until action like shown below.
Getting EPA Component Attempt Data
To retrieve the paginated list of EPA components for an apprentice you’ll need to loop through each apprentice. We shall follow similar steps as before:
- Initialize variables
- Get the data
- Parse the data
- Iterate the data
Select "New Action", search for "apply to each" and select the "Apply to each" option under the Control heading.
Select the "Body data" from the parse json step as your output in the dropdown.
Since the list of component attempts returns a paginated list, we need to define another variable for it’s page numbers.
Select "New Action" under the Apprentice Page Number variable action and search for "variable" and select the "Initialize variable" option under the Variable heading.
Fill in the Name, Type and Value of the variable as follows.
Within the apply to each apprentice action, select "New Action", search for "increment" and select the "Increment variable" option under the Variable heading.
Under the name dropdown, select the initialized variable "Component Page Number" defined previously and fill in the value as 1.
Select "New Action", search for "http" and select the "HTTP" option under the HTTP heading.
In the form under the HTTP section, fill out the URI, Method and Headers as follows.
Set the apprentice ID, the page as "Component Page Number" and how many components you need perPage.
Select "New Action", search for "json" and select the "Parse JSON" option under the Data Operation heading.
Under the content, select the body from the lightning bolt icon.
Click on "Use sample payload to generate schema" and paste in the following into the window and click Done.
{
"total": null,
"currentPage": null,
"perPage": null,
"totalPages": null,
"data": [
{
"id": null,
"apprenticeId": null,
"epaAttemptId": null,
"epaId": null,
"epaType": null,
"grade": null,
"gradeDate": null
}
]
}It should look something like this:
Processing EPA Component Attempt Data
Not every apprentice will have a component attempt, depending at what status they are in. For that we need to set a condition to check if the GET Component Attempts HTTP request has returned any data.
Select "New Action", search for "condition" and select the "Condition" option under the Control heading.
Set the condition to check if the returned component attempts total pages is greater than 0.
If the component attempts exists, i.e. under the True section, we need to loop through each apprentice’s component attempts.
Select "New Action", search for "apply to each" and select the "Apply to each" option under the Control heading.
Select body data from the Parse Components JSON step as your output in the dropdown.
Similar to the steps to process a paginated list of apprentices, we need to have a "do until" loop within the "apply to each apprentice" action which will retrieve the list of components for every component page returned and then parse it.
Select "New Action", search for "do until" and select the "Do Until" option under the Control heading.
Drag the "Increment Component Page Number", "GET Component Attempts", "Parse Components JSON" and "Do Component Attempts exist" actions within the Do until action.
We need the "do until" action to run for every component attempt page. But since not every apprentice has component attempts, we create a variable to store the total component attempt pages if it exists and default the variable value to 1 (i.e. always assume a page is returned with no values).
Back at the top, where the variables are declared, select "New Action", search for "variable" and select the "Initialize variable" option under the Variable heading.
Fill in the Name, Type and Value of the variable as follows.
When the do component attempts exist condition passes, we need to set this newly declared "Total Component Pages" variable to the returned total pages value.
Select "New Action", search for "variable" and select the "Set variable" option under the Variable heading.
Select the "Total Component Pages" variable.
Set the do until loop to run till the "Total Component Pages" variable is equal to the "Component Page Number".
To reset the component page number to 0 for every apprentice, within the apply to each apprentice action and outside of the do until component action, select "New Action", search for "variable" and select the "Set variable" option under the Variable heading.
Select the "Component Page Number" variable declared previously and set the value to 0.
Getting Apprentice Related Data
To retrieve an apprentice’s TPO and standard details, under the apply to each apprentice section, select "New Action", search for "http" and select the "HTTP" option under the HTTP heading. Repeat this step to add another HTTP action.
In the form under the HTTP section, fill out the URI, Method and Headers for both the actions as follows. Assign the TPO ID as "body tpoId" and Standard ID as "body standardId" from the "Parse Apprentices" action.
To parse the response of both endpoints, select "New Action", search for "json" and select the "Parse JSON" option under the Data Operation heading.
Under the content of each of the Parse JSON actions, select the body from the lightning bolt icon.
Click on "Use sample payload to generate schema" within the GET TPO endpoint and paste in the following into the window and click Done.
{
"total": null,
"currentPage": null,
"perPage": null,
"totalPages": null,
"data": [
{
"id": null,
"name": null,
"street": null,
"city": null,
"region": null,
"postcode": null,
"contactName": null,
"contactNumber": null,
"contactEmail": null,
"ukprn": null,
"parentId": null,
"createdAt": null
}
]
}It should look something like this:
Click on "Use sample payload to generate schema" within the GET Standard endpoint and paste in the following into the window and click Done.
{
"total": null,
"currentPage": null,
"perPage": null,
"totalPages": null,
"data": [
{
"id": null,
"stCode": null,
"name": null,
"level": null,
"sector": null,
"datePublished": null,
"endDate": null,
"approxMinDuration": null,
"description": null,
"archived": null,
"larsCode": null,
"courseOption": null,
"version": null,
"assessmentPlan": null,
"numberOfAttachedApprentices": null,
"highestGrade": null,
"epas": null
}
]
}It should look something like this:
Building Your Excel Report
Under the apply to each component action, select "New Action", search for "add a row" and select the "Add a row into a table" option under the Control heading.
In this example we're making use of an Excel workbook with a table that lives on our OneDrive. Though you can make use of SharePoint if you'd prefer.
Our Excel workbook started life looking as a blank table with the name "Table1".
Once the table has been found by Power Automate you'll be able to assign data to fields, your config should look something like this:
In the same section, under Advanced parameters, click on "Show all" to list out all the fields from your excel table.
Fill out the fields with their appropriate values to be displayed in the excel report.
Note that we can retrieve the apprentice and component details from "Parse Apprentices" and "Parse Components" action.
But the GET TPOs and GET Standards endpoint for each apprentice returns an array, hence if we need the data we must specify the array key like below.
Body(‘ParseTPO’)?[‘data’][0]['fieldName']
Body(‘ParseStandard’)?[‘data’][0]['fieldName']
This basically gets the parsed data, and the first item in the array/list and returns the required field like name, ukprn or stCode. Replace fieldName with the name of the field.
TIP: Use the little fx icon to type in the expression.
Once you've assigned the right json fields to correct columns, "Save" your flow.
Assuming your flow saved without errors, it's time to test your new flow.
Using the toolbar at the top right of the screen click "Test".
Under your test flow sidebar, click "Manually", then "Test", "Run flow" and finally "Done".
It'll take a minute but your flow should run. Keep an eye on your Excel workbook and on the status of your flow.
After a minute or so you should start seeing entries appearing in your Excel report.
And that's the basics of getting components data from ACE360 Connect into an Excel workbook.
Summary
By following this tutorial you should now be able to create custom Excel reports using Power Automate. The example we provided here won't be particularly clever but serves to give you a basic understanding of how to get data from ACE360 Connect and start manipulating it.
Next Steps
We'd recommend that you duplicate the report and experiment with checking for existing values, removing older unwanted data, and filling out more than one table. You could even start experimenting with our API filters to trim down the data you receive from Connect.
Need Inspiriation?
We've got an automated email guide coming soon!
Are you a PowerBi user?
Check out our basic PowerBi report tutorial.