How to show columns from multiple tables in a Canvas app Gallery or Table

I am back with another blog on PowerApps and this time it’s on Canvas apps. With canvas apps gaining popularity among customers by each day, more and more possibilities are explored and as a consultant I am exposed to a wide array of customer requirements.

And here in this blog, I am going to discuss about a requirement which is pretty simple but again frequently required in projects. In this blog I am going to focus on the below topics.

• How to show data from multiple tables in a Gallery control/ DataTable control of Canvas app
• How to dynamically add columns from different tables to create a single view and display in Gallery control of Canvas app.

So let’s get started here. Whatever I show here would be applicable for any datasource, be it tables from excel, SharePoint or CDS. Being a Dynamics 365 veteran, I am going to showcase this with CDS entities. If you are in this blog and you need similar functionalities with tables from Excel, the same concepts shall work for you.

I have taken a Data Table control and inside the control I want to display data from both the Account and Account Address (custom entity) entities of Common Data Service (CDS). Every Account have few addresses which are synced from external systems to ‘Account Address’ entity in CDS. Sample fields for Account address entity illustrated below.

There is no relationship between the entities. However address of each account can be found based on the Account code value which is same as the Account number field in Account entity. In this example, say I want to fetch the City and Pincode value for each address and show in the DataTable control.

I set the Items property of the DataTable to the following formula.

AddColumns(Accounts, 
"City", 
LookUp('Account Addresses', 'Account Code' = 'Account Number' && Text('Address Type') = "Global Sales Office").City,
"Pincode",
LookUp('Account Addresses', 'Account Code' = 'Account Number' && Text('Address Type') = "Global Sales Office").PinCode)

While the above code shall work, there are certain glaring flaws in the above code. The first and foremost is that we are using the Lookup function each time to get a related column value. And this is what most beginners tend to do.

We actually don’t need to do it. Let’s approach our scenario with a different formula this time.

AddColumns(Accounts, 
"AccountAddress", 
LookUp('Account Addresses', 'Account Code' = 'Account Number' && Text('Address Type') = "Global Sales Office"))

Instead of taking individual fields, I take the AccountAddress object. So I just use one Lookup function. Now that I have the Account Address object with me, I can now use the properties of Account address for each column. Example screenshot below.

Before I end this post, one quick point here. The output of “AddColumns” function is subject to the non-delegation record limit. In this example, only 500 records (or whatever delegation limit you have set) are returned even if the AccountAddress data source has 501 or more records.

You may also like the below posts

Debajit Dutta
Business Solutions MVP