cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bubba
Level: Powered On

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
Administrator
Administrator

Re: Filter a related table

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...

 

@TopShelf-MSFT

tchin-nin
Level 10

Re: Filter a related table

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 295 members 6,463 guests
Please welcome our newest community members: