Solved: Re: Relationall Database

Bluebell · ‎05-03-2017

Ihave been reading a lot of information surrounding the move from AWA to PowerApps with some people saying that relational databases cannot be created in PowerApps and others saying that Lookups can be used.

Can I please have a definitive answer before I starting trying to migrate my AWA's.

Can I do the following tasks in PowerApps

Link data from several tables (lists)
Allow input from multiple users when up and running
Provide views which combine data from several lists
Allow a link from the PowerApps to Excel so that database can be easily downloaded and refreshed for further manipulation. So refresh can be clicked to download any changes that may have been made.

If the above is possible, then I have one more Access Standalone database that I was going to migrate to an AWA. Do I need to migrate the tables into Sharepoint lists then proceed to move to PowerApps?

If I know the above can be acheived I will make a start on migrating the data.

Many thanks

stormea · ‎05-03-2017

@Bluebell wrote:
Ihave been reading a lot of information surrounding the move from AWA to PowerApps with some people saying that relational databases cannot be created in PowerApps and others saying that Lookups can be used.

Can I please have a definitive answer before I starting trying to migrate my AWA's.

Can I do the following tasks in PowerApps
Link data from several tables (lists)
Allow input from multiple users when up and running
Provide views which combine data from several lists
Allow a link from the PowerApps to Excel so that database can be easily downloaded and refreshed for further manipulation. So refresh can be clicked to download any changes that may have been made.
If the above is possible, then I have one more Access Standalone database that I was going to migrate to an AWA. Do I need to migrate the tables into Sharepoint lists then proceed to move to PowerApps?

If I know the above can be acheived I will make a start on migrating the data.
Many thanks

Hi there, yes you can definitely achieve all the use-cases you outlined above. Since you are on AWA, there actually is a way to connect to Access Web App Data, check this forum post out. Maybe for the production app do that, but I'd say for testing / demoing stuff you should use SharePoint still and I'd even suggest considering SQL Server for everything (my primary choice). Either way, let's focus on the PowerApps capabilities.

Link Data from several tables

It's not the most robust / complex query functions, but I've been able to have an app work just fine with an 8 table schema with a "many-to-many" relationship and a few "one-to-many" relationships.

An easy approach for working back and forth between related tables is to use the foreign_id value from your item and perhaps have a search bar / filter variable that your list of related items is filtered on. Once you select an item on the main list, it will navigate you to the other page, populate the search bar with that foreign_id value, and then trigger a refresh on the data to filter accordingly. I have some demos that do exactly this for a really cool search experience.

Allow input from multiple users when up and running

One of the best parts of the PowerApp itself is that behind the curtain, it is just HTML5 packaged into a .msapp file. The PowerApps ecosystem handles all the typical pains of supporting a web app like authentication, deployment, and ensuring the system is up and running. You don't have to worry about scalability because Azure supports the whole ecosystem without you ever having to worry about a thing.

Provide Views which combine data from several lists

Views (or screens) are very flexible and not bound to anything inherently. You could create a view with a gallery of items on one side of it, and then put another gallery that is refined to show, when you select from the first list, a filtered list of items from a separate table. You could do that, or you could create a mixed set of values from two different tables and just put it into one gallery of items. You could also just put a bunch of pictures up and have them move around based on different timers. Very flexible development IDE when it comes to building out the UI.

Allow a link from the PowerApp to Excel...

This one is actually fairly straitforward, but I haven't tested this out too much let alone the limitations of it, but their is an OOTB control for "exporting" data. You typically populate a local table variable (called a collection) with data, and you can export that collection as a file. Maybe someone else can speak to some tests they've done for this though.

General Suggestion

With all that said, my advice would be to start trying it. The value of PowerApps is that to try and spin something up really quick to test an idea is very easy and can be relatively quick. If you already have a license for O365, it's free, or you can get the trial for two weeks and try it out. Once you do that I'd suggest trying to:

1. Start by just spining up a basic schema on SharePoint that is similar to your AWA schema.

2. Go ahead and add some dummy fields / items so that you have a dataset to work against.

3. Now go create a PowerApp and use that site as the template.

4. PowerApps will create a fully functional app to highlight how you can search / develop / and update items.

5. Once basics are working, start looking into doing more complex things with the UI / your schema.

Two biggest points of concern are:

1. PowerApps is still slacking in being able to query large datasets.

2. Very Complex relationships can be tricky to build and ensure are correct consistently because of number 1.

Even with those concerns, I think it's worth getting into it and tinkering with it. They have some great tutorials to get started you can find at https://powerapps.microsoft.com/en-us/tutorials/getting-started/.

Hope this helps!

View solution in original post

stormea · ‎05-03-2017

@Bluebell wrote:
Ihave been reading a lot of information surrounding the move from AWA to PowerApps with some people saying that relational databases cannot be created in PowerApps and others saying that Lookups can be used.

Can I please have a definitive answer before I starting trying to migrate my AWA's.

Can I do the following tasks in PowerApps
Link data from several tables (lists)
Allow input from multiple users when up and running
Provide views which combine data from several lists
Allow a link from the PowerApps to Excel so that database can be easily downloaded and refreshed for further manipulation. So refresh can be clicked to download any changes that may have been made.
If the above is possible, then I have one more Access Standalone database that I was going to migrate to an AWA. Do I need to migrate the tables into Sharepoint lists then proceed to move to PowerApps?

If I know the above can be acheived I will make a start on migrating the data.
Many thanks

Hi there, yes you can definitely achieve all the use-cases you outlined above. Since you are on AWA, there actually is a way to connect to Access Web App Data, check this forum post out. Maybe for the production app do that, but I'd say for testing / demoing stuff you should use SharePoint still and I'd even suggest considering SQL Server for everything (my primary choice). Either way, let's focus on the PowerApps capabilities.

Link Data from several tables

It's not the most robust / complex query functions, but I've been able to have an app work just fine with an 8 table schema with a "many-to-many" relationship and a few "one-to-many" relationships.

An easy approach for working back and forth between related tables is to use the foreign_id value from your item and perhaps have a search bar / filter variable that your list of related items is filtered on. Once you select an item on the main list, it will navigate you to the other page, populate the search bar with that foreign_id value, and then trigger a refresh on the data to filter accordingly. I have some demos that do exactly this for a really cool search experience.

Allow input from multiple users when up and running

One of the best parts of the PowerApp itself is that behind the curtain, it is just HTML5 packaged into a .msapp file. The PowerApps ecosystem handles all the typical pains of supporting a web app like authentication, deployment, and ensuring the system is up and running. You don't have to worry about scalability because Azure supports the whole ecosystem without you ever having to worry about a thing.

Provide Views which combine data from several lists

Views (or screens) are very flexible and not bound to anything inherently. You could create a view with a gallery of items on one side of it, and then put another gallery that is refined to show, when you select from the first list, a filtered list of items from a separate table. You could do that, or you could create a mixed set of values from two different tables and just put it into one gallery of items. You could also just put a bunch of pictures up and have them move around based on different timers. Very flexible development IDE when it comes to building out the UI.

Allow a link from the PowerApp to Excel...

This one is actually fairly straitforward, but I haven't tested this out too much let alone the limitations of it, but their is an OOTB control for "exporting" data. You typically populate a local table variable (called a collection) with data, and you can export that collection as a file. Maybe someone else can speak to some tests they've done for this though.

General Suggestion

With all that said, my advice would be to start trying it. The value of PowerApps is that to try and spin something up really quick to test an idea is very easy and can be relatively quick. If you already have a license for O365, it's free, or you can get the trial for two weeks and try it out. Once you do that I'd suggest trying to:

1. Start by just spining up a basic schema on SharePoint that is similar to your AWA schema.

2. Go ahead and add some dummy fields / items so that you have a dataset to work against.

3. Now go create a PowerApp and use that site as the template.

4. PowerApps will create a fully functional app to highlight how you can search / develop / and update items.

5. Once basics are working, start looking into doing more complex things with the UI / your schema.

Two biggest points of concern are:

1. PowerApps is still slacking in being able to query large datasets.

2. Very Complex relationships can be tricky to build and ensure are correct consistently because of number 1.

Even with those concerns, I think it's worth getting into it and tinkering with it. They have some great tutorials to get started you can find at https://powerapps.microsoft.com/en-us/tutorials/getting-started/.

Hope this helps!

Bluebell · ‎05-09-2017

Sorry about my late reply, but it was my daughters wedding at the weekend so events rather overtook me.

Thank you for your very detailed article. I am still not sure whether to advise the SQL route or to try PowerApps, but I will have a go at creating one and see how I get on.

Many thanks

Anonymous · ‎02-09-2018

Hi,

Link Data from several tables

It's not the most robust / complex query functions, but I've been able to have an app work just fine with an 8 table schema with a "many-to-many" relationship and a few "one-to-many" relationships.

An easy approach for working back and forth between related tables is to use the foreign_id value from your item and perhaps have a search bar / filter variable that your list of related items is filtered on. Once you select an item on the main list, it will navigate you to the other page, populate the search bar with that foreign_id value, and then trigger a refresh on the data to filter accordingly.

I have some demos that do exactly this for a really cool search experience.

Would you mind sharing some of these demos, please? Really struggling to get a grasp of getting my queries working in powerapps.

CWesener · ‎02-14-2018

Hi,

I'd suggest discussing your requirements with @GregLi from our team who is working on how customers can migrate from AWA to PowerApps and the Common Data Service.

Thanks,
Clay.

Anonymous · ‎05-15-2018

I am brand new to powerapps and trying to figure out the best way to build a filter or lookup  on dropdown boxes and textbox. I have two drop-down boxes for All Options (Locations) and Symptoms.  Then one text box for department.   If the user selects All options it should bring back my symptoms in my sharepoint symptoms list.

We have tried to set-up some kind of relationship through FK for the different share list files.   Also, tried the option of doing entities and relationships but do not have the permissions.  There are some One to Many relationships for the symptoms.  I have attached my sharepoint lists sample in the file below.    Any suggestion welcomed!!