cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
isaac1
Helper II
Helper II

Table structure advice - a PA based on Sharepoint lists

I'm just starting out to learn Power Apps.  The only tools I have right now is Power Apps built over a Sharepoint Online list(s).  

Our records tend to make frequent use of people-type of assignments - like who is the assigned _______, the assigned _______, and so on and so forth.  In many cases, we need to assign multiple people - so for the Foreman column, there might be 5 of them.  

 

In the past, under sloppy development (i.e., sharepoint-only), we might just cram 5 people into that column, in Sharepoint, column type=Person or Group.  But I want to begin incorporating all of my database development training into my Power Apps experience as much as I possibly can.  So, obviously, this is a one-to-many relationship (one primary record, many Foreman values), so I can just begin by making a second Sharepoint list meant to contain one record for each Foreman person......all linked (foreign key) to the master record............Right?  It will be possible (later, when I'm making the Power app), to do all of the Power Apps patching that I need in order to keep this relationship faithful, right?  Any other suggestions?

10 REPLIES 10
WarrenBelz
Super User III
Super User III

Hi @isaac1 ,

Please have a read of this blog of mine - happy to answer any questions.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Drrickryp
Super User II
Super User II

@isaac1 

In addition to @WarrenBelz 's excellent blog check out my post https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Principles-and-Pow...and the one following it to see how to apply database principles in PowerApps.

Thanks Warren.  I had read your blog on Delegation specifically before.  Wow, it's a LOT to try to remember!  From all the things I have learned/seen about PowerApps, over the last 6-8 months, I'd say this whole Delegation thing would be #1 at the top of the list of "disappointments".  I mean what front end app in the whole world cannot read an unlimited # of records from the RDBMS?  Oh well - I'll stop complaining now.

 

Regardless, it seems to me that my previous life as a database developer serves me well.  Because I already follow most of the rules you recommend for P.A. dev:  Keeping data types simple, using only Text/Date/Number in Sharepoint, avoiding creative data types (even in SQL server, my types are almost exclusively varchar/nvarchar, decimal, int, bigint and datetime - almost never a need for anything else in 15 years of dev).  No spaces, not changing column names, etc.  I think I am well positioned to obey most of the rules here which makes me feel better.  

 

I'm going to keep reading your blog (priceless guide, thanks!) and also @Drrickryp  your blog too, as one of my main questions was to "confirm" that I will be making tables with PK and FK just like elsewhere - which a quick perusal of your article sounds like 'yes' I will, although I need to finish reading it carefully and will do so now.

 

Thanks gentlemen, I will be back as my development progresses...I sincerely hope I don't wear you out!  🤣

Hi Warren.  

On here: Starting your app – good practices – Practical Power Apps

It says this, and I've put my "questions" (what I don't understand) in brackets:

Next you have the Error and Star fields [what is Error and Star fields???, never heard of these?] – no real need to rename these, but one tip here – if you are going to use the built-in SharePoint validation for things like compulsory fields, keep them on the controls where this is necessary, [I won't, I'll use the front end like you, but what does Keep Them On The Controls mean?] but if you are going to use your own validation (as I do) delete the lot [what does Delete the Lot mean? Delete what?] and it saves overhead on the form loading (particularly relevant on large forms on mobile devices).

Hi @isaac1 ,

When a data card is created automatically by Power Apps, it generally has four elements inside it

  • A data field control (text box, combo, date picker etc)
  • A label with the name of the control
  • A label with a "star" character that shows if the field is mandatory in the data source - this is generally at top left and hidden unless needed
  • A label with an error message if there is an error state - under the control and also hidden unless needed.

I was referring to the last two which I do not use  as I handle errors in Power Apps, but many others for quite valid and useful reasons do.

I go through and delete both of these off every card - apart from removing clutter that is not needed, it also potentially helps performance as there are 40% (counting the card) less elements to add.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Ahh .. ok, got it.  Thanks for the explanation.  Since users will only interact with my data via the App, and I want to keep it as simple (especially trapping errors) for myself as possible, I'm thinking to do the same as you, no Sharepoint columns will be required as a table constraint - FE app will handle.  Thank you again.

 

Tomorrow I may begin actual development beyond my Sharepoint lists, which I believe are ready.  I think my head will spin with "when do I use a Screen, Form, Data table, or Gallery?"--type questions but I'm sure I'll get it sorted probably with more reading!  Thanks!!

@isaac1 

I suggest you allow SharePoint to create the app from your child list. This will create an app with 3 screens. You can then add the parent list as a second datasource. Please review my blog to see how to implement the relationship in PowerApps.https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Design-fundamental... 

@Drrickryp 

I've been thinking for a while now of how to reply, as I want to communicate the utmost respect and also I'm grateful for your help and time.  

Probably 99% of the time when an expert gives me advice, I'll follow it.......But, I'm hesitant on this one.

In my past development experience, any time I have the choice to either do something from scratch or allow a Wizard (etc) to create it for me, I've found that when I use the Wizard:  1) I have to spend a lot of time changing things away from the defaults the Wizard created, and 2) worse - I fail to learn what I need to learn.

 

Unlike (perhaps) some Power Apps creators, I am not looking for the shortest, fastest way to anything I can call an App.  I want to thoroughly and fully take the time to understand each type of object - When a Form is indicated, when a Screen is indicated, how to create each control from scratch and examine all its properties.  

 

I have taken MANY power apps tutorials at this point, and they have been overly focused on "let the wizard do it for you" type of thing, which makes it harder for me to learn how to actually do it.  I'd rather begin now in developing the discipline to understand each object from scratch.  Unless maybe I am wrong and nobody does this in power apps?  

All I can say is, so far, every time I let power apps "do all the work for me", I end up with a product I don't understand and couldn't duplicate on my own which frustrates me.  

I need to find some way to understand each object, and when it's appropriate to use it, to really feel that I wrap my head around this platform. 

Did that make any sense?  Do you think I am going in the wrong direction here?  I hope that I have not been rude as that is not my intention.  Just trying to become a true P.A. developer (not a hobbyist as is so common in Access and many no-code/low-code platforms).

@isaac1 

You don't have to use it but it is a good template to see how the various icons, galleries and screens work together.  It will help you as you put together your own App.  Remember, PowerApps is simply a front end and the basic structure of your data tables is not changed so it won't affect anything if you let Powerapps create an app and then you delete it.  Moreover, there is no cost to doing it. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,793)