A better PowerApps Data Model - Flexible and fast 

I've watched so many of your videos to help me learn Powerapps and this one just saved me days of effort building a new app. You are a sanity saver when I have a tough app.

Dennis helped me with this exact situation on Friday! Thanks for the help and enjoy working with y’all!

Shane you are awesome, as a beginner with PowerApps I went through most of your video’s which are the best tutorials I could find. Further from another training pro kudo’s for the way you conduct the training.

I find your videos amazingly helpful. I am so grateful to you as have a functional app i'm building and these really help, especially having less controls.

Hi Shane, you're a lifesaver! I've been looking for this for a week now and was almost giving up... Then your video popped up. Amazing! Thank you very much. I look forward to a video with advanced options for this. I would like to add a note field to the questions that get answered No with an explanation on why it didn't get done. Stay safe out there.

This is perfect for a project I'm doing! Using the gallery to dynamically repeat a few handy variably visible controls, you got a seriously flexible solution! thanks again.

Great stuff as always Shane. I have no doubt I'll be using this on customer projects soon!

Shane......excellent! I watched this once through just now, I REALLY like the 'A Better Design'' mechanism. I don't understand all of it yet, but I'll watch this however many times more - to pick up all the nuances, etc. I'm all eyes into shorter code and a scalable structure. Thanks very much!

I'm with myquest84, perfect timing. I was creating a 50 column list and was struggling with getting it lined up in PowerApps. Thanks for putting this up.

Very helpful video I got the chance to watch this just in time I am about to create this kind of question and answer app with many columns Thanks

Hi Shane. I love your work - you're great a explaining the core concepts in an easy-to-follow manner which can be expanded upon. Your demo got me started in creating a dynamic form allowing users to add almost any new field type (including people pickers using office 365 data). Users can add text boxes with configurable data types, dropdowns with custom values, checkboxes, show / hide info icons, configure the message for info icons, and group questions into categories via a "question" gallery nested in a flexible height "category" gallery. Thank you for doing these.

Fantastic as always, Shane! Thank you!!

This is perfect timing. The next step for me is figuring out how to randomly select a few questions each from several different categories or questions.

Shane, you're awesome man. Thanks for this!

Absolutely great!!!! Learnt many things today, plain AWESOME 👍🏻👏🏻👏🏻👏🏻👏🏻

Hi Shane, Please make more videos about common data Service! I used your training about info paths repeating tables and done a project for my company! Thanks a million!!👍

Thanks Shane, as always very useful!

Thanks Shane. Keep up the great job! Hope to work with soon.👌

Thank you Shane, very helpful all your videos

Hi Shane..You enthusiasm is still same though situation due to COVID is getting worse day by day. Keep up the good work.

Thank you Shane, exactly what I needed!

YYEESSSS. This is awesome 😎 Thanks Shane!

just the thing i wanted, you're awesome. Thank you so much :)

Heyyyy Shane. Another good one as usual. Thanks.

I think it’s an important point you’ve made- you can shape your tables however you want. You can use pivot / unpivot inside Power Query to change the way the data looks in a report. I guess the balance to find is applying this technique to columns that need to be dynamic, and keeping the others static so that the data can be used by someone who might not be so proficient with data models and Power Query.

Hi Shane, loved the video! I'm curious to know if there's a video after this where you explain or show how to add more items to the gallery like a camera or comments? Also is there a way to show a number automatically get updated when a person attempts a questions like '2/7 questions' done?

This is Gold!!!! Thanks Shane

Great video thanks Shane!

Hi Shane. Love your work and I've learned a ton in a short time. This video inspired me to try to build a workout application. I've gotten the basic function to work with is great for a single user... adding routines to an "events" list easily adds to the gallery. I am stuck trying to see how I can use this model to now allow selection of multiple people, where an instructor would want to track each active members performance. I am trying two nested galleries, where the members are in the parent gallery, and the events row is in the child gallery. This also works (visually) But... i am stuck trying to grow the event list onChange from row1 to row2. Trying "forall" against the students list bit no avail... any thoughts if this technique generates row like ID'S (ThisItem) if you tried to apply this same thing but with more then one gallery listing of activity? Thanks for any thoughts. I know I could build it the traditional way but as of now... I don't know how many events may get added as traditional columns... cheers.

Hi Shane, great video and very helpful. Maybe in your next video on more advanced options you could show us a way to give multiple answers (eg from a dropdown or combo box) to each question with the possibility to add own/custom answers (if possible in the same dropdown/combo box or text input) and how this would be handled in Sharepoint. Keep up the great work!

Great Video. You're awesome! Thanks!

Hello Shane, you made so many videos... Thank you! But, I'm looking to the video you said you would make regarding how to have different type of answers to the questions. I guess in your question definition list, you have the answer type, some validation, default value, list of choices for dropdown... How do we format that in the gallery? That's the video I'm looking for... 🐕👍

Hi Shane you have just blown the CDS people’s argument that you cannot do relationships without CDS out of the water. Keep up the good work !

Great Video, thank you for your help in understanding this concept. Would you by any chance know what to add as default if you are adding an attachment instead of the dropdown.

Thanks the video!! Where can I find the continuation of the video, where input answers would vary?

Thanks again for another great video. I am using your design for a new solution, but as mentioned in a previous comment, I have to modify this to allow for the user to save an inspection as "In-progress" as they won't usually finish it in one session. I would love it if you could expand on this in a sequel to this video. I have managed to bring in other field types to collect more than Yes/No information, but would love to have a clear understanding on how to bring this altogether through the use of drafts that can then be converted to the final results....

Brilliant ! Thank you

Thanks for this video Shane, it’s brilliant, when I eventually wrapped my head around it! What do you think about all the ‘experts’ who say sharepoint lists shouldn’t be used like a relational database? Also are you against using fancy columns in dataverse too?

Shane take the video to the next level

Nice demo

Thanks Shane can you use term store taxomology metadata as drop downs in the list or do they have to be independent sharepoint lists? Thank you

Thanks Shane, it took me whole long weekend to update the "wide list" app I made to use this data model using a junction table. It appears to work great, although I am running into an issue. (1) If the 'save chores' button is pushed twice, it adds a whole new set of answers to the Chore Answers list. Another snag I hit... what logic would you recommend to go back to a partially completed chore list? 👏

Hi Shane: I am new to PowerApps. I have been watching your PowerApps videos. Thank you very much for sharing. I have a question in this video. On Narrow screen, onSelect of "Save Chores" button, in ForAll statement, where did varRecord.ID come from? Did it come from "Set(varRecord,Patch('Chores Narrow',Defaults('Chores Narrow'),{Title:TestInput1_1.Text,ChoreDate:DatePicker1_1.selectedDate}));"?

Hi. Really like your videos. Could you please do one where you explain when and how to use the disambigous (@) operator? Thanks!

Yess!!!! Thank you Shane

Amazing!!! Thank you very much.:-)

Thank you Shane, exactly what I needed!, Also If updating the chores answer half the way , I want to exit and come back and to call back the chores narrow and continue the Chores answer, Please help on this

Please demo different Form entry types, "to the next level" ;-} with repeating entries; like items in an order. What do you think?

Hi, Shane! I would love a recording on creating Charts in a PowerApp - would be very timely for my current work-at-hand!!!

Hi Shane, can you please show a way to build a checklist in which the "Preparer" and "Reviewer" both check off items, for example there are questions and two users select "Yes" and "No"

thanks for this video it helps me very much. but is it also possible to put a image control in it and save the pictures in the sharepoint list

Super helpful, if I were to build an app to document the decisions call center associates make with customers, eg 40k customers, they call in about products they purchased and we need to ask 3 questions about each product, and we have maybe 5000 different products, how would we efficiently model that data? Thinking maybe. SQL of all customers, all products, and all Orders (matching customers to products). Would i add questions to orders list?

Hi Shane, it is a useful video. May I check what will happen if the retrieve back the 'Chores Answer' will there be an error since it will exceed 500 entries? As now the column is reduced but the row is increasing. Thank you

Shane as always, rocking with powerapps :D, i came up with one question, is it possible to build an app where new employe fills out own info and it creates azureAD account strait a way?

Okay, this is awesome and I dove right in... and now I need a lifeline. I have a scenario where I have a SharePoint list that is providing the details for my fields, including field types. With this column, one of the options is 'choice'. I then have a column that contains the text for the choices. How do I make this work dynamically?

Hi shane, thank for the excellent video. just one question, what if it involve attachment? can it be done?

Shane, how do you get your back button to dynamically hide on the home screen allowing the user to change the home screen name? Is that possible?

Hi Shane and thank you for yet another great video. I lack a dialogue about efficient and flexible data models in the Powerapps forums so I am grateful for this video. I wonder, is it possible to transpose the collection so that I can update a Sharepoint list instead of just add new rows? If, how?

Hi Shane! Your videos are very helpful! What would I do if I have a Gallery with 3 buttons as response options instead of a dropdown? Would I need to do a patch function for this?

Hi Shane, just wondering this changes in terms of gallery to ask the questions, will there be a significant change towards set up of PowerBI in terms of data collection. :)

Hi Shane, Is there a way to retrieve the submitted answers to the dropdowns and update /change existing submitted answers.

Hey Shane! Thanks for all your videos, you are GREAT! One question: How about the SharePoint list's limit? I guess it's about 5000 rows. Isn't the limit easily reached with the method? Keep on your great work Thomas

Shane, as usual - ya hit the nail right on it's head. That's a great sample of proper Relational DB Normalization, just the way it SHOULD be 👍🏻

Hi Shane, How would you manage this solution if they for example are doing an inspection and the inspections are throughout the day? so they open the app do a couple of inspections, they submit and close, reopen later to carry out more insepctions (repeat) until they are ready to submit that days inspections at the end of the day. I have tried to use the SaveData\LoadData to store the collection but for some reason when i reopen the app and hit the reload data nothing happens? using this in other scenarios it works as expected?

Hi Shane. Thanks for the amazing content. Goes without saying how much help you are for the community! Have a question: I have exactly the same situation being solved except the answers can be an image, Yes/No or Text. For that, do you suggest I create multiple Answer columns like Answer_Text, Answer_Bool, Answer_Image, Answer_MultiSelect in both the Answer collection and in SharePoint and patch them separately like you showed, first patch Parent table (ChoreNarrow), then Answer table for only the Bool Answers and then Answer table for Image answers and so on.? Any suggestion would help. Thanks again for the great content.

Thanks Shane! This looks like a good strategy for an onboarding checklist app I need to make. Would check boxes or radio buttons work the same as yes/no drop downs or would some adjustments have to be made?

Hi Shane, Do you ever go to edit an app in Powerapps and in takes forever to load or does not load? I am having that issue with one of my apps, could it be too big? Is there some limit? Thanks for all of the useful videos!!!

Hi Shane, great video. Is this relationship model also good for common data service with it's limit of 300 entities?

Hi Shane. Which data type you prefer to use for storing Foreighn IDs?

Awesome work, Shane. Quick question? Does gallery not allow rich text input? If so, is there a work around for it without using forms?

Simple et efficace !

Hi Shane, this is awesome consideration. Wouldn't this start to run into issues with the data row limit in the sharepoint list feeding the app?

Will it work with DataVerse as a source as well?

Hi Shane, I created this questionnaire with 2 dropdowns per question. I'm finding that it is behaving very strangely, like selecting a response option in one dropdown automatically selects a response option in the second dropdown. I know this sounds as though I have some linkage somewhere but I don't... just wondering if you can reproduce that?

Can you show the block diagram (Share point Lists) and power Apps Screen.

Shane, This looks very usefull ,however I can't get it to work . So please shine your light on this: When I use Set for the global variable collection to get the existion SPList, I have no results in the preview. Syntax is correct, List is recognized, There are columns and dummy-records available , but the content does not show. I get in the preview: "We did not find any data".

Just for curiosity. Would you still prefer the gallery input over a Form?

Very good ! :-)

Hi Shane, thanks for your videos. They are a great source of knowledge and help. I am kind of stuck with one issue and wondering if you can share some ideas. I have a SharePoint list with few columns. There is a column Name which is a free text column. And there is another column 'Potential Used By". This column is a multi-valued column where user can select from ComboBox multiple values. Sample data might look like this - Name1, ["UsedByA","UsedByB","UsedByC"] Name2,["UsedByA","UsedByB"] Name3,["UsedByC"] I need to create a Collection that will help me groupby UsedByA, UsedByB and UsedByC. Would that be possible to create a collection from above datasource that might look like below on which then I can group by UsedByA, UsedByB, and UsedByC Name1,UsedByA Name1,UsedByB Name1,UsedByC Name2,UsedByA Name2,UsedByB Name3,UsedByC Thanks for your time !

Hey Shane, I am just thinking ahead, but I am no master like you, but would this approach not be more hassle as the data set will grow very quickly and cause problem when trying to load collections in later down the road? Massive fan btw!

You are the greatest fucking Powerapps guy EVER. The spinner thing is so brilliant... Thanks!

Hello Shane. This worked like charm EXCEPT until we hit ComboBox. Cannot patch ComboBox On Change because it changes each time a selection is made. So, the only way we saw out is to not patch OnChange of ComboBox instead patch everything collected from combobox using a submit button which because is inside the gallery looks bad because it repeats once per question. Any alternate for this in your experience?

For some reason I cannot get the following part to work. //ThisItem, {Answer: Dropdown2.Selected.value}); Has something changed in powerApps??

Hi Shane Can gallery filter a SharePoint list based on columns from another SharePoint list? I encountered PowerApps internal error trying to filter a SharePoint list based on a variable. I’ve 2 SharePoint Lists: 1) MData_ClaimsUsers (containing user’s info such as Name, Email, Section, Role etc.) - I used Email (single line text column) and Section (choice column) as filtering criteria for the other SharePoint List. 2) MData_Supplier-Section (containing Supplier Type (single text column) and Section (single text column) I’ve set global variables to identify the Section from MData_ClaimsUsers Set(varUser, User()); Set(varUserSection, Text(Lookup(MData_ClaimsUsers, CU_Email = varUser.Email, CU_Section.Value) But when I inserted varUserSection as part of Filter formula, I encountered error. SortByColumns(Filter(‘MData_Supplier-Section’, varUserSection = IsMarine), “SupplierType”, Ascending) Any advice to resolve the error or any other way for me to do the filtering or u have any videos about how to do SharePoint list as relational database for PowerApps?

Hi Shane, how do i patch only selected answers and not the blanks. so just the answered questions and not patch questions that were not answered?

How would you add the child records to a Dataverse table and relate them to the parent record?

Please make a video on making basic model driven apps with sharepoint list and also portal apps...These really help

after 19 mins my brain strangled my throat!!!

Shane what will happen to powerapps form and microsoft flow if the person who has created it has left the company and his email address is now disabled? If forms and flow will stop working then how to make it work again ? how to avoid such situations? Please guide me

Hey Shane! Thanks for this...I’ve thought often about how to make question fields dynamic and felt there MUST be a better way! Great video. I have a question which I probably know is not possible, but thought I’d ask anyway: My client wants to put all of their questions on one screen that the user does not have to scroll up or down. So basically I’m looking for a gallery that increases horizontally and not vertically so that it fills the screen with no scrolling. Sort of like columns of questions. Any ideas?

I saw that you defined _Purple to be a ColorValue in your OnStart and am trying to repeat that in my application, also. I believe I faithfully copied the form you provided, producing this code: Set(_LightestBlue, ColorValue("#08c5fb")); After running OnStart, I see that _LightestBlue is initalized as a color, but the variable has no value. I posted this question in the Power Apps forum, and was told to set the _LightestBlue variable to the hex itself, and then use that in the Fill as ColorValue(_lightestBlue), and this works. Clearly, your method is superior so, if I can get your method working, I can save keystrokes down the line. Would you mind explaining how you got your code to work, please? The forum question is, "Setting app colors in OnStart" in case you want to post it there. KrishnaV got one Solution credit; I will award your submission a solution, too. Thank you for your help, and these excellent, excellent videos.

Hi Shane! What if each checklist was not going to be completed on the first attempt. Each checklist was a work in progress over the course of several months? How can we go back and edit each one without creating new entries for each time it is updated?

Hi Shane, great video again, I’m currently building a timesheet app which has a similar set up to this and some of your points will help. One thing I have been wondering about my own app though is that if hypothetically it had 250 Columns in the SP list and you were to change to this model the SP list that stores the answers will not be wide now but if you get 1,000 entries your gonna end up with a list that’s got 250,000 rows. Is that going to affect performance? My app will theoretically have up to 100 rows of data per week per employee, do you see this as an issue? Is there a row limit. I’m assuming as long as you avoid the blue squiggles that it’s should be ok and all the data can be pulled back easily enough as I’m going to need to lookup records for reporting.

I tried and followed the same, can't get the colChores collection appears on the gallery

Hi Shane, after watching your video I got right into creating. I’m running into a few errors on my dropdown and I was wondering if you could help me: When I try to set the Default to ThisItem.Answer I get this error: “Name isn’t valid. The identifier isn’t recognized.” When I set OnChange to Patch(colInspections, ThisItem, {Answer: dropAnswers.Selected.Value}) I get 2 errors: ThisItem - The specified column ‘Author’ does not exist. The type of this argument ‘Answer’ does not match the expected type ‘Text’. Found type ‘Error’. Could you tell me what am I doing wrong? Thanks!

Hi Shane, apologies for the long post but can't see a simpler way to explain an issue. How about a video covering more in-depth stuff relating to the interaction between forms and dropdowns/comboboxes? I've been banging my head against this for 2 days and am possibly missing something!!! My test app consists of: SharePoint Lists ---------------- ---------- OrderHeader List: ID (Standard SharePoint list ID column) StoreID - Foreign Key - Number - references ID in Stores list OrderNumber - Text Stores List: ID (Standard SharePoint list ID column) - Primary Key StoreName - Text PowerApp -------- --------- Screen1 Gallery1: Gallery1.Items = OrderHeader Displays ID, OrderNumber, StoreName (StoreName is retrieved using Lookup to Stores list based on ID = StoreID) Form1: Datasource = OrderHeader Form1.Item = Gallery1.Selected Datacard1 - StoreID field deleted, inserted new StoreDropdown in its place Datacard1.Default = ThisItem.StoreID Datacard1.Update = Dropdown1.Selected.ID StoreDropdown.Items = ShowColumns(Store,"ID","Title") StoreDropdown.Default = ThisItem.StoreID Issue ----- ---- The store dropdown shows the list of stores and Dropdown1.Selected.ID reflects the correct value when selecting a store in the dropdown list. However, the dropdown does not display the correct store based on the StoreID value of the selected record in Gallery1 - it should change the store name based on the ID in the gallery record. It always shows the first store in the list of items. When submitting the form, the selected ID in the dropdown is not saved to the SharePoint OrderHeader list. It seems like there is a disconnect between the Dropdown on the form and the form's data. This behaviour appears to be the same in Comboboxes. Any suggestions would be gratefully received!

Hi shane.. can you teach me.

Come'on... your kids do all this?!? Heck you ought to teach "PowerParenting with PowerApps" !

This is exactly how I model my data and it’s definitely hard to show noners the benefit of this style. My issue is merging it all back together if I need the wide list. Parent: Roster (I expect to have about 5000 records in here- currently around 500) Child1.1: Roster PII Parent 2: missions Child2.1: mission Manning (using the names from the roster and I also need the roster PII- only about 200 records in here now, but there will be 10ks at some point) Right now I’m merging that 2.1 list into a collection that takes FOREVER to load in browser and crashes an iPad Pro. I need help with the on start “code”. I’ve tried ClearCollect(colMerge, AddColumns(Child2.2MissionManning, “RosterDATA”, Lookup(Roster, userID = userID), “RosterPIIDATA”, Lookup(PIIRoster, userID = userID) ) ) I like this version because it brings back the whole child record which is helpful if the company need to add more columns of data to those lists but I’ve also tried adding each individual column from the child records that I need to reference but that is tedious and also super slow to load. Please help! Thanks