The SQL Server as a system to manage and store information can be very useful for business intelligence operations and transaction processing. Using integration tools such as Power Automate (previously Microsoft Flow), you can automatically export your iAuditor inspection data to populate a SQL Server table, so your business analytics are always up to date with the latest information from your frontline users. This approach could also work for other database platforms supported by Power Automate, such as Oracle, DB2, and MySQL.

This article shows you how to create a Power Automate flow to automatically export inspection data into a SQL Server table. If your organization uses Zapier as the integration tool, you can create a Zap that delivers an equivalent solution!

Before you begin

You must have a Power Automate account on Premium to create flows with premium connectors such as iAuditor. As the integration requires an iAuditor API token, you must also be on our Premium subscription in order to create the flow.

This article requires the use of Power Automate expressions and question unique identifiers to export inspection responses, we recommend that you familiarize yourself with the use of expressions and learn how to locate question item IDs for integrations.

Please note that this flow does not support inspection questions that are within Repeat sections, or multiple-choice questions that have “multiple selection” enabled. Also, keep in mind that this flow inserts a single SQL row per inspection, meaning heavy usage where thousands of inspections are created daily can result in performance bottlenecks and delayed row insertion. The number of rows inserted per second is largely dependant on several different factors. These include but not limited to the number of loop iterations and HTTP requests the Power Automate platform handles. Learn more about the limits on Power Automate.

This flow utilizes SQL Server tables as the solution to store inspection data. Make sure you have an existing table structure ready in your SQL Server. If your organization does not use SQL Server tables to store data, it can be interchanged with other connectors such as Excel Online and SharePoint lists.

Please note, the inspection data that gets exported are based on the inspections, which the account that generated the API token has access to. If you’re an administrator, learn how you can auto-share inspections to yourself or assign the “Data Access” permission to have visibility over all templates and inspections in your organization.

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

1. Recurrence

  1. Sign in to Power Automate.
  2. Click “Create” from the menu on the left-hand side and select “Scheduled flow”.
  3. Configure the following fields:
    Flow name: Add a name for your flow so you can refer back to it easily.
    Starting: The date and time you want your flow to start running at.
    Repeat every: The frequency you want the flow to run on. Set this to “1 Hour”.
  4. Click “Create” to create the flow and its first step.

2. Get past time

  1. Click “+ New step”.
  2. Search for “Date Time” and select the “Get past time” action.
  3. Set the “Interval” to “1” and leave the “Time unit” as “Hour”.

3. Search modified inspections

  1. Click “+ New step”.
  2. Search for “iAuditor” and select the “Search modified inspections” action.
  3. If this is your first time using iAuditor on Power Automate, you would need to authenticate the connector. Configure the following fields and click “Create”:
    Connection Name: We recommend naming the connection by the username from which the API token is generated. This way you can refer back easily should you need to set up multiple iAuditor connections with different accounts.
    API Key: Input your API token in the format of “Bearer [token]”. Make sure there is a space between “Bearer” and the API token.
  4. Configure the following fields:
    Modified After: Click the field and select “Past time” from the dynamic content list.
    Filter by template: As inspection data differs between templates, you must filter by a specific template for this flow. Click the field to select the template from the list, or select “Enter custom value” and input the template’s unique identifier.
    Archived (optional): Click the field and select “true” if you wish to only export archived data or select “both” if you wish to export both active and archived data.
    Completed (optional): Click the field and select “false” if you wish to only export incomplete data or select “both” if you wish to export both complete and incomplete data.
    Ownership (optional): Click the field and select “me” if you wish to only export data owned by your account or select “other” if you wish to only export data that are owned by other users in your organization.
    Limit: Keep in mind that only a maximum of 1,000 inspections can be processed for each run. If your organization conducts more than 1,000 inspections every hour, you should narrow the frequency in “1. Recurrence” and “2. Get past time”.

4. Get a specific inspection

  1. Click “+ New step”.
  2. Search for “iAuditor” and select the “Get a specific inspection” action.
  3. Click the “Audit ID” field and select “Inspection Audit ID” from the dynamic content list. This puts the action within an “Apply to each” action.

5. Filter array

  1. Click “Add an action”.
  2. Search for “Data Operations” and select the “Filter array” action.
  3. Configure the following fields:
    Array to filter: Click the field and select “Expression”. Then enter the following expression. If the question response you wish to export is in the Title Page, replace [‘items’] with [‘header_items’].
    body('Get_a_specific_inspection')['items']

    Choose a value (left): Click the field and select “Expression”. Then enter the following expression.
    item()['item_id']
    Choose a value (right): Click the field and enter the unique identifier (item_id) of the question.

  4. You should see something that resembles the following example. We recommend that you click the 3 dots on the upper-right of the action and “Rename” it to something you can refer back to as the question label, such as “Jobsite Hazard Check”.

6. Compose

  1. Click “Add an action”.
  2. Search for “Data Operations” and select the “Compose” action.
  3. Click the field and select “Expression”. Then enter your expression based on the corresponding question’s response type. You can copy and paste the samples we have for each response type. For example, if the response type is text answer, the following expression should be entered.
    body('Filter_array')?[0]?['responses']?['text']
  4. Please note that the ‘Filter_array’ in the sample refers to the label of the action in “5. Filter array”. If you followed our recommendation and renamed the action, you should match the expression with the new name. For example, if the filter array action was renamed to “Jobsite Hazard Check”, the following expression should be entered with underscores (_) substituting spaces.
    body('Jobsite_Hazard_Check')?[0]?['responses']?['text']
  5. You should see something that resembles the following example. We recommend that you click the 3 dots on the upper-right of the action and “Rename” it to something you can refer back to as the corresponding question’s response, such as “Jobsite Hazard Check Response”.
  6. Repeat steps “5. Filter array” and “6. Compose” in combination, for each inspection question data you wish to export.

7. Insert row (V2)

  1. Click “Add an action”.
  2. Search for “SQL Server” and select the “Insert row (V2)” action.
  3. If this is your first time using SQL Server on Power Automate, you would need to authenticate the connector by signing in to your SQL Server account.
  4. Depending on your authentication type, you may need to configure the following fields:
    Server name: Click the field and select the name of the SQL server.
    Database name: Click the field and select the name of the database.
    Table name: Click the fields and select the name of the table.
    Column Name: The columns of the selected table will appear as fields to populate. Click each field and select each “Compose” action’s “Outputs” from the dynamic content list.

  5. Click “Save” on the bottom or upper-right of the page to save your flow.

Test the flow (optional)

  1. Click “Test” on the upper-right of the page to begin testing the flow. Please note that the flow must be turned on for testing.
  2. Select “I’ll perform the trigger action” and click “Test”.
  3. Click “Run flow” and click “Done” to monitor the test flow.
  4. If successful, you should start seeing inspection data being exported to populate your SQL Server table.
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!