Power Apps: Multiple Data Sources in one Gallery #PowerApps #PowerAutomate #delegation #sharepoint

hey everyone welcome back so in today's video we will learn how we can connect multiple data source so that a SharePoint list or library to a single power apps Gallery control so let's take an example this is my SharePoint and I have these three different list account assets it assets and finance asset all of these three different list actually having a very similar dat data so they are tracking different assets like laptop smartphone across different department now what I want to do I want to create an app that can show me all the data coming from these different list so instead of creating a different app what I'm trying to do have a single app that will show me a consolidate data from these all three different list into a single Gallery control that you are seeing on the screen so this is my app I have this Gallery control and if you see on the right hand side this is the indicator showing the data coming from the different list so this is for finance I can just keep scrolling down I'm bringing first 50 result and then as you can see now it's coming from it and if I keep going now it's coming from accounting I can click more it's going to keep bringing the more data as in my list so I can keep going further it's going to keep bringing data from my SharePoint list okay so we're going to build or I'm going to show you how you can build app like this where a single Gallery control can bring data from different SharePoint list so stay [Music] tuned welcome back so let's start understanding the data source that I have so this is my SharePoint I have created three list okay and the all these three list are having the same column it's not necessary that they should have the same column but but for this video I'm keeping them same if you have the different list this method will still work okay so as you can see here I have account list I have it and the finance now what I want to do I want to create an app that should show the data coming from all these three different list into the single Gallery control okay so let me log into my power apps and we will create a new blank app okay and I'm going to add a new screen here so let me make a connection to all those three list and then we will go after that so I'll go to the add data connections select the SharePoint okay and I'm going to select connect those three list click connect so what this is going to do this is going to create the three connection now why I'm doing it because I need to show you something that why we really need this method what I'm going to show you okay so we have the three list I'm going to insert a gallery control here okay now as you can see here I can only select one of these data source to show in my gallery right I cannot show multiple now there may be a question that oh we can actually create a collection and collect all the data together and show it so yeah let's try that okay so what we're going to do on app on start I'm going to collect or I'm trying to collect data from all these three list okay so what I'm doing I'm creating a collection called Data collect and then passing all these three list data source that I've created okay I will run it so click on and start so this will execute and then I will go back to my gallery here we can say data collection okay and then changing this item do title so we can see the value and also let's change this to the department so we can see from which list this is coming in okay and this is the internal column name in my list so if you if I go to the department you see this is the column name for my list so just make sure that you are selecting the column name that you have now if I run it by looking at it it looks perfectly fine but let me do one setting change to check the delegation or to make sure that our app is ready to work with the large data set I'm going to change this 500 to one so as you notice the moment I change this change the number of data row limit to one that means so it's saying that if the delegation is not support it bring on you first record and if I go back and click on Run start to rerun the call and you see here from all of those three list I'm only getting one recall so what does that mean that means that if I go here this is limited to 2000 that's the limit that's Max I can go right so if if any of my list is going to have more than this record it's not going to be supported or delegable okay so instead of using this method we're going to use another method that can support any number of the item that you can have across these three different list okay so what I'm going to do now I'm going to insert a button here okay and what we're going to do we're going to call a power automate flow from here so select the button and on the left hand side you will see this power automate click create new flow okay create flow we not going to select any template okay now this is this will automatically have this action that is trigger point we are triggering this from Power app now here we're going to add couple of parameter right so the first parameter that we will add type text we will call it list URL okay and while we are creating this in this parameter we going to send the URL of the list and the second input we're going to add is a list title this will allow us to send any list or any number of list that we want to combine together okay so these two parameter we need okay now we're going to click on the next step search for SharePoint and what we're going to use we're going to use this send an HTTP request to SharePoint okay select the site address so our all three list under this particular site collection you can also have your list into the different site collection as well and you can just pass that parameter from the Power app here we are keeping everything in one single site collection method is going to be the get and here we're going to use the API and the this API pretty simple and you can actually see what the example is and we are actually going to use exact the same one so underscore API web list get by title and then you need to pass the title of your list okay okay so this part is pretty straightforward API web list get by title single code and then we're going to select the list title that we are getting from Power app okay name slash items so this will give me all the items then you need to add one more thing okay so here we going to pass some parameter that will allow us to keep getting more and more results from these list to the power app it's kind of a pagination so question mark okay and then we're going to put the list URL and I will walk you through what you need to pass in this list URL okay this is the API that you need to create so we made our API call to get the data from any list and by the number of item that we going to choose when we call going to call this PR power app now only thing we need to do we need to send this back to the power app so add a new Step search for Power app okay and respond to a power app or flow add an output we're going to call it response okay now before we send anything back to the power app let's see how we are getting the result right so I'm going to save it for now I'm going to pass just the empty string save it make sure the name of this flow is good okay and so I change the flow name and I'm going to also change this rename it to C items just a good practice to always keep these name so that easier for you to understand so now if I come back to my app it should have a right name display here okay so now now I'm going to go on select get list item. run this is how you call the flow from your app here you see there are two parameter text and text one so the first one is the list URL okay here I'm going to pass dollar top equals to 20 now what is this actually this is what we are saying that give me the top 20 results so when we're going to send this API request to SharePoint list we going to only fetch first 20 and then we going to keep fetching 20 after 20 okay and you can increase this you can go 100 200 okay and the second one is what we're going to pass is the title of the list for now run it click button okay now I'm going to go to make. powermate.com okay my flow and I should see my flow listed here as you can see our get list item now if you go to the Run history you should see one successful like we will go here okay open the get items and this is what I want you to see here right so we have passed we have used this API request it assets is the name of the title and we say top 20 what this is going to do is going to just bring the first 20 record and if you look at here the response that we are getting is d results okay when we are sending it back to the power app we need to make sure that we are sending starting here so entire results that we are getting okay so if I added this flow now okay so in the response what we're going to do we're going to come to the response select the result of this C item that is body right but you remember right this is not what we want to send what we want to send s is anything that is after that D so I'm going to contrl C or copy it paste it so this will become body get items question mark D okay so this is what we will send click save now I'm going to go back to my app click button okay so now you see right I got the entire result or response that I got in my power automate but if you see the data type is saying text so this going to be useless for us because we need to extract the information coming in the different columns that we have in our SharePoint list okay so to do that there is a function in power app called pars Json right and what it does it take an input that is in any in the Json structure and it passs it to the Json so that's all we are just going to pass the response into the Json okay and then if I run it again now if you go to the list data it's saying untyped so don't worry about it that means the data the response that we got has already been converted to the Json but because it was not power app doesn't know the structure so it's saying yes I convert it but it's not a type but I can still use it and how I'll will show you coming back to this Gallery okay and one more thing that I'm going to do put a semicolon and here I'm going to do a clear collect okay create another collection okay and what I'm going to do I'm going to take the response or the variable that I created here because this data has already been formatted so I'm what I'm going to do I'm going to convert it to the table okay and this is where you're going to see that it is converted so as you know we have that results right if you look at the response that we were getting so in the power automate the response that is going so D this is what we selected so we are sending this results so here is the results what we can do I'm going to type the same name here and you notice that I'm not getting any errors so that means it understanding it just not sh you what is inside so I got the results so what I did I did my variable do results that is here and I'm passing it to this data collection and now I'll come back to my gallery and instead of this I will use my new we created data collection I'm going to run it again so it will start getting fetching the value and now you notice it has some records so if I go to the each and every item here in my table this item dot you see right so it is showing up so saying this item do value that's going to give you the entire thing dot now I need to know my column name that I'm using my list similarly if I go to this one this item do value dot I know my column name is fieldcore 6 awesome right okay so what I did what we did now we send the request to power automat pass the dollar 20 that is top 20 record pass the title get the response pass it to the Json and collecting into the new data and converting the response that we got into the table okay and then converting it to table and then assigning it to the gallery now if you want to use more list it's very simple approach the only thing we need to do we need to copy this entire thing right so let's say in my case I have three list correct I'll do it at three times and just going to change the name of my list that I have and you notice right so we got results from it list Finance list and accounting list pretty much straightforward right now what about getting more results because we are only getting for 20 right and how you going to do it I need to show you the result one more time so every request that you're going to make right this is the result let me quickly show you this result okay so this is the response that we are getting okay we have we are passing this D and this is what's going results will have all the information about the items and columns and everything and then you'll also get this underscore uncore next what this URL is going to do it's going to give you the next 20 so after we get the result if we call this particular URL we will get next 20 and then keep going until us we reach to the last item so we're going to utilize this to get the next 20 or what whatever number of uh item in part per request you're going to decide if you decide 10 it's going to keep giving you 10 10 if you decide 50 or 100 it will just give you the next 100 or next50 okay so this is coming in underscore uncore next okay so I'll go back to my power app okay here what we're going to do we're going to create three more variable okay so we created list one URL list two URL and list three URL and use the initial load here and and here I'm going to pass the parameter here so list one URL list two okay now why we are doing it because we need to when we opening this app first time we going to pass this in the URL but when we going to after the first load when we need the more next result we're going to instead of passing the stop 50 we're going to use our next URL that you have just seen okay now what I'm going to do I'm going to copy this entire thing because this is for the first load right so I'll go to the app app on start this is where I'm going to call these functions so this is our initial load when app is going to start I'm going to set up these URL like this call make the call to the power automate for the first set of result and I am passing 50 you can change it and then collect the information here okay minimize it click on three dot run on start this is how you can test if you're not wanted published this app right and you see right the data is here now in this button we're going to change few things you don't need to initiate that right but what we need to do now we need to find out the right URL that we should be using right so if you look at this URL okay and I just decode it so you see here it's saying this is my site collection and then we have the the API that we were using API web list get list by item the list name items question mark and after question mark this is what we need to get out right and why we are getting this out because if we look at our flow we already have this part until question mark right everything is is going to match here so instead of passing only dollar top 20 we also needs to pass these parameter that will allow us to get next results right and so how we can achieve this this is coming as a string if I split this with the question mark the later part or the second half of this split will give me this so we're going to use this function in our power app and let me quickly show you okay so here I'm going to say set list one URL that that's what we are setting and what's going to be value so last okay split and what we are splitting text list one data Dot and the character is question mark dot value okay so let's understand this function first we are converting this list one data so list one data is the response that we are getting from Power automat so this one this entire thing right and we are saying next this is the parameter that we are passing here right so this this this line of the code is going to give me this value right and then I'm going to split this with the question mark and that will give me anything that is after question mark that is My URL for the next right so this this this entire section is going to give me the next URL and I'm passing that assigning that to the list one URL and passing it here very straightforward uh now I'm going to repeat this for three time because I have three list if you have two list you can repeat this two time okay so we have created our three next URL and after that everything is going to remain the same we're going to just call these call our power automate flow and assign it to the collection let's run it now when I click on the button what it should do it should go and get me the next 20 results for all of my three list and you can see here right and you can look at the ID they have started from 51 because I have the the parameter that I set is 50 click again now it will get me the it will get me the next 50 results okay so as you see after two attempt we got this error saying that something like tax blank cannot be assigned what does that mean that means that one of the list has already completed all the records this list URL when we say underscore next it's coming as empty because it's only going to give you this underscore uncore next if there is a result that it you can query right so to cover this we also need to have a check that if a particular list completed its record we don't need to have an error but just keep continue on the other list so I'm going to start with this and we're going to repeat this to all the list right so here we going to we're going to check the URL is blank so if this this particular variable is blank then we're going to just pass the blank right so the blank variable so passing nothing and then if it is not blank that means we have the next result we're going to continue on that okay so we applied the same log logic same if logic to all the three list I'm going to restart the app here run it and now I'm going to keep clicking on the button so it's going to give me the more and more results so now if you notice my third list item were not appearing here because we are now at the 100 plus and my account as at list only have 60 item so that way it's bypass the one list it's keep going and bringing the value for other list right and if you keep going it's going to just get all the results finished right the one thing we can also do so what I did here I'm just checking if list one URL list two URL list three URL if all of these three are blank then make my this button disable so that you won't just keep clicking keep clicking on that one right what we can also do I'm going to just duplicate this button and this button can be the home so we can just go go back to the front value and what the logic should be the same logic that we are doing on the app start right so this home button will have the same logic that we have on on start and it won't have the logic to disable it so we going to keep the display mode at it because we want this home button to be always activated okay now if I click on the home button you see right I got my first record first set of record I can click on more it's going to bring me the next set of result and keep going and I can always go back from the home yeah so this is all now you learn how you can connect Multiple List to a single power apps Gallery yeah so this is all for this video I hope this will be helpful for you thanks for watching and thanks for subscribing

Channel: Deepak Shrivastava

Views: 3,943

Keywords: multiple data sources in one gallery, powerapps multiple sharepoint lists, powerapps multiple lists, powerapps sharepoint data, powerapps sharepoint data source, powerapps, join more than one table in power apps, power apps, powerapps tutorial, power apps beginner, how to, powerapps delegation, delegation in power apps, delegation, non-delegable queries, data row limit, power apps tutorial, powerapps 500 item limit, power apps delegation, sharepoint delegation

Id: rLsZoW8GH_k

Length: 24min 58sec (1498 seconds)

Published: Wed Feb 21 2024