PowerApps: Offline / Disconnected. Add new plus modify and delete existing records then Synchronize

hi Paul here coming to you from datacom in Christchurch with another of my power-ups videos thanks for watching so in this session I'm gonna have a look at working in offline mode or disconnected mode show you how you can set your application up so that you can work with data locally adding records as well as editing and deleting records doing that all locally on the device and then hitting a sync button to synchronize all of your changes back to in this case a SharePoint set of lists so it's quite complex there's a few different things going on but I'm going to build the whole thing from scratch so take a little while but hopefully it will give you a good insight into how to do it so what we're going to do is we are going to imagine a scenario where a customer looks after a certain set of customers and they are going to go out sales reps are going to go out and take orders from those customers and they don't know if they're going to be connected or not so it's going to work in offline mode there are other use cases for this sort of technique as well so when you're working disconnected when you're just making all these changes directly on your device or in your web browser even then it will be really really quick because you haven't got any of the overhead of the data transfers backwards and forwards to SharePoint or whatever your data sources so even if you're in a situation where you know you're always going to have a connection you might want to have a look at using this technique to speed up some of your screens boot up some of your forms so you can imagine you know these customer facing scenarios where you want the interaction on the device to be as quick as possible and then when you finish with that customer you can hit a sync button or even if you've got a queue of customers you know when you finish the last one and you can see that you've got 30 seconds where then you can do your think to make sure that all of your data on back to the server okay so that's enough talking let's actually build something so what I'm going to do is I'm actually going to use ss to create my tables in fact I've already done it but I'll show you show you how I'm doing it what I'll do is I'll get rid of one of these again and recreate it so what we can do is we can create our tables link there we go we can create our tables in Access and then push them to SharePoint I just find it a little bit quicker to work that way so we can create a new table we can go into the design mode this is going to be my customer table if I can type properly and what I'm going to want to do is I'm going to want to have the customer name which is going to be short text and I'm going to want to have let's say a region for the customer and I don't need the ID I want the customer to be the ID so I'm going to switch the primary key delete out the ID column save all of that I can even put in a few psalm for customers we're going to have Wayne Enterprises and they're going to be in region north and we're gonna have doc and two prizes and they're going to be in region north and we'll have one more which is gonna be acting look at them in region south there we go so I've got a table with a little bit of sample data in it and what I can do is I can go to the external data tab in access more SharePoint list I'm using a site called Paul's test site so I want it to be called customer and I'm just going to upload that list and it's sample data to my site go so let's jump into the site now here we are so this is the select content so no customer there yet I'll hit the refresh button and as my customer list fantastic so let's have a look at these other lists as I say we're going to build a system in which we can take some orders so we're going to have a customer list to increase the customer an order header where we're going to record design view easier to see where we're going to record who the customer is we're going to use the customer ID for that we'll record the date of the order some notes and I'm going to put the user email against this as well and I'll explain why in a moment the order detail then is going to point to the order header ID so we know which order header is the parent for this detail record we're going to select a product ID that's from this product table and a quantity again we're going to have the user email in there and we've got a product table which is just ID and product name so just very very simple we're going to set up our application so that we pull in our customer list and our product list those are going to be static lists we're not going to add customers or products when we're in the app but we're going to add orders and order details each time you move a table to SharePoint it will give you a little report so this is where I put the customer table into SharePoint and it's just giving me a little report saying okay look this was your primary key field but it can't be also generated it has to be typed in it's not an auto number or anything like that so it's just giving me a little warning so I'll get rid of that there we are okay so I'm going to now put my other tables into SharePoint because right now in SharePoint I've only got the customer list so let's pop the other tables in just a quick note on the other tables for the IDS for the order header ID and for the order detail ID I'm going to use short text fields I will explain that in a bit more detail later for my static lists where I don't want to update any data from within the app for customer and product I'm happy to use a normal ID though I haven't even bothered with an ID field for that one and in fact I can remove ID from there as well because they will get an ID column when they move into SharePoint anyway but for order header and for order detail I'm putting my own IDs in which are of the type short text that will all become clear as we progress so let me very quickly then do that uploading so you just need to make sure that your tables are not in design view you can't upload a table and it's in design view there we are sir customers already in so we'll do order header next then make sure it go right sight antastic so there's all the header again I've got my little warning about auto-generated values so that's fine don't need to worry about that what was that I just put in was that order header yes order detail next then it's frustration that you can't select multiple and load them all in one go I'm sure that used to be an option but it seems that we have to do them one at a time now and we'll do product so obviously you can just create your lists directly within SharePoint I'm just showing you a little trick that I sometimes use cuz I find it a little bit quicker to do the design work sometimes in Access and then push the list into SharePoint so let's go and have a look now at our site content so we've got to do a refresh and now here we are we've got customer order header order detail and product that's great okay so now I'm just going to create and power up to use okay so I'm going to do my power app in tablet type mode because I want plenty of space on the screen and I want the text to be reasonably readable for you so that's just why I'm using that particular layout okay so the first thing we've got to do then is go to our data sources and just add those lists in so let's do that now so gonna add those all in so we'll hit the connect great now what we're going to want probably on our front screen are three routines in fact one of them has to live in the app on start but we're going to want three routines in all and on start routine which is going to pull in any cached data from the device we'll get to that later a routine which is going to load in the existing data from the server and a synchronization routine which is going to push any new data or modify data that we've got on the device back to the server so let's do the first pose so I'm going to stick a button on and I'm gonna call this button load data from server oops it's called BTN load data and put the text on it as load data from server right there we go so what we're going to do is in the on select we're going to do a clear collect and we're going to pull in the data for the customers from the server and we're going to put it into a collection which I'm going to call call my customers okay and we're going to bring in the customer data you just make sure that these things match it isn't critical but it helps later on when I'm making typos and we don't actually want to bring in all of the customers because we only want them for a particular region so I'm gonna say filter customer we're region of course north and the reason I'm going to do that is simply because whenever you are loading data into your application you want to bring in as few roads as you actually need so in reality what we would probably have is a table for the users so you could check hang on which is the region for this user and then you could bring in the relevant region only I've hard coded it for now just to get the concept across so well I'm going to do now is I'm going to click that button and we're going to go to view and collections just to see what came in so there we are here's called my customer and we can see there we've got our wayne enterprises our Stark Enterprises and all of the usual SharePoint columns the SharePoint fields so when you do a clear collect from one collection in this case a SharePoint list into another then all of those fields all of those SharePoint fields are coming into this call my customer great okay so what we're going to do now then is we're just going to repeat this for the other fields as well so that we're bringing in all of the data that we want so we've got a call roduct and for that one I'm just going to bring in all the products we're not filtering our product table all we want all products available in all regions I'm gonna do call my order header here and we're gonna do the alter order where the user email so remember in our table here if I go to order header we've got a field for user email so every time we create an order header we're going to populate the user email field with the user who took that order and then when we do our loading data from the server we're only going to pull in the data for that particular user so we're imagining a scenario here where you're having the same salesperson rep whatever dealing just with their own orders not having to pick up the orders that someone else has created and again this is just to reduce the amount of data that you're having to pull in from the server each time the more data you pull in the more time it's going to take and of course we are also limited by our delegation limit as well so the maximum delegation limit you can set is 2,000 the default is 500 so if you're doing one of these sorts of apps where you've got bringing in information to use locally it's a good idea just to crank that right up to the maximum and then you don't have to worry about not getting all of the customers or not getting all of the products if there are a great number of them so as long as there's not more than 2,000 this is going to be fine if you've got more than 2,000 customers or more than 2,000 product then you're probably going to have to start thinking about filtering those down so only the relevant products for that particular salesperson come in okay so I was halfway to rewriting this filter sorry so for the my order header in our capitalization right we're going to bring in the order header information but only for the current user so we can get the current users email by user open bracket close bracket dot email right that's all looking okay why do you not like that because I haven't put enough close bracket on there we go so yes so whenever you're bringing in the data have a think about applying filters to it so that they synchronizations take less time and so that we're less likely to hit the delegation limits now if we're doing stuff based on the user we can see we've got a problem here we've got a delegation warning but we can get around that delegation warning let me show you how what I'm going to do is I'm going to do an update context to create myself variable which is scoped to the screen I'm going to call that con user email and I'm going to set its value to user dot email there we go and then I'm going to use this instead and you'll find that that delegation warning goes away because by this point we know that user email is going to be some text and our Apps Script seems to be happier if we do it that way okay so I'm going to just want to do exactly the same thing again for the order detail so in terms of naming conventions naming conventions are good and naming conventions I'm using I'm using coal to represent that it is a collection I'm using my to indicate that it's been filtered in some way to only give back the data for the in this case the user that we want or here the customers in a particular region okay so that load data from server button if it goes I can go to my collections now I've got local versions of customer product order header and order detail I've got some information already in customer and product my order header and my order detail are currently empty but we can see that I've got all of the SharePoint columns in there which will become useful later on ok now a quick tip here the way I've written this at the moment is that first of all we're going to pull in all of the customer information and then when that's finished we're going to fill in the product information and that's finished we're going to pull in the order header information and so on so the total time that it's going to take for this load data to run is going to be this time plus this time plus this one plus this one we can make things quicker by wrapping these statements in a concurrent so concurrent allows you to run multiple statements side by side now what we need to do we separate them out with a comma because now each of these is becoming an argument of the concurrent statement and then we've got to put our final close bracket on there to close off the concurrent and now because all these are running simultaneously the time it takes it's going to be the time of the longest one so that's going to save you a lot of time when you're building a real app which has got more tables and more data in it so concurrent very much worth taking a look at okay so let's hit the button again there we go so flashes by very quickly so now we're loading our data in from the server what I'm gonna do next is I'm going to insert a gallery which allows me to choose a customer for which to create an order so you've got to remember now that we've brought our information in from SharePoint so we're not going to reference the SharePoint list we're going to reference the internal copy of that list or at least a part of that list if it's filtered that we've got in power apps so I'm gonna base it on coal my customer and I'm going to add a label in there which is going to be this item dot customer name so now I can see who my customer is there we go so we'll stick with just a two customers for the moment great so now I've got a way that I can click on sir I can see my customers the next thing is to be able to click on the customer and start taking order for them and to be populating their order inside our local collections so let's create a new screen and in here I'm going to say when I click on the customer name what I want it to do is to navigate to screen - now I'm not bothering to name all of my controls at the moment in a real application I definitely would use cover and what we're going to do is we're going to pass through to the next screen the details of this customer by saying con-con for context context variable current customer and we're just going to set that to this item we've got the details of that customer so that's great I'm going to click on there and it takes me through to screen - what I'll do just so I can see what's going on I'm going to add a label here which I'm just going to set to on customer now I need to put a dot after this because Cohn customer is the actual record because I set it to this item this item is the whole record so now what we will do is put in the customer name so we can see which customer we're dealing with again let's make that a little bit easier to see so we're going to be doing in order for Stark Enterprises so let's pop a couple of other controls on here because there's a few things we need to record for an order header so I'm going to remind myself what goes into order header so customer ID and ordered eight notes user email and an order header ID which we'll come to in a second so let's put in the order date and the notes other stuff can be hidden so insert an order date are you gonna want that from a date picker so here's a date picker fantastic and we will insert a text input box for some notes there we go right we're looking good so far so I'm also going to put in a button which we can click to confirm to create the order for Stark Enterprises so when we hit this button we are going to do a patch we're going to patch into the my order header collection so patch means writer record or modify a record in this case we're writing a new record and the way we do that is to use the default command and then we start specifying what fields we want to put in so we need to put in a customer ID so we can get that from our con customer dot ID we want to put in the order date and that's going to be my DT what I haven't renamed them have I they pick a two I'll rename them in a second got selected date I'm going to want to put in the text into my notes field so my notes is going to be text input or and my user email it's going to be user dot email yes try and match up all of my brackets and things again so there we are and we'll call this button BTN new order you order so that's for creating a new order quickly going to rename these because otherwise I'm sure that's going to bother me later DT order date and text input for it comes order note and of course the nice thing in power apps is that once you change the name of something it changes the name in the formula so this formula is now much easier for me to read even easier still if I format the text yeah so it's easy to see what's going on I've made a little error there actually order notes dot text best to specify which property you want sometimes it will work without it but best to be on the safe side okay now there was one other thing that my table needs and that's going to be the order header ID now remember I'm creating these order headers while I'm disconnected if you like from a SharePoint list I'm creating it in here this called my order header now might have a bunch of people using my app so what ID number should we use a sequential ID number is going to be a problem because there might be three orders in the system now so I create an order order number four but another user is creating an order that ends up being order number four as well so that's going to be a problem we need a unique number for a unique identifier for these records so this is quite common in systems that will work when they're disconnected from the main data source they have what's called a globally unique identifier so what I'm going to do here is I am going to generate a unique number so I'm going to do an update context I'm going to call this Kahn order header ID and the way I'm going to create the unique number is I'm going to use the GUI D command leave Moberly unique identifier some people call that grid some people call it gooood I'm going to call it do it you say tomato I say tomato the other thing that it's worth doing I have found is prepped fat in the text function so within power apps after a recent update globally unique identifier their own special data type but that data type isn't recognized by SharePoint SharePoint which treats it as text and as we go further through the app what you'll find is that sometimes you're bringing in the value from SharePoint in which case it's text and sometimes you're generating the value inside the app in which case it's the UID so if you just wrap this in text and you're sure you're always dealing with text and it just makes things a little bit easier later on okay so now we're going to set the order header ID to no doubt Heidi that unique number that I've generated and what I'm also going to do so that I can see what's going on I'm going to insert a label in my app and I'm going to set that to comm order header ID okay so we can see there's nothing in there at the moment because we haven't pressed the create a new order button here so we'll do that in a in a second in a real app then I would disable the new order button until we'd entered all of the necessary information for a new order but in the in in the interest of time I'm not going to do that right now so what I'm going to do here is I'm going to create a new order and then I'm going to show you how we can save that new order back into the SharePoint list and then we'll start going onto all the details and other bits and pieces as well I hit the play button here I'll choose tomorrow's date I'm going to put in some text this is an order hey dot and I'm going to hit the new order button yes oh yeah sorry I've done the necessary bits so hit a new order button so that should written a new record this locally to my Col my order header table and that's the ID number that it's assigned to it so it doesn't matter that these ID numbers are very long remember it's a random number and you're never going to get two numbers the same generated okay so I'm going to now look in my collections and look at my order header collection and we can see that this is an order for customer two there are the notes I put in this is an order header there's my order header date and there's my order header ID so at present this only exists on my device because I've put it into my local collection so let's go into the order header table in SharePoint and we'll see that it is still empty there's nothing in there okay so let's jump back we'll put this on our front screen what we're also going to want to do is have a button which synchronizes our local data back to the server so let's put that button in so I'm going to call this button BTN sync and we'll just call it sync they're fantastic and now what we're going to do is we are going to say I'm gonna do a collect it's not a clear collect this time it's a collect we're going to send the record that we've got in our local coal my older headers into the order headers table list in SharePoint it's going to go into order header what I want to put in there is call my order header okay so because my order header was generated from order header in the first place I know that all of the fields match we've got no problem they're just doing a straightforward collect when you're collecting one collection into another or a collection into a into a list then all of the field names and the fill type should match and we know they do because when we did a load data we created our local copy from SharePoint okay now I'm going to hit my sync button there's something else I need to do here and I'm going to come back to it but I'm going to show you the issue first of all so I'm going to hit my sync button lovely and I'm gonna go back in to order header and now I can see the order header that I created this is an order header there's the date it was for customer - I'm the user and so on we've got all our information in there now what's going to happen if I press the sync button again fresh ah and I would have expected that to put the record in again hmm okay that's interesting but I wouldn't always trust that to to work in my local collection I've still got this and I've certainly seen before sometimes when you try and sink again it ends up putting the same record multiple times in so what we're gonna do is although it's not causing a problem now I don't trust it not to cause a problem in the future so I'm going to filter what I send and I'm going to only send new records back now how do I know if a record is a new record well look when I look in my order header I've only got the field that I have completed locally on the device I haven't got for example an ID number now anything within SharePoint or that has come from SharePoint will have an ID number so if I go to product and we look at the ones that we pulled in from SharePoint these all have an ID so I'm going to use that fact in here to say I only want to upload records where the ID is blank fantastic now what I want to do once I've uploaded the record is I want to run this load data from server again so that I've got all of the latest information and so that the record had I pushed up I will see its ID so if I go to add columns here show/hide and we put in the ID we're going to want modified later so let's pop those both in okay so I want to be able to see that information coming back into the device so I'm simply going to say select BTN bio-data and that's going to run all over the code which is sitting behind the on select of the load data button so let's remind ourselves quickly again in our collection my order header is showing us something which is only on the device now I'm going to hit my sync button which is also going to run my load data button now when I go into collections there's my order header with all of the additional information which has been input by SharePoint and it's telling me that it's got an ID of one and that's fine I can think as many times as I like now because my sync says only send data back to the server or any add data to the server if the ID is blank and the ID is not blank now so I couldn't think of many times it's like and in my order header I'm only going to have the one record in there so that's cool so we're already at a point where we can run in a disconnected mode create new records and send those records back to the server and then we can load all the latest data from the server back into the device that's a good start ok so I'm gonna go back to screen 2 again now now what we're probably going to want to do is whenever we come back to screen 2 is to clear out what was already in there so this is going to be our taking an order screen and I'll make a separate screen for modifying and deleting orders later so this is going to be our taking order screen so what we're going to do is we're going to say when this is visible what we will do is we will do an update context and set our on order header ID to blank and we're going to do a reset on our txt all denotes naming conventions naming conventions are good try and stick to them just like I didn't write ok so let's do that a game yeah we are txt or denotes and we're going to reset the DT order date there we are think that only two things that we're doing there isn't it okay so we're gonna reset those values each time we come in because this is our creating a new order screen so let's just do that and go and do one for Wayne Enterprises now oh so now we've got an empty one so we can create a new order so I'll tell you what I will go ahead and create an order there and then we're going to want to be able to add product to the order so I am going to insert a make it a combo box for the product product the items property for that is going to be call product and the field that we want to use are going to be think I call it product name product product name yeah name has to be inside the there we go okay that's better so we'll use product name for the display and the search field and the other property we're going to want to set is to say that we can only select one item so we're going to add our products one item at a time so let's just see yeah those are at products and then we're going to add a text input box so that we can put in the quantity of the product that we're gonna order and we're going to change the format of that to be a number we're going to change the name to txt quantity so now we've got somewhere where we can record the product that we want to add and the quantity of the product we'll just give ourselves a little Add icon so that we're then adding that on so when we select this we're going to do a patch we're going to do a patch into Col my order details so order detail is what product and how many we're adding a record so we use the default instruction and we have to specify the collection again and now we can start putting in the fields so we're going to need check our fields first of all order detail easier to see them in design view right so my order detail ID remember we need a a unique ID for this what we're going to do is we're going to use our old friend the good and in our order header ID is going to be our con order header ID so that's the order header ID we generated for this order which is stamped against this order and then I'm going to need act ID product ID and we're going to set that to whatever we selected in the combo for product CBO product dot selected dot ID there we are oops ID and then we also need the quantity which is going to be what we've got in that quantity text box txt quantity dot txt and it's upset there I think because yeah it's even giving us the the information error saying hang on a minute you're setting quantity which is a numeric field to the value that yet you get from txt quantity dot txt now even though I've set that to be only accepting of numeric values by using those properties on the right hand side there I said set the format to number I've still got to make sure I changed the data type from text to a number by wrapping this in the value statement and that will convert it into a number right so now let's see what's happening so Wayne Enterprises are going to order some batarangs we're going to have ten and we're going to click the plus button so when we click the plus button it should have done all this patching for us so let's just go have a little look let's go to our collections and order detail and what did we do we ordered ten batarangs batarangs a product ID one let's just double-check that product ah okay cuz they're in SharePoint now so SharePoint will have assigned the ID so let's go check it in SharePoint so site content and the product and we'll have to show that column there we are sorry a little bit long-winded but Batarang is indeed ID one okay so at the moment we can't actually see that so let's give ourselves a gallery and I'm to set the items for the gallery as being called my order details but we only want it for the current order so we are going to filter it to where the order header ID is equal to common order header ID and now we can drop some labels in there so we'll have a label so it's item dot product ID and I'll have the quantity over here item quantity and then over here we'll do a lookup on this so we're going to look up in the Col product and we want it where the ID equals product ID and the value that we want to get back is the product name there we go so there we ask that we've got our 10 batarangs let's put something else in this order as well I think we might need a new Batsuit at one of those just tidy up our template size here now we are and we'll have five lots of shark repellent so obviously if I were taking time over this application after adding each of these I would reset the quantity box but you know this is just to run you through some concepts quickly okay so let's go back to our view and our collections and our order details so now we can see that we've got our orders in place and so when we go back to screen1 and we want to do a sink we're bringing back in the order headers at the moment we're going to want to have another line for bringing in the or sending back to SharePoint the order detail so send back those points order detail list call my order detail so you see how we're starting to get warnings come up customer ID doesn't exist because I've got a mismatch of order detail and order header so as long as these all match we're all good and the other thing that we would want to do there of course is make sure those two commands run concurrently so that everything goes nice and quick now one do we start getting lots of closed brackets and things going on I like to use that format text option because then we can see which brackets match with which commands there we go so that's going to synchronize my information back so let's hit the sync button there we go so that should be sending my information back we then also load back the data from the server so let's see what's happened on the server so yeah I've got to order headers in there now and if we go and have a look at our order details there we've got our order details for those three order lines that we've just put in and when we come back into here and review our collections I have I got nothing in my order detail my order detail should be populated let me just see what I've done wrong there I did say at the beginning that there were lots of places where this could go a little bit wobbly so we definitely sent the information to share because there it is why can't I see the information from SharePoint back inside here oh did I forget to put the user email in my patch statement let's check that out yeah looks even though user email in there so let's quickly jump back to my patch statement that was the one on this button so I left a column off where you want to say user email should be set to user dot email because remember when we're loading the information in we're only loading our information to try and cut down on the amount of rows that we're bringing in so I forgot to stamp those records with my user email so I think what I'm going to do at this point is I'm going to quickly just delete out the data from order detail in order header so we've got something nice and clean to work with and we'll jump back in here again so no data in there at the moment Stark Enterprises I'm going to place an order a new order and they're going to place an order for iron armor for five of them boom and I haven't got any other buttons on here at the moment but then we'll put an order in for Wayne Enterprises what I'll just do while I think about it is set the unvisible of this now that I've got a few more fields on there to also reset my combo and reset my txt quantity and I'll tell you what as I've already typed that out I'll pop that reset of the txt quantity on here as well so each time the number is cleared out again okay so that should be our order for our five opps of I and armor and we'll go to Wayne Enterprises now and have a big order for them so again we're going to have five batarangs books plus button and one new bat suit and two lots of shark repellent great okay so back to our synchronization screen and I'm going to hit the sync button and I'm just going to check what is in my order details there we are load of order details now that I remember to put my user email against them and we should have order headers there as well all this in the refresh on that we are did I place one order okay right so let me just have a little look here so my order header has not come in for these three rows I didn't hit the confirm order button did I okay apologies I'll show you what I mean all right yeah so I said if this were a real app to stop me making mistakes I would make this stuff disabled until a new order had actually been put in so here we are all this stuff was created with no order header against it so that's easy enough to stop us from making that mistake again so apologies in the interest of time I didn't bother to do this but now I'm going to do it so if we haven't hit the new order button then our order header ID our context variable for our order header ID is going to be blank so what I can do here is I can say if comm sorry is blank if is blank comm order header ID then make these disable otherwise make them unable sorry it isn't because it's the display mode there we go so I cleared that back out again apologies for that but here we go so I'm going to do my load data I'm going to go Stark Enterprises so I can't make the mistake now and start typing things in there until I've actually created the order here so I won't bother putting in any text this time but I'm going to create my new order now I'm gonna order my iron armor that's done and I'll put a back button in in a moment we'll go to Wayne Enterprises new order and this is where we're going to want to put in our Batman stuff cool so now let's jump back to our sink screen and I'm gonna hit the synchronize button so this time hopefully our order details will come in with order detail and order header and we've got our order headers in there so apologies for that little mistake that okay so that's kind of cool because we can now create records when we're disconnected and we can as we saw there create records for multiple customers and it's just when we hit the sync button that that gets pushed to SharePoint so what about if we need to modify or delete some of records as well so what I'm going to do now is I'm going to create a new screen and this is going to be for modifying orders in a real app I would probably try and repurpose this existing screen so I could use it for new and existing but it's going to be a little bit easier here with the formulas just to have these things separated out so let's pop in a gallery and in the gallery I'm going to put in my list of order headers so this is going to be based on Col my order header and things I want to put in here it's obviously going to be who the customer is so here I've got the customer ID I'm going to do this with a lookup again now if you are doing lookups and you're doing lookups to data which is on SharePoint or on your external data source then they can be very slow because I also have operate one at a time if you've got a gallery with lots of items you know you can actually see it sort of churning through them and things populating because we're working with the data locally that's not going to be a problem for us it's going to be very quick to do these lookups because there's no trip to the server and back affects the data the data is already here so I'm going to look up in coal my custom where the ID equals this items customer ID and we're gonna bring back the customer name there we are so let's make that a little bit bigger make that clearer for us to see where we are and let's put in our order notes that's hate it when it does that's copy and paste into text rather than copying and pasting the text box or label right there we are and then we'll have this item dot order date and we'll there we are drag those up nicely so now I can see the orders here on the left and I'm gonna make it so that I can see the order details on the right I'm gonna save myself a little bit of time I'm going to grab that gallery that we've already created over here for showing products and quantities and I'm going to drop it on this screen move it over the side there and I am going to say filter my order detail I haven't got a context variable for what a header I want it to be the selected item from the gallery so this gallery here is gallery 7 dot selected dot order header ID there we go so there we are so Stark Enterprises ordered the iron armor and Wayne Enterprises ordered all this Batman type equipment so cool I'm going to add the option to delete items so if we want to cancel one of these older lines so let's show you how we can do that I am going to pop in a little cancel or delete type icon I'm gonna make it red because deleting you want to be careful about so when we click this cross let's have a little think about what can happen if I haven't yet set this is sent to this information to SharePoint I can just remove it from my collection it doesn't matter how many exists on the device but if it exists on SharePoint what I need to do is not remove it but mark it in some way so that I can hide it from this gallery and also when I do the sync I can send an instruction to SharePoint to get rid of that record so let me show you how we're going to handle that so when we click on this when we select it what we're going to want to do we're going to want to check whether this has been saved to the server or not we know it's saved to the server if it has an ID so we're going to check if it has an ID so if this item got ID I always do this back to front if he's blank this item dot ID then it hasn't gone to the server and we can just remove it you can just say remove this item yeah sorry it got to put the call my order detail in there this item that's right so the remove is where do you want to remove it from what item do you want to remove so if it's not gone to SharePoint we can safely remove it from the local collection but what if it has gone to SharePoint so let's fill you now else if it's gone to sharepoint we need to mark this record in such a way that we can pick it out later when we do in our sink and easily get rid of it from sharepoint so the way i'm going to do this is I'm going to patch call my order detail the record I want to patch is this item and what I'm going to do is I'm going to change the modified date so remember we're using SharePoint so everything that goes into SharePoint end up with a modified date and that modified date gets overwritten anytime it's modified so I'm going to use this column and I'm going to set it to my own date value which has got special meaning to me I'm going to say the first option 3000 because we're not likely to ever get that date in SharePoint is a legitimate one so I'm marking that as being something that I want to delete so let me just go to my view collections and my order details look at my modified dates they're all sensible modified dates at the moment they all went in at the same time because it was when we did the synchronize so now I'm going to hit this I'm going to hit the button it's not going to be removed because it's on the server the modified date is going to be changed so let's go check one of my order details should now have a modified date of there be our first of January 3000 couldn't see it for a moment for looking there we are so that's done what I want now I can still see it in here because I just said look give me the stuff in my order detail table for this order header so I need to modify this filter a little bit now so I'm going to say and where the modified is not okay value first of plan 3000 and now once I get my formula right then the row that we marked as deleted should disappear and the thing I've done wrong again is I haven't put my quotes around my date there we are so now there's only two items in there because one of them is marked for dilution so your user can now merrily work away in when they click on the cross the item will disappear whether or not that item has previously been saved to SharePoint but of course now what we need to do is we need to go back to our synchronization button and we need to say hang on a minute it's possible to delete orders as well so all of the new orders all of those with a blank ID get added what we're going to do is we're going to remove something now okay now when you do everything inside the concurrent all the commas and brackets get a bit confusing that is the way that you would want to do it but again for the purposes of saving a bit of time I'm not making too many mistakes I'm just going to do it in a in its own statement after nice thick and current for the moment so I now want to find all of those records that are marked for deletion so for everything in Col order detail the filter on that but everything in Col or detail where the modified date is equal to date value one can three thousand so all those items I want to do a remove if and we're removing from order detail let me just put the my order detail there that will stop it complaining a little bit remove from my order detail where the ID of this item in order detail matches the ID of this one in cold my order detail where the date value is the first of June 3000 now the problem that we have there is that power apps is going to assume that both of these IDs are in the order detail table and in that case the order detail ID in the order detail table is always going to match the order detail of my tea in that table and it's just going to delete everything so we need to be able to disambiguate they call it we need to say hang on a minute this ID is the ID from here and this ID is the ID from here now there's different ways but the way that I find easiest is I say rename columns in here and I want to rename the ID column and I'm going to call this the deletion ID that's the ID of the record that I want to delete so now I can put this down here and now it's clear what's going on okay so anytime the field name matches something which is in the inner collection then it's going to use the one which is in the inner collection so yeah disambiguation right now let's see if that is going to work so remember we are going to be getting rid of this one that's its date and the quantity on that one was five I've got several with five but look we've got four orders at the moment so when we look in our order detail there's four orders when we hit the sync button then one of those should get removed so that we only have three orders so hit the sync button boom let's have a little look in order detail and there we are we're down to three orders instead cool so let's jump back in to screen three again so now we're in a situation where we can add records when we're disconnected offline and we can delete records let's now do modifying a record so it's going to be very very similar concept but we'll do it quickly so I'm just going to delete out the label and replace it with a text input box instead so that we can change it I'm going to set the default value to do it here the default value to be this item dot quantity and we are set the format to be a number and then I'm going to say when I change this then patched the underlying record now there's a handy thing which is delay output we're going to set that to true if you don't do delay output then as you're typing it will be writing record you don't want that you want to say okay when I've finished typing you know and moved away from that text box then you can go ahead and run your own change event so in the on change I'm going to do a patch I'm fetching to call my order detail the record I want to modify is this item I want to patch my quantity and I want that to be OH I haven't named my box let's name the box quickly txt quantity I've already got a txt quantity alright then txt also quantity and we've got to say dot txt and we've got to remember to wrap it in a value statement to make sure that we're only passing numeric values through just get my curly brace in there okay so that's fine that would modify the quantity which is great but I need something so that when I'm doing my synchronization that that change will get pushed to SharePoint at the moment that change would just be lost because we are only dealing with things where the ID is blank or where the modified date is first of jan 3 2015 4 pache there again there we go right yeah so now I am marking the record oh and remember this record might not actually have gone to share point yet so we need to deal with that situation as well so let's do our little check first of all and say if is blank this item dot ID and we don't need to worry about telling share point to get rid of it we can just do a straightforward patch with the quantity okay rid of that get braces in the places again there we are in the format text so it's easier to read so if this is a record which is on the device only it's not gone to the server yet you can just go ahead and modify the quantity otherwise then you're going to want to modify the quantity and the modified date so that we know we've got to do something special with this record when we do the synchronization there's other ways of writing this statement we could have the if statement inside the modified it instead I'll tell you what I'll them I'll do that let me just drop this into notepad in case I screw things up but the other way of writing this one would be do the patch and for the modified if is blank this item dot ID then we can set the modified value to blank or leave that blank otherwise then we can set the date value so up to you which is meter now certainly a little bit less formula here so that's probably the preferable version but they'll both do the same thing okay so let's change the number of batsuits we are ordering we decide that we only need one Batsuit instead of two so i'm going to change that to one I'm gonna check my collection from my order detail so this will be the line this one here with the quantity of one so that should have a modified date wherever it is there we are modifydate of the first of gann 3001 so this was an existing record so we need to change the record on the server so we know what we need to change the quantity - and we know which record we need to change so let's jump back to our synchronization screen so here we can see we've got our for all which is going to go through all of the records which have been marked for deletion because they've got a modified date of first Jan 3000 and we go through and delete them we're now going to do pretty much exactly the same thing but for the records which have a modified date of three thousand and one but we're not going to be deleting them we're going to be updating them so I'll call that update ID and we don't want to remove here what we want here is a patch and so we're going to be patching to order detail the record that we want to be patching is where the ID so we've got to do the lookup haven't we look up where the Ord detail ID equals the update ID so that's going to identify the record that we want to change and then the thing that we want to change about it is we want to change the quantity - oh and I think we're gonna have to uh note we're good we can just say quantity here because it can only come from the one place so what's not happy here patch statement has some invalid ah okay there we are quantity I left a t out and that's got a problem now we probably need another bracket to close off that for all statement again I recommend using the format text so that you can see where all of your brackets of matching up okay so let's go back to our order detail so at the moment we've still got two batsuits ordered because we haven't yet synchronized our information so in here in our device here we are we've changed our back super order to only one Batsuit but that's just local because we haven't pressed the sync button yet so back to here press the sync button and we should find if I go back to my order detail and refresh there we are that our quantity change from two back down to one again so we've now got an app where we can load our data in from the server we can add records and they get pushed back to the server we can edit existing records and we can delete existing records and when we think those changes get pushed back to the server as well that's all looking pretty good so this is all working in what we might call its connected mode can be offline the problem we've got is if we are truly offline and we're working away on a device you know we're out in the field with our mobile and we've taken a record we can't press a sync button because we're offline what happens when we close the app what we would lose all of our data all of our changes so what we need to do next is we need to persist the changes to our device and I keep saying device here because you can only persist your data in the power apps player so you can't do it in a web browser so that's one thing to remember and the other thing to keep in mind is at the moment the windows power apps player is broken and it won't let you start apps when you're offline it's a known bug it's been a known bug unfortunately for quite a number of months now but it is something which has been acknowledged and it is supposed to be being worked on and fixed but for the moment if you want to be able to run your apps offline and store data on the device until you're back in connectivity then you're gonna have to use iOS or Android ok so what we're going to do next then is we are going to say whenever we pick data up or whenever we change data we're going to save that data locally on the device so that if Power Act closes down and then starts up again and we're not able to get an internet connection then we can just pull the information directly off the device itself so let's show you how are we going to do that and I've got a little bit of code here which I'll crop in the comment but is the code that I tend to use I didn't write it myself I got it off the power-ups community forum this is a bit of code which is going to tell you whether the save data function is supported on the device that you're using or not so we're going to run this once to populate a global variable that tells us whether we can save data or not otherwise every time we try to save data if we're working in a web for hours when we try to save data we're going to get errors coming up so this way you've only got to do the test once ok so this is going to attempt to save the data and bring it back again and if it works then save data is supported if it doesn't work and save data so I'm going to use the save data command so save data is going to take one of my collections or any collection and it's going to save it locally onto the device using device storage now you used to be limited to only 20 megabytes of data per app for offline storage but a recent update has taken that limit off so you don't have to worry too much now about how much data you're storing it dependent on how much data you've got available on the device you're using rather than uh limiting power racks so what I'm doing here is I'm saying save the data for called my customer into a a file if you like on my device and I'm going to call that SD for save date of my customer now you'll see that these if you want to call them file names just in double quotes so you've got to be really careful not to get mismatches in the names so come up with a naming convention that works for you stick with it possibly even you know put all the names into a text editor and then copy and paste them so you don't get any problems because they are swine to try to figure out later so that's saving my customer data I'm next going to save product and then gonna save my order header and fine we're going to save my order detail okay so now when we're running the load data from server command we load all the data in you know two to memory and also we're saving it all out as files so that if the next time we start up we're offline we can just pull the data from here instead so at this point you're going to want a routine in the on start of your app and what it can do is it can check to see whether you are online or not so let me grab my a my online code so here I'm creating a variable called var online mode and it's checking to see whether we have a connection either a standard connection or a metered connection and then we can say if online mode and bar is save data supported so sorry what I want to do is say if our online mode is false so if we're not online but save data is supported then what we want to do is we want to use the load data command we're going to load data into call my customer from SB my customer and I guess what we could do here is we could actually just pull all these into here overwrite that one just change all the save data and load data there we go so when the app starts up if you're not online but saving data is supported on the device you're on then go check to see and load in data that you may already have on your device and what we're going to need to do as well is whoops I should have wrapped this one in an if as well if bar is save data support itself save data is supported then go ahead and do all of those and what would then have to do is we have to say look everywhere where we're saving data where we're doing our patches where we're making modifications to our local data then if save data is supported and go ahead and make sure that my data is also saved to the device so that if I turned the device off or closed power apps before I have a chance to sync then all that data is still going to be there so and the plus button there on the on select do the same thing at this time it's my order detail and jump onto this screen and there are two places on this screen where we're making changes we're doing it on the delete button and we're doing it on the on change event of the textbox okay so they were all the changes where we all the places where we're making changes so every time we make any sort of change to the data when we're loading the data in or when we're modifying any of the data then we're going to save it to the device so that if the device gets switched off or perhaps gets shut down whatever the next time they start power apps up again on that device it's going to pull all that data back in for you so you're not going to have lost anything right one other little thing that I like to do is to show the user how much stuff they've got just locally on their device that hasn't yet been synchronized so let me show you how I do that so I'll put it on this first screen again and I'm just going to pop a gallery on over here on the right-hand side and we're going to populate this gallery with a table that we generate ourselves which is going to tell us it's going to tell how many records we've got that appending being written to SharePoint how many new records how many modified records and how many deleted records so the user can say hang on a minute I've got data here which hasn't yet gone to the server and how much data so right now the thing called label and we're gonna say this one is for the order header so how many order headers have regenerated so how many new order headers have we got it is going to be count rows of call my order header Oprah filtered where is blank ID how many modified have we got well we didn't allow modification of the headers not quite right there what have I done those dr. filter but not closed off the count rows there we are I have modified and then how many deleted well as I say for this one it's going to be zero but then on the next row we're going to deal with the order details so here we are label order details call my Ord detail and let's separate these out a little bit so it's a bit easier to work so the number of modified records is going to be we're going to count our rows again but this time for modified we're going to count our rows where our modified column equals date value 110 three thousand and one for the modified ones data value eight value there we go and then for the ones that are deleted it's going to be where it is 3000 okay so that should build up a little table for me and now in here I can simply add a label come on clear some of these things off the screen I don't need that anymore okay there we are order header order detail so let's get these in so that's going to be this item dot new and copy paste that this item but modified and this and this one will be and obviously I'd put some I'd put labels up at the top so that we could see which thing was rich so yeah that's to our load data from server okay so we've got nothing on the device which is not on the server I'm going to start putting some more orders in all right thing I said about how very annoyingly when we're in design mode combo boxes if you've messed around at all with their items property I mean even though you haven't been into the formula and changed it what you've done is you've changed something about the collection we refresh the collection we've cleared it out and repopulated it and so it says all I know what I'm gonna do I'm gonna screw up all of your display in search fields for you so do watch out for that so let's order some items here and then what we'll do is we'll jump back to our screw you one and we can see look I've got one new order header and three new order details okay I'm going to do a sync on that information and then I'm gonna go into screen three and I'm going to delete an item and I'm going to change the quantity of an item and then again when we jump back to screen1 where you can see we've deleted one and we've modified one so yeah helps if you've got the labels on but remember it was new modified and deleted cool got a little red dot appearing on here we haven't got any little red dot on our app checker because that is a runtime error and the runtime error is that there was a problem saving your data remember I'm working in a web browser and we said that when you try to save data try and use the save data command in a web browser you get an error back because save data is not supported when you're in a web browser so don't worry about that little red button there it is annoying that save data isn't supported because what it means is when you're developing make sure that everything is working properly you've got to work on your app in the studio on the web browser then you've got to publish it then you've got to pick up your mobile device you've got to make sure that you've loaded the latest version of the power app on there and then you've got to do all your testing on there and then if there's a problem you've got to come back to the WebSphere videos make your changes and push them backwards and forwards it is a painful process so it did take me a while to get all this working the way that I wanted but I have now so I have shared it with you yeah okay so as I say that is working disconnected or working offline offline has obvious benefits you can still carry on working when you've got people out in the field who may not have an internet connection but you may want to consider the you know also using this method just to make it faster when you're working on your record so it's very quick and easy for me to add things here you know if I had this set up so that every time I was hitting the plus button it was writing values back to SharePoint then it wouldn't be as quick as that so as soon as I'm hitting the plus the items are being added to my list there's no delay there I'm not having to wait for any activity lights to work their way slowly across the screen everything is bang instant really really quick and with the added advantage that if you are going to be working offline then you've just got to make sure that you put in these save data commands everywhere where you're making a modification and you make sure that you've got your load data commands on the on start property of the app itself okay well thank you very much it's been a fairly marathon session thank you very much if you stayed through all the way to the end please give me a like please drop me a comment that encourages me to keep making these videos to know that people out there are actually getting some kind of benefit from them okay thank you very much indeed and happy power wrapping

Channel: Paul O'Flaherty

Views: 6,963

Rating: 5 out of 5

Keywords: PowerApps, Offline, Disconnected, Sync, Synchronize, Synchronise, SaveData, LoadData

Id: ZAzZCh6hfbw

Length: 99min 47sec (5987 seconds)

Published: Thu Feb 21 2019