cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
halifaxious
Resolver II
Resolver II

Relate records in a many to many

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
halifaxious
Resolver II
Resolver II

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".

 

 

View solution in original post

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)')
        ) 
    )
);

 

View solution in original post

3 REPLIES 3
halifaxious
Resolver II
Resolver II

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?

halifaxious
Resolver II
Resolver II

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".

 

 

View solution in original post

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)')
        ) 
    )
);

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (2,543)