Unanswered

Create Relationships between tables with existing data

(0) Share

Report

Posted on by PatrickProvoke

Howdy folks,

I'm having some trouble wrapping my head around working with relationships between 2 or more tables, where the data already exists in both tables.

I have a Customer table which has CustomerID - already populated. I have an Order table which has CustomerID. Data has been imported already as stated. I want to create a relationship between both tables on CustomerID. How exactly do I go about this? I know you can create a lookup column between tables - but that's with the intention that a user would create the data after the fact.

I suspect I'm missing something pretty obvious!

Any help would be awesome 🙂

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report

Hi @PatrickProvoke,

Unfortunately you can't convert an existing column to a Lookup. You'd have to create the new column, then copy over the data from the old column to the new one. If you don't have a large number of records, it's fairly straightforward to do this using the out of box Export to Excel / Import Data, or Edit in Excel via the Power Apps for Excel add-on.

---
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.

Was this reply helpful? Yes No
PatrickProvoke 15 on at

Like (0)

Report

Thanks Chris,

That appears to work ok - however I'm still a little confused with doing multiple lookups/relationships from one table to many other tables.

Say I have 3 Tables. Property, Work Orders and Work Orders Special. Property has PropertyID. Work Orders has many PropertyIDs, same with Work Orders Special.

Will I need to create individual columns in Work Orders and Work Orders for EACH relationship? That seems like there would be 3x columns in Property like 'PropertyID, WorkOrderProperty and then WorkOrderSpecialProperty' with the same data in each.

Was this reply helpful? Yes No
PatrickProvoke 15 on at

Like (0)

Report

Accepting solution above here. For anyone looking, it can be a little tricky to setup relationships that match what you might have designed in an ERD or data diagram - especially if you're coming from a SQL or Oracle background. You can still build the relationships you would normally create, but if you're dealing with importing existing data - you will likely need to import/export to excel numerous times to fill those lookup columns you create with data that links entities together.

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report

Hi @PatrickProvoke,

Yes you need to create a Lookup column for each relationship. It will automatically create a many to one relationship for you.

---
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.

Was this reply helpful? Yes No