Updating a field from another table in dataverse

[Arcallah]

I have a  dataverse table called "Teams", each row within the table has a column called teamid that contains a unique value, and teamstatus which has a a choice value. In a related PowerApp, I can update the teamstatus to change the selected choice.

I have another dataverse table called "Members",  each row is a member within a team and there can be multiple members within the same team. There is also a column called teamid and team status in the members table. I need to update the rows in "Members" so that the team status is updated to match the status in "Teams", using the teamid as the foreign key.

I have looked into creating a relationship between the two fields in dataverse, but it doesn't appear to be an option. I think I need to create a powerautomate flow that lists the rows in Teams where the team status is not null, and update the team status in the users table with apply to each to go through the records. I'm getting as far as listing the records, but am a bit stumped on the next step.

Is this possible and if so, please could someone point me in the right direction? I've had to replace the names of the tables, as they are linkable to my work.

Thanks in advance, 
Andy

[FLMike]

Hi,

You can certainly use a flow, but I am perplexed, can you please share exactly what the UI says when you try to create the relationship? It sounds like a very simple 1:M, because yes, you should just create the 1:M relationship and get the status directly from Teams. I wouldn't do any of this work below, but I'd need to know why you cannot create it to help with that part. So instead I will answer (as well)

I would like to make a suggestion:

A) I can answer as you asked (for your pseudo design)

B) You can also do a Trigger on the Teams table. When the Status changes, it triggers and you update the Members table. Now you may need to do A) first to "catch up and then B) teh rest of the time

Option A:

Trigger: Reoccurring

Step 1: Use List Rows and set the filter of columns to the TeamId and TeamStatus columns (no sense pulling back the rest)

Step 2: Apply to Each, with an INPUT of the Dynamic Property called "value" from the Step 1 List action

Step 3. Now you said you want to Update the Team status, so you need a List Rows, against the Members table, and filter on the TeamId that you get from the Apply to Each (value) property

It seems like you will get 1:M rows back.

Step 4: Add an Update a row Action (dataverse)

Use the Row Id from the second Apply to Each(for members)

and use the Status from the Teams (Apply to Each)

Option B:

Is exactly like A excely that instead of you doing a List Rows and an Apply to each YOU TAKE THOSE OUT and Step 3 above becomes Step 1, since you have the TeamId and TeamStatus from the Trigger properties.

Step 4 (option A) is now Step 2

And done

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Arcallah]

Thanks very much, I've not been able to look at this yet due to other priorities but will let you know when I've been able to have a look.