PowerApps Tip: Create SQL Views

Another tip that I’m putting up here so that I can find it later myself.

Scenario: You’ve got a PowerApp that’s displaying data from a SQL database and you want to display information from multiple tables in a single gallery.

Issue: Each table has a different number of columns and matching columns have different names in different tables. This makes adding all the tables to a single collection infeasible (and would be too resource intensive anyway).

Solution: Create a view in SQL that joins the disparate tables while dealing with the different column names at the same time.

CREATE VIEW <ViewName> AS

SELECT <Column1> as <Column1>, <Column2> as <Column2>, 'Source1' as "DataSource"
FROM <Table1>
WHERE <Condition>;

UNION

SELECT <Column3> as <Column1>, <Column4> as <Column2>, 'Source2' as "DataSource"
FROM <Table2>
WHERE <Condition>;
  • Using “as” in the SELECT statement enables us to name the final column that will appear in the view
  • We can create and populate new columns (like DataSource) by using inverted commas
  • The UNION statement will add the second table into the same view, using the column headings we define with the “as”
  • Any number of UNION statments can be used
  • We can use the WHERE statement to filter the records returned to the view

By using SQL views, we can cut down the amount of work that has to be done at run time in the app.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s