The SQL Server as a system to manage and store information can be very useful for business intelligence operations, analytics operations, and transaction processing. Using Power Automate, inspection data captured in iAuditor can be transferred automatically into SQL Server tables. This approach may also work for other database technologies supported by Power Automate that we have not yet tested with, such as Oracle, DB2, and MySQL.

The guide outlines how to export inspection data from iAuditor to an Azure SQL Server database with Power Automate. Connecting to on-premises SQL Server using a gateway is also possible, but a bit more complex. Check with Microsoft if your version of SQL Server is supported.

Requirements

  1. iAuditor Premium subscription
  2. iAuditor API Token
  3. Microsoft Office 365 user account
  4. Access to the Premium version of Power Automate (previously Microsoft Flow)
  5. Knowledge of working with Power Automate Flow Expressions
  6. Knowledge of locating inspection item IDs
  7. Access to and knowledge of working with SQL servers
IMPORTANT NOTE

If you run into any errors or have any questions regarding the setup process, please contact our customer support team for assistance.

Steps to set up your flow

There are several ways to trigger flows, either from a designated event, manually, or on a recurrence set by the user. All, however, once triggered, follows a series of actions that flow into an output, such as uploading an inspection report into a cloud drive.

This guide assumes you have a working knowledge of SQL server and an existing table structure you wish to populate. In the flow, the trigger is scheduled to reoccur every 1 hour, where all completed inspections within each hour passed are retrieved, then uploaded to Microsoft SharePoint.

Step 1: Recurrence

  1. Log in to Power Automate with your Office 365 account.
  2. Click “Create” on the left-hand side.
  3. Select “Scheduled flow”.
  4. Name your flow to help identify the automation moving forward.
  5. Configure the following:
    Starting: The date and time you want your first flow to start – set this accordingly.
    Repeat every: The interval you want the flow to run on – set this to “1 Hour”.
  6. Click “Create” to finalize step 1.

Step 2: Get past time

  1. Click “+ New step”.
  2. Search and select the “Get past time - Date Time” action.
  3. Configure the following:
    Interval: Set this field to “1”.
    Time unit: Set this field to “Hour”.

Step 3: Search modified audits

  1. Click “+ New step”.
  2. Search and select the “Search modified audits (preview) – iAuditor” action.
  3. Set the "Modified after" field to use "Past time" from the available dynamic content.
  4. (optional) If you wish to only export data from a particular template, click the “Filter by Template” field to select your template from the list or input the template_id.
    IMPORTANT NOTE

    A maximum of 1000 inspections can be processed during each interval. You will need to shorten the interval in step 1 and step 2 if your organization completes more than 1000 inspections per hour.

Authentication:

  1. Authenticate the connector if this is your first time using iAuditor on Power Automate.
  2. Configure the following on authentication:
    Connection Name: We recommend naming by the username from which the API token is generated, so you can refer back easily should you need to set up multiple iAuditor connections.
    API Key: Input your API token in the format of “Bearer [token]”. See our support article on how to generate an API token.
  3. Click “Create” to continue.

Step 4: Apply to each

  1. Click “+ New step”.
  2. Search and select the “Apply to each - Control” action.
  3. Click on the “Select an output from previous steps” field.
  4. Select “Audits” from the dynamic content list.

Step 5: Get a specific audit

  1. Click “Add an action” within the “Apple to each” window.
  2. Search and select the “Get a specific audit (preview) - iAuditor” action.
  3. Click on the “Audit ID” field.
  4. Select “Audits Audit ID” from the dynamic content list.

Step 6: Filter array

  1. Click “Add an action” within the “Apple to each” window.
  2. Search and select the "Filter array - Data Operation" action.
  3. To identify your questions easily, we recommend renaming the action to your question label.
  4. Click the 3 dots in the upper-right corner of the action.
  5. Select “Rename” and type in your question label.
  6. Click the "From" field to define the array to filter.
  7. Select the "Expression" tab from the right-hand side list.
  8. Type in the following expression: body('Get_a_specific_audit')['items']
    IMPORTANT NOTE

    Please type in the expressions manually, as copying and pasting the expressions could return an error message showing “The expression is invalid”. In this case, retype in the expression manually.

  9. Click "OK" to continue.
  10. Click on the left-hand side "Choose a value" field to set the value within the array.
  11. Select the "Expression" tab from the right-hand side list.
  12. Type in the following expression: item()[‘item_id’]
  13. Click "OK" to continue.
  14. Click on the right-hand side "Choose a value" field to set the question (item) ID.
  15. Type or paste in the “item_id” of the question you wish to export data for.

Step 7: Compose

  1. Click “Add an action” within the “Apple to each” window.
  2. Search and select the "Compose - Data Operation" action.
  3. Click on "Inputs”.
  4. Type in the flow expression corresponding to the response type of the question you wish to export. For example, for a text answer question, type in the following expression: body('Filter_array')?[0]?['responses']?['text']
    IMPORTANT NOTE

    The ‘Filter_array’ part of the expression should be replaced by the name of your filter array action if you have renamed it to your question label. Use underscores to supplement spaces. For example, if the Filter array action is named Integrations are the best, instead of typing body('Integrations are the best'), you would type body('Integrations_are_the_best').

  5. Repeat step 6 and step 7 for each question you wish to export data from. The result could look something like this, where each question (filter array) has a corresponding response (compose).

Step 8:

  1. Click “Add an action” within the “Apple to each” window.
  2. Search and select the "Insert row (V2) - SQL Server" action.
  3. If this is your first time using SQL Server on Power Automate, you will be prompted to authenticate the connector.
  4. Depending on your authentication type, you may need to configure the following fields:
    Server name: Select the name of the SQL server.
    Database name: Select the name of the database.
    Table name: Select the name of the table.
  5. Once the table is selected, column headers appear as fields in the action.
  6. Click on each field and select the “Output” for each corresponding response (compose).
  7. Click “Save” on the upper-right corner of the page to turn on your flow.

Limitations

This guide walks you through configuring a flow that will insert a single SQL row per inspection. Heavy usage where thousands of inspections are created daily can result in performance bottlenecks and delayed row insertion. The number of rows created per second is largely dependant on several different factors. These include, but are not limited to the number of loop iterations and HTTP requests the Power Automate platform handles. Learn more about the limits on Power Automate.

Was this article helpful?

We love your feedback. Please tell us what you think.


Yes No
Care to share a bit more so we can continue to make improvements for you? Care to share a bit more so we can make this article even better for you? Thanks for your feedback!