cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SD_PowerUser
Frequent Visitor

Canvas App - How to get values from related table?

Hi there,

I have three tables. Table 1, Table 2 and Table 3. 

Table 1 is related to Table 2. 

  • Table 1 has following fields
    • Answer responses
    • Question Name
    • Lookup field to Table 2 

 

Table 2 is related to Table 3. 

  • Table 2 has following fields
    • Lookup field to Table 3 (Category)
    • Question Name

I am planning to create a grid which displays a count of responses based on the category and their possible responses (Table 3)

 

Currently, I am able to get the count responses by grouping the responses from Table 1 but I am unable to get the category values from Table 3.

I am able to achieve the Overview column which gives me the unique count based on the responses but I am unable to relate it to the "Category" column. What is the best way to relate Table 3 with Table 1?

I have tried various functions such With, ForAll and other references including but no luck. 

https://michelcarlo.com/2021/04/28/power-apps-merge-tables-collections-and-get-distinct-records-with...
https://www.youtube.com/watch?v=2n4j3t_0Obo

Here is an example of the grid that I am trying to achieve.

CategoryOverview
Category 1
Response TitleCount
Response A2
Response B3
Category 2
Response TitleCount
Response C1
Response D1
7 REPLIES 7
CNT
Super User
Super User

@SD_PowerUser Can you post your current formula in the Chat?

SD_PowerUser
Frequent Visitor

Hi there,

I have created a collection for table 1 which I have filtered based on my requirement that gives me responses, question name and lookup table to table 2. I am trying to add a column which gives me data from the Table 3.

For eg.
ClearCollect(
SpecificRecord_FromTable1,
AddColumns(ShowColumns(
Filter(
'Table1',
'Tabl1'.ID = GUID(ID)
),
"response",
"questionName",
"lookup_Table2"
), "CategoryValue", LookUp('Table3', Category = lookup_Table2.Category.CategoryId).Category)
);


This formula gives me blank values on the "CategoryValue" column in my collection. 

 

@SD_PowerUser Category = lookup_Table2.Category.CategoryId will not work. You can't go more than one level deep. So, first you have to do a LookUp to get the related record from Table2 and then do another LookUp to get the related record from Table3.

SD_PowerUser
Frequent Visitor

Ok, thanks for this @CNT.

So you are saying to do something like this?
ClearCollect(
SpecificRecord_FromTable1,
AddColumns(ShowColumns(
Filter( 'Table1', 'Tabl1'.ID = GUID(ID)),

"response",

"questionName",

"lookup_Table2"),

"CategoryValue", LookUp('Table3', Table3Id 

= Lookup(Table2, Table2Id

= [@Table1].Table2Id).Table3Id).Category));

 

Thanks heaps!

@SD_PowerUser Yes. Did it work?

SD_PowerUser
Frequent Visitor

Nope, it didn't. The category column doesn't display any value. It is blank. 
No errors are thrown as well. 

SD_PowerUser
Frequent Visitor

Conclusion: 
Closing this ticket as there was no straight forward solution. Had to do a work around in order to reach our objective. Thanks for your help. @CNT  

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,904)