CODEWITHSUNDEEP
ms-accesspowerappsdataverse
Ulrich
Ulrich

Reputation: 225

PowerApps: Access-like update query

I’m pretty new to PowerApps and need to migrate an Access database over to PowerApps, first of all it’s tables to Dataverse. It’s a typical use case for a model-driven app, with many relationships between the tables. All Access tables had an autogenerated ID field as their primary key.

I transferred all tables via Excel ex/import to Dataverse. Before importing,I renamed all ID fields (columns) to ID_old and let Dataverse create its own, autogenerated ID field for each table.

What I want to achieve is to re-establish all relationships between the tables, where the foreign key points to the new primary key provided by Dataverse, as I want to avoid double keys. As a first step I created relationships between the ID_old field and the corresponding (old) foreign key field in the related table.

In good old Access, I’d now simply run an update query, filling the new (yet empty) foreign key field with the new ID of the related table. Finally, I would change the relationship to the new primary and foreign keys and then delete the old ID fields.

Where I got stuck is the update query. I searched the net and found a couple of options like UpdateIf / Patch functions or Power Query or Excel ex/import and some more. They all read pretty complicated and time intensive and I think I must have overseen a very simple solution for such a pretty common problem.

Is there someone out there who might point me in the right (and simple) direction? Thanks!

Upvotes: 1

Views: 261

Answers (2)

Ulrich
Ulrich

Reputation: 225

I solved the issue as follows, which is pretty efficient in my perception. I”m assuming you have a auto-numbered ID field in every Access table, which you used for your relationships

  1. Export your tables from Access to Excel.
  2. Rename your ID fields to ID_old in all tables using Excel, as well as your foreign key fields to e.g. ForeignKey_old. This will make it easy to identify the fields later in Dataverse.
  3. Import into Dataverse, using the Power Query tool. Important: Make sure, that you choose ID_old as additional primary key field in the last import step.
  4. Re-create all relationships in Dataverse, using the Lookup datatype. This will create a new, yet empty column in your table.
  5. Now use the “Edit in Excel” feature to open your table in Excel. You should get your prefix_foreignkey_old column with the old foreign keys displayed, as well as the reference to your related table, e.g. prefix_referencetable.prefix_id_old, which is still empty.
  6. Now just copy the complete prefix_foreignkey_old column values into the prefix_referencetable.prefix_id_old column.
  7. Import the changes and you’re done.

Hope this is helpful for some of you out there.

Upvotes: 1

Henk van Boeijen
Henk van Boeijen

Reputation: 7928

A more efficient approach would be to start with creating extra ID columns in Access. Generate your GUIDs and fix your foreign keys there. This can be done efficiently using a few SQL update statements.

When it comes to transferring your Access tables to Dataverse you just provide your Access shadow primary keys in the Create message.

Upvotes: 1

Related Questions