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

Simple One to many relationship adding a record to the related table

Hi all,

 

I have a very simple dataverse setup, 2 tables, Events (fields: Name, Date, Deadline)  and participants (fields: Email, Name)

with a one to many relationship.

I have a screen where I show the event in a form and a button to subscribe.

 

I just have no idea what to put in the button on select event to add the user to the participants table.

 

I have another screen with the event information and a dropdown where I want to list all the related people in the participants table, I'm also baffled how to fill this dropdown with the participants.

 

I tried to patch it like

Patch(Events,Gallery1.Selected,{Participants.Email

but the related participant is not available yet

Seems something simple but I can't seem to find anything on the web about this

 

Thanks in advance

 

Rbie

1 ACCEPTED SOLUTION

Accepted Solutions
v-jefferni
Community Support
Community Support

Hi @Rbie ,

 

Could you please share more details about your scenario? Would you like to select multiple Users in a Combo box within the EditForm of Events table and add them to the Participants table then relate the event with these participants?

 

If so, you should note that each time App users select Users from Users table in the Combo box and add them to the Participants table would create new records so you will need to avoid duplicates first.

 

On another hand, when we talking about 1:N relationships, there would be a LookUp field in the table(N) which relates to the other  table(1), take the two tables in your scenario as an example, you will be able to see the LookUp column has been created in the Participants table:

1.png

But look into your scenario, I think it is not a 1:N relationship since a single event could relate to multiple participants and meanwhile a single participant would relate to multiple events as well, right? So it should be a many-to-many relationship instead.

 

When with a N:N relationship, you will have to leverage the Relate function to create the relations between one record in a table with multiple records in another table:

 

Please refer to below blogs about relationships in Dataverse:

https://powerapps.microsoft.com/en-us/blog/relate-records-in-many-to-many-relationships/

https://powerapps.microsoft.com/en-us/blog/one-to-many-relationships-for-canvas-apps/

 

So, please follow the steps:

1\ Create many-to-many relationship between Events and Participants tables.

 

2\ Add a custom card, add a combo box in it. Set Items of the Combo box to the Users table.

 

3\ Apply below formulas on the submit button:

SubmitForm(Form1);
ForAll(ComboBox1.SelectedItems,If(IsBlank(LookUp(Participants,Email = ThisRecord.Email)),Patch(Participants,Defaults(Participants),{Name:ThisRecord.'Full Name',Email:ThisRecord.Email}));
ForAll(Filter(Participants,Email in ComboBox1.SelectedItems.Email),Relate(Form1.LastSubmit.Participants,ThisRecord))

Formulas explanation: first create new event records in Event table, then add selected users to Participants table avoid duplicates, in the end relate the participants that equal to the selected users with the new created event.

 

4\ Set Items of the ListBox to:

//if it's in custom card within a Form:
ThisItem.Participants.Name
//otherwise:
Gallery1.Selected.Participants.Name

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

1 REPLY 1
v-jefferni
Community Support
Community Support

Hi @Rbie ,

 

Could you please share more details about your scenario? Would you like to select multiple Users in a Combo box within the EditForm of Events table and add them to the Participants table then relate the event with these participants?

 

If so, you should note that each time App users select Users from Users table in the Combo box and add them to the Participants table would create new records so you will need to avoid duplicates first.

 

On another hand, when we talking about 1:N relationships, there would be a LookUp field in the table(N) which relates to the other  table(1), take the two tables in your scenario as an example, you will be able to see the LookUp column has been created in the Participants table:

1.png

But look into your scenario, I think it is not a 1:N relationship since a single event could relate to multiple participants and meanwhile a single participant would relate to multiple events as well, right? So it should be a many-to-many relationship instead.

 

When with a N:N relationship, you will have to leverage the Relate function to create the relations between one record in a table with multiple records in another table:

 

Please refer to below blogs about relationships in Dataverse:

https://powerapps.microsoft.com/en-us/blog/relate-records-in-many-to-many-relationships/

https://powerapps.microsoft.com/en-us/blog/one-to-many-relationships-for-canvas-apps/

 

So, please follow the steps:

1\ Create many-to-many relationship between Events and Participants tables.

 

2\ Add a custom card, add a combo box in it. Set Items of the Combo box to the Users table.

 

3\ Apply below formulas on the submit button:

SubmitForm(Form1);
ForAll(ComboBox1.SelectedItems,If(IsBlank(LookUp(Participants,Email = ThisRecord.Email)),Patch(Participants,Defaults(Participants),{Name:ThisRecord.'Full Name',Email:ThisRecord.Email}));
ForAll(Filter(Participants,Email in ComboBox1.SelectedItems.Email),Relate(Form1.LastSubmit.Participants,ThisRecord))

Formulas explanation: first create new event records in Event table, then add selected users to Participants table avoid duplicates, in the end relate the participants that equal to the selected users with the new created event.

 

4\ Set Items of the ListBox to:

//if it's in custom card within a Form:
ThisItem.Participants.Name
//otherwise:
Gallery1.Selected.Participants.Name

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,347)