A better PowerApps Data Model - Flexible and fast

[@jenniferbruegman4986]

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.

[@ShanesCows]

Great to hear! Good luck with your app Jennifer.

[@peterscheepers5780]

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.

[@ShanesCows]

Thanks Peter. Glad you have enjoyed the videos. 😀

[@myquest84]

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.

[@ShanesCows]

I have a video on Random. The key is to use the shuffle function

[@myquest84]

@@ShanesCows Thanks! I got it to work using a firstn/shuffle/filter formula inside the show column part.

[@b0rme]

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.

[@ShanesCows]

Yup. I love how you are thinking deeper about this. 😍

[@Msolenberg]

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.

[@ShanesCows]

Awesome Matt! Thanks for sharing and great job!

[@starmole5000]

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.

[@ShanesCows]

Thank you. I use this one A LOT.

[@starmole5000]

@@ShanesCows I bet! Many thanks Shane. Would have taken me a while to figure this one out by myself. Might have missed it completely.

[@jeffmann9235]

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.

[@ShanesCows]

Glad to help Jeff. Wide list are the devil. 😀

[@Dantresinari]

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.

[@ShanesCows]

Glad I could help!

[@amarjabhalolage]

I am also looking to have options of adding Note field to the questions that get answered with no, how to store that info, In another comment column?

[@spencercooper4546]

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

[@ShanesCows]

That is awesome. I didn’t even know. 😀 It us came up with a bunch of customers in the last couple of months.

[@russellniebolt1493]

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!

[@ShanesCows]

Glad to help Russell. 😀

[@ronaldallen6876]

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.

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@ashokpershad]

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

[@ShanesCows]

Thanks Ashok. Stay safe!

[@nigelprice9474]

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 !

[@artkarp]

He demoed an app that has one data relationship. Not the hundreds or more data relationships that you can easily have in typical corporate data. If you are working with relational data, CDS easily handles that and gives you way more functionality than SharePoint. Use the right tool for the problem you are trying to solve. Don't just have one tool for all problems.

[@AakashDhawanKash]

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.

[@ShanesCows]

attachments don't work with Patch directly. :( Check out my Upload multiple documents. Maybe that will give you some ideas on how to incorporate here but I don't have an easy answer for this one. Sorry.

[@kyawzin-kz]

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

[@ShanesCows]

Awesome 😎

[@SS-cd1dl]

Shane take the video to the next level

[@ShanesCows]

Will do. Hopefully sooner than later. 😀

[@lukasvolkel6696]

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!

[@ShanesCows]

Hey Lukas the way I handle that in another app is I have an “other” option. If they choose other then I show a textinput and save whatever they enter there.

[@michelrail]

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... 🐕👍

[@ssma1368]

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!!👍

[@saminenam]

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

[@brendanc3705]

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....

[@ShanesCows]

The children don’t matter. The key is set the parent record status to “in progress” and control the parent. The children will just follow mom. 😀

[@humzasagheer3936]

Im curious to find out if you found a solution to this as I also have the same issue

[@munkybutler1]

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?

[@ShanesCows]

Yes, I don't like the fancy columns in Dataverse either. I get the people who hate on SharePoint as a data source BUT for a lot of people it is the best thing they have, due to licenscing. So I roll with it.

[@louiscutmore]

YYEESSSS. This is awesome 😎 Thanks Shane!

[@ShanesCows]

Glad you like it!

[@simonowen76]

Fantastic as always, Shane! Thank you!!

[@ShanesCows]

Glad to help Simon. 😀 have a good day

[@mohdraqibamirudin8356]

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

[@ShanesCows]

You would need to store the file in a doc lib and then reference the link. Do able but lots of little moving pieces.

[@lovestromberg4137]

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

[@ShanesCows]

That doesn't sound fun. 😁 Long story short? I NEVER use it. If it is needed I am doing things to confusingly so I try doing them differently. Not saying there is any issue using it, I have just avoided it for the last 3 years.

[@jeremyleff]

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

[@ShanesCows]

Fantastic! Glad it helps Jeremy.

[@AlanShieldsJr]

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

[@ShanesCows]

Look for my video on repeating tables like infopath I think that is what you are looking for. 😀

[@asifkhawaja]

Heyyyy Shane. Another good one as usual. Thanks.

[@ShanesCows]

Thanks 😊

[@humzasagheer3936]

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?

[@krishnaagarwal8610]

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

[@ShanesCows]

Rock on!

[@andresp2777]

This is Gold!!!! Thanks Shane

[@ShanesCows]

Thank you 🙏

[@sujaysooknanan3430]

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

[@ShanesCows]

Glad to help you :)

[@carolevandenberg1474]

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

[@ShanesCows]

Hey Carole I don’t use charts much. They aren’t very good. I usually use powerbi

[@haisamrazzak9691]

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"

[@ShanesCows]

I will consider it. 😀

[@dainiuszivelis4153]

Thanks Shane, as always very useful!

[@ShanesCows]

Glad to help. 😀

[@bschraders]

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? 👏

[@GustavoPalaciosgap]

Thank you Shane, very helpful all your videos

[@ShanesCows]

Glad you like them!

[@frenamakenson9844]

Nice demo

[@ShanesCows]

Thanks 😊

[@muyiwaishola3196]

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

[@ShanesCows]

Thanks 😊

[@titusjv360]

@@ShanesCows uhu

[@thoerzer]

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

[@ShanesCows]

SharePoint doesn’t have a 5000 item limit. 😀 But it does get slow if you try to read big lists. If you have that much data I would consider another datasource

[@thoerzer]

@@ShanesCows Thanks for the answer! Probably my company set the limit !?!? Anyhow, i can't use another data source, but I'll make it

[@despotter71]

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

[@ShanesCows]

I haven't tried but I don't see why applying this concept kzbin.info/www/bejne/aYLEmoBuiMqKnK8 wouldn't work.

[@coranismyway9087]

Great Video. You're awesome! Thanks!

[@ShanesCows]

Thanks for watching!

[@charlessterling379]

Great video thanks Shane!

[@ShanesCows]

Trying to make you proud. 🤓

[@aliendb]

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.

[@constructioncarpentersltd7764]

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!

[@ShanesCows]

It can generate massive amounts of data but I would prefer a long list than a smaller but wider list. Just depends on the though. Not sure there is a right answer 😀

[@constructioncarpentersltd7764]

Thank you for replying, after much thought and another go on this video and the penny finally dropping, I understand you logic now... You group the column entity in a SP list first which gives it an ID that you use to build relationships between galleries. Pure genius, thanks Shane 👌

[@tiranirani6501]

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!

[@ShanesCows]

Is it maybe a combobox? They use DefaultSelectedItems not default.

[@ramshyam604]

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.

[@karteekabcd]

I am looking for something very similar too. We have 10 questions all having Yes or No and corresponding picture. Like, Question1 -- Is the door close? Yes or No and Question2 -- Attach a picture of the door. So, I would love to know the answer if that is possible to do using this new data model.

[@ShanesCows]

Yes it is possible. For all text things I use one column and just define the type of input at the question level. I will do a video or class one day. For images I would have a third list where I stored those most likely.

[@ramshyam604]

@@ShanesCows Oh I see. Thanks a lot for the input and waiting for the video. :)

[@mozgus79ify]

Thank you Shane, exactly what I needed!

[@ShanesCows]

Awesome 😎

[@teresaagustin5191]

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?

[@ShanesCows]

Yup. In my Advanced class I showed off a more complex version with different controls. They all work. 😀

[@anaadona4155]

@@ShanesCows Hi Shane, I signed up for the training courses to learn more about this specific app. Can you let me know which video in there is the advanced class you are talking about? Thank you so much for the help!

[@humzasagheer3936]

@@anaadona4155 Could you let me know if you got access to the full version of this app and how?

[@christopherho1013]

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

[@ShanesCows]

Only if the query to get the chores back isn’t delegable

[@barnaliroy8703]

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

[@ShanesCows]

Model driven apps can’t use SharePoint as a datasource only cds. Sorry

[@barnaliroy8703]

Ok....but can we make a custom sharepoint entity in cds and then use it in model driven apps?

[@jbwise2002]

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?

[@artkarp]

Consider CDS for that kind of data row requirement.

[@brandondoramus4758]

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?

[@ShanesCows]

You can. Or have the buttons set variables then use the variable value when you save. 😎

[@brandondoramus4758]

@@ShanesCows thank you sir! Appreciate your willingness to help!

[@PPramov]

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?

[@ShanesCows]

I haven’t made a video on that yet. The basic idea is you need to have a way of tracking the choices. So I did it in my questions list.

[@labahlabahoren]

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

[@ShanesCows]

Not sure. View> collections and see if the collection has data

[@dgreer08]

I was having the same issue. My problem was that I had not made the screen visible (since the formula is on the "On Visible" property). Solution: create another screen that navigates to the "Narrow" screen. Play the app and navigate to the "Narrow" screen. When it becomes visible the collection will populate. I literally face-palmed when I figured this out. Hope this helps!

[@AakashDhawanKash]

@@dgreer08 Thank You for letting me know. I been in the same boat and struggling.

[@olahenningsson6949]

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?

[@ShanesCows]

Ola yes, I don't see why not. Where I patch in new rows you wouuld need to do LookUp funciton to find the record you want to change. So you would need to keep the ID of the records in your collection.

[@olahenningsson6949]

Shane Young I`ll try to figure that out “in baby steps” of course. In the mean time I got a way to do by PK Hong with First and Last of the gallery.

[@thomasboxler2536]

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

[@ShanesCows]

I think it would work well

[@artkarp]

I believe the default entities on CDS is now 500. It can be increased beyond that with a request to MS support. And yes, this demoed data model scales and works way better than having 200+ columns.

[@Eichenlaub]

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!!!

[@ShanesCows]

Yes. If the app is too big it gets slow to open and slow to edit even. There are no hard limits. Just a feel. Sounds like you have the feel.

[@glhuanto]

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}));"?

[@mohammadakmalabutaib194]

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

[@ShanesCows]

Sorry I don’t understand

[@skarJones]

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.

[@ShanesCows]

I can’t think of why this wouldn’t work for you. I do it all do the time. 🤔

[@shimaurasheed6922]

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

[@ShanesCows]

Glad to help. 😀

[@Ylotan]

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

[@ShanesCows]

When using SharePoint as a data source? Either text or a number column.

[@metalsnake00]

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?

[@ShanesCows]

Not directly. We have done this but we used Microsoft Forms for the input and then had it trigger a flow that ran PowerShell. Not trivial but possible

[@Salstravels]

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

[@JGNiDK]

I've never seen rich text in a gallery. But maybe Shane knows better?

[@andresfriss919]

hi, i have a project where the gallery has rich text, it works very well

[@jinweitan797]

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. :)

[@ShanesCows]

Yes, your Power Apps data model change would mean you would have to slice PBI differently

[@NightStreak4668]

after 19 mins my brain strangled my throat!!!

[@ShanesCows]

I hope that is a good thing? 😊

[@jeremywiatkowski7135]

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

[@maxcanada2010]

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

[@glore2jc]

Create another sharepoint list that holds a variable for the users progress. On app start- create the variable based on logged in users information //See if they ever logged in before// If(IsEmpty(Filter(SPlistUserVariables, email = User().Email)), Patch(SPlistUserVaraibles, Defaults(SPListUserVaraibles), {Title: User().FullName, Email: User().Email, LastLogin: Now(), Status: “New” } ), Patch(SPlistUserVaraibles, Lookup(email= User().Email) {lastlogin: Now()} ) ) Then on the onchange ti the form- you have to patch directly to the data source not the collection. And on the first on change(use a variable for this) patch the user variable sP list referenced above and update the status ti “started”. Chores Narroe would include the USer() info and next login - the app on start would filter the narrow list for any entries made by logged User() in status “started” or you could filter the child chores answers too and if “true” the data populated and they can continue the survey. I hope that makes sense. It’s hard to type this stuff out on a YT comment

[@georgewinters2066]

Brilliant ! Thank you

[@ShanesCows]

You're welcome!

[@iansuffield351]

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?

[@ShanesCows]

Ian you do have to manage those SharePoint lists when you use it as a data source. SharePoint has pros but does have some cons and scale is one of them. :(

[@avinoamrothenberg1244]

@@ShanesCows In such case I'd use CDS as my DB and gain relationships too ;)

[@bryanchaves5691]

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

[@ShanesCows]

I rarely uses forms. They are just so difficult.

[@elizabetharcher8502]

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

[@warlumola]

Yess!!!! Thank you Shane

[@ShanesCows]

You are welcome 🙏

[@ktaber317]

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?

[@jwvicho]

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?

[@ShanesCows]

I show filtering a child/parent relationship in SHarePoint in this video kzbin.info/www/bejne/eqrPqaaOfrqsesU

[@suzym1000]

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".

[@utsukprani8344]

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 !

[@ShanesCows]

Yes look at the Concat function. I have a video on it kzbin.info/www/bejne/d5-og5l9fqprndk

[@viniciuspereira2459]

I have one form with more than 120 questions, this is going to save me when need to update. But i have more than 1600 itens on my masters list, after all is updated i will have more than 190k rows on details list. How to view that in Power apps?

[@ShanesCows]

That much data you are probably looking at Power BI 😎 Sorry, at that scale it is more than just a quick answer. I don't want to mislead you based on a quick sentence. Hopefully your data source is up for the task also. Hint, SharePoint is not if that is what you were thinking.

[@nickchapman7562]

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?

[@ShanesCows]

Sorry I am behind on comments. kzbin.info/www/bejne/hKjFopd3bs9re80 Did you see my inspection app video?

[@mikenicholaou8246]

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

[@mikenicholaou8246]

I want to expand on my question. First, make the assumption that the parent-child relationship in these two Dataverse tables is established by explicitly creating the relationships using the "Add New Relationship" menu item in Dataverse, and that it's a one-to-many type relationship. So somehow, behind the scenes, a relationship link is established. Now imagine there is a PowerApps screen with a gallery of parent records next to a form containing controls linked to the fields in the child records. When it's time to submit the form, how is the linkage to the parent record established? In this video, which uses SharePoint as the database, the ID field in the child record was manually created and set to the value of the parent ID. Although this approach could be used in Dataverse, it doesn't appear to take advantage of the established relationship. How should it be done?

[@ShanesCows]

This video kzbin.info/www/bejne/oYeopZ1thsmFj7s

[@Itsprincessa01]

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?

[@Itsprincessa01]

because the list will keep getting longer and the blanks will keep taking up all the space.

[@ShanesCows]

ForAll(Filter(collection, Field Blank), Patch(logic))

[@Itsprincessa01]

@@ShanesCows thank you Shane

[@danielaholzner7892]

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?

[@ShanesCows]

Did you set the default property of the Dropdown to thisitem.fieldname ?

[@daniela-brownella]

@@ShanesCows I have yes.. on both (in my case thisitem.fieldname1 for dropdown1 and thisitem.fieldname2 for dropdown2). I also note that this behavior only occurs for dropdowns, radio buttons work as expected but just take up too much space...

[@ilkkaalhoke6195]

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

[@ShanesCows]

I don't think so. Can you leave a new comment with your full formula and I can take a peek.

[@jean-philippedelaune2607]

Simple et efficace !

[@ShanesCows]

Thanks 😀

[@danielszelagiewicz2262]

Will it work with DataVerse as a source as well?

[@sokgat7019]

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

[@ShanesCows]

Glad you liked it!

[@TheBantick]

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?

[@ShanesCows]

Save them as a status of draft. Create a gallery that lets people see their draft forms. Load the previous responses in when they edit the draft.

[@TheBantick]

@@ShanesCows Have you previously created a video on how this is accomplished?

[@brendanc3705]

@@TheBantick I am also looking into solving this issue, just trying to get my head around collections enough to make this work. At the moment I am working on two buttons, one for submit inspection when the user has completed it fully in their first attempt, then a "save progress" that saves as a draft, as Shane has mentioned. My problem so far, is that I have been working on a collection which means the data is lost when the user closes the app etc (at least that has been my experience so far). I am now looking at have a SharePoint List that is Draft instead of Result. Then enable the user to view completed inspections or active (draft) inspections, which when selected, pulls the data from the Draft SP list. Still fumbling through this so it may still not work.....

[@TheBantick]

@@brendanc3705 Any luck with your endeavor?

[@humzasagheer3936]

@@TheBantick I am also interested if this was achieved? Would be a life saver if you could let me know.

[@MsAmyrock]

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?

[@ShanesCows]

Columns have a number of columns settings you could mess with. There is a lot of dynamic settings just have to get real creative 😀

[@moxaku]

Very good ! :-)

[@ShanesCows]

Thanks 🙏

[@danielbell9192]

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.

[@artkarp]

At 250K rows of data you might consider using CDS instead of SharePoint. CDS is designed for relational data and scales to millions of rows by design.

[@user-bk8db7ud6k]

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

[@ShanesCows]

Awe shucks! Thanks. 🐶

[@avinoamrothenberg1244]

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 👍🏻

[@ShanesCows]

Thanks 🙏

[@ramshyam604]

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?

[@ramshyam604]

I was able to solve it. The issue was that because of On Change on ComboBox, the gallery was getting reset. (May be I didn't set the defaultselecteditems property on the combobox right). While I used colChores as the items for Gallery, I created a copy of colChores called colGalChores and then patched my answers to colGalChores no issues. I removed the default property on the controls too and had no issues. I know you mentioned in this video that the Default property is very important to be set right in this case but I couldn't figure it out correctly for Combobox and the above solution worked. Thanks again for the great content..!!

[@ShanesCows]

Glad you got it.

[@emiruzunovic6781]

@@ShanesCows Hi, I experienced the same issue as Ram Shyam, gallery restart on update and since screen si scrollable it never returns to the same Y value so you end up lost in question. I tried to patch to the same local collection, I tried to patch with a lookup to the different collection, I tried different controls, radio, button, and so on with the same result. So I ended up having something similar to your "Wide" solution. If you have any idea that would be great. Thanks

[@Saif_Ali_Khan_1301]

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

[@saracoolidge3722]

Hi! We had a similiar situation. So we created a generic user account that our entire IT dept has access too. Now, when anyone sets up a flow, they grant ownership permissions to this generic username as well.

[@Saif_Ali_Khan_1301]

@@saracoolidge3722 okay I understand but in MS flow each triggers and actions are having a connection to be set by default it takes the logged in user profile, in order to make the forms run smoothly we should change connection in actions to that generic account

[@Saif_Ali_Khan_1301]

@@saracoolidge3722 why I am saying this because When I was new to power apps I created a very big workflow with 10 update actions each updating 15 fields atleast and there was several parallel stage approval process too. After creation when it had to go live, I was handing it over to support team, they said they want there Team generic account access to be given so that email address can be changed for each notification etc. And then when I was about to leave the company in 2 months, so I had to change all the connection after giving owner access, Everything was fine but When I changed connection from my account to any other account for update action, it resets the update action😒😒😒 and I had to sit back and reconfigure everything which I had created in last 15 - 20 days

[@SteveGarciaJr]

Hi shane.. can you teach me.

[@ShanesCows]

Hey Steve we have lots of training and mentoring options at www.PowerApps911.com 😎

[@Dantresinari]

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!

[@saracoolidge3722]

I am having the same issues as well! Great video, would love to get some help on these items.

[@Dantresinari]

Sara Coolidge thanks for sharing. I’ll update if I’m able to figure out what is wrong.

[@saracoolidge3722]

@@Dantresinari - Thank you Daniel! I will do the same. I will say, I have built out the rest of the app despite these errors, and it KIND of works. It will still sync to your data sources, etc. However, my sharepoint "answers list" will only display the last submitted response for all the other questions (so if my response to the last question was "blue", all my other questions incorrectly show "blue" as the answer). So close, and yet so far. I am sure it relates to our dropdown errors.

[@Dantresinari]

@@saracoolidge3722 I think I'm on to something here... instead of using "ThisItem" in the patch function I pointed to the item on my collection like this: Patch(colInspections, First(Filter(colInspections, ID = ThisItem.ID)), {Answer: dropAnswers.SelectedText.Value}). The error is gone and now the dropdown gets patched to the right question! 😊Give it a try.

[@saracoolidge3722]

@@Dantresinari - Thank you so much for sharing, glad to hear your app is working! I updated my OnChange property with your code and while it does seem to help with updating my collection in live time, I am still getting the dropdown errors, and my sharepoint list repeats the same last answer for each question. Did you also happen to make any changes to the Default property of the dropdown? Is it still ThisItem.Answer?

[@willbee6591]

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

[@ShanesCows]

Oh neck no Will. My kids do closer to none of that. 😂

[@glore2jc]

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