Accessing the 'Relationship Entity' associated with a many-to-many relationship

[bfing]

I am using CDS and I have a many-to-many relationship between Entity A and Entity B.

In Power Apps, I'm trying to collect all the related records of Entity B from a specific record in Entity A.

Previously, I've used a lookup similar to:

ClearCollect(colEntityBRecords,Lookup(EntityAs,GUID=TargetGUID).EntityBs)
//note the plural on Entity B after the lookup referencing the relationship to Entity B, returning a table of records

 This has been working great, however, now the quantity of Entity B records that I'm collecting in this way is greater than 500 (also less than 2000) and I only collect the first 500 Entity B records.

I understand CDS will make a joining table automatically (i.e. the 'Relationship Entity' as CDS calls it) when a many-to-many relationship is first created - by default it is named something like EntityA_EntityB using logical names.

Questions:

As a workaround to the 500 row limit, is it possible to access this automatically generated joining table (i.e. the Relationship Entity) and filter on the Entity A side and collect the resulting records on the Entity B side?  If so, can you please provide a sample of how to reference this joining table and how to collect the related Entity B records?

I've tried referencing the EntityA_EntityB 'Relationship Entity' name displayed on the relationship screen in CDS, but it is not recognized by Power Apps.  

Lastly, I've modified the Data Row Limit in settings from 500 to 2000.

[Drrickryp]

Hi @bfing 

It is not possible to access the Joining table when using the OOB many-to-many relationship in CDS.  This is one of the reasons that I never use it.  You can create your own Joining table as a new entity and use the One to many relationships on both sides of the Joining table.  One major advantage to doing this is that you can add additional fields that are specific to the Joining table.  For example, in a database with Students and Classes, a joining table could include the grades, dates of attendance, comments and other fields that would not be appropriate in either the Students or Classes tables.  This is impossible to do with the OOB many-to-many relationship in CDS

[v-siky-msft]

Hi @bfing ,

No way, the relationship entity isn't available.

However, I just found it is delegable to reference the related record of entityB using your code.

I set the data limit to 2, but it still generates all relate records to collection, which proves the code is delegable.

Please check your app to see if there is anything else that caused the delegation issue.

Sik

[bfing]

Thank you for your feedback.  You answered my initial question about not being able to reference the Relationship Entity for filtering, etc.

However @v-siky-msft , you raise a different point so I updated the subject of this post and am removing the ClearCollect from the discussion.  I am not getting a delegation warning on the code either.  However, despite no warning, I'm not retrieving all the records.  To illustrate, I did a couple tests:

Test 1 - I created a label counted all the records in the entity DCQuestions as follows:

"Qty of DCQuestions: " & 
CountRows(DCQuestions.DCQuestion)

The resulting label indicates there are 708 records.

Test 2 - To ensure every record of this entity (DCQuestions) is related to one specific record of the other entity (DCLogs), I ran the following on a button press (this took a couple minutes)

ForAll(
    RenameColumns(DCQuestions,"crec7_dcquestionid","rcDCQuestionID"),
    Relate(
        Lookup(DCLogs,DClog=galDCLogs_0.Selected.DCLog).DCQuestions,
        LookUp([@DCQuestions],DCQuestion=rcDCQuestionID)
    )
)

Then using the same lookup in my initial post and in your post, I counted the rows across the relationship

"Qty from Lookup: " & 
CountRows(
    LookUp(
        DCLogs,
        DCLog=galDCLogs_0.Selected.DCLog
    ).DCQuestions.DCQuestion
)

The resulting label indicates there are 500 records. not 708.

In your Product/Contact scenario, are you getting more than 500 records using the lookup?  Or is there a 500 record limitation when using LookUp in this manner? 

Note:  I have the 'Enhanced Delegation for Common Data Service' setting enabled.

[v-siky-msft]

Hi @bfing ,

RenameColumns() function isn't delegable, so if you set the delegation threshold to 500, it only return a 500 rows table, which only 500 rows can be related to specific record of DCLogs. 

Since you only have 700+rows, if you increase the threshold to 2000, the delegation issue won't have any influence.

Sik

[bfing]

@v-siky-msft , I agree the delegation issue should have no influence when the limit is set to 2000 (since I only have 700+ records).  However, the limit is/was set to 2000 already.  So everything should have been successfully related to the selected DCLog record.  

You hadn't responded to the following questions from my previous post:

In your Product/Contact scenario, are you returning more than 500 records using the lookup?  Or is there perhaps a 500 record limitation when using LookUp in this manner? 

Thank you for your continued assistance with this question.