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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (43,563)