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
RandyHayes
Level 10

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.

emh
Level: Powered On

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
RandyHayes
Level 10

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.

emh
Level: Powered On

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.