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

Displaying two tables from SQL Server in one screen

I have created a simple PowerApp that at current allows me to search through a list of individuals, then navigate to a screen detailing that specific individual.  I want to add a gallery/data table to the bottom of the second screen to show records (from another table) that refer to the individual being viewed.  

 

I am really new to PA, so am sure its my lack of experience and think the way to do this is to have a filter on the gallery/data table that references the ID being viewed, however am unsure how to reference it.  The issue might be that I started with the data model approach, I am also using SQL server and a gateway if that plays a part.

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User II
Super User II

Hi @PaulMugs 

Let's say your have two tables, Table1 and Table2.  These tables are in a one-to-many relationship and there is a foreign key in the Table2 that refers back to the primary key field or ID field of Table1, call it table1ID

On the first screen, you will have a gallery, say Gallery1 with an Items property Table1.   Inside the gallery, in the top row, place a ">" icon and set its OnSelect property to Navigate(Screen2,None,{personid:ThisItem.ID}). The element in the Navigate() function enclosed in curly braces creates a variable. in this case personid that will be carried to the second screen and identifies the record selected in Gallery1.  In this case personid could be any name you want to choose and it is an alias for the ID of the chosen record.

On Screen2 you can have a ViewForm that has the same datasource as Gallery1 and the Item property would be Lookup(datasource,ID=personid).  This can show all of the fields in Table1 for the record chosen in Gallery1.  

Below the ViewForm place a datatable.  The datatable should have its datasource Table2 filtered by the ID of the record chosen in Gallery1, the variable personid. To accomplish this, set the Items property of he datatable to Filter(Table2,table1ID=personid).  

The datatable will show all of the records in Table2 that are related to the record chosen in Gallery1 and the fields in Table2 can be displayed.. 

View solution in original post

2 REPLIES 2
Drrickryp
Super User II
Super User II

Hi @PaulMugs 

Let's say your have two tables, Table1 and Table2.  These tables are in a one-to-many relationship and there is a foreign key in the Table2 that refers back to the primary key field or ID field of Table1, call it table1ID

On the first screen, you will have a gallery, say Gallery1 with an Items property Table1.   Inside the gallery, in the top row, place a ">" icon and set its OnSelect property to Navigate(Screen2,None,{personid:ThisItem.ID}). The element in the Navigate() function enclosed in curly braces creates a variable. in this case personid that will be carried to the second screen and identifies the record selected in Gallery1.  In this case personid could be any name you want to choose and it is an alias for the ID of the chosen record.

On Screen2 you can have a ViewForm that has the same datasource as Gallery1 and the Item property would be Lookup(datasource,ID=personid).  This can show all of the fields in Table1 for the record chosen in Gallery1.  

Below the ViewForm place a datatable.  The datatable should have its datasource Table2 filtered by the ID of the record chosen in Gallery1, the variable personid. To accomplish this, set the Items property of he datatable to Filter(Table2,table1ID=personid).  

The datatable will show all of the records in Table2 that are related to the record chosen in Gallery1 and the fields in Table2 can be displayed.. 

View solution in original post

Fantastic, thank you had been struggling with this and this has cracked it!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (20,488)