SafetyCulture
Last updated:

Power Automate: Export inspection data to SQL Server tables

Learn how to create a Power Automate flow to automatically export inspection data into a SQL Server table.

What you'll need

Preparations

This is an advanced flow that requires the use of expressions and inspection question item IDs.

Before you start building this flow, ensure you have an existing table structure ready in your SQL Server. This can be interchanged with other connectors such as Excel Online and SharePoint lists.

Please note 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. Learn more about the limits on Power Automate.

Please note that exporting data from the following types of inspection questions is not supported:

  • Questions with repeat sections.

  • Multiple-choice questions that have "multiple selection" enabled.

Inspection data that gets processed are based on the inspections, which the account that generated the API token has access to. If you're an administrator, you can provide yourself inspection access or assign yourself the "Override permissions: View all data" permission to have visibility over all inspections in your organization.

1. Recurrence

  1. Sign in to Power Automate.

  2. Click Create from the menu on the left-hand side and select Scheduled cloud flow.

  3. In the pop-up window, configure the following fields:

    • Flow name: Add a name for your flow so you can refer back to it easily.

    • Starting: Set the date and time you want the flow to start running.

    • Repeat every: Set the frequency to 1 Hour.

  4. Click Create to create the flow and its first step.

2. Get past time

  1. Click image below "Recurrence" and select Add an action.

  2. Search and select Get past time under the "Date Time" connector.

  3. Set the "Interval" to 1 and leave the "Time unit" as Hour.

3. Search modified inspections

  1. Click image below "Get past time" and select Add an action.

  2. Search and select Search modified inspections under the "SafetyCulture (iAuditor)" connector.

  3. If this is your first time using SafetyCulture on Power Automate, authenticate the connector by configuring the following fields, and then clicking Create New.

    • Connection Name: We recommend that you name the connection by the account username or email from which the API token is generated.

    • API token: Enter your API token in the format of "Bearer <token>". Make sure there is a space between "Bearer" and the API token.

  4. In the side panel, click Show all for advanced parameters and then configure the following fields:

    • Modified After: Click the field, then click image and select Past time from the 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 click Enter custom value and enter the template's unique identifier.

    • Archived (optional): Click the field and select true if you only want to process archived inspections, or select both for active and archived inspections.

    • Completed (optional): Click the field and select false if you only want to process incomplete inspections, or select both for complete and incomplete inspections.

    • Ownership (optional): Click the field and select me if you only want to process inspections that are owned by your account, or select other for inspections that are owned by other users in your organization.

    • Limit: Keep in mind that each flow run can only process up to 1,000 inspections. If your organization conducts more than 1,000 inspections every hour, you should narrow the frequency in "1. Recurrence" and "2. Get past time".

If you encounter any error related to "Data loss Prevention (DLP) policy" when creating the connection to SafetyCulture or any other apps, please contact your IT team to review Microsoft's DLP and unblock the connection.

4. Get a specific inspection

  1. Click image below "Search modified inspections" and select Add an action.

  2. Search and select Get a specific inspection under the "SafetyCulture (iAuditor)" connector.

  3. In the side panel, click the "Audit ID" field, then click image and select Inspection Audit ID from the list.

  4. This should add the action within a "For each" action.

5. Filter array

  1. Click image below "Get a specific inspection" but within "For each" and select Add an action.

  2. Search and select Filter array under the "Data Operations" connector.

  3. In the side panel, configure the following fields:

    • From: Click the field, then click image and enter the following expression. If the question response you want to export is on the title page, replace ['items'] with ['header_items'].

      body('Get_a_specific_inspection')['items'] An example of the filter array action's "From" field expression in a Power Automate flow.

    • Filter Query (left): Click the field, then click image and enter the following expression:

      item()['item_id']

    • Filter Query (center): Leave it as "is equal to".

    • Filter Query (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 rename "Filter array" on the upper-right of the side panel to something you can refer back to as the question label, such as "Jobsite Hazard Check". An example of the filter array action in a Power Automate flow.

6. Compose

  1. Click image below "Filter array" but within "For each" and select Add an action.

  2. Search and select Compose under the "Data Operations" connector.

  3. In the side panel, click the field, then click image and 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, enter the following expression: body('Filter_array')?[0]?['responses']?['text']

  4. Please note that the 'Filter_array' in the sample refers to the label of the action in section "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 rename "Compose" on the upper-right of the side panel to something you can refer back to as the corresponding question's response, such as "Jobsite Hazard Check Response". An example of the compose action in a Power Automate flow.

  6. Repeat sections "5. Filter array" and "6. Compose" in combination, for each inspection question data you want to export.

7. Insert row (V2)

  1. Click image below "Compose" but within "For each" and select Add an action.

  2. Search and select Insert row (V2) under the "SQL Server" connector.

  3. If this is your first time using SQL Server on Power Automate, authenticate the connector by signing in to your SQL Server account.

  4. In the side panel, configure the following fields. Depending on the type of your SQL Server, some of the fields may not apply to you.

    • 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: Click Show all for advanced parameters and the columns from the selected table will appear as fields to populate. Click each field, then click image and select each "Compose" action's "Outputs" from the list. An example of exporting inspection data to a field in a Power Automate flow.

  5. Click Save on the upper-right of the page to save the flow.

Test the flow (optional)

  1. Click Test on the upper-right of the page to begin testing the flow.

  2. Select Manually and click Test.

  3. Click Run flow.

  4. Click Done to monitor the test flow.

Please note that our customer support team can only provide limited help for single sign-on, user provisioning, and integrations via third-party platforms such as Power Automate, Power BI, and Zapier. If your organization is on the Enterprise Plan, please contact your customer success manager for assistance.

Need more help?
Email
Phone
Ask the community

Thank you for letting us know.

If you would like a reply from someone, please contact us atsupport@safetyculture.com.

In this article