Can you create lookup columns on Virtual Tables in Dataverse?

[axskifree]

Hello,

This is specifically for Virtual Tables in Dataverse. I'm asking because I've found mixed information in my research. I've been having issues creating lookup columns on my virtual tables and might need a sanity check.

Is it possible to do this if you don't have a lookup column in the source table? For context, my approach is to use some SQL Tables as my source data, porting them in as Virtual Tables, then building out the relationships/lookup columns in Dataverse so that our users can work with the data in a model-driven app. I can get as far as creating the column in Dataverse (by creating the Relationship), but it doesn't work when I try to populate that look-up field (error message at bottom).

Additional details I've found:

"Lookup" was under the section of "supported columns" earlier this year, but something must have changed recently. It looks like as of 09/27/2023 lookup columns are no longer supported for virtual tables?

Limitations and troubleshooting virtual tables with Power Apps - Power Apps | Microsoft Learn

Data types not support for virtual tables:

Files and attachments

Image

Lookup

(I tried old guides like this with a basic set-up and couldn't get it working: Setting up relationships with virtual tables with Power Apps - Power Apps | Microsoft Learn )

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

To test, I dumbed it down to a very basic dataset in SharePoint and used that to test the functionality of lookup columns on Dataverse Virtual Tables. I still can't get it working properly. 

I attached my very basic set-up. It seems as though it won't let me write to the lookup column since it doesn't exist in the source data. I've tried it every which way with this very basic data set (and other data sets) but it still won't work.

I'm all ears if anyone has any suggestions. We might just scrap the virtual tables and go with a traditional integration approach. Without functional relationships and lookup columns across Virtual Tables, the entire notion of Virtual Tables seems meaningless for most use cases. Thanks in advance for any words of wisdom.

Error: There was an error in saving this record. APIM request was not successful : StatusCode : BadRequest, Reason : Bad Request, Response from APIM: {"status': 400, "message": "The passed-in field could not be
found

[ChrisPiasecki]

Hi @axskifree,

Lookups are definitely supported in virtual Tables. The limitation you're referring to is that it won't be automatically mapped using the virtual table connector provider wizard - -- you need to manually define the Lookup as described in the 2nd link you mentioned.

Did you make sure to specify the correct column schema name in the External Name for the Dataverse table you are adding the Lookup for?

Would you mind posting a screenshot of the relationship page?

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[axskifree]

Thanks for the response. Yes, I'm attempting to manually define the relationship with the understanding it won't carry over automatically from the source data (aka the SharePoint Lists). "Title" is the Primary Name Column on both tables and "ID" is the Primary Key (both of which are auto set by the wizard).

 

I've tried it a handful of different ways (with the external name field, etc.) I've attached some screenshots.

I think this might be a limitation with SharePoint as a data source for connecting virtual tables via relationships/lookups since the ID and Title are auto set, although I can't get it working with a SQL DataSource either. The CRUD operations work fine, but I can't for the life of me get lookups to work across virtual tables (all created the same way, same data source, etc.)

About ready to pull the plug 😓

     

   

 

[axskifree]

Update: Officially pulling the plug. Can confirm the Virtual Table to Virtual Table lookup columns do not work as advertised. I've worked with several Microsoft consultants at this point and no one can get it working.

I suggest seeking a work around like using a standard table to build lookup columns to your virtual tables. The default documentation suggests that you need to delete the column you wish to use as a lookup and recreate it, but this approach does not work (nor do any other approach). When you create the relationship, the "new" column fails to recreate.

Setting up relationships with virtual tables with Power Apps - Power Apps | Microsoft Learn

I think this probably worked back in January but I wouldn't waste your time on it at this point.

[Hinzzy]

Hello,
I'm currently trying to do the same thing. I have 2 virtual tables from SQL and want to create a child-parent nested grid in Model Driven. I did the relationship and it created the look-up column. Currently what I found out is that if you populate the lookup column with values that can be found in your Parent table (I will call Parent table the table without the lookup column and Child table the one with the lookup column) then it will actually work. My current question is how to move the data from an existing column in the Child table to the lookup column from the same child table. If I manage that then it should properly do the relationship.

[Oualid20011]

Have you guys found any solution for this we are struggling to with this issue even if the lookup is populated but the related columns can not be shown