Displaying values of non-primary key column of one table as a lookup choice in another table in Dataverse for Model Driven Apps

(0) Share

Report

Posted on by Aditya16

Hi All,

I am doing a test of converting a SQL table to dataverse table, below is the structure:

Master Table : Company which has columns CompanyID(Autogenerated and Primary key), CompanyName(text) and other columns
Child Table :Products which has columns ProductID(AutoGenerated and Primary Key),ProductName(text), CompanyID(Lookup from Company table)

Front end application has a form for Products which has columns: ProductName and Company which has a dropdown to display CompanyName from the Company table and based on its selection it is storing relative CompanyID from Company table in the CompanyID column of the Products table.

Following are the issues:

It seems that only value from primary key of a table can be used as lookup and other columns cannot be used hence CompanyID instead of CompanyName is visible , and it is not useful as CompanyName should be visible instead of CompanyID.
Should CompanyName be made a primary key and CompanyID as a Alternate key to resolve the issue of lookup and then create a additional column "CompanyNameLookup" in the Product table that would display the CompanyName from the Company table. PowerAutomate can be used to update the value of CompanyId based on the value selected in CompanyName.

Please suggest the best approach when converting a sql table to dataverse and the secondary key of master table is used as a lookup in another table.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @Aditya16

Did I answer this question for you before? its so oddly similar.

This Statement, is not a question, but it's also inaccurate.
It seems that only value from primary key of a table can be used as lookup and other columns cannot be used hence CompanyID instead of CompanyName is visible , and it is not useful as CompanyName should be visible instead of CompanyID.
.

You can easily use any field you want, based on the LookUp, but it sounds like you do not know how, so you are making statements that are not true.

Also, when you look at the Tables in Dataverse, through the UI, you do in fact see the Name (if its the primary field) not the GUID.

So I am not following

Also this question is moot and makes inaccurate statements, learn how to use LookUps to get other values.
Should CompanyName be made a primary key and CompanyID as a Alternate key to resolve the issue of lookup and then create a additional column "CompanyNameLookup" in the Product table that would display the CompanyName from the Company table. PowerAutomate can be used to update the value of CompanyId based on the value selected in CompanyName.

Since there was no actual real question, per se, I responded in kind.

However to tell you how you correctly do it.
1. If you are in a Canvas App, you simply use the Current Record from TableA to do this
TableA.MyLookUpTableName.MyLookUpTableValue.

2. If you are in a power automate, you simply do a Get item by id, where the ID is equal to the Dynamic Property listed as FieldName (Value)

3. If you want to pass the GUID of the lookup to power automate froma canvas app
TableA.MyLookUpField.TableName

If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Aditya16 8 on at

Like (0)

Report
Copy link

Link copied!
@FLMike

Thanks for the reply,
I hope below points would make it clear:
I want to create Model Driven App and not the Canvas based app, In canvas app I can easily refer any column from another table.
When I am creating a lookup column in one table which is referring to another table, it do not give option to select which column should be referred and values from the primary key column would be displayed by default.
Please provide any link for referring non primary key column from one table as a lookup column in another table in case of model driven app

Was this reply helpful? Yes No
Verified answer

Fubar 8,487 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
What you can do in a Model Driven App is create a Quick View form on the table in the Lookup, on the quick view form you put the fields you want to display, then on the main form add the quick view linked to the Lookup field - when the lookup is populated the quick view will show the other fields.

Terminology wise (it is a common mistake):
Primary Key is a GUID that is held in a field/column that is the same name as the Table with an "id" suffix
Primary Name field/Column is what is displayed by default on the form for the lookup value in Model Driven Apps. The Primary Name field is not unique unless you create an Alternate Key on it.
Apart from Alternate Key's enforcing uniqueness, their other benefit is when integrating data as the Web API allows you to use them as the key rather than having to know the GUID.

Was this reply helpful? Yes No
Aditya16 8 on at

Like (0)

Report
Copy link

Link copied!
Could you please provide any reference link, I have tried the approach and following are my observations:
Created a quick view with columns Name and ID
If the ID column is my Primary Name field then the IDs are appearing in Lookup dropdown and for user they need to select id to see the corresponding name in the Quick view, that's working fine and storing data in form of id
If the Name column is my Primary Name field then the required information is appearing but my backend needs to have ID value instead of name, so I have triggered a flow to update the ID column , any other approach to use apart from the power automate?

Was this reply helpful? Yes No