Custom fields for department users best practices

[danielmbaquero]

Hello world,

In short, I want to know what the best practices are to create/add department specific information for users in dataverse.

Context:

I’m developing a solution to manage the different projects and activities within those projects in my department. Inside the department we have three different teams and work on the projects based on the requests from the other departments in the company. Also, within those teams there are different roles, there is a manager (that won’t be assigned to any specific team, but she would have total control), a coordinator for each team, analysts, and interns.

Current approach:

I created a new table (for the example I will call it DepartmentUsers) with a lookup (relationship) to the User standard table. In this new table I added another lookup/ relationship to a table where the names and acronyms of the teams are. The DepartmentUsers also has the role field.

As any activity can be assign to many department members (even across different teams) and any member can have many activities assigned to him/her (a many to many relationship), I created a junction table (based on multiple posts in the forum) to manage the relationship of DepartmentUsers with the custom table Activities.

Current problems:

I’m trying to create views and reports of this junction table to show the different activities a user is assign to and vice versa. However, as there isn’t a way to go 2 levels deep into relationships for views, I’m stuck with a view that shows the user id (that horrible hexadecimal code) instead of the name or email of the member.

This is also happening with the team’s id/name.

Question:

What is advised to tackle this kind of situations? What I did, creating the DepartmentUsers table, does not give me the flexibility for reporting that is needed. However, the other alternative is to add custom columns in the default User table to add this department-team-specific information. All the other members of the organization would have those fields empty and that isn’t a best practice in any database. Is there another approach I’m missing? Can I add those fields only to specific users in the organization?

[ChrisHarper]

Hi @danielmbaquero 

You are correct that you cannot directly go 2 levels into a Dataverse relationship, but you can use a Lookup function combined with dot.notation to achieve this

My recent recent blog post explains how to do this in full detail: https://hiredgun.tech/dataverse-relationships/

[danielmbaquero]

Hi @ChrisHarper,

I've read your post and notice that it is meant for canvas apps. How could I achieve something similiar in a model-driven app?

[rampprakash]

Hi @danielmbaquero,

Please use webapi to drill through two levels

Step 1 : First retireve First Level Entity Using WebApi

Step2 : Pass the Retrieved First level Entity (lookup) Values to second Level Entity and you can use it.

let me know if you need some samples

Please mark as Answer if it is helpful and provide Kudos

Blog : https://microsoftcrmtechie.blogspot.com
Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA

[danielmbaquero]

Hello @rampprakash,

Some examples would be great.

Thank you!

[rampprakash · Accepted solution]

Hi @danielmbaquero,

You can have a look on this

var storeParentResult="";
Xrm.WebApi.retrieveMultipleRecords("tableValue", "?$select=tableField&$top=1").then(
function success(result) {
//for (var i = 0; i < result.entities.length; i++) {
storeParentResult = result.entities;
// }

},
function (error) {
console.log(error.message);
// handle error conditions
}
);

var finalValues="";
if(storeParentResult.length>0){
Xrm.WebApi.retrieveMultipleRecords("tableValue", "?$select=tableField&$top=1&$filter=lookupFieldid eq"+storeParentResult[0].entities.fieldname).then(
function success(result) {
finalValues = result.entities;
},
function (error) {
console.log(error.message);
// handle error conditions
}
);

//finalValues you can collect all the values from second relationship

}

Please mark as Answer if it is helpful and provide Kudos

Blog : https://microsoftcrmtechie.blogspot.com
Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Please mark as Answer if it is helpful