Look up column from another table in Dataverse.

[Az8PnG]

Hi there!

I tried to study about the lookup column function in dataverse but I didn't really understand.

Here is my situation:

1. I have a table named "Stock In Latest", in this table I have a calculated column name "Quantity Stock In".

2. I want to make the calculated column named "Quantity In" to be store in another table which is "E-Stock".

I already create a relationship between this 2 tables. And in "E-Stock" table, I created a lookup column name "Quantity In" and the related table that I choose is "Stock In". 

Then, when I check in the "Quantity In" in E-Stock table, the value for the lookup column is the Part Number. It is should be the "Quantity Stock In" right?

Can someone help me on this, already try to configure almost 3 days.

[MarioRing]

Hi, it's a little hard for me to understand your problem, can you describe it using only business requirements without any technical names? Could you also draw the desired data structure?

Anyway, I have some insights, which may be helpful for you:

1. If you create a Lookup field that points to another record, the system will always show you the main value of this record, which usually is its name. For example, in the Account table, we have a lookup field "Main Contact" which points to the record of type Contact. In the view of the Accounts, in column "Main Contact" we see the first and the last names of the selected main contact of the Account because the main field of the Contact is the column "Full Name".

2. However, on the view we can add foreign columns of related records. For instance - I can add to my Account's view an additional column "Phone number" from the related Contact record. This value will be shown only if the lookup field "Main Contact" is filled with a Contact record that has its phone number.

Please note, you must use the "View editor" feature to add the foreign column. It looks like it is not possible to do this on the table editor you showed on the screenshot. 

[Amik]

@Az8PnG To understand how this works, let’s use a basic Form in a Canvas App as an example. 

Suppose we have a Table with a list of Issues, and another Table with a list of associated Actions. The Primary Name column in the Issues Table is "Record ID".

In the Actions table, we create a Lookup column associated to the Record ID column in the Issues Table. We will call the Lookup column “Related Issue” (a one-to-many relationship).

1. We create an auto generated three screen app based on the Dataverse Actions Table.

2. We add the Lookup field into the Edit Screen of the Form if it not there already. You will notice Power Apps will automatically create a Combo Box control for the Data Card Value.

3. Note the Choices expression used in the Items property of the Data Card Value, it is using our relationship Lookup column ("Related Issue").

4. If you select Edit on the Combo Box properties pane on Fields, you will notice you can select any related field from the Issues table.

5. Suppose we want to include a Single Line Text column from the Issues table. Add a custom card and include a Label control inside it.

6. In the Text property of the Label control, enter ThisItem.'Your Lookup Column Name' followed by a period/full stop.

7. At the period sign, notice you can now select any column from the related Issues table

8. Final result:

If you're using Model Driven Apps, you can achieve similar functionality using the Related tab or via Views such as the Quick View Form. 

https://learn.microsoft.com/en-us/power-apps/maker/model-driven-apps/types-forms 

In summary then, so long as you have defined a relationship between your two tables (such as a one-to-many relationship), you will be able to return any associated column from that relationship in your App.

I hope this helps.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[Fubar]

A Lookup field sits on the Child (many) record in a one-to-many relationship and is populated with the parent (one) record - the Lookup relationship is the 'linkage' between the records.  The Lookup field (or more specifically its relationship) is also what is called a Foreign Key.

e.g. an Invoice has many Invoice Product items (and each Invoice Product can only be for one Invoice), this is a one-to-many relationship.  The Invoice Product record has a Lookup to the Invoice.

[Az8PnG]

Hi @Fubar @Amik @MarioRing Thank u for replying me.

My question is, is it the lookup field will only display the value of the primary column in the parent table?

In my case:

1. My parent table is "E-Stock".

2. My child table is "Stock In Latest".

3. I want to lookup the column from the "Stock In Latest" and store in my parent table which is "E-Stock".

4. When I do the lookup, I see that the "E-Stock" lookup column displays the value of the primary column in "Stock In Latest" (as shown in picture above).

So, is there any solution or formula that I need to change in order to make the lookup column display the value from other column?

[Fubar]

Lookup will is restricted to the Primary Name column, closest you would get is to edit the control properties and I believe you can set it to show 2 lines when the user is selecting.

Also note, lookups only save the internal guid not the display name (e.g. if you change the name in the source, the next time your user looks at the lookup field it will show the updated name, the guid will remain the same), if you need to save additional values you will need to copy them from the record the lookup links to

[MarioRing]

Are you thinking about the subgrids? This is a list of all records connected to the chosen one by lookups. For instance - There is a "City" table with a lookup field "Country" to choose the proper location. Of course, one City can be in one country only, but each Country may have any number of Cities. In that case, you can put a subgrid on the Country form, to see all the Cities which have this Country set in the lookup.

Please note there is no 1:1 relationship in Dataverse. It is not recommended to use that kind of connection - in most cases, the best solution is to merge two records into one. However, you can create on Table A lookup to Table B, and on Table B lookup to Table A, but both relationships will be independent. That means you must manually assign both sides or make some automation that does this for you. It is risky because you may lose data integrity when A->B and B->C.

[Amik]

@Az8PnG the explanation I provided was in the context of interfacing with the table via a Canvas App or Model Driven App. If create an App (either Canvas or a Model Driven), you can get the columns you need.