First Power Apps Project - Help deciding data model/data source

(0) Share

Report

Posted on by dash_7

Hello!

I about to start builing my first PowerApps App.

However I am not sure what would be the best data model and data source for my app. It would be great if I could get some pointers on this!

Heres what I trying to do:

I have a List of about 250.000 product-IDs. All those products have multiple levels of product categories and sub-categories. They also have a lot of other attributes.
The problem I am trying to solve: some products are related to other products in a way that (at the moment) isn't reflected in the database. Those relations can't be created automatically - they have to be created by humans.
My idea is to create a master-detail form: a user starts with the complete list of 250k products and reduces it by using filters and/or search function to one product - eg: the user finds the product "123" in the master list and wants to add products that are related to product 123.
In the detail list: the user uses the same list of 250k products. and by again using filters and/or a search function the list is reduced to products the user deems relevant. e.g: in the detail list the user finds the product 456 and the product 789. The user decides to add a new attribute to those two products stating that those two product are related to product 123.
There a different "kinds" of relations. Each kind of relation has different meaning. At the moment there are six kinds of relations: lets call them A, B, C, D, E and F. Lets say in this example that product 456 is related to 123 with a "B" relation and product 789 is related to 123 with a "F" relation.
The result should be: when looking at product 123 I should see that it is the master product to 456 in a "B" relation and that it is the master product to 789 in a "F" relation. Each product can be the master product to infinit detail product for every relationship kind.
Looking at product 456 I should see that it is a detail-product to product 123 in a "B" relation. Each product can be the detail product to only one master product for each relationship kind.
Also I should be able to search for all master products and specific kinds of relations and get all related sub-products. This Apps should also be the system where this information is being maintained. Only thing I need for the external database is new products and the information which products are obsolet.

Fine tuning: there should be a way to display differenct sub-sets of products to different users and every attribute/added relation should show the username who added this relation.

But this fine tuning is for later. Right now I am not sure about the data model and the source. Of course all this could be done in a Excel spreadsheet however I thought this could be a nice use case for power apps as well.

How should I set up my data? Unfortunately I do not have direct access to the data source.

I thought two options:

exporting the data to Excel and having this file on OneDrive
exporting the data and setting up a really simple SQL database in Azure

What would be the better approach? What would be a good way to store those attributes/relations added by the users?

Any help is very much appreciated!!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

timl 36,415 Super User 2025 Season 2 on at

Like (1)

Report

Hi @dash_7

For this use case scenario, SQL Server is the database to use. The other data source that you could consider is Dataverse.

Excel is out of the question because it cannot cope with this quantity of data. It has a 2000 row limit, and the size of the Excel file cannot be more than 2MB.

The typical way to support this type of data structure would be to have 3 tables - a product table, the relation table, and a product relation table.

Product(ProductID, ProductDesc, ....)
Relation(RelationID, RelationDesc, ....)
ProductRelation(ProductRelationID, ProductID, RelationID, ....)

The relation table would store the list of relations (A,B,C,D,E,F), and the ProductRelation
table would store the links between product and relation.

There would be some additional fine tuning that would be necessary, but this would provide the basic data model for this type of scenario.

Was this reply helpful? Yes No
dash_7 77 on at

Like (0)

Report

Thank you for your answer @timl !
This is very helpful feedback and clearing things up a little bit for me!
Just to be sure however: for the use case I described I would definitely need premium connectors, right? None of the standard connectors are able to handle this amount of data?

And is there any preferance towards canvass or model driven app for this use case?

Was this reply helpful? Yes No
timl 36,415 Super User 2025 Season 2 on at

Like (1)

Report

Hi @dash_7

Yes, SQL Server and Dataverse are premium connectors.

The only data source with a standard connector that you could consider would be SharePoint. However, with that quantity of data and the search features that you need to carry out accross multiple tables, you'll certainly hit delegation problems and you'll need to devise workarounds.

SQL Server can much more easily cope with large amounts of data, and provides database features that you can utilise in your apps, such as access to views, stored procedures, and indexing of tables for performance.

Was this reply helpful? Yes No
Verified answer

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report
Hi @dash_7,

Dataverse would be the recommended go to over SQL Server. With Dataverse you can define your data model with relative ease and you get views, forms, search and automatic indexing.

Whether go to with model driven or canvas is typically decided by a few factors:

Do you need to connect with other data sources in your app? If so, then you'll need a Canvas App.

Does the user interface need to have a very specific look? If so then you'll need a Canvas App.

If the above 2 are not a requirement, than model driven apps are very convenient and quick to build compared to canvas apps as the Forms are very much defined already in terms of look and field and you get a very functional grid for views with little configuration. Out of box search is very powerful and navigation for related records is made easy. You'll spend less time troubleshooting your app as there is a much lower chance of "messing things up" whereas with a Canvas you are fully in control of the user interface therefore have to take caution to do things correctly.

Hope this helps.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No