cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mark-carlisle
Level: Powered On

Join two SQL tables

Having some real difficultly in understanding how to get PowerApps to joing two tables together. My situation is this. I have two tables in a SQL DB, the first contains EmployeeId and UserId columns, I'll call this Table1, the second contains UserId and AppId, this will be Table2. I want to join Table1.UserId to Table2.UserId so I can input a EmployeeId into a text input field and the AppId's are then displayed.

 

These tables are both over 500 rows so I keep getting warnings about delegation and I am not getting complete results because of it.

 

What do I need to do to get this working?

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Join two SQL tables

@mark-carlisleI don't believe in your case you need to worry so much about joining the tables.  What you are doing is a master/detail scenario where, as you state, you will enter EmployeeID and then show AppId's.

 

So, in this case, a text input box for the EmployeeID (lets call it txtEmployeeID) and a gallery for the AppId's

Assuming your EmployeeID's are numeric...

 

You would set the filter of the gallery to:

  Filter(Table2, UserId = Lookup(Table1, EmployeeId=Value(txtEmployeeID.Text)).UserId)

 

As for the delegation warning. One thing you can do is to pre-fetch the tables. However, in my examples here I don't believe you have this issue since all of these functions support delegation on SQL.

 

Hope this can help some.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
emh
Level 8

Re: Join two SQL tables

 

If I understand your needs correctly, one way to do it is below. This way does not include any validation. For example, if the employeeID is not found you may want to display a pop up message. Also, you will need a way to initialize and reset the context variable to false. 

 

Text Input field: inputEmployeeID

 

BUTTON: OnSelect: Set(lookupUserID,LookUp('[dbo].[Table1]',EmployeeID=Value(inputEmployeeID.Text),UserID)); UpdateContext({DisplayAppId: true})

 

GALLERY: Visible Property: DisplayAppId

       ITEMS property: If(!IsBlank(inputEmployeeID), SortByColumns(Filter('[dbo].[Table2]', UserID=lookupUserID), "AppID", Descending)

mark-carlisle
Level: Powered On

Re: Join two SQL tables

Thanks, both of these are great, don't know why I was having such issues as I was attempting to do the same things, my syntax must have been incorrect.

 

In the end I've went for a button to filter the UserId in Table1 and then a label to concatenate all the returned AppIds from Table2 that has been filtered.

 

Also added in a message if there were no Id's returned. Thanks for that idea.

3 REPLIES 3
Super User
Super User

Re: Join two SQL tables

@mark-carlisleI don't believe in your case you need to worry so much about joining the tables.  What you are doing is a master/detail scenario where, as you state, you will enter EmployeeID and then show AppId's.

 

So, in this case, a text input box for the EmployeeID (lets call it txtEmployeeID) and a gallery for the AppId's

Assuming your EmployeeID's are numeric...

 

You would set the filter of the gallery to:

  Filter(Table2, UserId = Lookup(Table1, EmployeeId=Value(txtEmployeeID.Text)).UserId)

 

As for the delegation warning. One thing you can do is to pre-fetch the tables. However, in my examples here I don't believe you have this issue since all of these functions support delegation on SQL.

 

Hope this can help some.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
emh
Level 8

Re: Join two SQL tables

 

If I understand your needs correctly, one way to do it is below. This way does not include any validation. For example, if the employeeID is not found you may want to display a pop up message. Also, you will need a way to initialize and reset the context variable to false. 

 

Text Input field: inputEmployeeID

 

BUTTON: OnSelect: Set(lookupUserID,LookUp('[dbo].[Table1]',EmployeeID=Value(inputEmployeeID.Text),UserID)); UpdateContext({DisplayAppId: true})

 

GALLERY: Visible Property: DisplayAppId

       ITEMS property: If(!IsBlank(inputEmployeeID), SortByColumns(Filter('[dbo].[Table2]', UserID=lookupUserID), "AppID", Descending)

mark-carlisle
Level: Powered On

Re: Join two SQL tables

Thanks, both of these are great, don't know why I was having such issues as I was attempting to do the same things, my syntax must have been incorrect.

 

In the end I've went for a button to filter the UserId in Table1 and then a label to concatenate all the returned AppIds from Table2 that has been filtered.

 

Also added in a message if there were no Id's returned. Thanks for that idea.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 41 members 4,004 guests
Please welcome our newest community members: