cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kosenurm
Post Partisan
Post Partisan

Relationship between tables

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

 

RolePermissionSensitive permission
Customer RepCreate a new purchase 
Customer RepEdit a purchaseYes
Customer RepDelete a purchaseYes
ManagerOverride purchasesYes
ManagerView 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

 

UserRole heldContains sensitive?
User1Customer RepYes
User2ManagerYes
User3CleanerNo
User4ClericalNo

 

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

K.

1 ACCEPTED SOLUTION

Accepted Solutions
dpoggemann
Super User
Super User

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.

  1. 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:
    1. Total Sensitive - Whole Number field
    2. Contains Sensitive - Yes / No field that should be a Calculated field if Total Sensitive > 0 then set to Yes otherwise set to No.
  2. 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
  3. 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".
    1. 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.
    2. Make the "Contains Sensitive" field on the form read only as it will be set by the realtime workflow only
    3. For all users (except system admin) do not allow "write" to this record, only create / delete
    4. Setup realtime workflow on the Delete of the Role Permission record to update the Total Sensitive on the Role to reduce by 1.
  4. 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.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

3 REPLIES 3
eleung83
Resolver I
Resolver I

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

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.

dpoggemann
Super User
Super User

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.

  1. 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:
    1. Total Sensitive - Whole Number field
    2. Contains Sensitive - Yes / No field that should be a Calculated field if Total Sensitive > 0 then set to Yes otherwise set to No.
  2. 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
  3. 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".
    1. 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.
    2. Make the "Contains Sensitive" field on the form read only as it will be set by the realtime workflow only
    3. For all users (except system admin) do not allow "write" to this record, only create / delete
    4. Setup realtime workflow on the Delete of the Role Permission record to update the Total Sensitive on the Role to reduce by 1.
  4. 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.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,701)