Unanswered

Virtual tables: Remap lookups using FetchXML?

(0) Share

Report

Posted on by ThomasMV1

Hi All,

I have 2 dataverse tables (Flights and Bookings) and 1 virtual table in Azure SQL (Flights). I want to create a lookup on the bookings table to point to the flights virtual table, to replace the old dataverse flights lookup. Both flights tables are the same, except the PK for virtual table is a new guid.

What would be the best way to create an automation to update all the new lookups to point to the relevant record in the virtual table, which has a matching key to the original flights dataverse table?

I considered using power automate, calling FetchXML and doing joins between all 3 tables, then running an update record on each record returned in the fetchxml result. When using fetchXml, I can't seem to look at the dataverse table and virtual table at the same time. The flow doesn't throw any errors, but it doesn't include any of the virtual tables columns.

Here is some fetchml xml where I have tried to join the 2 flight tables in a list rows action, but only the columns from cr88e_flight_old are returned.

<all-attributes />

<all-attributes />

</link-entity>

</entity>

</fetch>

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (0)

AhmedSalih 6,678 Moderator on at

Like (0)

Report

Hello, @ThomasMV1, have you thought of using in your Power Automate flow connections to both Dataverse and Azure SQL within Power Automate. So instead of connecting to the virtual tables, connect to the source tables directly in SQL.

If my reply helped you, please give a 👍 If it solved your issue, please give a 👍 & accept it as the Solution to help other community members find it more.

Visit my Blog: ahmedsalih.blog

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

Microsoft Business Application MVP

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Hi @ThomasMV1,

Maybe I don't understand the issue properly so I'm oversimplifying it, but can't you just set the new lookup reference directly based on the old lookup? So if your migration tool of choice is Power Automate, looping through the bookings records, you then:

Retrieve the flight ID/key field from the old table (Dataverse flight table)

Set the new lookup value based on the flight ID/key field. By flight ID/key field I mean the "Unique identifier" column you configured of the virtual table:

If you cannot determine the flight ID/key from the old table, then perform and extra retrieve (List Rows action) to the virtual table to get it based on the current lookup value.

Hope this helps!

Was this reply helpful? Yes No