cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShadowTech
Post Prodigy
Post Prodigy

Building a collection with multiple SQL tables

Hello,

 

I wanted to find the best way to add (3) SQL tables to a collection with only certain fields within those tables. Also the value in some of these fields rely on data that's in the other tables. What's the best way to accomplish this? This will be used for the data source of a gallery.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
mogulman
Impactful Individual
Impactful Individual

SQL Views in Powerapps are a life saver.  They allow you to simplify your app.  Here is a sample view that combines 3 tables and creates a special column.

 

 

CREATE VIEW [dbo].[TradeV]
AS
SELECT m.TradeID,
       m.TradeName,
       s.Stage,
       CASE
         WHEN s.Stage LIKE '%Onboard%' THEN 'Onboard'
         WHEN s.Stage LIKE '%Terminated%' THEN 'Terminated'
         WHEN s.Stage LIKE '%Suspended%' THEN 'Suspended'
         WHEN s.Stage IN ( 'Settlement (Completed)', 'Demand (Completed)','Transfer (Pending)', 'Transfer (Completed)' ) THEN 'Completed'
         WHEN s.Stage NOT LIKE '%Terminated%'
           AND s.Stage NOT IN ( 'Settlement (Completed)', 'Demand (Completed)', 'Transfer (Pending)', 'Transfer (Completed)' ) THEN 'Active'
       END AS TradeStatus,
       m.SettlementDate,
       co.CompanyName AS ClientName
FROM   dbo.Trade m
       INNER JOIN Company co
               ON m.ClientName2 = co.CompanyID
       INNER JOIN Stage2 s
               ON m.Stage = s.StageID 
GO

 

 

View solution in original post

8 REPLIES 8
Drrickryp
Super User
Super User

@ShadowTech 

Start with a table on the many side of the table relationships and use AddColumns() to add the additional columns and you can use lookups to identify the values you wish to add based on the common field in the tables.

@Drrickryp Can you provide any sample examples? Just so I can visualize it.

 

Thanks

timl
Super User
Super User

Hi @ShadowTech 

The way that I would do this would be to build a SQL Server view that returns those 3 tables. That would be by far, the most performant method. The SQL in your view would join/union the other tables.

If your ultimate aim is to display this output in a gallery, you would set the items property of your gallery to the view and avoid the use of any collections.

Calling a function like ClearCollect will collect a maximum of 2,000 records. If the combined data in your 3 tables exceeds this, you'll need to write some batching formula.

By setting the source of your gallery to a view, you can optionally nest the data source within a call to Search/Filter, and any search you carry out can be completely delegable across all three tables.

@ShadowTech 

This is an example of how to add additional data from a related table can be generated as a collection.  In this case It shows how to add the customer details to the orders table in a collection. The formula shows how the collection is generated and it is displayed in the datatable below.  @timl  is quite correct that large tables will run afoul of the delegation limits due to using AddColumns(), a function that is not delegatable.  However, it may be that you are not able to access the tables in SQL to create the views and will have to do it in PowerApps. 

2.jpg

mogulman
Impactful Individual
Impactful Individual

SQL Views in Powerapps are a life saver.  They allow you to simplify your app.  Here is a sample view that combines 3 tables and creates a special column.

 

 

CREATE VIEW [dbo].[TradeV]
AS
SELECT m.TradeID,
       m.TradeName,
       s.Stage,
       CASE
         WHEN s.Stage LIKE '%Onboard%' THEN 'Onboard'
         WHEN s.Stage LIKE '%Terminated%' THEN 'Terminated'
         WHEN s.Stage LIKE '%Suspended%' THEN 'Suspended'
         WHEN s.Stage IN ( 'Settlement (Completed)', 'Demand (Completed)','Transfer (Pending)', 'Transfer (Completed)' ) THEN 'Completed'
         WHEN s.Stage NOT LIKE '%Terminated%'
           AND s.Stage NOT IN ( 'Settlement (Completed)', 'Demand (Completed)', 'Transfer (Pending)', 'Transfer (Completed)' ) THEN 'Active'
       END AS TradeStatus,
       m.SettlementDate,
       co.CompanyName AS ClientName
FROM   dbo.Trade m
       INNER JOIN Company co
               ON m.ClientName2 = co.CompanyID
       INNER JOIN Stage2 s
               ON m.Stage = s.StageID 
GO

 

 

@mogulman Normally We don't have access to this database. That is why I asked. If there was another alternative if we could not create an SQL view.

 

Thanks and sorry for that late reply.

Will the SQL view be updated by the tables that contain the data when they are updated with new records?  Or will the view only update when it is called?

timl
Super User
Super User

@tonykiefer 

From the SQL level,  the view will always return the latest data.

However, if you've accessed the view from Power Apps prior to adding or updating records in the base table, Power Apps will most likely cache the initial values and the view won't display up to date values. Therefore, it's best to call the Refresh function against the view prior to point where you want to display up to date values.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-refresh

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,897)