cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bubba
Frequent Visitor

Filter a related table

I have two SQL 'one to many' linked tables.

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

Table 2 is "Members."  The one-to-many join field is "EntityID," also an integer.

 

I have a ComboBox that helps to look up a particular "Team." A FormViewer on the Screen shows various details for the "Team."  For testing, I have included a datacard (DataCard25) showing the "Team" ID.  

 

I want the Screen to show "Members" (in a table format) that are associated with the selected Team.

 

I have tried a couple of different approaches, using both a 'DataTable' and using a 'Gallery', to display the filtered data.  I've attempted a number of filtering formulas.  I receive notices that my formula is incorrect or delegation warnings...

                Filter('[dbo].[Members SubTable]', DataCardValue25.Text in Text(EntityID))    --Delegation

                Filter('[dbo].[Members SubTable]', DataCardValue25 = EntityID)                     --Delegation

                Filter('[dbo].[Members SubTable]', EntityID = DataCardValue25)                     --Invalid Argument

 

The only way I have been able to filter the table is to hard code the filter; i.e., 

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

          [Teams] table.  (This works for both the datatable and gallary methods.)

 

Any insight would be greatly appreciated.

 

 

2 REPLIES 2
Anonymous
Not applicable

Hi @Bubba I'm sorry to hear you're experiencing this issue. User @tchin-nin has a new blog post up with a deep dive into Delegation. I am going to include the link below. If this does not help please reply in this thread so I can find you a better solution. 

 

https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Deep-Dive-Into-Delegation-What-Is-It-Ex...

 

@Anonymous

tchin-nin
Memorable Member
Memorable Member

Hi @Bubba

 

EntityID expects a number, and DataCardValue25 is a textbox. So using the Value(textbox.text) retrieves the number filled as text into the textbox.

 

Can you try :

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

 

Also, you don't need to use a textbox containing the ID, you can directly use your 'Team reference' = what you're using in the Form.Item property.

 

Théo

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,166)