General Use of Collection question

[StuCundy]

I have just moved my access data base to Sharepoint and in the process of setting up my app and custom forms so learning a lot and quickly.

I have a list of hotels that I need to make in to a different groups for each project I have. Hotels can belong to many groups and groups can have many hotels.  So I need a Many To Many relationship there. So lets say I create MyProject1 as one group

I have a list of guests who are also attached to the group (Myproject) One group can have many guests.  I want each guest to a hotel, each guest has one hotel, Hotelst can have many guests.  One to Many.

In Access I used to use queries.  In Powerapps is Collections they best way to do this?  

Do I have my group and bring in the selected hotels by making a collection? (Collection1)  I assume I will use  Collection1 as the list of hotels to assign the guests to.   

After the guests are assigned to hotels does that become a new collection? I.e. I have a collection of hotels to use for project1 (Collection1) and then a collection of guests assigned to hotels in MyProject called Collection2.

Can I create a MyProject2 easily without erasing My Project1.  Do I just keep creating more collections?

Once the Project is done with hotels group and guests assigned I want to output hotel guest lists.  Can this be done by connecting the project collection to an access table?

Am I on the right track?  Looking forward to some advice, Thanks.

[Drrickryp]

Hi @StuCundy 

I too moved from Access to PowerApps and originally started with SharePoint.  Be aware that SharePoint has limitations related to which functions can be delegated to the server compared with Sql and CDS.  Also when the number of items in a list grows above 2000, this can become a critical limitation.  That said, the principles of database modelling apply to PowerApps the same as in Access.  If you have an N:N relationship then the best way to handle it is with a junction table. That way you can model each side of the relationship as a 1:N or N:1.  I address this issue in my series on database principles and how they pertain to PowerApps.  In particular the one that addresses this is https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-... .   

[StuCundy]

@Drrickryp Thank you.  Ill study this.  It looks very familiar but will be a good base to get my head around this restructure.  Did you get where I was heading with collections or was I on the wrong track?

[Drrickryp · Accepted solution]

@StuCundy 

I wound up going with CDS and pretty much abandoned SharePoint.  To answer your question, collections are a very useful tool for getting around some of the limitations of SharePoint, particularly those that are related to delegation of functions and using collections as a datasource instead of the true backend datasource can greatly increase the speed of the app.  However, they exist only in memory and have to be recreated every time the app is closed and reopened.   Also the table shaping functions like AddColumns(), ShowColumns(), etc are useful as well to address relationships between lists.  In my opinion, the best approach is to apply the principles of database design and build your tables according to those principles right from the start rather than trying to work around them. Then you don't have to go through contortions to try to make your app work. 

[StuCundy]

Hi @Drrickryp 

OK Thank you I will focus on getting the basics done first.  Just a question on this design...If I have my Junction table bringing together my Projects and Hotels for the many to many relationship, the two tables will be joined by the foreign keys in the junction table.    So when filtering by Project name I can see the list of selected hotels for that particular project. 

When assigning guests to a hotel that only is relevant for the particular project, do I need to make an additional junction between hotels and guests and then join the two junction tables?  Or are the guest fields part of the Junction table in the beginning?

[Drrickryp]

@StuCundy 

In its simplest form, I would visualize your database to look like this.  With this design you could easily develop the galleries based on Project, Hotel, and Guest. Additional tables could be added if there are additional 1:N relationships.  For Example, another table related to projects listing employees assigned to the project. 

[StuCundy]

@Drrickryp 

Thank you for that, it makes sense.

[StuCundy]

@Drrickryp 

Could you please point me in the right direction as to where to start from when making my app?  My first objective is to open a Project and assign some hotels.  In access I would have used a query of course.  Do I create the app from the starting point of the Junction list or the Project list?

[Drrickryp]

Good Morning @StuCundy 

In SharePoint, create your 4 lists and DO NOT use the lookup type.   Instead, for all of the foreign fields use a whole number.  Put some data in each of the lists except the junction list. Make a note of the URL where your lists are located.  Go to make.powerapps.com and select the SharePoint option. Follow the prompts and select the Projects list and let PowerApps create your app.  It will create 3 screens, a Gallery, View and Edit.  Then go to data sources and under the connectors select Sharepoint and bring in the other 3 lists. Using the Edit screen for the Projects list as a guide, create Gallery screens and edit screens for each of the lists except the Junction.  This will allow you to enter or edit the data in each list individually.  This is where you will add or edit new Guests, Hotels, and Projects. 

The real magic is on the next screen.  Put a Dropdown box at the top of the screen and make the Items property Projects.  Put a gallery below it and make the Items property Filter(Hotels, ProjectID=Dropdown1.Selected.ID).  When you add a label to the Gallery, the Text property should be ThisItem.HotelName.  The gallery will fill with the names of the hotels associated with the project.  Beyond that there is too much to add in one thread but check out the 4th part of my series on database fundamentals. https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple...  This one describes in detail how to implement the relationships in PowerApps.  

[StuCundy]

GoodMorning @Drrickryp 

Nice time difference!  I have made the app as you described and things seem to be working without errors which I am pleased with.  Now you say I do not need to have any entries in the Project Junction list but how to I bring in the hotels and guests into the project linkage?  I am looking at the Projects as Customers and the Hotels as products, the Project_Hotel junction table is like my orders.  I need to create (an order) by making new records in the junction list? Ill keep working through you other post too and thanks so much.