Relationship between tables

(0) Share

Report

Posted on by Kosenurm

802

Hi there,

I have two Dataverse tables which I need to create a relationship between.

The first table lists user roles for one of our legacy systems, and includes the permissions within each role, and whether or not the permissions are sensitive so let's say...

Role	Permission	Sensitive permission
Customer Rep	Create a new purchase
Customer Rep	Edit a purchase	Yes
Customer Rep	Delete a purchase	Yes
Manager	Override purchases	Yes
Manager	View purchases

I then have another Dataverse table which lists all of the users and the roles they hold. What I want is, if the user holds the role of say, Customer Rep, for the Contains Sensitive? field to be updated like below on this other DV table. So, basically, for the column to lookup on the other dataverse table any row where the Role held as the same as role, and display back Yes in the below in Contains sensitive? if any of the permissions within that role on the other table are Yes for Sensitive permission

User	Role held	Contains sensitive?
User1	Customer Rep	Yes
User2	Manager	Yes
User3	Cleaner	No
User4	Clerical	No

I don't know where to begin here. There are hundreds of roles and some permissions are repeated across roles (e.g. Manager holds the same permissions as Customer Rep, plus other permissions some of which are and aren't sensitive)

Please help with where I can begin. This is to then display the information in a model driven app.

Thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

eleung83 232 on at

Like (0)

Report
Copy link

Link copied!

From memory, D365 doesn't natively accommodate doing aggregations for boolean fields (your Yes/No option for contains sensitive).

There's a few ways you could approach this, of which I list 2:
1. If you need to show a Yes/No for contains sensitive, then I would create a separate role entity and add a Yes/No field to the role entity. I would then have plugins/workflows that capture whenever a new permission was created for this role, or an existing permission updated for this role, to track whether any permissions related to this role had the "Sensitive permission" set to "Yes". I would then update this new "Yes/No" field on the role entity to indicate whether this role had any associated permissions which had the "Sensitive permission" set to "Yes".

2. If you have flexibility in how the "Contains Sensitive" column is displayed (i.e. show a number instead of Yes/No), you could do the same as (1) with the new role entity, but then use a rollup field instead of the "Yes/No" field to track the number of sensitive permissions a role has, Note that the rollup field is async and is based on a schedule so wouldn't be realtime (unless you triggered in manually). From your 2nd table you could then show how many sensitive permissions a user has based on each role they hold

Was this reply helpful? Yes No
Kosenurm 802 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your reply. Most helpful. Thankfully the column type is Text... I have just used Yes or No in it.

I think I will go with option two. How can I trigger a rollup field manually?

Thanks
K.

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Kosenurm ,

You might want to think about the following data model to normalize and allow for easier management. I also provided a different approach (although it has considerations at the end) to not need to utilize the rollup field. Rollup is a good approach though if you would like to utilize.

Role table - This will contain the list of all the unique roles. Each role will be in this table 1 time. Add the additional two fields as well:

Total Sensitive - Whole Number field

Contains Sensitive - Yes / No field that should be a Calculated field if Total Sensitive > 0 then set to Yes otherwise set to No.

Permission table - List of the unique permissions that exist. Each permission will be in this table 1 time. The permission table would have the "Contains Sensitive" field

Role Permission table - Intersect table that will contain a lookup to the Role table and the Permission table and will contain the field for the "Contains Sensitive".

Set the "Contains Sensitive" field with realtime workflow on creation of record. Run real-time workflow as a system administrator permissioned user. Second step in this workflow should add 1 to the count of "Total Sensitive" on the Role table if the permission is sensitive.

Make the "Contains Sensitive" field on the form read only as it will be set by the realtime workflow only

For all users (except system admin) do not allow "write" to this record, only create / delete

Setup realtime workflow on the Delete of the Role Permission record to update the Total Sensitive on the Role to reduce by 1.

User Role - This table will contain the assignment of the User to the Role. You should be able to see in a View the User and the value from the Role table's Contains Sensitive field (by adding related fields to the view)

Considerations with not using rollup:

If you change the permission table record on the specific permission value this will not change all the role permissions and you would need to setup a flow that would do the following:

Update all Role Permission records that are tied to the changed permission to update the Contains Sensitive value

Update every Role Total Sensitive that contains the updated permission to set the updated total on the count

Just another approach / thought.

Was this reply helpful? Yes No