Displaying a joined value in a data table

(0) Share

Report

Posted on by glower power

161

Hello,

I added a Data Table to my Canvas App using the following

Filter('dbo.status_schedule', EVENT_ID = selectedEvent.Selected.ID, HIDDEN_FLAG = "N")

This returns a simple base table which simply uses the ID value from the user driven selectedEvent.Selected.ID.

I add 3 columns, STATUS_ID, PLANNED_DATE, ACTUAL_DATE which all display fine.

My question is, I have another database table. 'dbo.status' that has the actual name of the STATUS and not just the ID (ID is foreign key in previous table). I want to display the STATUS from this second table in the first column, instead of just the STATUS_ID. Is there some way to utilize SortByColumns(AddColumns( or Filter( formulas for something like this? Essentially referencing multiple SQL/Oracle tables in one data grid.

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

NickMerrick 419 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

You can use the AddColumns function to add the data per row of what you need.. I.e. add the Status column with the lookup condition of Status id. Or, you just create a view in SQL with a JOIN clause and display that on the table.
glower power 161 on at

Like
a
(0)

Report
Copy link

Link copied!

@NickMerrick Can you provide an example based on the formula I provided in teh first post? I tried

SortByColumns(AddColumns('dbo.status', "NEW_COL_TEST", "ThisItem.STATUS_ID", "TYPE_ID"))

Error is - Invalid number of arguments - received 4, expected an odd number.
Verified answer

NickMerrick 419 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
SortByColumns(AddColumns('dbo.status_schedule',"STATUS_Test",Lookup('dbo.status',STATUS_ID = 'dbo.status_schedule'[@STATUS_ID])),"TYPE_ID")
I'm assuming STATUS_ID is the column name for both collections, please replace with the respective collections actual STATUS_ID name.