This guide will take you through the process of using the iAuditor connector available in Microsoft Flow to automatically create Excel 365 rows for each Action created in iAuditor. It assumes you have a working knowledge of Excel and an existing Sheet structure you wish to populate.
Begin by adding the Schedule – Recurrence trigger in a new blank flow. We’re going to configure the flow to check iAuditor for new Actions to process every hour.
Go ahead and set up the trigger so that the Interval is 1 and the Frequency is Hour.
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 actions operation listed under iAuditor and set the Created After Date field to use Past time from the available dynamic content.
Add an Apply to each loop, selecting Actions, from the available Dynamic content:
Now the “Add a row into a table” operation can be added inside the loop and the different Action properties mapped to your columns. You may need to click “See More” within the “Dynamic Content” selector to find more available fields.
The assignee information isn’t directly selectable from the Dynamic content pop-out. An Expression has to be used for each.
- The expression for the assignee name is: item()?[‘assignees’]??[‘name’]
- The expression for the assignee’s user ID is: item()?[‘assignees’]??[‘id’]
This flow will populate your Excel sheet with the Actions as they’re created. To enable two-way sync, you’ll also need to create two other supporting flows which:
- Update Excel365 rows when an Action is modified in iAuditor
- Update Actions in iAuditor when the relevant row/cell is modified
This guide walks you through configuring a flow that will insert a single row per inspection. Heavy usage where many thousands of inspections are created daily can result in performance bottlenecks and delayed row insertion.
The number of rows created per second will be largely dependant on a number of different factors. These include but are not limited to the number of loop iterations and HTTP requests the Microsoft Flow platform handles.
Microsoft Flow provides some information on the platform’s limits here.