Return a related field from related Dataverse table

[Amik]

Hi everyone,

Background

I have two custom Dataverse tables (People_Data and Role_Data). These two tables are related using a common identifier called “RoleAssignment_ID”.

The relationship cardinality is one to-many.

I have a Gallery control and its items property is associated to People_Data. The OnSelect property uses the classic practice of declaring the selected item as a variable instead of Gallery.Selected. E.g.

Set(

    VarRecordEmployee,

    ThisItem

);

Problem

I have a label control in a form. The DataSource for the form is VarRecordEmployee. There is a field in that form called “Role Name” assigned to the label control. This field is a free text type and comes from the Role_Data table.

Because the DataSource for VarRecordEmployee is from the People_Table, and I want to return the Role Name from the Role_Table based on the RoleAssignment_ID value in the People_Table, the traditional method is to lookup the RoleAssignment_ID field in the People_Table and then return the associated Role Name from the Role Table.

However, because the tables are related, I want to leverage that relationship and display the associated Role Name based on the RoleAssignment_ID in VarRecordEmployee without using a lookup function. However, this results in blank.

I can always use a lookup function to get this work. For example the below syntax returns the value I need based on the RoleID.

If(

    Form_General.Mode = FormMode.New,Blank(),

    LookUp(Role_Data, RoleID = VarRecordEmployee.new_RoleAssignment_ID.RoleID).Role_Name)

But I did not think I would need to do this given the tables are related. I would have thought I would see the associated Role Name based on the RoleAssignment_ID. I suspect the answer could be obvious?

best regards,

Ami

[v-jefferni]

Hi @Amik ,

Since the new_RoleAssignment_ID is a LookUp column within People_Table, in the form you will be able to select this column when editing fields of the Form Control:

After doing so, there will be a Data Card where includes a Combobox with Items property automatically set to 

Choices(People_Tbale.new_RoleAssignment_ID), and DefaultSelectedItems being Parent.Default. Users will be able to select one or more items from the Role_Data table, based on the setup of this column, whether it allows multiple selections or not.

Best regards,

[Amik]

Thanks @v-jefferni but the problem is not specific to forms, but just trying to reference a record in a related table into a label.

[Drrickryp]

@Amik

You can "walk" the dots through the relationship. No need for a variable or lookup(). 

Gallery.Selected.Role_Table.rolefield

will return any field in the role table. See https://hiredgun.tech/dataverse-relationships/   for a more detailed description of dot notation. 

[Amik]

Thanks @Drrickryp but that does not return anything either. For example I know there is a "Directorate" value in the related Role Table based on the selected Gallery record, but the value does not get picked up.

[Amik · Accepted solution]

Apologies for time wasted on this post - I fundamentally misunderstood how this works and went back to researching more about the feature (mostly stuff from Shane on https://www.youtube.com/watch?v=BPkv7S11Zp8).

All working for me now.