How to create a Canvas Apps table that contains all grandchild records of a selected record?

Like (0) Share

Report

Posted on 12 Feb 2021 21:52:12 by RandyBristol

Hello,

I am working with some Dataverse/CDS tables where the Game Types table has a many-to-many (N:N) relationship to the Round Types table which has a many-to-many (N:N) relationship with the Station Types table. In my Canvas app, I would like to create a table that contains all of the Station Type records that are related to all of the Round Type records that are related to the selected Game Type record in a drop-down control. Or to put it another way, find all of the Round Types records that are related to the selected Game Type record (easy to do), and then find all of the Station Types records related to each of those Round Types records (hard to do?), and put them in a table.

I understand how to use dot notation to access one level of parent and/or child records however this will only allow me to traverse one level and I need to traverse two levels. If the dot notation traversed more than one level then my formula for the table would be easy like this:

drpGameTypes.Selected.'Round Types'.'Station Types'

However, when I try this I get the error message "The specified column is not accessible in this context." with a red squiggly underline under the .'Station Types' portion of the formula.

Can this be done in Canvas Apps? If so, how?

Categories:

Using Apps Using Formulas

All responses (4)

Answers (0)

RandyBristol 22 on 15 Feb 2021 at 19:34:00

Like (0)

Report

Re: How to create a Canvas Apps table that contains all grandchild records of a selected record?

OK, so if Microsoft doesn't want me to traverse multiple levels of child relationships in one formula, how about I split the formula into two so that each relationship traverse is in a separate formula?

To test this, I created a button that created a collection for just the Round Types records:

ClearCollect(
colRoundTypes,
drpGameTypes.Selected.'Round Types'
)

This works fine to put the correct related child Round Types records into a collection. (I do note, however that the only field value it brings in is the Round Type field value, the GUID for the record).

Then I created another button to create a collection for the Station Types records that are related to each Round Types record in the colRoundTypes collection:

ForAll(
colRoundTypes,
Collect(
colStationTypes,
ThisRecord.'Station Types'
)
)

Unfortunately, this formula still produces the syntax error "The specified column is not accessible in this context" on the ".'Station Types" area of the formula.

It feels like Microsoft REALLY REALLY doesn't want me to traverse more than one N:N child relationship to "grandchild" records in the same app.

Ideas?
RandyBristol 22 on 15 Feb 2021 at 19:19:37

Like (0)

Report

Re: How to create a Canvas Apps table that contains all grandchild records of a selected record?

So I think I understand that dot notation is only going to allow me to traverse one level (up to a parent or down to children) at a time. That is why this won't work:

drpGameTypes.Selected.'Round Types'.'Station Types'

So I tried to revise the formula to use two separate instances of referring to child records via dot notation by adding a ForAll() function like this:

ForAll(
drpGameTypes.Selected.'Round Types',
Collect(
colStationTypes,
ThisRecord.'Station Types'
)
)

Unfortunately I am still getting the syntax error 'The specified column is not accessible in this context' on the ".'Station Types" area of the formula. Note that a formula with:

drpGameTypes.Selected.'Round Types'

works fine on its own to return related child Round Type records, and a formula with:

drpRoundTypes.Selected.'Station Types'

works fine on its own to return related child Station Type records, but when I try to combine them both in the same formula using a ForAll() function (substituting "ThisRecord.'Station Types'" for "drpRoundTypes.Selected.'Station Types'") I get the syntax error.

It feels like Microsoft REALLY doesn't want me to traverse more than one relationship in the same formula.

Does anyone have any ideas on how to accomplish this?
RandyBristol 22 on 15 Feb 2021 at 17:51:50

Like (0)

Report

Re: How to create a Canvas Apps table that contains all grandchild records of a selected record?

@Drrickryp, Thanks for pointing me in this direction. The Relate and Unreleate functions are indeed great additions to PowerApps that allow us to relate records using an N:N relationship from Canvas Apps. The records in my tables are already related to each other however so I am not sure if or how these functions can help me in this situation.

The issue that I am having is that I need to traverse two relationships at once (Game Type N:N Round Type N:N Station Type) to build a table with all of the Station Type records that are related indirectly to the Game Type record through the Round Type records.

I realize that I could just relate all of the Station Type records directly to the Game Type records in Dataverse/CDS but this creates a user experience (for record setup and maintenance) and de-normalized data nightmare.

So, I am looking for a way to create a table in PowerApps Canvas that lists all of the Station Type records that are already related (N:N) to all of the Round Type records that are already related (N:N) to the selected Game Type record.

Does anyone know if this can be done and how to do it?
Drrickryp Super User 2024 Season 1 on 12 Feb 2021 at 22:22:39

Like (0)

Report

Re: How to create a Canvas Apps table that contains all grandchild records of a selected record?

@RandyBristol

Please refer to this blog post to see if it has the answer to your question. https://powerapps.microsoft.com/en-us/blog/relate-records-in-many-to-many-relationships/