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

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

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?

4 REPLIES 4
Drrickryp
Super User II
Super User II

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

RandyBristol
Frequent Visitor

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

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
Frequent Visitor

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?

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (107,690)