Can we pass paramters to SQL Server to get the related data

(0) Share

Report

Posted on by johnjohn123

3,514

We have the following system which contain these main entities and the relation between them:-

- Project

- TaskList. One Project can have many TaskList. and one TaskList can have one to zero Project

- Task. One Task can have one TaskList. and one TaskList can have many Task.

- TaskVersionHistory. One Task can have zero to many TaskVersionHistory. and one TaskVersionHistory can have one and only one Task

- TaskPersonnel. One TaskPersonnel can have one and only one Task. and One Task can have zero to many TaskPersonnel.

- TaskMilestones. similar relation to TaskPersonnel and Task.

now we want to build a filtering screen, which have the Projects as drop-down list. and users can select the related Project or Projects and view the related TaskMilestones based on the above relation. so can we define a view or function inside SQL which accept the Project ID/s from Power Apps >> do the filtering inside the database and return the data to Power Apps?

Thanks

Categories:

Building Power Apps

All responses (3)

Answers (2)

Sort by

Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(2)

Report
Copy link

Link copied!
@johnjohn123

Yes, it's possible to build a view in SQL Server to carry out this task. It looks like you want to traverse 3 tables. Therefore, you can build a view that joins the TaskList, Task, and TaskMilestone tables. It would look something like this:

CREATE VIEW vw_TaskMilestones AS SELECT tl.ProjectID, <other columns you want to return> FROM TaskList tl JOIN Task tk ON tl.TaskID=tk.TaskID JOIN TaskMilestone tm ON tk.TaskID=tm.TaskID

In Power Apps, you would a drop down/combo box control (eg, cboProjects) and set the Items property to the Project table.

To display the milestones for the selected project, you would add a gallery control and set the Items property to this:

Filter(vw_TaskMilestones, ProjectID=cboProjects.Selected.ProjectID )
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!
@timl Ok thanks, so will this filter :-

Filter(vw_TaskMilestones, ProjectID=cboProjects.Selected.ProjectID )

be done inside the SQL server or inside the user-side ? as this should be a delegate operation so the filtering should be done on the sql server itself? am i correct?
Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

@johnjohn123

Yes, you're correct. This is a delegable operation so SQL Server will execute the query server side.