Relate records in a many to many

(0) Share

Report

Posted on by halifaxious

217

I am going right round a tree with this thing. 😵Please save me!

I'm building an embedded canvas app for my model-driven app. There are 2 tables, 'PRA Scores' and 'PRA Answers' that are in a many to many relationship with each other. The relationship is called new_PRAScores_have_PRAanswers. The corresponding system table (that invisible thing I can't touch. Grr!) is called new_prascores_have_praanswers.

When I come to update/create PRA Score records, I can do everything but relate the PRA Answers to the record. The error says 'PRA Scores'.'PRA Answers' is not accessible in this context. Here's my code:

//patch Dataverse table from Collection
Patch('PRA Scores',
 ShowColumns(ResponseCollection,
 "new_PRAQuestion","new_prascoreid","new_PrimaryRiskAssessment","new_score","new_scores")
);

//refresh same Dataverse table
Refresh('PRA Scores');
ClearCollect(patchedScores, Filter('PRA Scores',ThisRecord.'Primary Risk Assessment'.Activity=[@ModelDrivenFormIntegration].Item.Activity));
ForAll(patchedScores As S,
 ForAll(Filter(ResponseCollection,new_PRAQuestion.'PRA Question'=S.'PRA Question'.'PRA Question').theAnswers As A,
 Relate(S.'PRA Answers',A) 
 )
);

Why is S.'PRA Answer' not available in this context? What should I do instead? And why is something that should be simple so hard?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (2)

halifaxious 217 on at

Like (0)

Report

Well, I found part of the reason things weren't working. Apparently, to relate many-to-many tables in a canvas app, the experimental feature "Record scope one-to-many and many-to-many relationships" must be turned on. So I did that. Now I don't get the context error any more. But no records become related to each other. Why?

Was this reply helpful? Yes No
Verified answer

halifaxious 217 on at

Like (0)

Report
This was a beastly problem because I needed to not create/update Dataverse until all the questions were answered and the user pressed 'Save Test'. Here's how I solved it:

local collection ResponseCollection that was an exact duplicate of the Dataverse table PRA Scores, created like this

ClearCollect(ResponseCollection, Filter('PRA Scores',ThisRecord.'Primary Risk Assessment'.Activity=[@ModelDrivenFormIntegration].Item.Activity) );

previously I had only used the columns I needed and I had renamed some for convenience
update ResponseCollection like this

f( IsBlank(LookUp(ResponseCollection,new_PRAQuestion.'PRA Question'=currentQuestion.qGUID)), Collect(ResponseCollection,{new_PRAQuestion:LookUp('PRA Questions','PRA Question'=currentQuestion.qGUID),new_score:Sum(Self.SelectedItems,weight), new_PrimaryRiskAssessment:[@ModelDrivenFormIntegration].Item, 'PRA Answers':Blank()}), Patch(ResponseCollection,LookUp(ResponseCollection,new_PRAQuestion.'PRA Question'=currentQuestion.qGUID), {new_score:Sum (Self.SelectedItems,weight), new_PrimaryRiskAssessment:[@ModelDrivenFormIntegration].Item,'PRA Answers':Blank()}) ); //removing any existing relations to 'PRA Answers' table ForAll(Self.SelectedItems As A, Relate(LookUp(ResponseCollection,new_PRAQuestion.'PRA Question'=currentQuestion.qGUID).'PRA Answers', LookUp('PRA Answers',new_praanswerid=A.answerGUID)) ); //answerList dropdown is a local collection of 'PRA Answer' records

save to Dataverse like this

//patch Dataverse table from Collection Patch('PRA Scores', ShowColumns(ResponseCollection, "new_PRAQuestion","new_prascoreid","new_PrimaryRiskAssessment","new_score","new_scores","new_PRAScores_have_PRAanswers") );

I still don't understand why I can use Relate() on a local collection but not on a Dataverse table with the identical structure. And I have to say that with many-to-many, MS has a long way to go before they can call their solution "low code".

Was this reply helpful? Yes No
Verified answer

halifaxious 217 on at

Like (0)

Report
And sadly, there is a postscript to this adventure. The solution code I had stopped working. I wasted days until it finally occurred to me to check the Power Apps release # for the last working version of my code. Sure enough, the code in the accepted solution worked under 3.21081.48 and stopped working under 3.21081.49.

I've reverted to something similar to the code in my original question. It's working. But as of the time that I reverted, the Power Apps release # was 3.21082.28. So who knows whether it will continue to behave!

And to add insult to injury, I cannot find detailed release notes for any of the patch version. Very not happy. Here's the code I'm using now, in case it helps someone in a similar situation:
//patch Dataverse table from Collection Patch('PRA Scores', ShowColumns(ResponseCollection, "new_PRAQuestion","new_prascoreid","new_PrimaryRiskAssessment","new_score","new_scores","new_PRAScores_have_PRAanswers") ); //refresh same Dataverse table Refresh('PRA Scores'); ClearCollect(patchedScores, Filter('PRA Scores',ThisRecord.'Primary Risk Assessment'.Activity=[@ModelDrivenFormIntegration].Item.Activity)); Patch(patchedScores,{'PRA Answers': Blank()}); //unrelate all answers from each score-- this is currently not working so the next step adds relationships on top of any old ones. :( ForAll(patchedScores As S, ForAll(Filter(ResponseCollection,new_PRAQuestion.'PRA Question'=S.'PRA Question'.'PRA Question') As R, ForAll(R.theAnswers As A, Relate(S.'PRA Answers',A); Notify(A.'PRA Answer (English)') ) ) );

Was this reply helpful? Yes No