- Integrations
- Power Automate
- Power Automate: Export inspection data to SQL Server tables
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.
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 SafetyCulture 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.
If your organization uses Zapier as the integration tool, you can create a Zap that delivers an equivalent solution!
We recommend that you familiarize yourself with the use of expressions and learn how to locate question item IDs for integrations.
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.
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. This can be interchanged with other connectors such as Excel Online and SharePoint lists.
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, you can provide yourself inspection access or assign yourself the "data access" permission to have visibility over all inspections in your organization.
This flow does not support the following types of inspection questions:
Questions with repeat sections.
Multiple-choice questions with multiple-choice questions that have "multiple selection" enabled.
If you run into any errors or have any questions regarding the integration, please contact our customer support team for assistance.
1. Recurrence
Click Create from the menu on the left-hand side and select Scheduled cloud flow.
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.
Click Create to create the flow and its first step.
2. Get past time
Click
New step.Search for "Date Time" and select the Get past time action.
Set the "Interval" to 1 and leave the "Time unit" as Hour.
3. Search modified inspections
Click
New step.Search for "SafetyCulture" and select the Search modified inspections action.
If this is your first time using SafetyCulture on Power Automate, authenticate the connector by configuring the following fields, then clicking Create.
Connection Name: We recommend that you name the connection by the account username or email from which the API token is generated.
API token: Input your API token in the format of "Bearer <token>". Make sure there is a space between "Bearer" and the API token.
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 want to only email reports of archived inspection, or select both for active and archived inspections.
Completed (optional): Click the field and select false if you want to only email reports of incomplete inspections, or select both for complete and incomplete inspections.
Ownership (optional): Click the field and select me if you want to only email reports of 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".
4. Get a specific inspection
Click
New step.Search for "SafetyCulture" and select the Get a specific inspection action.
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
Click Add an action.
Search for "Data Operations" and select the Filter array action.
Configure the following fields:
Array to filter: Click the field and select Expression. Then enter the following expression. If the question response you want 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.
You should see something that resembles the following example. We recommend that you click
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
Click Add an action.
Search for "Data Operations" and select the Compose action.
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']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']You should see something that resembles the following example. We recommend that you click
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".
Repeat sections "5. Filter array" and "6. Compose" in combination, for each inspection question data you want to export.
7. Insert row (V2)
Click Add an action.
Search for "SQL Server" and select the Insert row (V2) action.
If this is your first time using SQL Server on Power Automate, authenticate the connector by signing in to your SQL Server account.
Configure the following fields. Depending on the type of your SQL Server, some of the fields below 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: 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.
Click Save on the bottom or upper-right of the page to save the flow.
Test the flow (optional)
Click Test on the upper-right of the page to begin testing the flow.
Select Manually and click Test.
Click Run flow.
Click Done to monitor the test flow.
Was this page helpful?
Thank you for letting us know.