How to patch related Dataverse Tables?

[alexesprtu29]

Hello, so I have a couple of tables in Dataverse and I'm doing a soft-delete function (just setting a status to inactive).

Roles - List of available roles when onboarding.

Documents - List of available documents when onboarding.

RoleDocuments - Mapping of roles to documents (both just lookup columns)

Role

Role 1

Role 2

Role 3

Role 4

Documents

Doc 1 

Doc 2 

Doc 3 

RoleDocuments

Role 1 - Doc 2

Role 1 - Doc 4 

Role 2 - Doc 1

Role 2 - Doc 2

I was already able to do the patching for direct deletes (deleting an item in the documents/roles table).

However I am having some trouble with deleting the associated roles/documents in the RoleDocuments table when I am deleting a Role/Document. Not sure if I'm making sense but if I delete Doc2 then the mapping of all roles with doc2 on them should be inactive as well.

ForAll(
    Filter(
        RoleDocuments,
        DocID.'Document Name'= ThisItem.'Document Name'
    ) As _item,
    Patch(
        RoleDocuments,
        LookUp(
            RoleDocuments,
            DocID.DocID = ThisItem.DocID
        ),
        {cr2d9_roledocumentstatus: 'Role Document Status (RoleDocuments)'.No}
    )
);

This is the first thing I tried but it says that the function cannot operate on the same datasource. I did see on another answer that I can just rename the ID column differently to make it work so it did, however it only updates one row in the RoleDocuments column. Any help would be appreciated! 

[AhmedSalih]

Hello, @alexesprtu29, another easier way to do this is using a collection, UpdateIF, and Patch:

ClearCollect(colRoleDocuments,
Filter(
        RoleDocuments,
        DocID.'Document Name'= ThisItem.'Document Name'
    ) 
);
UpdateIf(colRoleDocuments,true, 
{cr2d9_roledocumentstatus: 'Role Document Status (RoleDocuments)'.No}
);
    Patch(
        RoleDocuments,
        colRoleDocuments
);

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[AhmedSalih]

Hello

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.

, @alexesprtu29, I wanted to check and see if my recommended approach worked for?