Blog

SQL - How to lookup/display related details with SQL Server Views

When we build apps that use SQL Server as a data source, a core task is to join tables in order to display details from related tables. The most efficient way to perform this task is to use a view.

This post provides a basic introduction on how to create and to display data from a view. We'll start with an auto-generated app that's based on the table of issues. This table stores the numeric ID values that correspond to the property and tenant that is associated with each issue record. The property and tenant details are stored in separate tables.



We'll adapt our auto-generated to add the full address and tenant name against each issue record in the main gallery screen. To begin, here's a brief definition on what is a SQL Server view, and how to create one.

What is a SQL Server View and what are the benefits?

A view is a named database query that we can use to display data from Power Apps. Typically, a view joins multiple tables and can return subsets of columns. It offers many benefits including:

  • The ability for SQL Server to retrieve data in the quickest and most efficient way by utilising indexes and other database optimisations.
  • Better delegation support/ the abiity to search accross multiple tables - Without the use of views, we need to join data from Power Apps by calling the Lookup function. If there's a requirement to filter data by content that spans multiple tables, the formulas we use are typically non-delegable and will not return all of the expected data.
  • Ease of use - From Power Apps, it can be very difficult to construct the correct formula to join multiple tables with Lookup, especially in cases where traverse 3 or more tables. Additionally, related tables often share the same column names, which makes it necessary to properly understand disambiguation syntax.
The post beneath highlights some of hte headaches of joining tables from Power Apps without views.
https://powerusers.microsoft.com/t5/Building-Power-Apps/Surpassing-the-500-limit-with-SQL-Collection/m-p/940472#M298718

How to create a View

To create a view, the simplest way is to use the graphical designer from SQL Server Management studio. From the Views folder beneath the target database in Object Explorer, we use the 'right-click' New View menu item to open the graphical designer, as shown beneath. Here, we can construct a query that joins muliple tables, and we specify the output columns of the view.


In this example, we'll create a view that joins the Issue, Tenant, and Property tables. We'll name this view vw_IssueDetails. The T-SQL for this view looks like this:

CREATE VIEW vw_IssueDetails
AS
SELECT dbo.Tenant.Firstname,
       dbo.Tenant.Surname,
       dbo.Issue.Description,
       dbo.Issue.IsEmergency,
       dbo.Issue.CreateDateTime,
       dbo.Property.Address1,
       dbo.Property.Address2,
       dbo.Property.City,
       dbo.Property.Postcode,
       dbo.Issue.IssueID
FROM dbo.Issue
    INNER JOIN dbo.Property
        ON dbo.Issue.PropertyID = dbo.Property.PropertyID
    INNER JOIN dbo.Tenant
        ON dbo.Issue.TenantID = dbo.Tenant.TenantID;

Displaying data from this view from Power Apps

From Power Apps, we can now add the view through the data panel and  can use it as the data source for galleries and other data controls.

As the screenshot below illustrates, we can change the standard formula on a gallery control to refer to the view, rather than the underlying 'issue' table. Notice how it's possible to extend the target search columns by specifying the additional columns in the call to the Search function.

How to update records

The part that app builders tend to struggle with, is how to confguring an app to modify the record that a user selects through a gallery control that is bound to a view.

To add to this feature to our auto-generated app, we can start by applying the technqiue that I described in my post here:

First, we modify the OnSelect property of the gallery control with the formula beneath. This sets the global variable varCurrentRecord to the value of the selected record in the gallery, by referencing the ThisItem keyword.
 
Set(varCurrentRecord, ThisItem);
Navigate(DetailScreen1, ScreenTransition.None)


Next, we set the Item property of the display and edit forms to the formula beneath. Note that we can change any other references to BrowseGallery1.Selected (such as those on the delete icon) to the same formula.

LookUp('[dbo].[Issue]', IssueID=varCurrentRecord.IssueID)


This completes the change to the app, and users will now be able to modify the selected record through the edit form.

Conclusion

In cases where we need to join tables and to display details from related tables, the most efficient way to perform this task is to use a view. This post provided an introduction on how to display related data on a gallery control, and how to configure an app to update a record that a users selects through a gallery control that is connected to a view.
  •   Categories: 
  • sql
Previous
Next
Related posts