Get Direct Reports and query SQL Server with results

(0) Share

Report

Posted on by PK-14081126-0

Hi, I'm trying to build a flow that will send results back to Power App.

1: Power Apps send email address of the manager to power automate

2: I get a list of Direct Reports and then for Each of those I get Name, Job Title and Employee ID (Done so far)

3: Now i need to get rows from SQL for each of those Employee ID and get the Job Training history for Course ID SAR003

4: Combine the results of each employee and send it back to Power Apps

I'm not sure how to create Step 3 and 4

Any help would be appreciated

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

chiaraalina 1,864 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
Hi

Before the loop add:

Initialize variable (action)

Name: AllTrainingResults (or similar)

Type: Array

Value: []

This variable will collect the SQL results from each loop iteration.

Inside the loop after “Get user profile (V2)” add:

Execute a SQL query (V2) (action)

Connection: your SQL Server

Query example:

SELECT EmployeeID, CourseID, CourseName, CompletionDate FROM TrainingHistory WHERE EmployeeID = '@{body('Get_user_profile_(V2)')?['employeeId']}' AND CourseID = 'SAR003'

This retrieves the training history for that employee.

Inside your loop but after the SQL query add:

Append to array variable (action)

Variable: AllTrainingResults

Value:

{"EmployeeID": "@{body('Get_user_profile_(V2)')?['employeeId']}",

"DisplayName": "@{body('Get_user_profile_(V2)')?['displayName']}", "JobTitle": "@{body('Get_user_profile_(V2)')?['jobTitle']}", "Training": "@{body('Execute_a_SQL_query_(V2)')?['resultsets']['Table1']}" }

After your loop: Respond to a PowerApp or flow (action) and add @{variables('AllTrainingResults')}

2 people found this reply helpful.

Was this reply helpful? Yes No
PK-14081126-0 62 on at

Like (1)

Report
Copy link

Link copied!

thank you

I'll try it and get back to you

Was this reply helpful? Yes No
PK-14081126-0 62 on at

Like (0)

Report
Copy link

Link copied!

Hi, I got an error message from the SQL Server and that is because the Direct Reports query includes Employee + Contractors

The SQL Database only has data for Employees

Is it possible to Filter out the Get Direct Reports to remove all Employee ID's that begin with letter "C"?

This way only the SQL query will only have employees

Was this reply helpful? Yes No
chiaraalina 1,864 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @PK-14081126-0

Between “Get direct reports (V2)” and your “Apply to each”, add a new action: “Filter array”.

From: Select the dynamic content value from “Get direct reports (V2)”
Edit in advanced mode (bottom right of the Filter array card) and paste the following expression (or similar):

@not(startsWith(item()?['employeeId'], 'C'))

Then Update your “Apply to each” with the output of the "Filter array".

Was this reply helpful? Yes No