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 it’s 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 our frequency.
We’ll also need to add a “Get past time” action; add this to your Flow and configure it to match the settings of the “Recurrence”
The next step is to add the “Search modified audits” action listed under iAuditor. Once this is added, we’ll need to set the “Modified after field” to use “Get past time” action we created earlier. You’ll find this within the “Dynamic content” pop-out:
Note: At this stage, you’ll be prompted to add your iAuditor API token. If you haven’t already set up authentication for the connector within Microsoft Flow, please see our how to get an iAuditor API token guide.
An authentication prompt should appear; if this doesn’t automatically pop up, click the 3 dots in the top right of your action and select “add new connection”,
Enter “iAuditor” as your “Connection Name”. If the prompt does not appear, please
Within the “API Key” field, type “Bearer” followed by a whitespace and your API token. Your connection settings should look like this: (your token will replace the one shown)
Click the “+” sign and select “Add an apply to each”
Click the empty “Select an output from previous steps” field. From the “Dynamic content” pop-out, select “Audits” from our “Search modified audits” action. 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” field, select “Audit ID” from the “Dynamic content” pop-out.
Your “Get a specific audit” reference should appear as below:
In order to drill down into the body of the Audit, we’ll now need to filter the audits contents.
Add a new action and search “Filter array”. Locate the “Data Operations – Filter array” from the list of actions.
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 the “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 select “Expression” tab from the pop-out box.
Type the below expression into the field:
*If your expression fails, check the characters/symbols; you may need to re-type the apostrophes being used.
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 ID”. We have a separate guide available on how to find Item IDs from your 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”.
Click into the “*Inputs” field. From the pop-out, select the “Expression” tab again and enter the below:
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.
Return back to your Flow. We’ll now 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 referencing. 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” pop-out 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” pop-out 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.