Building a collection with multiple SQL tables

[ShadowTech]

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.

[Drrickryp]

@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.

[ShadowTech]

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

Thanks

[timl]

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.

[Drrickryp]

@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. 

[mogulman · Accepted solution]

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

[ShadowTech]

@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.

[tonykiefer]

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]

@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