3 minute read

Since I was testing and discovering Microsoft  PowerApps I wanted to create a new app that would be able to create new invoices and expenses from my mobile phone. To be able to do this I would need to link 2 different data sources (in my case 2 different SQL tables) together and perform an inner join or something similar. At present, this is something that is not clearly documented, hence this blogpost.

Backend infrastructure

First of all let’s build the required backend to support my test application.

I quickly created a SQL database hosted in Azure with couple of tables and foreign key relationships. For example in my invoice table I would have a ContractorID column with a relationship to the Contractor table containing more information regarding the contractors.

sqldiagram

Build your app in PowerApps

Now that we have our backed database we can start building our PowerApp. First sign up to PowerApps through your Office365 subscription or follow this tutorial: https://powerapps.microsoft.com/en-us/tutorials/signup-for-powerapps/

Once you are signed up, to build your new app go to https://web.powerapps.com and click new App.

file-new

In PowerApp you can retrieve data and make connections to a large number of different sources like Office 365, Dropbox, Twitter, and other common SaaS and enterprise services. We are going to create a new connection to our SQL server that is running in Azure and select our invoice table.

dataset

PowerApps will then start building your app based on that table and will create 3 views by default: 1 browse screen, 1 edit and a new item screen.

As you can see above my app is not showing me the data of my other tables, it’s just showing me the foreign key ID’s. To be able to show data from the other tables you have to add the other tables as separate data sources:

  • In the right-hand pane, click or tap the Data sources tab, and then click or tap Add data source.
  • If the right-hand pane doesn’t show a Data sources tab, click or tap any screen in the left navigation bar.

Linking 2 PowerApps data sources

Now that I have my different datasources in PowerApps I looked up how to inner join data from 2 different SQL tables expecting I could use inner joins just like in SQL. However at the time of writing you cannot use inner joins at all! Hopefully this feature will come very soon.

Despite searching everywhere on the internet on how to link 2 SQL tables with an inner join together I couldn’t find any relevant information on how to do it. The only chance I had was to see if I could achieve my inner join with the LookUp function in PowerApps. The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.

LookUp( Table, Formula [, ReductionFormula ] )

  • Table – Required. Table to search.

This would be in my case “dbo.Contractor” as I wanted to find the contractor name in the contractor table.

  • Formula – Required. This formula is evaluated for each record of the table, and the first record that results in true is returned. You can reference columns within the table.

For the formula I wanted to compare the ID within the contractor table with the ID of This Item. So I used: “ID=ThisItem.ContractorID”

  • ReductionFormula – Optional. This formula is evaluated over the record that was found, reducing the record to a single value. You can reference columns within the table. If this parameter is not supplied, the function returns the full record from the table.

And I only wanted to see the name of the contractor, not the whole record, so I specified Name for the reduction formula. This is the final formula:

 

LookUp('[dbo].[contractor]',ID=ThisItem.contractor,Name)

To change the displayed text, click on the text box and change the formula from this:

To this:

This is the final result with all the fields changed:

 

Hope this helps,

Alexandre Verkinderen

Leave a comment

You may also enjoy