Welcome to all my blog readers. I am back with another blog on PowerApps.
Today I will discuss on how to use the AddColumns function of Power Apps with lookup fields in SharePoint. If you are looking to query parent table data from Lookup columns, you will also find this blog useful.
For this demo I have created couple of Sharepoint lists. I have a list named Report Inventory which show the list of Reports by Category.
I have another list named Report Activity where I have a column named Report. This column is a lookup to the Report Inventory table.
Whenever a report is accessed or modified, a record will be created in the Report Activity list. Example of few records.
My requirement is to query the Report Activity table and show them in a gallery. When doing that I want to fetch data for the related report from the lookup field. Below is the formula for the Items property of the Gallery.
AddColumns(
'Report Activity',
"Category",
LookUp(
'Report Inventory',
ID = Report.Id
).Category
)This formula is very simple. I fetch the Category column of the Report Inventory table through LookUp function of Power Apps.
The formula does not give me any error. But the problem is the way data is returned by Power Apps. If I evaluate this formula, below is the format of the data returned by Power Apps.
Now when I bind the Category column to a control in the gallery it does not show me anything.
So what should be the correct way? Below is the formula which works correctly.
AddColumns(
'Report Activity',
"Category",
LookUp(
'Report Inventory',
ID = 'Report Activity'[@Report].Id
).Category
)Look at the part highlighted in bold. You need to query in the format Table[@<lookup column name>]. Since I am comparing the Id field, I used .Id after that.
And that’s it. Now when I bind, I get the Category returned by query.
Hope this helped. You all also like the below posts.
Debajit Dutta
Business Solutions MVP