Lookup multiple columns in a related table

[RIYxWRK]

Hi all

Apologies if this has been asked before, but I am new to Model Driven apps and trying to figure out how to create a table that does lookups correctly.

• I have a custom table called Tasks.
• I need a column called 'Assigned' to lookup the User table.
• I need another column called 'task manager' to lookup the same User table.
• I then need to be able to have columns called 'Assigned email' and 'Task manager email' so that a flow has an email to send notifications to from the app when a stage gate is reached.

So basically what I don't understand I how I can make any column in a custom table lookup the different columns from the same relationship/lookup. Do I just create one relationship and set each custom column to be a lookup to the same table/relationship? Ideally, when you set a primary piece of information like 'User name', then the other custom columns like 'user email' should only show the email for the user you selected.

I hope that makes sense. Appreciate any help anyone can offer.

[parvezghumra]

@RIYxWRK 

If you Task table has been created with user/team ownership then you should get an ownerid column in it automatically - this can target the User or Team tables. You can then create an additional lookup to reference the manager, which can also target the User table.

When you create lookup column in a table, under the hood, there is a N:1 relationship created for you automatically. You can then traverse this relationship for records within your code, logic, customisations and automation to retrieve data from the related record. So in your case, this will mean the user's email address and managed email address do not need to be stored directly on the Task table. Instead you can grab this data from the referenced User in the lookup columns as and when you need. This approach will result in having a 'normalised' data model, with less data duplication etc

[RIYxWRK]

Thanks for this.

Can I check my understanding?

1. I set up a one to many relationship to the User table, and use the Primary Key of the lookup column as the Column Display Name.
2. Then I create a column called 'Assigned to' and tell it to lookup to that relationship.
3. Then in the App designer, I can create new fields on the form to show the email for that user and Power Automate will be able to reference that information too, just from that relationship connection?

Thanks

[MarioRing]

When you create a 1:N or N:1 relationship, the Dataverse automatically creates a new lookup field (column).

When you create a new field (column) of Lookup type, the Dataverse automatically creates a proper relationship for the given lookup field.

That means you have to perform either of these actions, and obviously the easiest way is to simply create a lookup field to point another record in the system.

Moreover - each lookup field has its own relationship, and you can freely create multiple lookups connecting the same pair of records but with different contexts.

If you want to show the e-mail of the user on the form consider using a quick view form. This is an form element, that allows you to show fields that come from the foreign record set in lookup field. In your case, you need to create a Quick View Form of User table, where you put user's e-mail addres. Then you use this Quick View Form on main Task form, connect it to the lookup field. 

However, Power Automate can browse through relations in every direction (like a spider!;)), so you don't need to put the user email directly on the Task form. The automation will find the proper value.

[RIYxWRK]

Hi thanks for the replies.

Can I check my understanding on how to do this with you?

I will revise my requirement below to be closer to my actual use case.

My tables:

PROJECT

• project number
• project name [primary]
• Project owner
• more fields..

PROJECT TASK

• task name [Primary] 
• task number
• Project name [same name in Project primary key in Project table]
• more fields...

REQUEST DETAIL (custom table for app)

• request notes
• task lead
• task reviewer
• more fields....

I need to create a model driven app where users pick the Project number (Project table), pick the Project name (auto filtered from project number), and then the Task name and Task number (both filtered from the task name) value.

So in my case, I need to be able to use values from PROJECT table and PROJECT TASK which link on the Project Name column.

So my questions are:

1. Do I need to create these fields in my CUSTOM table as lookups (a separate relationship for each column?)

• Project number [lookup to PROJECT]
• Project name [lookup to PROJECT(again)]
• Project task name [lookup to PROJECT TASK]
• Project task number [lookup to PROJECT TASK]

2. I need to be able to filter on certain data like if the task is actually active or published. Do I do that configuration in the model driven app form? It seems you can only lookup the primary key of a lookup table when assembling the table itself without being able to tell it which column to actually look at in the related target, so do I tell the app form designer which column to actually look at in the related table? 

My main concern is the modelling of the data and building the relationship in the proper way, which right now I don't fully understand. I don't know if I need to recreate those columns in my custom table (it's a silly question in some respects but I keep getting conflicting info). I guess so because I will need to store that info somewhere so it may as well be in the CUSTOM table with my other custom columns?

I hope that makes sense and really appreciate any advice that can get me past the starting line 😁

[MarioRing · Accepted solution]

• PROJECT table shouldn't have any lookups related to TASKS and REQUEST.

• TASK should have only one Lookup field to the PROJECT. Just name the field "Project" or "Related Project". I assume that a single TASK can be related to one and only one PROJECT. We don't need more lookup fields for the PROJECT's name or number. Imagine a situation where two lookups on the TASK will point to the PROJECTs, but one will refer to PROJECT1, and the other will refer to PROJECT2. It makes no sense. 

• If you would like to show on a form of TASK some additional fields stored on parent PROJECT, you can create something called a "Quick View Form" of the PROJECT table. That Quick View form will consist of PROJECT fields (Project Number, Project Name, Project Type, etc.). You can put this QV form on TASK main form. If you do this, the user who fills in the TASK form, after they choose the proper PROJECT in lookup, will see the values of the related PROJECT on the TASK form (because of PROJECT's Quick View Form).

• REQUEST DETAIL should have only one lookup to the TASK and one lookup to the PROJECT (from the database integrity and standards you shouldn't use PROJECT lookup, because choosing the TASK already points to the specific related PROJECT for the chosen TASK. Imagine if your request points to the TASK from different PROJECT that TASK points to. However, it will be easier for you to use both Lookups for now).
  You can use similar technique to with Quick View forms, to show related fields that belong to chosen PROJECT and chosen TASK. 

Answering another question - yes, you can apply a filter on the Lookup field, to allow users to choose records that fulfil filter criteria. 

Proposed Data Structure:

• PROJECT
  • Name (main field, text)
  • Project Number (text)
  • Owner (probably Lookup to User, or Contact, or just a text field)
• TASK
  • Name (main field, text)
  • Task Number (text)
  • Project (lookup to PROJECT)
• REQUEST DETAIL
  • Project (lookup to PROJECT)
  • Task (lookup to TASK)
  • Other fields

FORMS:

• Create a Quick View Form of PROJECT
  • Put there fields of Project you want to see on TASK and REQUEST DETAIL records form
• Create a Quick View Form of TASK
  • Put there fields of TASK you want to see on REQUEST DETAIL records form
• Edit the main form of PROJECT
  • Put the subgrid of TASKs to show all TASKS related to the given PROJECT
  • Put the subgrid of REQUEST DETAILS to show all REQUEST DETAILS related to given PROJECT
• Edit the main form of TASK
  • Put there lookup to Project
  • Put there Quick View form of Project, connected to the lookup above
  • Put the subgrid of REQUEST DETAILS to show all REQUEST DETAILS related to given TASK
• Edit the main form of REQUEST DETAIL
  • Put there lookup to PROJECT
  • Put there Quick View form of PROJECT, connected to the lookup above
  • Put there lookup to TASK
    • On the form editor, when you selct this field, and choose "Filtering" from the panel on the right, you'll be able to add filtering that shows only the TASKS that are related to previously chosen PROJECT.
  • Put there Quick View form of TASK, connected to the lookup above

[RIYxWRK]

@MarioRing ah this helps a lot thank you so much for taking the time to help me out.

There can be multiple tasks related to a project [name] and the use should be able to see a fileted list based on the Project [number and name] they in the app. The structure you propose makes sense -  I was overcomplicating it in my head.

Also thanks for clearing up the part about the lookup configuration in the app.