Relationship between lists

[Julien2]

Hello,

I am planning to build a Service Request Power app application, and the back-end will be SharePoint online lists.

I want your suggestions on how I should build the below list:

List 1:

I have a sheet that contains the following columns Company Name, Branches, Next visits.

One of the scenario in the App is:

When the user selects a Company name from a dropdown, and the branch, directly I want to populate all the "Visits Details" that are related to the branch and the company name. (Many different companies may have the same branch location).

For example:

Company Name                          Branches                    Next Visits Details

Microsoft                             Los Angeles                -Need a printer; 2;3;4
                                      San Diego                  -Need a mouse;..
                                      Roseville                  -Need a screen and a keyboard.

Company B                             Los Angeles                 -Need a network cable
                                      San Francisco               ....

Company C....

The next visit details column will have several items associated with the Company and the branch selected.

List 2:

The second list is the parent that also has information about the service request created, including the Company name and the branch that was selected but excluding the Next Visits Details that I believe, it should be in a separate list.

A summary of this scenario, a company has several branches, the branches have several visits, and the visits will have several items.

Based on the above scenario I am still overthinking on:

1. How many lists should I have?
2. What should be the types of the columns of the above fields?
3. How the relationships between these lists will be managed?

Can please someone provides an example and explain in detail to achieve the above?

Thank you in advance to anyone willing to help me out with this.

[Drrickryp · Accepted solution]

Hi @Julien2 

Your question has many implications but requires understanding relational database principles and how to use them in PowerApps.  To specifically respond to your question,  I refer you to my series on Database principles here https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-...  which discusses the role of keys in designing a relational database.  If you have time, start at the beginning of the series to see how to get to this point starting from scratch.  https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An... .

Once you have the lists and the relationships set up correctly, you can use Cascading dropdowns to limit your search as you have requested.   Shane Young @Shanescows has a very good You Tube on this subject https://www.youtube.com/watch?v=pkZG2boN7jQ 

I also recommend that you review this video posted this week by @RezaDorrani about some limitations to using SharePoint as a datasource and how to get around them.    https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/Power-Apps-with-SharePoint-as-a-data-... 

[Julien2]

Hi @Drrickryp ,

Thanks for sharing your series, and such helpful videos.

After I have reviewed your series, I can understand that SharePoint as a data source could not achieve my needs since it's not a relational database.

So my question is I should use SQL DB instead of SharePoint online to build the tables and relationships?

Looking forward to your response.

[Drrickryp]

Hi @Julien2 

It is true that SharePoint is not in itself a relational database.  However, it can be made to function as one through PowerApps (the post on Keys and the following post on how to use PowerApps with well constructed tables shows how for example).  

In fact, many if not most of the members of our forum use SharePoint for storing their data and constructing apps around it. However, there are limitations, particularly when you have a large amount of data to work with and also there are some functions that work with SQL and CDS but not with SharePoint.   The app that I use to run my business has over 10,000 patients and over 35,000 visits.  I found that in my case, after a few months,, SharePoint was too limiting and I moved over to CDS and have not regretted it. 

The main advantage to SharePoint is that it comes for free with an Office365 account.  Other options have an additional expense that may or may not be a limiting factor for you and your organization.   That would be a business decision that I can't help you with.