This guide will show you how to export inspection data from iAuditor so that you can automatically send it to any spreadsheet (columns/rows), SQL database or CRM system supported by the Microsoft Flow ecosystem.
In this example we’ll be looking to add rows to a spreadsheet based on a particular question and its response. Please read through our guide for how to find Audit item IDs before proceeding with these steps.
To kick start your new blank flow, begin by adding a “Schedule – Recurrence” trigger and set the interval as you wish. In this instance, we’re going to use 1 Hour as the frequency the flow will run.
The Get past time action is also needed for this. Add it to your flow and configure it to match the settings for the trigger.
The next step is to add the Search modified audits action listed under iAuditor and set the Modified after field to use Past time from the available dynamic content.
Note: You’ll be prompted to add your iAuditor API token if you haven’t already set up authentication for the connector within Microsoft Flow. See this guide for how to get an iAuditor API token if you need to. Should an authentication prompt appear, type “Bearer” followed by a whitespace and your API token, into the API Key field. We suggest “iAuditor” for the Connection Name field.
Click the “+” sign and select “Add an Apply to each”
Within the available field, please “Audits” from the “Search modified audits” step. This will apply any actions we created within the “Apply to each” to each audit discovered at the search event.
Add a new action within the “Apply to Each” and select “Get a specific Audit”
Within the “Audit ID” input, select the Audit ID from the “Dynamic content” selector.
Your “Get a specific Audit” reference should appear as below:
In order to drill down into the body of the Audit, we’ll need to apply an “Action” within our Flow to filter by an array.
Add a new action and search “Filter array”. Under actions, locate the “Data Operations – Filter array”.
To easily identify the question you’re filtering by, we’ll want to rename our “Filter Array”.
Click the 3 dots on the right hand side of the Action and select “Rename”
The text within the Action will become highlighted. Enter an identifier for your question as below (it’s important to note here that symbols such as “?” aren’t supported so only include standard text). Our example here is “Is the Car Clean?” question:
We’ll now need to configure the properties of the filter to look at the body of the Audit and search for the particular item ID (In this instance, a question within the Audit).
*From – To define which area we’ll be specifically looking at, we’ll need to enter an expression to reference the results of the “Get a specific Audit” action and look at the ID’s within the audit body.
To enter an expression, click into the field and the “Dynamic Content/Expression” pop-up box will appear; click the “Expression” tab:
Type the below expression into the field (if your expression fails, check your con
Choose a Value – Here we’ll specify the attribute we’re going to be looking for; we’ll need another expression to specifically look at the item ID.
Given we’re looking for a specific item, set “Is equal to” for the condition. We can now go ahead and input the specific item id we’re looking for; in order to get this you will need to understand how to find the specific Item IDs. We have a special guide for how to find Item IDs from audits or templates.
With the Item ID now discovered, place it within the below field as below:
So, now the item filter has been configured, we’ll need to discover the corresponding “selected” response within the Audit.
Add a new Action, this time “Data Operation – Compose”.
Within the required *Input box, enter the below expression:
Here we’re referencing the “Is the car clean?” Filter Array we created earlier then looking at the first selected response.
Our Question and Response have now been made available within the flow, we can now use this to populate our Spreadsheet (in this case, Office 365).
Head into Office & start a new workbook and create the column headers based off the question/item you’ve selected within your Flow.
Once your columns are set, select “Format as Table” from the menu bar and ensure “My table has headers” is ticked before selecting OK:
Your sheet should now look like this.
In this example, we’ve included an additional column for the Audit ID so we know which Audit the response has come from (this can also be customised as much as you’d like!).
Return back to your Flow. We’ll now make begin to map the items to the particular Columns/rows within the spreadsheet we’ve just created.
Add a new Action and search for “Add a row into a table” (Business) as shown below:
Once added, define the library, location, file and we’re looking at. After these are populated, you’ll see the columns within the sheet ready to map the response/audit data to.
Click into the “is the car clean?” column within the “Add a row into a table” Action. Within the Dynamic content selector locate the “Car Clean response” action we created earlier and click “See more”
Click the “Output” once this is expanded
Click into the “Audit ID” column within the “Add a row into a table” Action. Within the Dynamic content selector locate the “Get a specific audit” action and find the “Audit ID” entry.
Your “Add a row into a table” action should now look like this:
We’re now ready to run the Flow! Save, activate and run your Flow and watch the spreadsheet populate; the flow will continue to check every hour and add additional rows for any recently completed Audits.