Filter Related Table

(0) Share

Report

Posted on by AStill

I have two SQL tables, linked in a one-to-many relationship.

Table 1 is 'Teams.' The primary key is "ID," an integer.

Table 2 is 'Members.' The join field is "EntityID," also an integer.

I use a ComboBox to look up a particular "Team." A FormViewer on the screen shows various details about the "Team." I have included a datacard (DataCard20) which correctly displays the selected "Team" ID.

I want to display "Members" (in a table format) who are associated with the selected Team on the same screen.

I can hard code the filter and get expected results. For example,

Filter('[dbo].[Members]', EntityID = 1731), where '1731' is an actual "ID" from Teams.

However, my attempts at creating a filtering formula fail or generate warnings. Here are some of my attempts:

Filter('[dbo].[Members]', EntityID = DataCardValue25) (Errors with invalid argument)

Filter('[dbo].[members]', DataCardValue25 = Entity) (Delegation Warning)

For the time being, I can live with the delegation warning, but my table is near 2000 records and growing.

I have used both a DataTable and a Gallery to display the "Member" table. The resutls for each method are the same.

I would appreciate some guidance.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (2)

Report
Hi @AStill,

I think that your problem may be related to the filtering of your Members table by a datacard. This is probably unnecessary. Although Dropdown and Combobox controls show a single field in the window of the control, they actually contain all of the fields and can be referenced directly. To prove this to yourself, go to the Advanced tab on the right side of the screen and click the dropdown called value. All of the fields in the datasource should be present. My preference is to reference the combobox or dropdown directly.

Filter('[dbo].[Members]', EntityID = Combobox1.Selected.ID)

You could also correct your original formula to include the Value or Text property of the datacard.

Filter('[dbo].[Members]', EntityID = DataCardValue25.Value)

Without including that property you are referencing the control itself and not its value.

Was this reply helpful? Yes No
Bubba 19 on at

Like (0)

Report

Thank you @Drrickryp. The suggestion to use the ComboBox.Selected.ID was spot on.
Appreciate your knowledge.

Was this reply helpful? Yes No
Bubba 19 on at

Like (0)

Report

Thank you @Drrickryp
The suggestion to use the ComboBox1.Selected.ID was spot on.
Your knowledge is greatly appreciated.

Was this reply helpful? Yes No
Bubba 19 on at

Like (0)

Report

Many thanks!
Use of the ComboBox1.Selected.ID did the trick and was spot on.
Appreciate your knowledge.

Was this reply helpful? Yes No
AStill 5 on at

Like (0)

Report

Thank you.

The suggestion to use the ComboBox1.Selected.ID was spot on!
Appreciate your knowledge.

Was this reply helpful? Yes No