Lookup column display issues with Virtual Tables

[Squire4Hire]

I'm trying to create a virtual table from a SQL Server View as a lookup column in Tables but it's not displaying the Primary Field correctly in Tables nor on Dataverse Forms.

My configuration is pretty straight forward - the Primary Key is datatype INT (GUID is apparently not allowed in a View to PA Tables), and the Primary Field is datatype NVARCHAR. The column "hooks up" without issue, but when used, the label for the lookup disappears as soon as the record is saved. The value is technically still there, but isn't displayed correctly.

Before Save in Table:

After Save in Table:

When the field is added to a table form, the same sort of behavior applies. The value is visible until the record is saved - then it shows "(No Name)"

Before Save on Form:

After Save on Form:

Doing a hard page reset (Ctrl-Shift-R) on the Table will display the values, but doing a hard page reset on a Form does not.

[a33ik]

Hello,

The way I see to address your issue one of the following:

1. Add a JS that will query the record from lookup using WebApi and set the text value using the approach that is similar to the following - https://fredrikengseth.com/change-display-name-on-lookup-fields-in-dynamics-using-javascript/

2. Add a plugin that will handle "Retrieve" message of your entity, query the display name of the related entity and populate the value of the display name in that field.

[IvanaCRO]

Hi, 

When you click on the record 'No name,' do you encounter an error, or does the record open correctly?

In the past, we faced a similar issue, which was related to the primary key. In SQL, the primary key should be of the GUID datatype, as the same primary key is used as a GUID in Dataverse.

When creating a virtual table in Dataverse, please ensure that the primary key has the following Data Type:

Once the primary key is correctly configured, the primary field will become visible in the lookup.

[Squire4Hire]

Yeah. I think this is where Microsoft broke the functionality.

GUIDs aren't available as Primary Keys when the virtual table is created from a SQL View. I'd prefer not to link directly to the source table as I need to make some logistical changes to table column values prior to pulling it into Dataverse, and users should not have access to change the lookup value (a read-only feature suggested by Microsoft as 'built-in' when creating a virtual table from a View) - though I may be forced to if this isn't fixed.

Thanks for your response though!

[Squire4Hire]

Thanks @a33ik - I'll give it a try.