PowerApps Multiple Data Sources

and today's show we're taking your existing power app and adding data connections to other sources so that one power app has multiple sources we're using self file for one of the data sources and a SharePoint list we're going to use those connections to feed some dropdowns and pull a bunch of data together and then just write one record back to our master data source we're also gonna throw a couple bonus tips along the way so should be pretty fun but first here's our intro hi my name is Shane Young with bold zebras those guys in today's show we will look at adding multiple data connections to our existing power apps we're gonna play with some dropdowns and I'm gonna throw a couple little nuggets and tidbits in there all in the way because let's face it these are the type of things you want to do and really this is all coming from once again viewer questions people like hey I loved your first video but now how do i aughtta meant that how do I add other stuff so we're gonna take that first video and we're gonna build on to it so let's just switch over to my desktop and take a look and so over here where I want to do real quick is I'm going to show you what we're gonna build right get you all excited then we'll tear it down and then we'll put it all back together alright so if we hit play right this is the app we built in the other video there's a link below if you need it and if we go in and we say a new item you'll see that now under department I have a drop-down with different choices of what department do I want and for favorite color I am also having a drop-down now these dropdowns are being fed favorite colors coming from a SharePoint list and departments coming from a different Excel workbook so trying to show that we can pull data sources from lots of places to create this one record which we're going to write back to our master data pretty cool huh I thought so alright so the other neat thing that I'll show one of this neat thing let's close this as if we go in here to an existing person looks like Bill Gates works for me now how'd that happen but it also works for existing users when we're editing those and turns out we need a little bit of a little bit of finagling there right to make this work correctly so that's showing blank values well into a new form verse is filled in values when we're doing an existing form right we don't want to mess up the existing data and we show those and since they share the same screen we got to do a little if statements to do that all right so let's dive into it and so the first thing to do to dive into is I'm going to tear down what I built here so we can build it together and I thought this was one little bonus tip size gonna sneak in for you is you know how do you undo all this so if you look at this card right now actually I'll go to receive someone edit screen so go to edit form and so if we go to the Edit form one and do properties and then click on vertical we see all the cards right this is normal but we notice a department and favorite color are different colored and that's because those are custom card right those are ones we unlocked to make our changes but if you scroll the bottom the regular Department and favorite color are already down here so to get rid of the custom ones we're just gonna click the little things and say it delete this custom card same thing for favorite color delete this custom card and so then Department and favorite color will put those back in so right now it's that default experience and if I drag department up here a little bit it's right there so this should be very similar to what you have in you built that previous video right so that undid that close that the other thing I need to undo so that we can go throw it together is if I go to view and then data sources and so I added the colors so we will remove it and I added departments so we will remove that also alright so that gets us back to the same app that you have in front of you if you followed the last video and I thought that was a good bonus tip right let's walk through how I undo all these things I do is I kind of move back and forth you know practice the video make the video practice video a lot of repetition in this so with that all set up with a nice blank app what we're going to concentrate on in this one is the edit screen alright because we don't need to drop down on our display screen and we don't need to drop down or any of that type of stuff on our view screen right over there we're just you know we're just showing the default data so we need the dropdowns there we only need the dropdowns on our edit and new screen which it turns out in our default app here edit in new screens are the same thing so let's get to the Edit screen not the details there's the edit screen and the first thing we'll actually do here is let's go ahead and add our data connections so if we click on data sources we can see that right now we just have the default one so we're gonna add another data source and when that comes up then we're going to choose onedrive for business and so out here I created a different workbook called employee directory one it's the same workbook I just want to show you could use multiple Excel workbooks I made made a copy of it so we could connect to that so say employee directory one here are the tables departments or employees we want the departments table and so we'll say connect ok so now we have that data available to us and that's called departments will add another data source and this other data source we're going to choose our SharePoint list so it's going to showing my recent sites we're gonna choose this site and so here I'm going to choose the colors list so we'll say connect to that and just real quick for reference so you can know if we go over here to my SharePoint site you can see this color list I made a number value in the title I get color name I spelled it the fancy queen English Queen's English Way right I threw an extra U and there because I know so well saucy and then I did a color number code and the only reason I did this was just to show you how to kind of deal with if your Excel or your SharePoint list had multiple columns in it all right so this will feed that and then I guess to be complete employee directory one and so then over here you can see departments and all the things I have now you'll notice here that I'm put a blank line at for my departments and that's so that I can show that the drop-down right there's no way to have a blank in the departments drop-down unless you have a blank in your data so I put that in place to give us that flexibility so when we do the new form its blank there I didn't do the same thing in the SharePoint list I should have I'm a slacker that's right once you get the concept you should be good alright alright that's enough setup so over here what we're going to do is we're gonna click on edit form one and we will mess with the department first so right now right department is being derived from the existing data source well we don't want that so I'm gonna do some click on that card and I'm gonna click advanced and we're going to unlock it so we can change the properties and then once I unlock it we're gonna take this ugly field that doesn't give us choices and we're going to eat it uh-oh we got a couple yellow arrows warnings we'll fix those in a minute let's not sweat those right now but now what I need to do is click back on my Department card so make sure you got your Department card selected and we go to insert and controls and drop down and then we're gonna kind of click on the Department card again and make it a little taller then we'll click on this guy and kind of drag it over here so it looks nice and nice being relative right we all know that I make ugly stuff I tell you guys to make pretty stuff but I make ugly stuff because here we're learning mechanics all right let's so then for the drop down you see by default the items is drop down sample well that's not what we wanted we want to use departments right don't use Department Department is from the other one departments is that data connection we just created so we're quick on departments all right and so that would fill in their list they're just nicely if we hit play the drop down there you go that was literally it that's all it takes to get that in there now we're gonna do some cleanup work we're gonna make this better but the core of it right that was how easy it was to connect that simple little list okay so departments is good now we got a couple warnings so let's check those out first and so this first warning is you can see the red squiggly line so what it's saying is this little control is built off of the data card well that's no good and it looks like the heights are wrong well that's because we deleted the data card value 19 so what we need to do is we're just going to change this to be drop-down and this is drop-down 5 so drop-down 5 all right and then we'll do the same thing here drop down 5 again and boom that warning goes away right and all that's really doing is this little tiny control here is this little space sir it's not even necessary appreciators deleted it but I wanted to show you why there's an error message explain it so that's what I did so then here this one's a little more important this isn't a difficult one so this is saying hey the update value for this card right we see update written right here is broken that we need to fix for sure so what we're gonna do is we're gonna change this to drop down five dots selected dot value and so what that's going to do now is when we run our update form command right which is what happens when you click this little button right here right it submits our form so when the form gets submitted it's going to take the value from our drop down and push it into the department's record in the other list so that was an important fix but just like that we have taken care of our problem almost the last thing we need to look at though is the default value so right now the default value for this guy is what default it's a number one well that's not a list so it's not going to show it so it's kind of confused and if we don't fix this what's going to happen is we come in here and we edited this user's record and hit save it's gonna set his value to something we don't want in reality we want to keep the value that he's got but all right that's really easy to do but the problem becomes is if what if we're in the new mode right because we know use the same form for both edit transactions and new transactions so we need to insert an F into this default to make it all work and so what we're going to do is we're gonna say if we're gonna spell oh I hate when I press the wrong button see the mouse gets in the wrong spot it is messing me up right so if and then we're going to say edit form one dot display mode equals for mode dot edit right so if add it form one is in disappoint mode edit then we need to do one thing right and in that case we're gonna do do a comma so what do we do if that's true we're going to set this value to be parent default right because if you'd looked at the control I probably should have shown you before we deleted it but that's what the control was set to before we mess to it so we'll set it to parent dot default but if it's a new form we don't want it to make up some weird value for parent or taking any chances so we're gonna say if it's a new form we're going to set it to just a blank space and so then let's see I'm gonna say we're getting warning what's it matter oh you know what it's mad about Kai said display mode I did not mean to say disappoint mode I meant to say mode that sorry about that as a boo-boo on my part nice thing was power apps warn this gives a little blue squiggly we were able to fix it so now if we hit play all right so that looks like the dropdowns showing bill is in the maintenance department if we go back over here let's see I know that gray I don't know I don't know colas in the executives let's click on her so departments executive hit edit it's showing executive just like it should cool cool now what about if we hit new if we go in here new Department right now is blank that's exactly what we wanted so we've gotten our ability to change it and then let's go in Annecy so Luke is currently in the executive department let's demote him so say edit this item we can hit the drop-down and we'll put Luke back in the finance where he was before I started messing around this morning we'll say submit this item and there you go Luke is in our finance department yay we're writing data back and forth what's also hilarious though is I've already recorded the whole next second half but I went really bad I screwed it up so I just used that cut to start over so I've deleted everything since then and we're gonna rerecord it together now so you don't get to see the screw-up say ha ha alright so let's go back over to our edit data screen let's close out of this and so then down here at the bottom now that we've got the Department all working what's go and make favorite color blue let's make that field read from our SharePoint list and so our SharePoint list right in case why you haven't shown it to you here is a bunch of colors I can also see that put duplicates in here because I'm a bozo my son delete let's get rid of the duplicates you read those two or delete those and you can also notice so I got normal colors red blue green blah blah blah then also here I have an underscore and so we're gonna use that as our blank row right I couldn't figure out a way to make SharePoint have a blank item that power apps approved of so I'm going to use this an underscore as my color name for the the field we want to use that was the big screw-up that you didn't have to watch me suffer through huh alright so over here what are we gonna do first we're going to take our card so we're on the edit screen right we're gonna click on edit form 1 we're gonna click on properties and we're going to click on our friend layout then we're going to say alright favorite color we want to edit you so what advanced and then we'll do the unlock again as soon as we unlock it we will notice right the default values parent default so that's that thing we talked about earlier so we'll delete that easy enough let's make sure we're still in our favorite color data card here and we'll do an insert and we will insert a control and we will do a drop down again alright there's our drop down Oh click on the control to make it bigger as a whole maybe maybe not like so there you go and then we'll do this I'll put this down here okay so we got a great yellow triangles we got to fix again but before we get to those let's fix our SharePoint or our drop-down so from items now this time we're going to use colors same as before make sure you do the colors because that's what our data connection was and we'll do colors and now you can see right away that it put in one two three five right right down here so it's using the wrong column no problem how we're going to handle that is instead of saying use colors we're going to tell it to we want to use color name we fancy you in the middle and after we do that go down here hit preview there's nothing here right this is let's call it a let's not call it a bug let's call this power apps being grumpy okay so close out of that and if you just go back to the items again and basically undo it and then do it again like so we should have seen more drops you're why there you go so now it's red so for whatever reason when you do it on the fly power apps doesn't understand it so if you just do it again same exact thing it works right so could we hit play now there's our drop downs okay you'll also notice though that did you notice that yeah I probably too fast so we still have all of our duplicate right we have the two Reds the two blues and why is that that's because I changed the SharePoint list but I didn't tell power apps I changed it so make sure you go to view and then data sources and then color and then do a refresh doop doop do after a few seconds they'll finish refresh and so then now if we close out of here and do a preview we just see the one red and one blue so once again it's one of those things it causes a lot of frustrating like why isn't it showing my data because you didn't tell it to refresh that's why it's not showing your data usually a reason all right so we're good there now a couple things we want to fix about this one of them I meant to fix the other why didn't but so items right now is colors and color name but I can't stand the colors aren't in alphabetical order so what I'm going to do is I'm going to tell it that hey we want to sort by columns and we're gonna sort colors color name by the color name column oh this is gonna make me so much happier wet play and so then now we see those in alphabetical order and our little - as a top guy so fix that problem what other problem we need to fix another problem your fixes are default value again all right so let's fix that so default default currently is that that is not a thing so we're going to say if we're gonna say what is this it's edit form 1 again so we could have copied the other one would have been quite faster for us but edit mode 1 mode equals form mode dot edits so if the forms in edit mode then we want to set this value to what I'm going to set it to parent default again if it's not so that means it's a new mode then we're going to set it to the underscore just like before mmm that looks pretty good let's fix these two guys real quick and then we'll go test it so this is now what drop-down seven same tells that I I've been through some pain and suffering because drop-down 6 is gone we've got skipped because I had to delete it I broke it to drop down seven there all right so that fixes that and then remember the most important one for you to fix is this guy I'm so we're going to change this to drop-down seven dot selected dot now you oh that's looking pretty good let's hit preview and check it out okay so looks like this is looking good but let's kind of back all the way out and just make sure everything is doing we want so bills favorite colors currently green so a quick home bill that says green - that's good hit the drop-down there's green awesome let's change it back to our friend gold unless hit the check mark all right looks like Bill's got gold as a favorite color so that is working check let's back out of here let's do new and favorite department is boy we can choose one alright and favorite color is just like we wanted perfect so we've gotten all the things in place so awesome alright right for your bonus tip what's this bonus tip in here so the bonus tip what we're gonna do I'm typing in Shane here right I'll hit tab it jumps me to phone that is not data entry 101 I do not want to get tabbed into random places before we submit our data so let's X out of here and what you're gonna find out is that with each one of these pieces there is a tab index so his tab index is currently 0 so let's make him one boom that's your tab index go set your tab index to two like so hope your tab index is going to be when you find it 3 all right and when we'll stop there we won't keep going but now if we go back to our form so I type in Shane and I hit tab and then it's young and then Shane dot young and bold zeros right so that gives us the ability to control tabs and so you'd want to finish out the page 0 essentially means oh no it's it's not in the tab order so if that's all so I've just kind of random would jump around so we don't want that right now this is kind of jumping to wherever it wants to go but table index is one of those great little features right that makes your app just a hair bit usable especially in scenarios maybe not mobile phone entry scenarios but in desktop app scenario so thought I would throw that bonus tip in there because it's one of those things that makes my life a lot easier and I think with all that I guess maybe we should finish filling this out just to show you that dude yeah my title favorite color purple so we'll say check and then we should now see a Shane Young with a favorite color of purple scroll Scroll scroll so there's the old one there's a new and complete one so our data is being written back and forth multiple data sources being pulled in we're writing it all back to the default data source it's kind of what I promised to show you so I hope you enjoy it as always leave me comments below ideas questions you because that's what triggers these videos this video came from someone else's comment so hit me up with what you got alright thanks and have a great day hey it's me again just a reminder if you don't mind click the old subscribe button over here that always helps me out or if you want to work together you can always hit me up through the bold zebras or if really what you want is some more of these power rap videos which is probably what you want then the playlist is somewhere on the screen here alright thanks have a great day

Channel: Shane Young

Views: 143,578

Rating: 4.9403543 out of 5

Keywords: Shane Young, Bold Zebras, PowerApps Multiple Data Sources, powerapps data c, powerapps data card, powerapps multiple sharepoint lists, powerapps multiple l, powerapps multiple lists, learn powerapps, PowerApps tab index, powerapps excel data source, powerapps dropdown datasource, powerapps sharepoint data, powerapps sharepoint data source, powerapps excel dropdowns, shane young powerapps, powerapps

Id: fqH1U0_TanI

Length: 22min 9sec (1329 seconds)

Published: Fri Feb 23 2018