Automatically transfer key pieces of inspection data into SQL Server tables using the iAuditor connector available in Microsoft Flow. This guide assumes you have a working knowledge of SQL Server and an existing table structure you wish to populate. This approach may also work for other database technologies supported by Microsoft Flow that we have not yet tested with. For example: Oracle, DB2, MySQL, PostgreSQL, Teradata etc.
The article shows how to export data to an Azure SQL Server database. Connecting to on-premises SQL Server is possible but a bit more complex, using a gateway. Check with Microsoft if your version of SQL Server is supported.
Steps for setup
- Begin by adding the “Schedule – Recurrence” trigger in a new blank flow
- In this example, we will configure the flow to check iAuditor for inspections to process every hour
- 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 modified inspections” action listed under iAuditor
- Set the “Modified after” field to use “Past time” from the available dynamic content
- At this point, your flow is configured to perform an hourly check for inspections.
- Add the “Get a specific inspection” action as it will allow us to access all the data for each inspection that the flow processes. Set the “Inspection ID” from the available dynamic content and the action will immediately become nested in an “Apply to each” loop
- In our example scenario, we’re going to populate a table with the responses to key questions within our template. Each column will represent one of the questions, and then each inspection will result in a new row.
- Our example template contains a single information item of “Welcome on-site…” followed by standard yes/no/na questions.
Go ahead and add a Data Operations – Compose action and click inside the Inputs field. You’ll see a flyout appear on the right. Click the Expression tab.
- Enter this expression: body(‘Get_a_specific_audit’)?[‘items’]
- Rename the action to “Items.”
- Add a “Data Operations – Filter Array” action and set the “From to Output,” which will be available under the heading “Items” in the available “Dynamic content.”
- In the left-hand “Choose a value” input, enter this expression: item()?[‘item_id’]
- In the right-hand Choose a value input, enter the unique identifier for the item you wish to select.
- The unique identifiers for your template’s items will be entirely different from what is used in this example. Follow this guide to find out the unique identifiers for the items you wish to access.
- Rename the action to be more easily distinguishable as we will be replicating these steps for each item you want to access the data for.
- Add a “Data Operations – Compose” action. We’ll use it to access the selected response of the item.
- The type of item seen above is a ‘question’, so the expression to access the selected response, is: body(‘Safety_Book_Question’)??[‘responses’]?[‘selected’]??[‘label’]
- Note: Other item types will require slight variations to this expression.
- Now, whenever the response is needed, we can select it from the Dynamic content flyout
- Repeat these steps for each of your target items, adjusting the item ID within the Filter Array action and the expression for the Compose action.
- The flow in this example looks like this after adding the remaining items:
- Finally, add the “Insert a row” action which is available for SQL Server.
- Select your table and input fields should appear for all of the available columns
- Work through each input and select the relevant response from the available “Dynamic content.”
- You can now save the flow and turn it on to continually populate your table with item responses. Here’s the final result from this example:
This guide walks you through configuring a flow that will insert a single SQL 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 several 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.