Canvas App - How to get values from related table?

[SD_PowerUser]

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.

Category	Overview
Category 1	Response Title Count Response A 2 Response B 3
Category 2	Response Title Count Response C 1 Response D 1

[CNT]

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

[SD_PowerUser]

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. 

[CNT]

@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]

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!

[CNT]

@SD_PowerUser Yes. Did it work?

[SD_PowerUser]

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

[SD_PowerUser]

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