Designing data model - Part 1

8. 41. Adding Rollup Columns

Now, in this video, we're going to have a look at the other thing we can do, which is a rollup column. A roll up is a summary of a series of rows. So, for example, we've got this expense table which has expenses for a particular account. Or maybe in the account table we want to know the total number of expenses. We can add that as a column.

So for total expenses, make sure you get the data type right. It must refer to what the expenses are in this case, which is a currency. You'll see that if I get the data type wrong, I won't be able to do this roll up column. So let's now add our roll up. So we have to save the table. After a few seconds, it disappears. So it says that it's a source entity. So source the table while it's the account. There are some advanced things that you can do with hierarchies. For instance, maybe a manager wants to know how many employees he's got. That would be a hierarchy. So what's the related table? The related table in this case is the expenses table. And then what's the aggregation? What is the summary? So we can use sum, min, max average, and you may also be able to use count as well. For instance, count the number of rows.

But here I'm going to sum the expense value and you can see that that is the only column I can use. That's the only one which is a currency. Now, let's just have a look at the bottom bit because this is quite important. So this mass calculation job will be scheduled to run approximately 12 hours from the time that the role field is saved. In other words, it doesn't happen immediately. So if you're finding that you need something where you've got an immediate output, well, I'm afraid the roll up field is not your answer. So this is something that can be delayed. Now, once a mass calculation roll up field is done, then you can have a calculated roll up column job. And what that does is it does incremental small calculations of rolled up columns in existing rows for a specified table. And that can be done every so often. Again, it's not automatically done instantaneously. It's automatically done, but it's just not instantaneous. So let's check that save and close. And that is our total expenses for the day. And you'll notice that we have some additional columns added. So when was it last updated? For instance, what's the state? So the state could be the calculated value has not yet been calculated or it's been calculated as per this time or it failed because it overflowed on internal error or some other error.

Now, if I try to do this again, I'll call this total expenses too, and I'll call this a decimal number. You think currency in decimal numbers. This should be reasonable, but you'll find that when I try to do this roll up, I simply can't do it. So I chose my related table. So my last table is an expenses table, and then I choose my aggregation. So my aggregation is the sum of and it won't let me do any related columns because there are no related columns which are decimals. You will notice that it does allow me to use my account. Just one last thing I could add in a filter. So, for example, where a particular field column contains or doesn't contain data, for example, or example, if the expense of a value is greater than $500 or is greater than another column, then I want to only include those values. Now as I said, it doesn't automatically recalculate immediately. It automatically recalculates but at a time chosen by your administrator, let's say. So we can have a look at it by going intosettings, advanced settings, and then in the system section we have got system jobs, and if we change the view from allsystem jobs to all rollup field calculation jobs, you'll see that there are two jobs for this new calculation. Firstly, one for the column itself.

This is calculated 12 hours after the update, which means that it is calculated in nonoperational hours of the environment. So if I'm updating at 10:00 in the morning, then another 08:00 in the evening is probably a good time to calculate. Nobody's going to be using it, but there's going to be more processing power available. Calculate rollup fields for the entire table, which performs incremental calculations on all rollup columns in a specific table. And if I click on any of these, you can see that we've got various actions that we can do. So roll up columns lastly. They are there to have a look at one table in relation to another table that can be used for hierarchies in more advanced ways. So here in the account I'm asking the computer, what are the total expenses that were in the expenses table for each individual account? And that is a roll-up column.

9. Practice Activity Number 3 - The Solution

So how did you get on with this practise activity? So I asked you to create a new table called Device. So you notice none of these currents have got anything in the plural. So it's device as opposed to devices. So I go into the powerups portal, data tables, new tables. So I'll type in Device. So the plural name is devices. Now I said it should contain several columns, the first one of which is the primary name column. That's what I have to say right now. So I'm going to call it the ID device. You can see the logical name. The internal name is "ID device" without a space.

So let's click create on that. And now we'll add a new column. So this column is going to be Device Name. It's going to be text. And I said this should be required, so I need to be able to put in the device name. So similarly, I'm going to put in devicetype and again it's going to be required. So this is going to be a laptop, desktop, iPad, Android device, that sort of thing, serial number. So you can see the default is for it to be in properties in a way to have a capital letter at the start of each word. So I'd advise you to do something similar. So, device status, purchase date So this is going to be a date. So only the date. Now notice that all of these are now no longer required. They're optional. So it could be that we don't know when something was purchased, the original purchase value, and that this is going to be currency down there. Notice all of these are appearing in bold. They haven't been saved. Just because I'm entering them doesn't mean that they are now included in the table for us to use. current value This is a currency and device image, and this is going to be an image. Done.

So now you can see all of these that we have added. We can change the view so we can just see all of the custom ones if we wish to, but let's leave it to default. So I'm going to click on "Save Table." So here you can see the saving table. So that just takes a few seconds. Now I want to add a relationship to the user table. A single user may have more than one device, but a single device will only be associated with one user. Well, I'm currently at the device table. So how many users will have how many devices? Many devices for one user. So put that table in first. Many devices to one user, many to one. So I'm going to go down to the user and we can do things like change the type of behaviour if we want to, but I'm not going to do that here. The defaults are fine for me. So click "done." And that creates this new relationship and also creates a lookup data type as well. So next I want to calculate a column called Depreciation. So let's just type in that word. It's going to be a currency because it's going to be the difference between two currency columns and therefore must be in that data format. So I'll click on "Add Calculation." It's going to ask me to save the table. So. Yes, I am.

Then it's going to display a new form. So after about 20 seconds, it appears. So if something, then set this value. Now I don't have any ifs. I'm just saying give me the difference between the original purchase value and the current value. I'm not saying if something happens, but if there was, then I could put it in here. So I'm just adding an action. I can use the drop down so I can find my particular column. So I can just start typing. So I'll just start typing, say a phrase, and just pause. So there's your original purchase price minus the current value. Okay, save and close. And when that form disappears, click on Done. And there, near the top here, we have depreciation. All of these additional columns are also being created likebase and other things to do with currency.

That's absolutely fine. Then I need a rollup column in the user table. So I'll click on tables, go to users, Notice we didn't need to save the table. It does that automatically when I create a calculated or rolled up column. So I want to calculate the total original purchase value. So let's click on "add column." So again, this is going to be currency. So the name, original, purchase value, and total are put in the roll up. So again, it saves the table. So it says, okay, what is your related table? Well, my related table is going to be the devices table. So let's just scroll down to that. And then it is device brackets, user. That's the relationship we want. And what I want in terms of the aggregate is the sum of the original purchase price. So it's only giving me thecurrency related table fields there. So I'll check that and save and close. And now it's processing. So in this section, what we've looked at is designing the data model. We've had a look at the high level and then data models, designing them and how we've implemented them into the database. And hopefully you're getting really confident with this after the next quiz. We're going to now add the expenses table that we've added previously into our accounts canvas app. Please join me there.

Adding Expenses to our app

1. 36. Adding Expenses screens into our Accounts apps

In this video We'll incorporate the Expenses screens into our existing Accounts app. So just as a reminder, we have got the account screen and we can view details of the screen and we can edit it. We can make changes and that sort of thing. So this is exactly the same thing that we need for the expenses, but we're going to have this in the same app. So let's go to our app. So make sure we close it first. So there is our Accounts app, so I'm going to edit it. So I've gone to PowerApps Apps, I've clicked on the dot and gone to Edit. There's also an edit icon near the top as well. Now, we're not going to do anything new in this particular video, so if you're comfortable with the thing to be done, you might want to skip to the next video. So we've got a browser screen, an account screen, and an edit screen. So I'm going to rename these for counts. So I'll just put the word accounts right at the beginning of each of these. And then we'll need the same screens but for the expenses.

So I can just duplicate these screens and then change what I need. So I'm going to duplicate this screen and it's going to be called the Expenses Browse Screen and so forth. Now, the advantage of this sort of thing is that I then have these icons all in place. However, they're all pointing to the wrong place. I want this to point to the expenses and so forth. So what I can do is change the data source. So at the moment it's on Accounts, so I'll change that to Expenses, except I need to add in the data first of all. So let's do that.

Let's go into our data section. We currently have the accounts, so I'm going to add in my expenses there. So now when I go back to here and say, "Okay, what is this gallery going to be based on?" It's going to be based on expenses. So let's make sure that the name is correct and let's just get rid of the sort to begin with. We just have expenses, and you can see there are problems with this sort of thing. Yes, we get the same functionality, but I don't particularly like doing that unless I'm fairly secure with what I'm doing in terms of making sure all of the changes are all of the icons are changed. But we saw in the practise activity that it was fairly quick to be able to do this from blank. So I'm going to add in an expenses browse screen. So all I have to do is insert it in a gallery and I need to be able to link it to data so that data expenses drag that down and then I need to add an icon. These sorts of things Because it's a bitty, I'm going to copy them and also click on the first one.

Shifting while clicking on others. Copy them and then just paste them. So there is our expense browse screen. There we go to the top. What I'm going to do is click on the text over here. It's just actually just taking a few seconds to update. Now there's a problem with that. So what I'm going to do is just ignore that for the time being and I'm going to get a new screen. So this is going to be the detail screen. So it's taken quite a long time. So thankfully, I believe that there was a problem and it's now being resolved. It may be that if you've got some internet problems, you may have some delays. Perhaps you might just want to reboot the computer or just walk away from it. So I'm going to add in a display form. So this is connected to the expense data. There we go. And it's putting some things that I think I might want to know. To be honest, I don't particularly want those to be honest. But I will add in the date of expense, maybe the date to review as well as the approval status. These are just the description, the expense value, and maybe the expense receipt as well. So let's just click "add" onto those and see what happens. There we go. And then finally, what we need, if I go back to the tree view, is another screen.

So this is the expense edit screen. And so we have an edit form. Again, we connect to this data, but we have to do it over here on the right hand side. And I'll put in things that I want to add in, such as the date of the expense. I don't necessarily need to put in the date because that's automatically added, but let's just add it anyway and the expense of value and things like that. description So there we go. Now we just need a few icons which allow us to go from one to the other. So let's start with the expenses browse screen. If I am adding something, then I want to change the form. I want a new form and to navigate the budget. Again, you can see it's going to the edit form, and that's fine when the edit form is for accounts, when that's the only thing you've got. But when we've got accounts and we've got expenses, then again we need to be very careful about how we refer to things. So I'm going to call this the account gallery. So these sorts of things are useful to set up right at the beginning, so that when we add new things, we're not necessarily getting confused as to where we are. So there's the expense gallery. So this one down here is going to be the expense edit form. So let's go now back to our browsing screen. So here it's going to be accounted for, it form and the account edit screen. We can see that very clearly now.

So all we need to do is change that to the expense edit form and the expense edit screen and make sure you are using the singular or plural each time. Here I've got the expenses browser, so I'm going to change that to the expenses browser screen. So that one works. Now, the refresh icon needs to go into the refresh of the expense table, which is I believe called expenses. And then click on this. So that goes to OnSelect for the parent. The OnSelect for the parent is currently false. I want to take that to the details screen so we can go back to this gallery and the unselectand we can have a look at the code. It's just a simple navigation. So, if we go back into here and say navigate to the expense, and I believe I put in the plural expenses browse screen. So let's change all of these fromplurals to single, including the account. You have the option of selecting single or plural. Just make it consistent throughout, otherwise you will start getting into trouble as you go. Okay, was this a single or was this a plural? So here is our detail screen. So what I'm going to do is go back to this detail screen. I'm going to again take all of the things that I've got, copy them, and go back here to paste them much quicker. Thank goodness. And let's go back to the text here that says "Expenses Details. That's fine. This one again, we just change this to the expenses table and the expense gallery here. We navigate to the expense edit screen.

Here. Again, we need the checkmark and the X mark. So again, I'm going to copy all these. So you see, another way of highlighting the lot. Just draw an area over it. So go back to this text and you'll see we've got this. Are we editing or are we not? We're just new, in other words. And then we submit the form or we reset the form and go back. And if we submit the form, then what happens? So go to the form itself, not the screen, and you'll see the success and failure rates. So on success, we just go back. So we add that in. One thing I want to add to this, by the way, is the account. So we've got count up there and you can see when it comes up that it is a drop down. So it's linked to the other table. So let's see if we got everything right. So I've not done anything new in this. Don't you worry. What I've done is just add in the expenses for this app news coming up, don't you worry. So can I add an expense? Yeah, so I'll add an expense for this particular day. The expense value is this much description.

Hello, And the account. You can see it's listing in the accounts that we've got there in the previous table. Submit. So there is our first item. So I can view it, but it says there is no item to display. So let's go into our screen, or more accurately,I want to go into the form viewer. And you can see the item that is selected is the account selected. So I need to do something similar to this gallery or the form. So let's click on the form and change that to the Expense Gallery belt selected. So let's go back. Let's test it again. Testing is very important. So you can see if there are things that you've missed. So there we go. So I can edit it again. I require what we previously had. So let's go back to the edit form so you can see the item again. is expensive. gallery dot chosen At least that's the one that we need. So let's go back in here. So I go into the expense gallery and select dot. So now let's have a look at it. So if I start from the expense browse screen, I can go through to this, I can edit that, I can make a change, I can bin it, I can go back, I can refresh, I can add, I can do what I want, right? So in this video, what we've done is add three of the same screens, a brown screen, a detail screen, and an edit screen, to the app. However, what we've not done is connect the two. And we will look at how we can start to do that in the next video.

2. Practice Activity Number 4 - The Solution

So how did you do? I asked you to create another two-screen for the device table this time. Now I did say it's probably going to be easier if you manually add some data, so you've got something that you can work with. So I'm going to do that by going to my PowerAppsplatform data table labels, device data, and adding a record. So you can see, it's just giving me a form. I'll just type in an ID number, save, and refresh the data. So there is something.

Now I could have a look at all the columns and I could be editing further, but let's just stick with that. So let's add in a new screen. So this is the new blank screen and we're going to have a gallery. So it's going to be connected to a new source of data. So this is going to be the Device Table.Now notice that it's calling it "devices" even though it is actually called "device." So it's giving me the plural. So let's move this. So it covers half the screen, giving a bit of room at the top. We'll probably want something like a title subtitle body. But I'm just going to leave it roughly at that. That's because I don't have any real data that I've entered using the system. This is just really dumb. I just wanted to be able to see something.

Next, I'm going to insert a display form. So move that to the other side. And again, this is going to be connected to the Devices table or device. And again, I'm going to set it up. I wish I want to have the ID device I don't want created on. So I'm going to remove that and I'm going to have device name, go to device type, and device status. So notice that even though I put in device name, device type, and device status, it looked like it was happening in alphabetical order, so device status would come second. It actually adds them in the same way that I have added them into the add field. So you could choose to have a horizontal layout if you so wish. We're missing any data. That's because we need to go into the item and the item should say the name of the gallery, which is currently Gallery Two selected. So there we are. So now we'll be able to better see whether a horizontal or vertical layout would work.

Now, just like the other app, the one we're using for the main videos, it's now difficult to see whether I'm in the user or in the device. So I'm going to rename this and put user at the front and do the same here and here. I'm going to now call this the Device Detail Screen and we're going to change these galleries as well. So, user gallery and device gallery. And we've got edit form here. I'm going to change that to a user edit form. Right. So we've got this. So we need yet another screen. So insert a new screen. So this is going to be a display edit form, not a display form. So, once again, device source. So, as this is going to be an edit screen, we're going to have an edit form. So let's rename these right at the beginning. So this is our edit screen and our DeviceEdit form that's connected to the device data. Again, put it horizontal and we're going to have fields. So let's get rid of the artificial field. I don't want that. And the columns. This time it's basically all of them. So we've got the ID device, we've got the device name, device type, and serial number. Now I'm putting more into the edit because this is where we actually want more than just a summary screen. Because this is where we're actually going to add all of this data. Unlike the browse and detail screens, this is just a summary. We don't need to display absolutely everything. Do be careful where you click. If that's in, click outside of this box. You might be cancelling what you are doing. So nearly there.

That's a lot. So I'm not adding the device image yet. So wait a few seconds, here's everything. So you can see that three columns really doesn't work. Let's put it into one column and we can see yes, much better. Now you can see the computer has added the time as well as the date of purchase. I don't want that. So I can just set the visibility to NUM and you'll notice that I don't have to unlock it to do that. Regardless of the fact that they are inserted into the card, I can just set individual elements here (as you can see, visible or not). If I wanted to select them again, then I could go to the left hand side and select them there in the tree view. Now we need some icons linking dumbled. Before we do that, we have to put in the item number. So we need an item that this is going to show. So, the device gallery has been chosen. So let's now link these two. So I'm going to insert an icon, an edit icon here. So let's move that to the top. So these can be lowered slightly and joined at the same top. So what does this do? We want to go to the DeviceEdit screen, so that is to navigate to the Device Edit screen.

And if we want to put in a transition, we can do so there as well. Additionally, we want another icon up here. So this is going to be for a new item. So we need the new form. And so this is a new form for the Device Edit Form. So you can see the best thing, not actually to type, but just to see what the drop down is and then press one or two semicolons depending on your locale. And then we navigate to the Device Edit screen. And while we're at it, let's just tidy this up a bit. Let's put those rectangles that we've had previously, and maybe we'll give this a different colour scheme to show it in a different place. So go into the colour and maybe make this that color. And let's just copy and paste this across as well. There we go. And add some titles. So we're going to text label. So I'm going to call this device the current device. So let's copy that and paste it into the editor screen. Have this at the top. Let's change this.

So it is fractionally down. So move this across and I'll just call this Device Details. I won't have anything more than that for this particular practise activity. So, device details, right. So we've got icons now linking this screen to this one. So next, we need icons that submit or not submit. So let's have the check mark over here. So, in the OnSelect, we'll have the Device Edit form's submitform in the X. So again, go to the icons. So cancel. We will have the reset form for the device edit screen and then we'll go back. Then there's the form. So yeah, I'm in the right place for success. Then I want to go back. So let's test this. So I'm here on this item. I want to edit the item. That's fine. I can submit the item. However, as you can see, device name and device type are required. So my laptop is of the device variety. So I can enter into all of the new things that I want to enter into. So I'll put the original purchase price and current value as well as the purchase date. Check that. So here we can see things are being updated. Now, just as an aside, let's just add in another field. Let's add in the depreciation field that we put in the previous practise activity. And here we can see depreciation is working correctly. Depreciation here is 250. It's a non-entitable field. This just shows that what we did in the previous practise activity works well. I hope you enjoy this practise activity. And now we have got in the app screens from two different tables. However, they're not linked together as well as they could be. And in the next few videos, we will improve that link.

Prepared by Top Experts, the top IT Trainers ensure that when it comes to your IT exam prep and you can count on ExamSnap Microsoft Power Platform App Maker certification video training course that goes in line with the corresponding Microsoft PL-100 exam dumps, study guide, and practice test questions & answers.