cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VD
Helper III
Helper III

Azure SQL Views Relationship in Power Apps

I have two SQL views connected in power apps, POHeaderView and POLineView.

I want to perform join on this two views in power apps.

I tried using Lookup() but unable to achieve this.

below SQL query, want to write into power apps formula.

select * from POHeaderView a, POLineView b
where a.purchaseorderno = b.purchid
order by a.purchaseorderno, b.Linenum
 
how I can achieve this?
4 REPLIES 4
Dipak_Shaw
Helper I
Helper I

You cannot create Views on PowerApps. If you want to join your two SQL views there are two option

1. Either join them in SQL and use in Power Apps or

2. use ForAll function and iterate with one table's row and create a collection and add columns for 2nd table's data and add value in it.
For example:

Table1

IDTitleAge
1Dipak25
2Tony34

 

Table2

Table1_IDItem1Item2
1CarToy
2TruckPencil
2PenNotebook
1BikeBicycle

 

To join above two tables into one collection-

 

Clear(ColCombined);
ForAll(Table1,
 Collect(ColCombined,
     {
       ID:ThisRecord.ID,
	     Title: ThisRecord.Title,
	     Age: ThisRecord.Age,
	     Item1:Filter(Table2, Table1_ID=ThisRecord.ID).Item1,
	     Item2:Filter(Table2, Table1_ID=ThisRecord.ID).Item2
     }
)
)

 

 

This will create a collection and put another table's item in the first table's column as table.

 

You can access the column's value using period(.)

 

Please click Accept as solution & ‌‌👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it ‌‌👍 

VD
Helper III
Helper III

I tried but, In views having 2k plus data and I have joined my two views and save them into collection but, it took 3 to 4 min to check related records and save into collection.

Better way is, create another view for such a operation on server side and then use them in power apps or you can use this solution if you have less than 1k records.

timl
Super User
Super User

@VD 

As you've said, the best approach here is to create a third view that joins POHeaderView and POLineView. Eg, you could create a view called POHeaderDetails and to connect to that view from your app.

CREATE VIEW POHeaderDetails
AS
SELECT * FROM
POHeaderView a JOIN
POLineView b
ON 
a.purchaseorderno = b.purchid


There is no way to do this as efficiently by purely using formulas in Power Apps, such as LookUp.

Dipak_Shaw
Helper I
Helper I

The first option that I provide, you can go with that.

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,657)