Unanswered

Creating Lookup Relationship Between Virtual Tables in Dataverse

(0) Share

Report

Posted on by TaraAssadipour

Hello Power Platform Community,

I want to use data stored in OneLake as part of my Power Platform solution. There are two tables in OneLake that I would like to import into Dataverse to use in my model-driven app. Since I didn't want to copy the data into Dataverse, I used Virtual Tables in Dataverse to reference OneLake.

The relationship between T1 and T2 is one to many (T2 already contains a FK column storing T1's PK). Additionally, the OneLake data is going to be refreshed once a day. My question is: how can I create a lookup relationship between these two tables after the virtual tables are created? I don't seem to be able to change the column type of FK in T2 to lookup.

Any guidance on how to achieve this would be greatly appreciated!

Thank you in advance!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

MichaelFP 1,943 Moderator on at

Like (0)

Report
Copy link

Link copied!

@TaraAssadipour You need to Manually. First you gonna create these two table as virtual tables, and after you gonna link both using the default way, creating a relationship.

You can check more details here 👉 Setting up relationships with virtual tables with Power Apps - Power Apps | Microsoft Learn

Please if my answer help you, please give me a kudo. If solve your question please mark as solution.

Was this reply helpful? Yes No
TaraAssadipour 79 on at

Like (0)

Report
Copy link

Link copied!

@Michaelfp Thanks for your reply! I could successfully create a new N:1
relationship, resulting in a new lookup column in T2 that references T1. However, the new lookup column is empty (there is no value set but if you click on it the lookup values show up and you can select an item), and I want the existing FK column to be converted into this lookup column with the same values. I can develop a flow to update the new lookup column with the values from the existing FK column (and I have to run it every time data gets refreshed in virtual tables), but I wonder if there's a better way.

Was this reply helpful? Yes No
MichaelFP 1,943 Moderator on at

Like (0)

Report
Copy link

Link copied!

Would recommend that you create a plugin for the retrieve or RetrieveMultiple, so every time someone get the data from the virtual table, you check that and update the relationship.

If my answer helped you, please give me a Kudo (👍). If solve your question please mark as solution. This is help the community.

Was this reply helpful? Yes No
TaraAssadipour 79 on at

Like (0)

Report
Copy link

Link copied!

@Michaelfp Thanks! I'll do more research to see if I can find a low code/no code solution.

Was this reply helpful? Yes No
TaraAssadipour 79 on at

Like (0)

Report
Copy link

Link copied!

Well it seems that I cannot update the newly created lookup field neither manually (when I try to select a lookup value, it says changes are not saved), nor through power automate: "APIM request was not successful : StatusCode : BadRequest, Reason : A column name that doesn't exist in the table was referenced".

Was this reply helpful? Yes No