cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmcmun
Frequent Visitor

Create relationship between rows based on column values

Hi there,

 

I have a table in Dataverse with a hierarchical 1-N relationship configured. The table references equipment components in a factory and consists of the following columns:

 

- Component Code

- Component Description

- Parent Component Code

- Parent Component (relationship)

 

This is hierarchical as each component is made up of multiple child components, which may also have their own child components, and so forth. My table contains the "Component Code" of the parent to each component, however this is a text column, and does not yet relate the child to the parent via the lookup column.

 

I can manually create the relationships fine, however, I want to automate this so that the Parent relationship is created by searching in the table for the row where "Component Code" matches the "Parent Component Code" column.

 

How would i go about doing this?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
SeanHenderson
Impactful Individual
Impactful Individual

It's a little confusing because it's all in the same table, but here goes.

Manually triggered Flow, then List Records action that lists all Components:

SeanHenderson_0-1609973918379.png

Then create another List Records action, also for Components, but this one will have a Filter Query of parentcompanycode eq 'Component Code' (dynamic content, from the previous List Records action):

 

SeanHenderson_2-1609974205503.png

 

 

Obviously use the column name for your parent component code. Make sure the component code from the dynamic content is wrapped in single quotes. This will automatically create an Apply to each loop.

Next, add an update record action, select the Components table again and for the Unique identifier use the dynamic content from the second List Records action. This will create a second loop:

SeanHenderson_3-1609974387463.png

Expand advanced options so you can see all your fields. For the parent component lookup, enter the plural name for your components table followed by the unique identifier from you FIRST list records, wrapped in parentheses:

SeanHenderson_5-1609974668170.png

 

Save it (ignore the warning about no filter on your first list) and test it. Make sure you do this in a dev environment first, but it should populate all your records with the right lookup.

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

 

View solution in original post

6 REPLIES 6
SeanHenderson
Impactful Individual
Impactful Individual

Hi @rmcmun 

I'm a little confused. So, for a sub-component, it currently has a text value for Parent Component Code, but no value for Parent Component (relationship)? And you want to use the Parent Component Code to find the Parent Component and automatically populate the lookup accordingly?

We can do this with Power Automate. Then the question is, do you want to go back through all your data and update it, or is there a condition that will trigger this in the future, like on the creation of a new record?

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

HI @SeanHenderson ,

That's correct that I have a text value for Parent Component Code but no value for the relationship, and that I want to use the Parent Component Code to find the Parent Component and automatically populate the lookup.

 

For the moment I only need to go through and update all the data once. Does that make sense?

 

Thanks so much.

SeanHenderson
Impactful Individual
Impactful Individual

By any chance, is the Component Code also the Primary Name column? If so, a quick and easy way would be to make a view with both the text field and the lookup field, then open in Excel Online and copy/paste the text column into the lookup column. This will only work if the Code you're pasting is also the primary name column for the table. Let me know if it is, otherwise I will show you a solution in Power Automate that you can run on demand to do it.

SeanHenderson_0-1609966948901.png

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

So the primary name column is called 'Name', however I happen to have a business rule to set it to the value of the Component Code column. Meaning that the Name column does currently have the same value as Component Code.

 

However, from what you've said it would be great to know how to do it in Power Automate as well if you're happy to share.

 

I really appreciate the help!

SeanHenderson
Impactful Individual
Impactful Individual

It's a little confusing because it's all in the same table, but here goes.

Manually triggered Flow, then List Records action that lists all Components:

SeanHenderson_0-1609973918379.png

Then create another List Records action, also for Components, but this one will have a Filter Query of parentcompanycode eq 'Component Code' (dynamic content, from the previous List Records action):

 

SeanHenderson_2-1609974205503.png

 

 

Obviously use the column name for your parent component code. Make sure the component code from the dynamic content is wrapped in single quotes. This will automatically create an Apply to each loop.

Next, add an update record action, select the Components table again and for the Unique identifier use the dynamic content from the second List Records action. This will create a second loop:

SeanHenderson_3-1609974387463.png

Expand advanced options so you can see all your fields. For the parent component lookup, enter the plural name for your components table followed by the unique identifier from you FIRST list records, wrapped in parentheses:

SeanHenderson_5-1609974668170.png

 

Save it (ignore the warning about no filter on your first list) and test it. Make sure you do this in a dev environment first, but it should populate all your records with the right lookup.

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

 

View solution in original post

Amazing, thank you so much for the help. I really appreciate the very detailed answer!!

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,788)