Designing data model - Part 1

4. 41. Adding columns to our Dataverse table

In this video, we're going to add a few more columns. I'm going to start with the description column. So I'll click on "add column." So you can see I've got to enter a display name. So when you're describing, make sure you don't use any that you've already got. So, for instance, don't have a column called "createon." It just causes a bit of confusion. You can also put it in the description. So in this case, it's the description of the expense. Here we go. And we have then got something very important, the data type. So we have got text. So that is a single line of text. So if I use text, we go to advanced options. Right at the bottom, we can say I want the text to have a maximum number of characters, a max length. Alternatively, you can have a text area. So again, we can have a maximum length difference between text and text area. The text area is for a multiline text box. So, up to 4000 characters. If you want more than 4,000 characters, then use multiline text. So you can see, we've got three different types of text. single line text area, basically for paragraphand multi-line text that will take you up to a million characters, technically a million 48,576. So we have got those as possibilities. We've also got email, so that's a validated email address. So you won't be able to just put anything in. You'll have to send an email. Same with the URL. I also validated the website and phone number. So that's a validated phone number that can be called using Skype. You've also got a ticker symbol if you want to put in a stock market ticker symbol. So let's keep this as text. So you can say optional, recommended, or required. I'm going to say "recommended." So I'm not going to force you to put it in the description. It's just something that I want to do. Now, Note particularly that we've got the option here to enable calm security. We'll have a look at that a fair bit later when we have a look at security. So I'm going to say done and our description column will be added here. You can see in bold, it's a custom column, and you can see I put it as recommended. So let's also add the status. status, but we've already got status here, so make sure that the internal name is not going to be the same. Now let's just have a look at this status column. Maybe you will do everything that I want, and the answer is no, because you can see we have a data type of a choice. What is a choice? Well, previously it was called an option set, and what choice allows you to do is to say, "Okay, these are my particular items, active or inactive," and I want you to choose one of them. So really, this is for the internal workings of the table. I don't want it to have that, but I actually want to use choice for my status, so I'm going to call it "approval status," let's say. So we're going to call it "choice." The difference between choices and choices is that you can only choose one, whereas with choices you can choose one or more. It used to be called multiselect choice. It's now called choice option select and choices multiselect choice. You may see those on the internet, but just know that it's now been changed to choice and choice. So make your selection now. Well, what am I going to choose from? I could say yes, no, or I could say aparticular day, so that will give me the choice of seven days, but I'm going to say new choice and this new choice, well I'm going to say approval not needed, pending approved and rejected, so click save. So those are the choices for this particular approval. You'll notice there is a separate section for choices. Here we can define choices outside of creating columns and then say, "Well, my choices are going to be in this case approval states choice default value." I'm going to say none of them. To be honest, I could perhaps say pending, but I'm just going to say no default value. I'm going to leave it as optional for now, so let's say done. So next I'm going to put in the value, so expensevalue I'm going to put in. Now this is going to be numeric, but what sort of number could it be? A whole number is not great. If I want to charge$50 and $24, a whole number is not great. So then I have a few more choices down here, so I've got currency. So here is when I can choose an accuracy level. For instance, do I want to have the precision based on the currency? So if I was using yen, then I'd be going to zero decimal places. If I'm using dollars, then I'll probably be going to two decimal places, so I could also have an organisation standard as well. I could have a decimal number. This allows me up to ten decimal places, and you can choose how many decimal places you're going to use, or you can choose a floating point number. Use of floating point numbers should now be avoided at all costs. Yes, you can go up to five decimal places. However, it's an approximate figure, so if I put in twelve dot 34567, the value that gets stolen might not be twelve dot 34567, it might be twelve, something like that, so why would you ever use a floating point number? Well, it can improve performance based on the decimal number. It is better performance. You can also say minimum and maximum values for these, so for this I'm going to use a currency, so there we go, and again I can put in a minimum value or a maximum value. I'm going to leave those as is. At the moment, we'll be putting some things in power automation that will prevent me from saying, "Okay, if I put in $50,000, maybe that needs to be reviewed by somebody special." So I'm just going to leave those as is. But you should work out what the minimum and maximum value sensibly should be. But bear in mind that these then become limits. Nobody can enter a value greater or less than those minimum maximum values. So there's our expense value. So the next column I'm going to add is the date of expense. So again, field type and data type are quite important. So we have got, for instance, a duration. So this is to the nearest minute. So if you've got an event that is going on for two minutes, two hours, two days, or two two-hours, two days, that can be inserted into the duration. We've also got date and time or date only. So is time really that important for an expense? Probably not. So, date only is probably fine. Now, when it comes to date and time, then the behavior,is it going to be based on where the user is or is it going to be time zone independent? So I'm not going to worry about any of those. I'm just going to say date only. So let's change the behaviour to date only. So click "done." And I'm going to add another column. I'm going to call this the receipt. So I want people to be able to capture an image of the receipt if they still wish to do so. This is going to be an image data type. Now, the literature says that each table can only have one image column, but it's a bit hard because if I create this and then create anothercolumn, so we call this image two and sayimage, I'm able to create a second image. So maybe things have changed since that particular web page was written. Indeed, I found another one which says that there could be multiple image columns in a table, which certainly reflects what I've been able to do. There's also a primary image indicator, which is the main image for a particular table. So I'm going to say that this one is my primary image. And if you're wondering what sort of graphics you can use, it's the standard stuff. JPEG, JIFS, TIFs, BMPs, and PNGs. So let's just have a look at the other data types. Well, we have got a yes or no. So that's another version of choice. Yes and no used to be called two options. And you can see that it's not necessarily going to be yes or no. For instance, I can change it to "France" just like that. Or I want this, I don't want this. And you can see that you can put in the default value and whether it is required or not. So if it's in the on position, I want this. If it's in the off position, I don't want this. So, additionally, we've also got language. So this is from a list that has been enabled for the environment. We've also got a few other things that we won't actually need for this particular course. I just want to highlight one of those looks which the computer will automatically give me when I'm creating a relationship. So in this video we've had a look at how to add various columns. So we've had a look at how to add columns of text. So single line, bolted line, or a large number of lines are all possible, as are various validated versions such as email and URL, phone, and ticker symbol. We've had a look at how to create a whole number or decimal number up to ten decimal places or a floating number up to five decimal places, but an approximate figure, date and time, and date only, and then choices with yes and no. We've also had a look at how to create an image column as well. Now, just a couple of things before we finish this video. Firstly, you can see that we've got certain things in bold. These are pending changes; they haven't actually been saved. So you save it by clicking on Save table. It will take a few seconds. Discard doesn't mean get rid of the table. That would be to delete the table up here instead. What discard means is that I don't want the changes that I've just put in that you can largely see in bold. So you can see the little symbol next to the primary name column as well. Secondly, while it's not been saved, you can still edit quite a number of things. For example, you can have calculated or roll up, which we'll have a look at in a later video. You can change the data type and things like that. But as soon as I click Save Table,all of these options start being diminished. So let's click save table.So you can see now save in the table. It will take a few seconds and a few seconds later it's done. So everything is no longer in bold. There's no little symbol next to the ID and if I click on something now, the date of expense, you can see I can no longer change the date or the internal name. I can still change the display name. So I can call this datespace "Space Expense" if I want to. So approval, space status, that is all doable We now also note that we have got some new columns being created. So we've got a column here, expense value, brackets, base and there may be another two columns being created. So for instance, we might need to know, because this is a currency, what is the currency? And there needs to be a column for that. So don't be surprised if additional columns get automatically created. right? So now that has been done, you can also change what you're seeing, so if you just want to construct a custom column, you can do that and hide the managed ones. That's absolutely fine. I'll leave it at default. Now, that's been done. I'm going to click Save Table again and it's going to save all of these changes. So there we go, saved. If you want to rename the table, you can go to settings and there you can change the display name. You can't, as I said, change the internal name,but you can also do a few other things,including enabling attachment notes and files to be added to a single row, for instance. But there are a lot of settings that once created, you can no longer change, but there's still lots that you can do. So that is how we add columns to tables.

5. 41. Updating our Primary Name Column, and Keys

Now we've created our expense table. We now need to add the columns that we need. So if you remember, a few videos ago, we had a look at needing a description column, a status column, both with a text, a value column, which is a number and a date of expense, and also an aid column. So we've already got an ID column, but let's just have a look at it. So I'm going to click on it and you can see that this ID column is of data type text. And that's not actually very useful for me if I want the computer to autogenerate it instead. Let's have a look at the auto number instead. So if I click on this, then you can see that the computer is going to start generating,in this case, numbers as a string. So we have a seed value. That's the starting value that we're going to use for our auto number column. Then we have a minimum number of digits. So if I change this to six, for instance, then you can see that we start off not with the number 1000, but with a string. You can also have a prefix. So, for instance, something like ID 1000. Now, that's not the only thing. You can have it prefixed. You could have a date prefix. Assume you wanted the ID to include the date you are entering the information in. So that is perfectly doable. So here you can see Yeah, and then here we have the date and the month. So it's not in the usual format, but you can change the format if you want. Normally, I'd have your monthly. In this case, we're having a year, day, and month,but you can select a date format. So generally, I would go for an astring prefix number. You could have custom. This gets really complicated really quickly, but if you need a particular format, then you can do so. For instance, suppose I wanted the format to be a full sequential number followed by a six-sequential number with a hyphen in the middle. It's doable, but let's just stick to a standard string prefix number. So I'm going to give it time to grow. So I imagine I'm going to be adding more than 8999 rows to this example. So if I entered row 9001, for instance, we want this to say 10,000, but we still want it to be roughly in alphabetical order, if you sort of alphabetically as opposed to numerically. So let's just have some leading zeros at the beginning and right at the bottom, we've got the description. So the required name field you see is the auto-generated description. There are also a few advanced things which we don't really need to go into much at the moment. So here is our new ID. It is an auto number as opposed to text. So that means that the computer will take care of it. It is required. So we've got three different things that we can have. We can have optional, we can have recommended. So there may be some sort of indication saying "yeah, put it in and required" and then there'll be like a star and an asterisk sometimes when you say "here's the field, please put it in." We've had a look at the star before in the practise activity, so it's going to be required. That's usually good for a primary key, so I'm now going to say done. And there you go. And you can see a little icon next to it. It shows that I have edited it. So that is how we can adjust the primary name ID so that it becomes autogenerated. Now, in the previous video, I said that the primary name columns are on a single column. But suppose you wanted a compound key, so you wanted, for instance, to stop somebody from entering the same amount to the same person on the same day. So for instance, if I entered that I'd paid John $50 today and then I entered it again, you could think that's probably a duplication. I should have said $100. Well, you can do that, just not with the primary name column. Instead, you can do that with keys. So you can have different keys, which can be single columns or a compound of multiple columns. So I can say, okay, here is my IDkey and it is based on the column ID. So click "done." It's a bit duplicative, but you can do that. Now why would you want to create keys? Well, suppose we wanted everything in a particular column to be unique, like a primary key. So, for instance, suppose I wanted it so that the combination of person, amount, who to and date was unique. I couldn't add it a second time, while I could then add a key based on those four columns, and then it would ensure the uniqueness of those four columns when taken into combination. You can't insert a key if you've already got non-unique data. So this is really good, adding it right at the beginning. If you're adding it later, then you'll need to make sure that your column or columns are unique. Why? Apart from uniqueness, why would you want to create keys? Or keys also act as an index, so they can improve database performance, especially when you're linking to external databases. So in this video we've had a look at primary name columns, but we've also had a look at how we can add additional keys which can be based on single or multiple compound columns, and the keys will be used and required. So, in other words, it's got to be unique. There can't be another role wave at that particular value or combination of values and required. You will have to enter the information into the columns that we have said. You can't just leave it blank.

6. 42. Link entities by using Dataverse entity relationships

In the previous video we created a lot of columns, but there's one column that we haven't created and that is the account table relationship. Now, it is possible to create it as a local but it's much better to create it as a relationship and the computer then automatically adds them. It's quite easy to do. At the top, we've got columns,relationships, business rules, and so forth. A lot of these relate to what you get when you're creating a model as opposed to the canvas app which we're currently creating. So we'll be looking at things like viewsand forms, dashboards, and charts later. However, let's have a look at relationships. So you can see some default relationships are already included. So I'm going to create a new one. So we click on "Add relationship." Now it's going to ask me what relationship my current table has to the table that I'm linking to. So I have one account that can have many expenses, so it is a one-to-many relationship. However, I'm on the expense table. So it's not a one-to-many relationship but a many to one.It's going the other way. So there are many expenses on one account. So let's create that relationship. So I just need to go to Add Relationship and then ask many to one, many to many, or many to many. So many one-to-many and many-to-one relationships are also called parent-child relationships. So on the one hand, you have one account, and on the other hand, you could have zero, one, or many relationships. So just because it says many doesn't mean you automatically have to have two or more. Many really would avoid it if you can. This is when, for instance, many accounts could have many expenses and they share the same expenses. So, for instance, suppose I have a joint bank account with somebody and then a second joint bank account with the same person. So two people share two bank accounts. That's a money too many relationship. I would avoid it as much as possible. So because I am on the expense table,this is a many-to-one relationship. So I select my associated or related table. You'll see that word related a few times. Here is my column that it's going to create. So that's the display name and the internal logical name account. So relationships create a column. Now, there's not that much more to say about that. On the one hand, there needs to be a primary or unique key. But when you create a table, you have to create an aprimary key column anyway, so that's not much of a problem. Now, I do want to talk about these advanced options. So you get to change the relationship name as well, and you can put in the description that it's this type of behavior. So in other words, let's say I have an account called First National Bank and I have many expenses for First National Bank. So I've got expenses of 12345. Okay, so what happens if I delete First National Bank? Well, it could be that these two are joined together, but if I remove First National Bank,then it will remove the link between them. So I will then have expenses with no account associated with them. The account is now gone, but I've still got the expenses. Alternatively, I might want to say no. If an account has got expenses,you cannot delete the account. I'm going to restrict you from deleting one side. So I've got these five expenses. You've got to remove the five expenses first before deleting the account. And that can have a bit of security,a bit of, well, I can't accidentally delete an account that's active while we've got expenses. Now, I could say you've got a parental relationship. So if I delete First National Bank, that will delete all of the expenses. Alternatively, I could say we have a custom relationship. So what happens when I delete it? Well, I might be cascading all. So, in other words, I delete on one table from one side and it cascades like a waterfall. So imagine a waterfall effect going from the one to the many. It cascades downwards. Alternatively, I've got the remove link, so get rid of any reference to the account and the expense. They'll be orphaned. They won't actually have an account, but the expense will still be there. Or I could say, "Well, don't do that." Now, I can also change other things. Assign what happens when the table owner is changed. What happens to all of the other things? So does it cascade downwards? What happens when a table record is shared with another user or unshared? And what happens when you change the value in the first table, in the one table? So I can cascade all related table rows, or all those where the status says active, or all of those which are owned by the same user as the primary table. Or I can cascade none, do nothing, or I can remove the link, so remove the association for those particular roles so there is no account. Or I can restrict, I can prevent the one side from being deleted when related to table rows exist. So, to be honest, you don't need to worry about this too much. Just ask yourself a question. Okay, if I have an item on the one hand, if I have an account which has expenses on the menu side, what happens when I delete a role for the table which has expenses? Do you want the expenses to remain? Do I want it to say, "No, I don't want you to be able to delete the account because it's got expenses," or do I want it to cascade so you get the customer if you need it? So what I'm going to do in this case is to restrict. I'm going to say if there are expenses,then you need to keep the account. So click "done" and now it's done. I'll click Save table and then when we go back into the columns, you will see that there is a new column also called Account. Select a column by clicking it. There it is that has been created. It is a lookup data type. Now just to let you know, there is a second way of creating these relationships. This is a more modern interface that we are using. However, there is an older interface that you can also use and you can get to it by going to the gear settings, advanced settings. Now this opens up a new window. You may find that your web browser blocks pops up.If that's the case, then you'll have to allow pop-ups to be shown and then we'll click on this link here, this down arrow and go to customizations, customise the system, and that gets me into another pop-up and this is where we can have a look at basically everything that is here. So if I go into entities, so this uses theold wording, so entities are tables and go down to my expense, and here we have one to end, that's one to many end to 1 minute and endto-end relationships, that's many relationships, and then I can define a new one-to-many relationship if I wish. Why would I want to do this? Well, there are some additional options that you might want to use. For example, a hierarchical relationship, so that's a relationship where, for instance, an employee has a manager who has a manager and so forth. You might also be able to change, under certain circumstances, thisprefix and there are a few other things, but generally you won't need to be able to use this. You just need to know that it's there and you need to know how to get to the Solution Explorer, which is a name that is called and to do that you go to Settings. Advanced Settings Click on the drop down.Go to Customizations. Go to Customize the System. Expand entities and then go to the table that you're looking at. So let's go to the account table, and then you can have a look at relationships. You can also have a look at the columns. Columns are called fields in this version. So in this video we've had a look at how we can link two tables together. So we can do that using a relationship, and we can do that by going to relationships and relationships. Just make sure you get the right sort of relationship. Is it many to one or many to many? And that depends on which table you're at. If I was in the account table and I wanted to create a relationship with the expense table, then I would go to one to many because it's one account for many expenses, whereas if I was in the expense table, then it would be a one-to-one relationship. It just depends where you're starting from.

7. 41. Adding Calculated Columns

In this video, we're going to have a look at something that I skipped over when I was creating an acolumn, and that is this area calculated or rolled up. Now, this needs to be selected at the time of creating the column. So if I go back into one of my existing columns, maybe date of expense, you can see that that option no longer exists. Now, in this video, I'm going to concentrate on calculated columns. What is a calculated column? Well, it is a formula which is calculated based on a value in this particular table or in related tables. And now we've got the relationship added, we can have a look at that. So I'm going to add this column. So let's call it Date 28. So I'm looking for 28 days after the date of expense. You'll see that we won't actually be able to do this. Exactly. So, 28 days after the date of expense, So I'm going to call this a date type date. So first of all, I'm going to add a calculation. When I do this, the computer says you must save the current table first. I haven't actually made any changes, but it still wants me to save the table. Fair enough. Then a new window appears which says,OK, what is it you want? And this uses the older style of presentation. You can tell it's older because it's got the worddynamics in the URL, whereas the others have power apps. So let's create this calculated field. So I'm going to say I want it to be 28 days after the date of expense. So I'll ignore the condition for now. And in action, I'll click on action, and you'll see that we've got a new range of formulas. So it's disappeared because I resized the dialogue box. Let's just go back in again. So we have got lots of things relating to time. You've got to add, subtract, and diff in.So the difference between these is hours, days, weeks, months, and years. And that's basically it. There are three formulas for text as well. Additionally, we can also see all of the columns that we've inserted. So what I want is a date 28 days after the date of expense. So I'm going to say add days. I want to add 28 days to the date of expense. And there we can see it now using its internal name. If I click this mark, it will save the changes, and we can then go on to the next video. Ah, we have a problem. You can only use a user's local date and time of field.So what I'm going to do is change this to something else. I'm going to change this to the created date and time. You notice that the warning is still there. It's an error. Now I just click on the checkbox and save and close. So that dialogue box goes away and I click done and this will refresh this page. So what's happening? Well, if I go into Date of Time and into advanced options,you can see that the behaviour is date only if I want to be used in a calculated column and if you change that to user, I'm going to delete Date of Expense. In any case, there's no data in it, so let's delete the column. You can see the line go through. I'll save the table, then add a new column called "Date of Expense", which is date only. But this time I will call it "local" as opposed to "date only." So now if I go back to today (today 28), I can open up the calculation. So again, asking me to save a table, these things take lots of seconds if you're doing lots of these. Unfortunately, it's not all integrated into the new version of the presentation, Powerapps.com, and I can say that I will now change this to Date of Expense. There it is. Do the check mark and there we go. So, save and close. So this could be, for instance, when needed to review the expense, the latest date. So, date to review. But hang on, maybe I've got some accounts which are more important. I need to review those more urgently. So, for example, maybe an adventure work sample. I need to be able to review that within 14 days. No problem. Let's go back into the column. Just click on "Date" to review and open up the calculation. Save the table. And this is when we can go into the condition that I skipped over earlier. So, if the account does not refer to the current table or entities, it does refer to one, but it is an older version. But if an account name is equal to the value of AdventureWork Sample, then you can see we've got a few choices. First of all, we can choose what the table is. I can choose what the column is because it is a field. I can say equals, contains, begins with, ends with containsdata or the opposite, and I can put in a value or I can refer to another column. So if this column is the same as this column, for instance, or it's greater than this column, notice that we have a warning. I won't be able to sort by calculated columns that use local or related record fields. And I'm using a related record field because I'm using a different table, but that's a small price to pay. So if that's the case, then I want this to be reviewed within 14 days, and if that doesn't happen, then I want it to be reviewed within 28 days. So you can see, you can get fairly complicated and calculated fields if you want,as long as it involves dates. Yes, you can also get calculated fields that support text choice. Yes, no whole number, decimal number, or currency. But it's really date time that's got all of these calculations. Just think of the possibilities. Maybe I want an early review date if the expense value is greater than 500. OK, so let's add another condition. So I can say if the account name is a venture work sample or if the expense value is greater than or equal to 500, then I want this to happen as well. So I could say if the truth be told, both the account name is Adventure Works and the value is greater than 500, I can say if the account name is This or the value is 500, then give me the 14 days. But you can see we've got some teething troubles at the beginning. For instance, we had the wrong date of expense. We had date only, as opposed to user local,which meant we couldn't choose the add date function. So it's really good to set up these calculated fields right at the beginning. So now it is a new column right there. So let's have a look and see what we can do if we've got a number. So I'm not going to have this as an actual column, but just for demonstration purposes. So what I can do here largely is have two-columns multiplied, added, subtracted, and divided by each other. So, for instance, I could say I want the expense value and I want to multiply it by 1.2, for instance. So I can do that. So I will abandon that. I can multiply it by another column as well. But I don't have that huge range of formulas that I had previously. So it's multiply, divide, add, and subtract. So let's get rid of that delete column. And then finally, there are three formulas that we can use with text. And those formulas are consistent. So what concent allows us to do is join together two different columns, for instance, or two different strings, indeed more than two, if you want. And then we've also got left trim, or as they call it in this version, trim left and trim right. Now, this is where a bit of confusion might happen. Trim in other languages means getting rid of leading spaces. And so, for instance, if I already have hello, then trimleft would get me just hello without all the spaces. However, what trimming left and trimming right does is remove the first or last number of characters. So trim to the left. So suppose we had hello and I was having one,but trimming left would give me hello and trimming right would give me hell, for instance. So these are calculated columns. Now, there is just one big caveat that I do want to mention. How often is it updated? And the answer is that it is recalculated when a raw is saved. Okay? So let's just think about the implications of that. I'm typing the information. I type the date. I press tab or something to go into the next field. The raw is not saved. The review date is not updated because, yes, I've updated the field, but the row is not saved. It's only when I commit, it's only when I save the entire row that the calculated field gets updated. So don't think that just because I'm in anapp, I put in your data, your date, and any calculated fields will be instantly done. They won't. It will only be updated when the raw is saved. So these are calculated columns, so it uses columns from the current table or related parent tables you can use, and the majority of them are related to adding, subtracting, and diffing time periods. However, you can also use concat, trim left, and trim right, which removes characters, not spaces. And you can also multiply,add, subtract, and divide columns. So, for instance, you might want to add two columns together. That can be done using a calculated column. There are just a few limitations. The maximum number of chainedcalculators columns is five. So what does that mean? Well, a calculated column can refer to another calculator's column, which can refer to another calculatorcolumn, but it can only go five deep. Additionally, a calculated column in this table can refer to a calculator's column,for instance, in the accounts table. But if that also refers to a calculatedcolumn in a third table, then that's too many. It's only two tables. While calculated columns can refer to other calculator columns,there can't be a sort of cyclical chain. So you can't have a calculated column. I refer to calculated column two,which refers to calculated column one. That doesn't work, and sorting is quite often disabled or calculated columns. So it's not that you won't be able to sort by a calculated column.

ExamSnap's Microsoft PL-100 Practice Test Questions and Exam Dumps, study guide, and video training course are complicated in premium bundle. The Exam Updated are monitored by Industry Leading IT Trainers with over 15 years of experience, Microsoft PL-100 Exam Dumps and Practice Test Questions cover all the Exam Objectives to make sure you pass your exam easily.