Relationship Behaviour for Three Table Record Deletion

(2) Share

Report

Posted on by pp365

439

Hi all,

I have a requirement from a user where if they delete a record in a table, the appropriate records are deleted in two related tables. Per the structure in this simple diagram, if the user deleted a record in Table A, the appropriate record(s) are deleted in Table B and Table C.

I am familiar with the Parental relationship type, but my understanding is this only applies to one 'layer', for example, if a Parental relationship was set between Table A and Table B, and then a Parental was set from Table B to Table C, the user would have to delete the record in Table B first, which would delete the corresponding record in table C, then they would delete the record in Table A.

I thought to use the Custom / Cascade All relationship behaviour (as in below screenshot) to achieve the requirement.

Question 1: Would this fulfil the requirement (if a record in Table A is deleted, the corresponding records are deleted in Tables B and C).

Question 2: If the answer to Q1 is yes, presumably I just need to configure this relationship behaviour between A and B and B and C?

Question 3: If the answer to Q1 is no, what would be the way to achieve this requirement?

Question 4: I have tried setting up a Custom / Cascade All between Table A and Table B. No error message is generated and the save operation seems to complete. However when I open the relationship and expand Advanced Options, the relationship behaviour displays as 'Parental' and not the 'Cascade All' attribute. What am I doing wrong here?

Thank you very much for your help!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (2)

pp365 439 on at

Like (0)

Report

Can anyone help please...?

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
Hi @pp365,

Picking this up as no-one else has to date. I am not a Dataverse user, so will give you a possible alternative (note ThisItem may be something else depending where you are running this from
With( { _Data: Filter( Table2, KeyField2 = ThisItem.JoinField1 ) }, ForAll( _Data As _D, RemoveIf( Table3, KeyField3 = _D.JoinField2 ); RemoveIf( Table2, KeyField2 = ThisItem.JoinField1 ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
pp365 439 on at

Like (0)

Report

Hi @WarrenBelz,

Thanks for getting back to me, however, I want to achieve this within Dataverse at the data layer rather than in Power Fx in a Canvas etc. This data will be represented in a Model Driven App for the end user not using a Canvas App.

Many thanks.

Was this reply helpful? Yes No
Verified answer

Parvez Ghumra 1,579 Moderator on at

Like (2)

Report

Hey @pp365,

I believe this should just work using standard parental relationships as the delete operation on the parent table should just cascade the deletion down to the child table. So if you configure both relationships to be parental, this should just really be out of the box. ie. If a user deletes a record in table A, all related child records in table B should be deleted automatically, and the latter should also cause the same to trickle down to the related records in table C.

Have you tried this? Does it not work? I'd recommend testing it out, as I would expect it to just work

Was this reply helpful? Yes No
Verified answer

pp365 439 on at

Like (0)

Report

Many thanks @Parvez Ghumra, I can confirm your recommendation of setting a Parental from A to B and B to C does work as required, IE, a record is deleted in table A and the record(s) related to it in Tables B and C are deleted correspondingly.

I was previously under the impression that the Parental relationship connection would only apply to one layer of tables, and not more than one which caused the confusion.

For completeness, to address this part of the original post:

Question 4: I have tried setting up a Custom / Cascade All between Table A and Table B. No error message is generated and the save operation seems to complete. However when I open the relationship and expand Advanced Options, the relationship behaviour displays as 'Parental' and not the 'Cascade All' attribute. What am I doing wrong here?

I suspect that as I wasn't changing any of the attributes within the Relationship Behaviour Custom section (Delete, Assign, etc), the configuration was essentially Parental, hence why when I returned to this screen Dataverse had automatically reverted from Custom to Parental.

Was this reply helpful? Yes No